Running migrations in single transaction

[jalorenz94]

Hi everyone,

first of all, thanks a lot for the library :) Is there any chance to run the pending migrations umzug.up() in a single transaction? Currently, i build some logic around it, with the drawback that i have to remove the processed migrations from the SequelizeMeta table when the transaction was roll backed.

[mmkal]

This can be done with the 'beforeCommand'/'afterCommand' events, but locking varies pretty widely between databases so it might be tricky to do via sequelize. @slonik/migrator is postgres-only, based on umzug and allows running all migrations in a single transaction this way.

[klondikemarlen]

Would it be possible to add an aroundCommand? Then with sequelize + cls you could do

const Sequelize = require('sequelize');
Sequelize.useCLS(namespace);

const sequelize = new Sequelize(....);


const migrator = new Umzug({
  context: sequelize.getQueryInterface(),
  aroundCommand: (command, runner, context) => {
    if (command === 'up' || command === 'down') {
      sequelize.transaction(() => {
        runner(context)
      })
    }
  },
})

See https://sequelize.org/docs/v6/other-topics/transactions/#with-cls-enabled

[klondikemarlen]

Or I guess with events that would be something like

const Sequelize = require('sequelize');
Sequelize.useCLS(namespace);

const sequelize = new Sequelize(....);

const migrator = new Umzug({
  context: sequelize.getQueryInterface(),
})

migrator.on("aroundCommand", ({ command, runner, context }) => {
  if (command === 'up' || command === 'down') {
    sequelize.transaction(() => {
      runner(context)
    })
  }
})

[klondikemarlen]

This would also work for knex or any other query context that supports transactions
e.g.

const knex = require('knex')({...});

const migrator = new Umzug({
  context: knex, // doesn't really matter since we are replacing the whole context in the around block
})

migrator.on("aroundCommand", ({ command, runner, context }) => {
  if (command === "up" || command === "down") {
    knex.transaction((transaction) => {
      runner(transaction)
    })
  }
})

[mmkal]

For anyone reading this, you may be able to extend the Umzug class's runCommand method for this, if you want to run in one big transaction:

class UmzugWithTransactions extends Umzug {
  async runCommand<T>(command: string, run: (params: ...) => Promise<T>) {
    return super.runCommand(command, async ({context: _ctx}) => {
      return knex.transaction(async transaction => {
        const context = {..._ctx, transaction}
        return run({context})
      })
    })
  }
}

[klondikemarlen]

Maybe its possible to do this already with the migration resolver? Something like

import { Sequelize } from "sequelize"
Sequelize.useCLS(namespace)

const sequelize = new Sequelize({
  // config options
})

const migrator = new Umzug({
  context: sequelize.getQueryInterface(),
  resolve: ({ name, path, context }) => {
    const migration = await import(path)
    return {
      name,
      up: () => {
        return context.sequelize.transaction(() => {
          return migration.up(context)
        })
      },
      down: () => {
        return context.sequelize.transaction(() => {
          return migration.down(context)
        })
      },
    }
  },
})

I'ma test this an report back ...

[klondikemarlen]

Ok. So I have something that works for Sequlize!

// src/db/sequelize-auto-transaction-resolver.ts
import { QueryInterface } from "sequelize"
import { MigrationParams } from "umzug"

export function sequelizeAutoTransactionResolver({
  name,
  path,
  context,
}: MigrationParams<QueryInterface>) {
  if (path === undefined) throw new Error("Path is undefined")

  return {
    name,
    up: async () => {
      const migration = await import(path)
      return context.sequelize.transaction(() => {
        return migration.up({ context })
      })
    },
    down: async () => {
      const migration = await import(path)
      return context.sequelize.transaction(() => {
        return migration.down({ context })
      })
    },
  }
}

Setup is

// /src/db/umzug.ts
import { Sequelize } from "sequelize"
import { createNamespace } from "cls-hooked"
import { Umzug, SequelizeStorage } from "umzug"

const namespace = createNamespace("sequelize-transaction-context")
Sequelize.useCLS(namespace)
const sequelize = new Sequelize({
  // config options
})

export const migrator = new Umzug({
  migrations: {
    glob: ["migrations/*.ts", { cwd: __dirname }], // or whatever your path glob is
    resolve: sequelizeAutoTransactionResolver,
  },
  context: sequelize.getQueryInterface(),
  storage: new SequelizeStorage({
    sequelize,
  }),
  logger: console,
})

export type Migration = typeof migrator._types.migration

Usage is:

// /src/db/migrations/2023.09.28T22.46.02.test-auto-transaction.ts
import { DataTypes } from "sequelize"

import type { Migration } from "src/db/umzug"

export const up: Migration = async ({ context: queryInterface }) => {
  await queryInterface.createTable("foo", {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      allowNull: false,
      autoIncrement: true,
    },
  })

  await queryInterface.sequelize.query(
    `
  UPDATE
    foo
  SET
    id = fail
  FROM
    foo
  `
  )

  await queryInterface.createTable("bar", {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      allowNull: false,
      autoIncrement: true,
    },
  })
}

export const down: Migration = async ({ context: queryInterface }) => {
  throw new Error("Not implemented")
}

Now all your migrations will run in a single transaction; and if anything fails the whole thing will get rolled back!

[klondikemarlen]

See icefoganalytics/elcc-data-management@d7ec1b1. Yay open source! I can actually share the code I write.