Quick Start: Node and TimescaleDB

Goal

This quick start guide is designed to get the Node.js developer up and running with TimescaleDB as their database. In this tutorial, you’ll learn how to:

• Connect to TimescaleDB
• Create a relational table
• Generate a Hypertable
• Insert a batch of rows into your Timescale database
• Execute a query on your Timescale database

Prerequisites

To complete this tutorial, you will need a cursory knowledge of the Structured Query Language (SQL). The tutorial will walk you through each SQL command, but it will be helpful if you’ve seen SQL before.

To start, install TimescaleDB. Once your installation is complete, we can proceed to ingesting or creating sample data and finishing the tutorial.

Obviously, you will need to install Node and the Node Package Manager (npm) as well.

Connect Node to TimescaleDB

TimescaleDB is based on PostgreSQL and we can use common PostgreSQL tools to connect your Node app to the database. In this example, we will use a common Node.js Object Relational Mapper (ORM) called [Sequelize][sequelize-info].

Step 1: Create your Node app

Let’s initialize a new Node app. From your command line, type the following:

npm init -y

This will create a package.json file in your directory, which contains all of the depenencies for your project:

{
  "name": "node-sample",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}

Now, let’s install Express.js by running the following command:

npm install express

Finally, let’s create a simple web page to display a greeting. Open your code editor, and add the following to a file called index.js:

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}`))

You can test your simple application by running the following from your command line and using your browser to view http://localhost:3000:

node index.js

You should get a “Hello, World” greeting.

Step 2: Configure the TimescaleDB database using Sequelize

Locate your TimescaleDB credentials in order to connect to your TimescaleDB instance.

You’ll need the following credentials:

• password
• username
• host URL
• port
• database name

Now, let’s add Sequelize to our project by first installing it (and its command line interface) and the packages for PostgreSQL from the command line:

npm install sequelize sequelize-cli pg pg-hstore

Now let’s go back to our index.js file and require Sequelize in our application. You’ll need your TimescaleDB credentials in order to build the connection URL as well. Once you have that information, add the following to index.js, below the other const statements:

const Sequelize = require('sequelize')
const sequelize = new Sequelize('postgres://user:[email protected]:5432/dbname',
    {
        dialect: 'postgres',
        protocol: 'postgres',
        dialectOptions: {
            ssl: {
                require: true,
                rejectUnauthorized: false
            }
        }
    })

warning

Note the settings in dialectOptions. These are critical in connecting to a TimescaleDB instance via SSL.

We can test this connection by adding the following to index.js after the app.get statement:

sequelize.authenticate().then(() => {
    console.log('Connection has been established successfully.');
}).catch(err => {
    console.error('Unable to connect to the database:', err);
});

Once again, start the application on the command line:

node index.js

And you should get the following results:

Example app listening at http://localhost:3000
Executing (default): SELECT 1+1 AS result
Connection has been established successfully.

Create a relational table

Step 1: Add TimescaleDB to your Node configuration

Now that we have a successful connection to the defaultdb database, we can build out our first database and model.

Let’s initialize Sequelize and create the necessary configuration files for our project. From the command line, type the following:

npx sequelize init

This will create a config/config.json file in your project. We will need to modify it with the connection details we tested earlier. For the remainder of this application, we’ll use a database called node_test. Here’s a full example file. Again, note the dialectOptions.

{
  "development": {
    "username": "[tsdbadmin]",
    "password": "[your_password]",
    "database": "node_test",
    "host": "[your_host]",
    "port": "[your_port]",
    "dialect": "postgres",
    "protocol": "postgres",
    "dialectOptions": {
      "ssl": {
        "require": true,
        "rejectUnauthorized": false
      }
    }
  },
  "test": {
    "username": "tsdbadmin",
    "password": "your_password",
    "database": "node_test",
    "host": "your_host",
    "port": "your_port",
    "dialect": "postgres",
    "protocol": "postgres",
    "dialectOptions": {
      "ssl": {
        "require": true,
        "rejectUnauthorized": false
      }
    }
  },
  "production": {
    "username": "tsdbadmin",
    "password": "your_password",
    "database": "node_test",
    "host": "your_host",
    "port": "your_port",
    "dialect": "postgres",
    "protocol": "postgres",
    "dialectOptions": {
      "ssl": {
        "require": true,
        "rejectUnauthorized": false
      }
    }
}

Now you’re ready to create the node_test database. From the command line, type the following:

npx sequelize db:create node_test

You should get this result:

Loaded configuration file "config/config.json".
Using environment "development".
Database node_test created.

Step 2: Add the TimescaleDB extension to the database

TimescaleDB is delivered as a PostgreSQL extension. Some instances and versions of TimescaleDB already have the extension installed. Let’s make sure the extesion is installed if it’s not.

To start, create a database migration by running the following command:

npx sequelize migration:generate --name add_tsdb_extension

You will see a file that has the name add_tsdb_extension appended to it in your migrations folder. Let’s modify that file to look like this:

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.query("CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;");
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.query("DROP EXTENSION timescaledb;");
  }
};

Now run the migration command from the command-line:

npx sequelize db:migrate

You should get the following result:

Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]
Loaded configuration file "config/config.json".
Using environment "development".
== 20200601214455-add_tsdb_extension: migrating =======
== 20200601214455-add_tsdb_extension: migrated (0.414s)

You can test and see if the TimescaleDB extension is installed by connecting to your database using psql and running the \dx command. You should get a result like this:

List of installed extensions
    Name     | Version |   Schema   |                            Description                            
-------------+---------+------------+-------------------------------------------------------------------
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb | 1.7.1   | public     | Enables scalable inserts and complex queries for time-series data
(2 rows)

Step 3: Create a table

Now let’s create a table and model called page_loads for our database using the Sequelize command line tool:

npx sequelize model:generate --name page_loads --attributes userAgent:string,time:date

You should get a result similar to this:

Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]
New model was created at [some path here] .
New migration was created at [some path here] .

And finally, let’s migrate our change and ensure that it is reflected in the database itself:

npx sequelize db:migrate

You should get a result that looks like 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)

Step 4: Create a model for the table

With the node_test database created and a page_loads table configured with a proper schema, we are ready to create the PageLoads model in our code. A model is an abstraction on the data stored in the table.

Above our app.use statement, add the following to index.js:

let PageLoads = sequelize.define('page_loads', {
    userAgent: Sequelize.STRING,
    time: Sequelize.DATE
});

We will now be able to instantiate a PageLoads object and save it to the database.

Generate hypertable

In TimescaleDB, the primary point of interaction with your data is a hypertable, the abstraction of a single continuous table across all space and time intervals, such that one can query it via standard SQL.

Virtually all user interactions with TimescaleDB are with hypertables. Creating tables and indexes, altering tables, inserting data, selecting data, etc. can (and should) all be executed on the hypertable.

A hypertable is defined by a standard schema with column names and types, with at least one column specifying a time value.

tip

The TimescaleDB documentation on schema management and indexing explains this in further detail.

Let’s create this migration to modify the page_loads table and create a hypertable by first running the following command:

npx sequelize migration:generate --name add_hypertable

You should get a result that looks like this:

Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]
migrations folder at [some_path] already exists.
New migration was created at [some_path]/]20200601202912-add_hypertable.js .

And there should now be a file in your migrations folder that we can modify to look like the following:

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.query("SELECT create_hypertable('page_loads', 'time');");
  },
  down: (queryInterface, Sequelize) => {
  }
};

Now run the migration command from the command-line:

npx sequelize db:migrate

You should get the following result:

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)

Insert rows into TimescaleDB

Now you have a working connection to your database, a table configured with the proper schema, and a hypertable created to more efficiently query data by time. Let’s add data to the table.

In the index.js file, we will modify the / route like so to first get the user-agent from the request object (req) and the current timestamp. Then, we will save call the create method on our model (PageLoads), supplying the user agent and timestamp parameters. The create call will execute an INSERT on the database:

app.get('/', async (req, res) => {
    // get the user agent and current time
    const userAgent = req.get('user-agent');
    const time = new Date().getTime();
    try {
        // insert the record
        await PageLoads.create({
            userAgent, time
        });
        // send response
        res.send('Inserted!');
    } catch (e) {
        console.log('Error inserting data', e)
    }
})

Execute a query

Each time the page is reloaded, we also want to display all information currently in the table.

To do this, we will once again modify the / route in our index.js file to call the Sequelize findAll function and retrieve all data from the page_loads table via the PageLoads model, like so:

app.get('/', async (req, res) => {
    // get the user agent and current time
    const userAgent = req.get('user-agent');
    const time = new Date().getTime();
    try {
        // insert the record
        await PageLoads.create({
            userAgent, time
        });
        // now display everything in the table
        const 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.

Next steps

Now that you’re able to connect, read, and write to a TimescaleDB instance from your Node application, be sure to check out these advanced TimescaleDB tutorials:

• Time Series Forecasting using TimescaleDB, R, Apache MADlib and Python
• Continuous Aggregates
• Try Other Sample Datasets
• Migrate your own Data