Database migration & seeding tool / container

Dockerfile

FROM node:12-alpine AS builder

COPY . /app
WORKDIR /app

RUN yarn
# `build` script is already run as a `prepare` script hook

RUN rm -rf /app/node_modules

FROM node:12-alpine

COPY --from=builder /app /app

WORKDIR /app
RUN yarn --production

CMD ["yarn migrate && yarn seed"]

migrations--01-v0.1.0-initial.sql

CREATE TABLE users (
  id              UUID PRIMARY KEY,
  name            VARCHAR(64) NOT NULL,
  email           VARCHAR(255) UNIQUE NOT NULL,
  email_confirmed BOOLEAN NOT NULL DEFAULT FALSE,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  last_login_at   TIMESTAMP
);

/* ... */

package.json

{
  "name": "@project/models",
  "version": "0.0.0",
  "author": "Andy Wermke (https://github.com/andywer)",
  "private": true,
  "scripts": {
    "prebuild": "rimraf dist/",
    "build": "tsc",
    "prepare": "run-s build",
    "migrate": "node ./dist/migrate",
    "seed": "node ./dist/seed",
    "setup": "yarn migrate && yarn seed"
  },
  "dependencies": {
    "dotenv": "^8.2.0",
    "postgres-migrations": "^3.0.2",
    "uuid": "^3.3.3"
  },
  "devDependencies": {
    "nodemon": "^1.19.3",
    "npm-run-all": "^4.1.5",
    "rimraf": "^3.0.0",
    "tslint": "^5.20.0",
    "typescript": "^3.6.3"
  }
}

seed.development.sql

INSERT INTO users
  (id, email, email_confirmed, name)
VALUES
  ('c3281068-0268-4957-a488-7cc4f15eca1a', '[email protected]', true, 'John Doe')
ON CONFLICT DO NOTHING;

/* ... */

src--cli--config.ts

import { fail } from "assert"
import * as dotenv from "dotenv"
import * as path from "path"

dotenv.config({
  path: path.join(__dirname, "..", "..", ".env")
})
dotenv.config({
  path: path.join(__dirname, "..", "..", "..", "..", ".env")
})

const config = {
  database: process.env.PGDATABASE || fail("PGDATABASE not set."),
  host: process.env.PGHOST || fail("PGHOST not set."),
  password: process.env.PGPASSWORD || fail("PGPASSWORD not set."),
  port: process.env.PGPORT ? Number.parseInt(process.env.PGPORT, 10) : 5432,
  user: process.env.PGUSER || fail("PGUSER not set.")
}

export type Config = typeof config

export default config

src--cli--index.ts

import * as fs from "fs"
import { Pool } from "pg"
import * as Migrations from "postgres-migrations"
import config from "./config"

const sleep = (delay: number) => new Promise(resolve => setTimeout(resolve, delay))

async function connect() {
  let lastError: Error | undefined

  for (let attempt = 1, delay = 250; attempt <= 5; attempt++ , delay = delay * 2) {
    try {
      const database = new Pool(config)
      await database.connect()
      return database
    } catch (error) {
      console.error(`Database connection attempt #${attempt} failed:`, error)
      lastError = error
      await sleep(delay)
    }
  }

  throw lastError
}

export async function migrateDatabase(migrationsDirPath: string) {
  // Just for the sake of the connection re-attempt
  await connect()

  const logger = process.env.LOGGING ? console.log.bind(console) : () => undefined
  const migrations = await Migrations.migrate(config, migrationsDirPath, { logger })

  console.log("Database migration done.")
  console.log("Migrations run:")

  if (migrations.length === 0) {
    console.log("  (None)")
  }

  for (const migration of migrations) {
    console.log(`  - ${migration.name}`)
  }
}

export async function seedDatabase(seedFilePath: string) {
  const database = await connect()
  const sqlContent = fs.readFileSync(seedFilePath, "utf8")

  try {
    await database.query("BEGIN TRANSACTION")
    await database.query(sqlContent)
    await database.query("COMMIT")
    console.log("Database seeded.")
  } catch (error) {
    await database.query("ROLLBACK")
    throw error
  }
}

src--migrate.ts

import * as path from "path"
import { migrateDatabase } from "./cli/index"

migrateDatabase(path.join(__dirname, "..", "migrations"))
  .then(() => process.exit(0))
  .catch(error => {
    console.error(error)
    process.exit(1)
  })

src--seed.ts

import * as path from "path"
import { seedDatabase } from "./cli/index"

seedDatabase(path.join(__dirname, "../seed.development.sql"))
  .then(() => process.exit(0))
  .catch(error => {
    console.error(error)
    process.exit(1)
  })