Setup PostgreSQL database and API for Node.js

In this short tutorial we will setup a Node.js application with a connection to a PostgreSQL database and provide data via API endpoint. It's also part of the series "How To: Smart Home setup to track indoor temperatures and humidity with sensors, Raspberry Pi, MQTT, Node.js, Vue.js and Chart.js"

At first, follow the official online documentation on how to install a PostgreSQL database system on your OS. On a Linux machine, you might do something like this:

sudo apt-get install postgresql postgresql-contrib

This installation comes with _psql, _a terminal-based front-end to PostgreSQL. Access to the (still empty) database system is provided with: sudo -u postgres psql Create a new database with the name "tshtest", list all available databases and connect to "tsh":

postgres=# \list
postgres=# \c tsh

From here you could create tables to store data, but it is also possible to do that from our Node.js application later. So here we go: Our Node.js application won't do anything more than handle our database requests from a (non-existing) frontend. You can test all API calls with a client like Postman or even just your browser. The next code snippet provides our basic Node.js (with Express.js) boilerplate already including the node-postgres library, the propably best client for Node.js. Because we are working on a web application which makes frequent queries we use a connection pool. The easiest and by far most common way to use node-postgres is through a connection pool.

const express = require('express');
const app = express();
const uuid = require('uuid');
const Pool = require('pg').Pool;
const port = 3000;
app.listen(port, () => console.log('App listening on port ', port));

The connection pool is triggered with the following code snippet. This code also checks on every server start, if the table "posts" is already existing. If not, it will be created. Right after that we will insert a first example post. If the SQL syntax is new to you, you can check out this great tutorial to know what's going on here.

let pool;
try {
    pool = new Pool({
      user: 'postgres',
      host: 'localhost',
      database: 'tshtest',
      password: 'password',
      port: 5432,
    })
    pool.query("CREATE TABLE IF NOT EXISTS posts (id VARCHAR(100) PRIMARY KEY, room INT, temp VARCHAR(30), humidity VARCHAR(30), time VARCHAR(100))");
    pool.query('INSERT INTO posts (id, room, temp, humidity, time) VALUES ($1, $2, $3, $4, $5)', [uuid.v1(), 1, 30, 40, 2019-04-20], (error, results) => {
        if (error) {
          throw error
        } else {
          console.log(results);
        }
    })
} catch (e) {
    console.log(e)
}

It's time to open up a new endpoint on /getData, which will fetch all posts from the table "posts" in the database "tshtest".

app.get('/getData', (req, res) => {
    pool.query('SELECT * FROM posts', (error, results) => {
      if (error) {
        throw error
      }
      res.status(200).json(results.rows)
    })
});

Just for fun I add another endpoint /deleteData, which will delete all data from the database if called (be careful with that one ;)).

app.get('/deleteData', (req, res) => {
    try {
      pool.query("TRUNCATE TABLE posts");
      res.end('Deleted data!');
    } catch (e) {
      res.send(e);
      console.error(e);
    }
});

Start you web app with node server.js and check the endpoint http://localhost:3000/getData, which works also just right out of every browser and displays all database entries in JSON format.