How to create postgres database and run migration when docker-compose up

Question

I'm setting up a simple backend that perform CRUD action with postgres database and want to create database and migration automatically when the docker-compose up runs.

I have already tried to add below code to Dockerfile or entrypoint.sh but none of them work.

createdb --host=localhost -p 5432 --username=postgres --no-password pg_development
createdb db:migrate

This code will work if run separately after docker is fully up

I have already tried to add - ./db-init:/docker-entrypoint-initdb.d to the volumes but that didn't work either

This is the Dockerfile

FROM node:10.12.0

# Create app directory
RUN mkdir -p /restify-pg
WORKDIR /restify-pg

EXPOSE 1337

ENTRYPOINT [ "./entrypoint.sh" ]

This is my docker-compose.yml

version: '3'
services:
  db:
    image: "postgres:11.2"
    ports:
      - "5432:5432"
    volumes:
      - ./pgData:/var/lib/psotgresql/data
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD:
      POSTGRES_DB: pg_development

  app:
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "3000:3000"
    volumes:
      - .:/restify-pg
    environment:
      DB_HOST: db

entrypoint.sh (in here I get createdb: command not found)

#!/bin/bash

cd app

createdb --host=localhost -p 5432 --username=postgres --no-password pg_development
sequelize db:migrate

npm install
npm run dev

I expect that when I run docker, the migration and the db creation would happen.

Solution

entrypoint.sh (in here I get createdb: command not found)

Running createdb in the nodejs container will not work because it is postgres specific command and it's not installed by default in the nodejs image.

If you specify POSTGRES_DB: pg_development env var on postgres container, the database will be created automatically when the container starts. So no need to run createdb anyway in entrypoint.sh that is mounted in the nodejs container.

In order to make sequelize db:migrate work you need to:

• add sequelize-cli to dependencies in package.json
• run npm install so it gets installed
• run npx sequelize db:migrate

Here is a proposal:

# docker-compose.yml

version: '3'
services:
  db:
    image: "postgres:11.2"
    ports:
      - "5432:5432"
    volumes:
      - ./pgData:/var/lib/psotgresql/data
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD:
      POSTGRES_DB: pg_development

  app:
    working_dir: /restify-pg
    entrypoint: ["/bin/bash", "./entrypoint.sh"]
    image: node:10.12.0
    ports:
      - "3000:3000"
    volumes:
      - .:/restify-pg
    environment:
      DB_HOST: db

# package.json

{
  ...
  "dependencies": {
    ...
    "pg": "^7.9.0",
    "pg-hstore": "^2.3.2",
    "sequelize": "^5.2.9",
    "sequelize-cli": "^5.4.0"
  }
}

# entrypoint.sh

npm install
npx sequelize db:migrate
npm run dev

Comments

[Ashkan]

npx sequelize db:migrate worked beautiflly but i still can't craete pg_development db and npx sequelize db:migrate also errors me that can't find pg_development

[Artem Titkov]

When you do docker-compose up please read through the logs to understand what's going on. You should see db_1 | CREATE DATABASE if the postgres container creates the database on initialization. Another thing to try would be adding npx sequelize db:create to entrypoint.sh (you might need to remove POSTGRES_DB: pg_development env var from db container).