Timescale Code Quick Start Guides are designed to help you integrate Timescale into your own programs. They use your favorite programming language to explain how to connect to a Timescale database, create and manage hypertables, and ingest and query data.
This quick start guide walks you through:
- Connecting to TimescaleDB
- Creating a relational table
- Creating a hypertable
- Inserting data
- Executing a query
Before you start, make sure you have:
- Installed TimescaleDB. For more information, see the installation documentation.
- Installed Node.js.
- Installed the Node.js package manager npm.
In this section, you create a connection to TimescaleDB with a common Node.js ORM (object relational mapper) called Sequelize.
At the command prompt, initialize a new Node.js app:
npm init -yThis creates a
package.json
file in your directory, which contains all of the dependencies for your project. It looks something like this:{"name": "node-sample","version": "1.0.0","description": "","main": "index.js","scripts": {"test": "echo \"Error: no test specified\" && exit 1"},"keywords": [],"author": "","license": "ISC"}Install Express.js:
npm install expressCreate a simple web page to check the connection. Create a new file called
index.js
, with this content:const express = require('express')const app = express()const port = 3000;app.use(express.json());app.get('/', (req, res) => res.send('Hello World!'))app.listen(port, () => console.log(`Example app listening at http://localhost:${port}`))Test your connection by starting the application:
node index.jsIn your web browser, navigate to
http://localhost:3000
. If the connection is successful, it shows "Hello World!"Add Sequelize to your project:
npm install sequelize sequelize-cli pg pg-hstoreLocate your TimescaleDB credentials and use them to compose a connection string for Sequelize.
You'll need:
- password
- username
- host URL
- port
- database name
Compose your connection string variable, using this format:
'postgres://user:pass@example.com:5432/dbname'Open the
index.js
file you created. Require Sequelize in the application, and declare the connection string:const Sequelize = require('sequelize')const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname',{dialect: 'postgres',protocol: 'postgres',dialectOptions: {ssl: {require: true,rejectUnauthorized: false}}})Make sure you add the SSL settings in the
dialectOptions
sections. You can't connect to TimescaleDB using SSL without them.You can test the connection by adding these lines to
index.js
after theapp.get
statement:sequelize.authenticate().then(() => {console.log('Connection has been established successfully.');}).catch(err => {console.error('Unable to connect to the database:', err);});Start the application on the command line:
node index.jsIf the connection is successful, you'll get output like this:
Example app listening at http://localhost:3000Executing (default): SELECT 1+1 AS resultConnection has been established successfully.
In this section, you create a relational table called page_loads
.
Use the Sequelize command line tool to create a table and model called
page_loads
:npx sequelize model:generate --name page_loads \--attributes userAgent:string,time:dateThe output looks similar to this:
Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]New model was created at <PATH>.New migration was created at <PATH>.Edit the migration file so that it sets up a migration key:
'use strict';module.exports = {up: async (queryInterface, Sequelize) => {await queryInterface.createTable('page_loads', {userAgent: {primaryKey: true,type: Sequelize.STRING},time: {primaryKey: true,type: Sequelize.DATE}});},down: async (queryInterface, Sequelize) => {await queryInterface.dropTable('page_loads');}};Migrate the change and make sure that it is reflected in the database:
npx sequelize db:migrateThe output looks similar to this:
Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]Loaded configuration file "config/config.json".Using environment "development".== 20200528195725-create-page-loads: migrating ========= 20200528195725-create-page-loads: migrated (0.443s)Create the
PageLoads
model in your code. In theindex.js
file, above theapp.use
statement, add these lines:let PageLoads = sequelize.define('page_loads', {userAgent: {type: Sequelize.STRING, primaryKey: true },time: {type: Sequelize.DATE, primaryKey: true }}, { timestamps: false });Instantiate a
PageLoads
object and save it to the database.
When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable.
Create a migration to modify the
page_loads
relational table, and change it to a hypertable by first running the following command:npx sequelize migration:generate --name add_hypertableThe output looks similar to this:
Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]migrations folder at <PATH> already exists.New migration was created at <PATH>/20200601202912-add_hypertable.js .In the
migrations
folder, there is now a new file. Open the file, and add this content:'use strict';module.exports = {up: (queryInterface, Sequelize) => {return queryInterface.sequelize.query("SELECT create_hypertable('page_loads', by_range('time'));");},down: (queryInterface, Sequelize) => {}};Note
The
by_range
dimension builder is an addition to TimescaleDB 2.13.At the command prompt, run the migration command:
npx sequelize db:migrateThe output looks similar to this:
Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]Loaded configuration file "config/config.json".Using environment "development".== 20200601202912-add_hypertable: migrating ========= 20200601202912-add_hypertable: migrated (0.426s)
This section covers how to insert data into your hypertables.
In the
index.js
file, modify the/
route to get theuser-agent
from the request object (req
) and the current timestamp. Then, call thecreate
method onPageLoads
model, supplying the user agent and timestamp parameters. Thecreate
call executes anINSERT
on the database:app.get('/', async (req, res) => {// get the user agent and current timeconst userAgent = req.get('user-agent');const time = new Date().getTime();try {// insert the recordawait PageLoads.create({userAgent, time});// send responseres.send('Inserted!');} catch (e) {console.log('Error inserting data', e)}})
This section covers how to execute queries against your database. In this example, every time the page is reloaded, all information currently in the table is displayed.
Modify the
/
route in theindex.js
file to call the SequelizefindAll
function and retrieve all data from thepage_loads
table using thePageLoads
model:app.get('/', async (req, res) => {// get the user agent and current timeconst userAgent = req.get('user-agent');const time = new Date().getTime();try {// insert the recordawait PageLoads.create({userAgent, time});// now display everything in the tableconst messages = await PageLoads.findAll();res.send(messages);} catch (e) {console.log('Error inserting data', e)}})
Now, when you reload the page, you should see all of the rows currently in the
page_loads
table.
Keywords
Found an issue on this page?Report an issue or Edit this page in GitHub.