feature request: non-transactional migrations for concurrent index creation

[tgriesser]

👋 First just wanted to say this project looks awesome! Pretty close to what I wish Knex was if I started it now, with the affordances of modern TypeScript instead of 10 years ago when we were stuck in ES5 and Promises weren't even really an official language construct yet.

I started to play around with it a bit and couldn't find a way to do non-transactional migrations, though there's a chance I was missing something. This is necessary when running statements like CREATE INDEX CONCURRENTLY which cannot be run inside a transaction in PostgreSQL.

Knex does this with a config property that can be set in a migration file:

https://knexjs.org/guide/migrations.html#transactions-in-migrations

export const config = { transaction: false }

Wanted to see if this was something on the radar or if you'd be open to a similar API for configuring per-file migrations, or another approach that'd offer similar support.

[koskimas]

Hi, and thank you!

I've used knex for a long time and both of my OSS projects owe a lot of their success to knex.

with the affordances of modern TypeScript instead of 10 years ago when we were stuck in ES5 and Promises weren't even really an official language construct yet.

Yeah, objection suffers from the same problem. It was also designed before typescript and good typescript support would mean almost a full rewrite.

About the issue itself: seems like a good (and necessary) addition.

[antogyn]

Hey 👋! I'm just starting out with Kysely, and this is a blocker for me. In my case, indexes sometimes need to be created concurrently in some production environments, so Kysely can't be used for migrations - at least not for creating these specific indexes.

To expand on the original post, Knex also has a global option "disableTransactions".

[reidswan]

We also have the use case of needing indexes to be created concurrently in production.

I'd be happy to pitch in with a PR for this if need be. Not sure what the process for deciding on an interface for this is though; but an idea is to add an enableTransactionalDdl option on the PostgresDialect and PostgresAdapter, and then the supportsTransactionalDdl method can just return that value?

[igalklebanov]

Hey @reidswan 👋

Thanks for offering to help! 🙏

I'm afraid that an option on a dialect is not the way to tackle this. However, it is a reasonable temporary workaround for anyone who needs to run migrations without a transaction using a dialect that otherwise will force a transaction.

PR's welcome to add disableTransactions as a Migrator option. Wanna be assigned to this?

[reidswan]

Hi @igalklebanov

That sounds good. Yes, please assign this to me 🙏

[igalklebanov]

@reidswan if you have any questions, swing by the Discord "contributing" channel or DM me.