Kysely•3w ago

Migrator transaction (postgresql dialect)

Hello everyone, will the code below ensure all migrations are in a single transaction? if kysely migrations don't support AsyncLocalStorage, does it make sense to use unmanaged transaction and put BEGIN before and COMMIT at the end?

    await db.transaction().execute(async () => {
        let results: any[], error: any;
        if (migrationDirection === 'down') {
            ({ error, results } = await migrator.migrateDown());
        } else if (migrationDirection === 'up') {
            ({ error, results } = await migrator.migrateUp());
        } else if (migrationDirection === 'to') {
            if (!migrationName) {
                throw new Error(
                    'Migration name must be provided when using "to" direction.',
                );
            }
            ({ error, results } = await migrator.migrateTo(migrationName));
        } else {
            ({ error, results } = await migrator.migrateToLatest());
        }
    });

    await db.transaction().execute(async () => {
        let results: any[], error: any;
        if (migrationDirection === 'down') {
            ({ error, results } = await migrator.migrateDown());
        } else if (migrationDirection === 'up') {
            ({ error, results } = await migrator.migrateUp());
        } else if (migrationDirection === 'to') {
            if (!migrationName) {
                throw new Error(
                    'Migration name must be provided when using "to" direction.',
                );
            }
            ({ error, results } = await migrator.migrateTo(migrationName));
        } else {
            ({ error, results } = await migrator.migrateToLatest());
        }
    });

Solution:

When you run migrateToLatest() all migrations are run in a single transaction. They either all succeed or none of them succeed. Also when you run migrateTo the same happens. The only case where you get individual transactions is when you run the migrations in multiple calls. You can't use migrateTo or migrateToLatest()?...

Jump to solution

7 Replies

koskimas•3w ago

No you discard the transaction. It has no effect whatsoever here, except that it holds one connection. We don't use AsyncLocalStorage. We used to, but it has nasty corner cases, where it doesn't work as you'd expect, which would lead to catastrophic results (wrong or no transaction used for some queries). For example, if you cache some promise globally and then await it. After awaiting the global promise, you enter its "thread" and get its local storage entries. Caching promises like that is very common. Consider loading some config from a local file for example.

mike•3w ago

ok. what is the kysely_migration_lock table for?

koskimas•3w ago

It has a single row that can be used as a lock by dialects that don't have explicit locks (like Postgres advisory lock). The lock is used to make sure only one migrator is running migrations at a time

mike•3w ago

I see. Thank you From the info it looks like we have to solve potential upgrade failure scenario in advanced devops flows rather than with simple successful migration transaction... correct?

Solution

koskimas•3w ago

Also see this issue https://github.com/kysely-org/kysely/issues/1154

mike•3w ago

Oh, so all the migrations are running in a one unique transaction...! From our previous discussion (few months ago) I understood each migration run in it's own migration. If all are in one transaction that's everything I need. 😍

Gaming

Programming

Migrator transaction (postgresql dialect)