Migration locks for TypeORM

March 13, 2022

Schema migrations is a must-have functionality for any DB framework.

TypeORM provides decent utilities for dealing with migrations, however having a decade of experience in Ruby on Rails I got really spoiled and take some features for granted.

One of these features is locking a database while a migration going on so 2 processes running concurrently don't step on each other's toes. This is also important when you run migrations in Kubernetes before launching your app.

I was really surprised to find out that this basic feature is not supported, so decided to implement it on my own.

Implementation

// typeormMigrationUtils.ts
import { Connection, createConnection } from 'typeorm'
import config from '../ormconfig'
import CRC32 from 'crc-32'

const MIGRATOR_SALT = 2053462845

async function withAdvisoryLock(
  connection: Connection,
  callback: () => Promise<void>
): Promise<boolean> {
  // generate a unique lock name, has to be an integer
  const lockName = CRC32.str(config.database as string) * MIGRATOR_SALT
  let lock = false
  try {
    // try to acquire a lock
    const [{ pg_try_advisory_lock: locked }]: [
      { pg_try_advisory_lock: boolean }
    ] = await connection.manager.query(
      `SELECT pg_try_advisory_lock(${lockName})`
    )
    lock = locked

    // if already locked, print a warning an exit
    if (!lock) {
      console.warn(`Failed to get advisory lock: ${lockName}`)
      return false
    }

    // execute our code inside the lock
    await callback()

    return true
  } finally {
    // if we acquired a lock, we need to unlock it
    if (lock) {
      const [{ pg_advisory_unlock: wasLocked }]: [
        { pg_advisory_unlock: boolean }
      ] = await connection.manager.query(
        `SELECT pg_advisory_unlock(${lockName})`
      )

      if (!wasLocked) {
        console.warn(`Advisory lock was not locked: ${lockName}`)
      }
    }
  }
}

export async function migrateDatabase() {
  const connection = await createConnection({ ...config, logging: true })
  await withAdvisoryLock(connection, async () => {
    await connection.runMigrations({
      transaction: 'all'
    })
  })
  await connection.close()
}

export async function syncDatabase() {
  const connection = await createConnection({ ...config, logging: true })
  await withAdvisoryLock(connection, async () => {
    await connection.synchronize()
  })
  await connection.close()
}

Usage

You can run them like this:

// package.json
  "scripts": {
    ...
    "db:migrate": "ts-node ./src/scripts/migrateDatabase.ts",
    "db:sync": "ts-node ./src/scripts/syncDatabase.ts",
    "db:migrate:prod": "node ./dist/src/scripts/migrateDatabase.js",
    "db:sync:prod": "node ./dist/src/scripts/syncDatabase.js",
    ...
  },

// migrateDatabase.ts
import { migrateDatabase } from './typeormMigrationUtils'

;(async () => {
  await migrateDatabase()
})()

// syncDatabase.ts
import { syncDatabase } from './typeormMigrationUtils'

;(async () => {
  await syncDatabase()
})()

Dmitry Ishkov