Mastodon hachyterm.io

Docker Postgres Knex Setup

Project Structure Here’s the project structure for my application. Adjust to your needs. I used the Express application generator to …

Last modified:

Project Structure

Here’s the project structure for my application. Adjust to your needs. I used the Express application generator to scaffold the program.

.
├── docker-compose.yml
├── Dockerfile
├── healthcheck.js
├── LICENSE
├── node_app
│   ├── app.js
│   ├── bin
│   │   └── www
│   ├── db
│   │   ├── Dockerfile
│   │   ├── knex.js
│   │   ├── migrations
│   │   └── seeds
│   ├── knexfile.js
│   ├── node_modules
│   ├── package.json
│   ├── pnpm-lock.yaml
│   ├── public
│   ├── routes
│   └── views
└── README.md

Dockerfile & docker-compose

The Dockerfile must have the Postgres connection string as a build argument.

See Docker ARG, ENV and .env - a Complete Guide for more information on ARG, ENV and Docker.

Example excerpt from Dockerfile:

# set database connection string
ARG DATABASE_URL
ARG DATABASE_URL_TEST
ENV DATABASE_URL $DATABASE_URL
ENV DATABASE_URL_TEST $DATABASE_URL_TEST

Example docker-compose.yml:

version: '2.4'

services:
  node_app:
    build:
      context: .
      args:
        - NODE_ENV=development
        - DATABASE_URL=postgresql://postgres:postgres@db:5432/mocha_chai_tv_shows
        - DATABASE_URL_TEST=postgresql://postgres:postgres@db:5432/mocha_chai_tv_shows_test
    command: ../node_modules/.bin/nodemon --inspect=0.0.0.0:9229 ./bin/www
    ports:
      - '80:3000'
      - '9229:9229'
      - '9230:9230'
    volumes:
      - ./node_app:/opt/node_app/app:delegated
      - ./node_app/package.json:/opt/node_app/package.json
      - ./node_app/pnpm-lock.yaml:/opt/node_app/pnpm-lock.yaml
      - notused:/opt/node_app/app/node_modules
    depends_on:
      db:
        condition: service_healthy
    healthcheck:
      disable: true

volumes:
  notused:

Install Knex.js & Postgres

We also use docker-compose.yaml to create a container for the Postgres database. Let’s call the service db:

db:
    build:
      context: ./node_app/db
      dockerfile: Dockerfile
    expose:
      - 5432
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 10s
      start_period: 10s
      timeout: 4s
      retries: 3

The database folder is located inside the node application: node_app/db.
Here’s the Dockerfile for the Postgres database:

# node_app/db/Dockerfile

# pull official base image
FROM postgres:12.2-alpine

# run create.sql on init
ADD create.sql /docker-entrypoint-initdb.d

create.sql spins up the databases for development, testing, production:

# node_app/db/create.sql

CREATE DATABASE mocha_chai_tv_shows;
CREATE DATABASE mocha_chai_tv_shows_test;
CREATE DATABASE mocha_chai_tv_shows_prod;

The Postgres connection string inside the docker-compose.yaml reflects this:

# build args for the node app
- DATABASE_URL=postgresql://postgres:postgres@db:5432/mocha_chai_tv_shows
- DATABASE_URL_TEST=postgresql://postgres:postgres@db:5432/mocha_chai_tv_shows_test

Your Node application must have Knex.js and the postgres driver.

For example:

docker-compose exec node_app npm install -g knex
docker-compose exec node_app npm install --save knex
docker-compose exec node_app npm install --save pg

Initialization

Initialize knex:

docker-compose exec node_app knex init
> Created ./knexfile.js

Now we can adjust the configuration to our needs:

module.exports = {
  test: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
    migrations: {
      directory: __dirname + '/db/migrations',
    },
    seeds: {
      directory: __dirname + '/db/seeds/test',
    },
  },
  development: {
    client: 'pg',
    connection: process.env.DATABASE_URL_TEST,
    migrations: {
      directory: __dirname + '/db/migrations',
    },
    seeds: {
      directory: __dirname + '/db/seeds/development',
    },
  },
  production: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
    migrations: {
      directory: __dirname + '/db/migrations',
    },
    seeds: {
      directory: __dirname + '/db/seeds/production',
    },
  },
}

Migrations

docker-compose exec knex migrate:make <example>

up and down functions: You’ll find a new file inside the migrations folder.

exports.up = function(knex, Promise) {
  let createQuery = `CREATE TABLE <examples>(
    id SERIAL PRIMARY KEY NOT NULL,
    message TEXT,
    created_at TIMESTAMP
  )`
  return knex.raw(createQuery)
}

exports.down = function(knex, Promise) {
  let dropQuery = `DROP TABLE <examples>`
  return knex.raw(dropQuery)
}

Run migrations:

docker-compose exec node_app knex:migrate --env development
docker-compose exec node_app knex:migrate --env test

Seeds

docker-compose exec node_app knex seed:make <example> --env development
docker-compose exec node_app knex seed:make <example> --env test

For example:

docker-compose exec node_app knex seed:make shows_seed --env development
docker-compose exec node_app knex seed:make shows_seed --env test

This will create new folders called seeds/development and seeds/test which you can modify.

In my repository, the files are node_app/seeds/development/show-seed.js and node_app/seeds/tet/show-seed.js

Then run docker-compose exec node_app seed:run --env development (and --env test).

Glue Everything Together

Create knex.js inside the database folder (node_app/db/knex.js):

const knex = require('knex')
const knexfile = require('../knexfile.js')

const environment = process.env.NODE_ENV || 'development'

const config = knexfile[environment]

module.exports = knex(config)

Example GitHub Repository

See GitHub.

Further Reading