Quick Start: Node, Prisma and TimescaleDB

Goal

This quick start guide is designed to get the Node.js developer up and running with TimescaleDB as their database.

Pre-requisites

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 Database Client for TypeScript and Node.js called Prisma Client.

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 Prisma

Now, let's add Prisma Client to our project by first installing it (and its command line interface) from the command line:

npm install -D prisma
npm install @prisma/client

With the CLI installed, you can initiate Prisma in your project which auto generated some project configuration:

npx prisma init

You should get output similar to the following:

✔ Your Prisma schema was created at prisma/schema.prisma.
  You can now open it in your favorite editor.

Next steps:
1. Set the DATABASE_URL in the .env file to point to your existing database. If your database has no tables yet, read https://pris.ly/d/getting-started
2. Set the provider of the datasource block in schema.prisma to match your database: postgresql, mysql or sqlite.
3. Run prisma db pull to turn your database schema into a Prisma data model.
4. Run prisma generate to install Prisma Client. You can then start querying your database.

More information in our documentation:
https://pris.ly/d/getting-started

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

Combine them into a database connection URI (or service connection URI): postgres://username:password@host_url:port/database_name?sslmode=require and modify the generated .env file so DATABASE_URL is set to that value.

Prisma Client currently does not let you generate a database client without a model defined, so add this to the generated prisma/schema.prisma file:

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
}

Then you can run npx prisma generate in your command line. The output should look like this:

Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma

✔ Generated Prisma Client (2.20.1) to .\node_modules\@prisma\client in 115ms
You can now start using Prisma Client in your code. Reference: https://pris.ly/d/client
```
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
```

As you are using plain Javascript in your project, add the following to the top of the index.js file:

const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()

We can test this connection by adding the following to index.js before the res.send() statement:

prisma.$connect().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
Connection has been established successfully.

Create a relational table

Step 1: Create a database and add the TimescaleDB extension

Until now, we worked with the default database name. As we want the database we work with to actually be called node_test, replace the default database name in your connection string in your .env file with node_test. This will be used later

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 prisma migrate dev --create-only

You will see a folder that has the name tsdb appended to it in your prisma/migrations folder, that contains a migrations.sql file. Let's modify that file to look like this:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Now run the migration command from the command-line:

npx prisma migrate dev

When prompted, input tsdb. You should get the following result:

Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": PostgreSQL database "node_test", schema "public" at "tsdb-2aa5e70e-prisma-0904.a.timescaledb.io:28514"

The following migration(s) have been applied:

migrations/
  └─ 20210404203041_tsdb/
    └─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client (2.20.1) to .\node_modules\@prisma\client in 132ms

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 2: Create a table

Now let's create a table and model called page_loads for our database. Replace the placeholder User from your prisma/schema.prisma file with this model:

model page_loads {
  userAgent String
  time      DateTime

  @@unique([userAgent, time])
}

Let's migrate our change and ensure that it is reflected in the database itself:

npx prisma migrate dev

When prompted input page_loads. You should get a result that looks like this:

Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": PostgreSQL database "node_test", schema "public" at "tsdb-2aa5e70e-prisma-0904.a.timescaledb.io:28514"

√ Name of migration ... page_load
The following migration(s) have been created and applied from new schema changes:

migrations/
  └─ 20210404204150_page_loads/
    └─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client (2.20.1) to .\node_modules\@prisma\client in 118ms

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.

As Prisma Migrate does not allow the creation of empty migrations yet, create a new folder in prisma/migrations similar to the existing ones but ending with hypertable and add an migration.sql file with the following content:

SELECT create_hypertable('page_loads', 'time');");

Now run the migration command from the command-line:

npx prisma migrate dev

You should get the following result:

Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": PostgreSQL database "node_test", schema "public" at "tsdb-2aa5e70e-prisma-0904.a.timescaledb.io:28514"

The following migration(s) have been applied:

migrations/
  └─ 20210404204600_hypertable/
    └─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client (2.20.1) to .\node_modules\@prisma\client in 118ms

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 (page_loads), 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();

    try {
        // insert the record
        await prisma.page_loads.create({
            data: {
                userAgent: userAgent,
                time: 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 Prisma findMany function and retrieve all data from the page_loads table, 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 prisma.page_loads.findMany()
        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.

TODO

Use Timescale specific functionality via raw query

jerriclynsjohn/timescaledb-quickstart-node-prisma.md