In this post we will look at creating tables and querying data from them in Node.js.

Originally published at https://fek.io.

This is part two of a three part series on using Postgres DB with Node.js.

Read part one here

In the first part of this series I wrote about how to get an instance of Postgres or TimescaleDB running and communicating with Node.js. In this post we will describe how tables and indexes are used to store and query data. We will also cover DDL, DML and DQL SQL statements.

Tables and Indexes

Relational database software is composed of specific database objects such as tables and indexes. Some systems also have objects like sequences. This will depend on the vendor.

Tables are used to store your raw data. Tables are composed of columns and rows. You will use columns to store individual fields in your table, while rows are used to store entire records.

Defining Tables

Creating tables is fairly simple. This can be done with a CREATE TABLE statement. This kind of query is considered part of the Data Definition Language or DDL for short.

Lets’ say we wanted to create a table for storing persons. We would probably need to have columns for the person’s name, the date the record was created and some sort of record identifier or id. We could create a table like this with the following statement in Postgres;

CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    firstname TEXT NOT NULL,
    lastname TEXT NOT NULL,
    createdate TIMESTAMP DEFAULT NOW() NOT NULL,
    active BOOLEAN DEFAULT TRUE NOT NULL
);

#timescaledb #nodejs #javascript #postgres