How to setup postgres database and tables in node pg

Question

I want to share a Node repo, and ideally it should be ready to use after the usual yarn install && yarn start.

However pg can't connect neither SELECT to/from a database and table that don't exist yet.

It's a hassle but at the moment these previous steps are required:

1. Get postgres; docker run --name my-postgres -e POSTGRES_PASSWORD={MY_PASSWORD} -p 5432:5432 -d --rm postgres:13.0
2. Lauch psql; docker exec -it -u postgres my-postgres psql
3. CREATE DATABASE MY_DB
4. \connect MY_DB
5. paste the following SQL script to create tables

CREATE TABLE my_table (
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  field_1 VARCHAR ( 50 ) UNIQUE NOT NULL,
  field_2 INT NOT NULL,
);

Since I am already getting postgres from docker, is docker-componse the logical solution? I'm not too familiar with dockerfile, is there an alternative?

What are the best practises to initialize postgres database in node?

How bad an idea would it be calling a script on yarn start (or yarn setup). Any example of such a script would be appreciated.

Answer 1

Scripts

Well the right place to put your configuration is in start script. This script should run two commands from it to setup a server (if it's not) and to run it. Example:

// package.json
{
  "scripts": {
    "start": "yarn run setup && yarn run server",
    "server": "node app.js",
    "setup": "node setup.js"
  }
}

Docker

If you want to run external stateful services like db, then you need to store it's state somewhere. --rm option removes a machine and it's state. So to prevent it from deleting use -v argument and put your PG db's from guest to host machine. For postgres it might look like this:

docker run --rm -v "$PWD/data/pg:/var/lib/postgresql/data/" -p 5432 postgres 

But as you've told it's better to use docker-compose.

Docker Compose

Docker compose let you run several containers as one. It uses compose-files as a single point of configuration. With compose-files you can configure all software your app needs (db, redis, file storage, etc) and link app data. It also allows you to run your app in a container. This is how app with Postgres DB might be configured:

version: "3.8"
services:
  postgre:
    image: postgre
    ## Store persistent postgres data
    volumes:
      - ./data/pg:/var/lib/postgresql/data/
    environment:
      POSTGRES_USERNAME: admin
      POSTGRES_PASSWORD: '********'
  app:
    image: node
    ports:
      - "8080:8080"
    ## Mount current project to guest as /app
    volumes:
      - .:/app
    ## Set PWD to /app
    working_dir: /app
    command: yarn start

Thus you can run your app with docker-compose start and stop it with docker-compose stop commands.

To connect to postgres from node's app container use postgre (or the name you specified in the compose-file) as a hostname.

Tip

In case if your local Node.js version differs from one from container, it could be required to install files to node_modules/ from a container and put it onto host system. To do so run:

docker run --rm -v "$PWD:/app" -w /app node yarn install

It will install correct versions of node modules into the host machine.

Answer 2

First of all, I like to let the developers choose the way they want to setup their environment and in particular the database. Some prefer to start directly a postgres server, other to start a docker-compose or simply docker.

Now suppose you want to setup everything anyway. You have multiple options:

Launching the database

Using a docker

If you want to initialize everything from a docker, you can add an entry point in order to create the database. Create a folder init_script and a file in it:

./init_scripts/init_db.sh

#!/bin/bash

set -e
set -u

function create_user_and_database() {
  local database=$1
  echo "Creating database '$database' with user '$POSTGRES_USER'"
  psql -v ON_ERROR_STOP=1 \
    --username "$POSTGRES_USER" \
    --dbname=postgres <<-EOSQL
    CREATE DATABASE $database;
EOSQL
}


if [ -n "$POSTGRES_MY_DB" ]; then
  create_user_and_database $POSTGRES_MY_DB
  echo "Database $POSTGRES_MY_DB created"
fi

Inspired from this script which enables to create multiple postgres databases. Then you can simply add other scripts to create the tables.

Now you can launch the docker with

docker start my_postgres_container || docker run \
  --name my_postgres_container \
  --volume "$PWD/init_scripts:/docker-entrypoint-initdb.d/" \
  --env POSTGRES_USER=my_user \
  --env POSTGRES_PASSWORD=my_password \
  --env POSTGRES_MY_DB=my_database \
  --publish 5432:5432 \
  --rm \
  postgres:13

A volume is mounted to the folder init_scripts at /docker-entrypoint-initdb.d/, docker will look at it to init the database. This script could be executed from npm or yarn:

"scripts": {
  "setup_db": "docker start ...."
}

---

Contrary to some databases, you cannot launch an in memory postgres.

Instantiate the database (postgres already running)

From a nodejs script

Independently of the postgres system, you can create a new database from the master connection with a script.

function createDatabase() {
  const client = new Client(masterPostgresUri);
  const user = 'my_user';
  const database = 'my_database';
  await client.connect();
  try {
    await client.query(`CREATE USER ${user};`);
    await client.query(`CREATE DATABASE ${database};`);
    await client.query(`GRANT ALL PRIVILEGES ON DATABASE ${database} TO ${user};`);
  } catch (_err) {
    // The database might already exist, you may want to check before
  }
  client.end();
}

To create the tables, you could use the same script but use the new database connection uri instead.

This script could be launch on the setup as well.

From a migration tool

There are several tools to cope with setup and migration. If you use for example the ORM Sequelize, it could be a good choice to setup everything in migration scripts. Sequelize migrations

---

For me, there are no perfect solutions, even if you create a command to launch a proper configured docker, one can argue that it didn't install docker so you still have a dependency. Hence, I think let the developers choose the best solution to start the postgres is great. And concerning the table creation and migration, I thing a tool is appropriate because it's an issue you have to deal with in your production environment and it will handle it during the application's restart.