Allow disabling transactions for specific migrations

Hi there, love the library, this has been a game changer for our app! I came across an issue/limitation with the migration system and I wanted to see what your thoughts are on it.

Migrations run in a transaction which is great most of the time. However, there are some special cases where we need to be able to run a migration outside of a transaction.

The issue I'm experiencing is with the CONCURRENTLY option around index operations. I'm trying to run a migration like so:

export async function up(db: Kysely<unknown>): Promise<void> {
  await sql`DROP INDEX CONCURRENTLY idx_foo`.execute(db)
}

This fails with error: DROP INDEX CONCURRENTLY cannot run inside a transaction block. The postgres docs say:

Also, regular DROP INDEX commands can be performed within a transaction block, but DROP INDEX CONCURRENTLY cannot.

Would it be possible to add support for disabling transactions on a per migration basis? I've used Knex.js in the past and it solved this by exporting a config object:

exports.config = { transaction: false }

This probably ties into this issue https://github.com/kysely-org/kysely/issues/1154, since allowing disabling transactions per migration would require individual transactions per migration.

Are there any other workarounds in the meantime?

kysely-org / kysely

Allow disabling transactions for specific migrations #1173