Nelson González – Nextialab

Assume the next versions:

$ node --version
v23.7.x
$ npm --version
10.9.x
$ podman --version
5.2.x

Create a Node project with npm:

$ npm init

This will create a new package.json file where to define tasks and dependencies. The dependencies needed at least are:

$ npm install body-parser dotenv express knex pg pug

A quick overview of each one:

body-parser: needed to transform the body payload into an object in req.body.
dotenv: needed to load environment information from a .env file such as the database password.
express: handles the web server with high level functions for methods and endpoints
knex: database abstraction to build SQL queries with code
pg: PostgreSQL driver for Node
pug: Template engine

And for tasks:

{
    "scripts": {
        "start": "node index.js",
        "migrate:make": "knex migrate:make",
        "migrate:latest": "knex migrate:latest"
    }
}

Where:

start: starts the web server, assuming our point of entry is the file index.js.
migrate:make: creates a new SQL migration file in the migrations folder.
migrate:latest: runs the migrations that have not been already applied to the database

A knexfile.json will be required to store the database connection details that will be used by knex, a basic implementation is:

require('dotenv').config();

module.exports = {
    client: 'pg',
    connection: {
        database: process.env.DBNAME,
        user: process.env.DBUSER,
        password: process.env.DBPASS
    },
    pool: {
        min: 2,
        max: 10
    },
    migrations: {
        tableName: 't_knex_migrations'
    }
};

This implementation will load the DBNAME, DBUSER and DBPASS information from the .env file with the dotenv module, and will use them to connect to the database. Also, it will use the table t_knex_migrations in the database to store the current migration state. This file will be used by both the knex migration script and the knex module during runtime.

The folder structure:

project
|-- migrations
|   |-- .
|-- services
|   |-- db.js
|-- .env
|-- .gitignore
|-- index.js
|-- knexfile.js
|-- package.json

The db.js file is just a nicer way to setup the database connection that can be reused by multiple components:

const knex = require('knex');

module.exports = knex(require('../knexfile'));

PostgreSQL

PostgreSQL can be either installed locally, run in a container, or deployed in PaaS. Personally, in Linux I prefer to have it installed locally and run it in a container otherwise. For the containerized option:

$ podman pull docker.io/postgres
$ podman run -d -p 5432:5432 -e POSTGRES_PASSWORD=yourdbpass --name pg_container postgres
$ podman stop pg_container

Author: Nelson González

Simple Express and Postgres project

PostgreSQL