CockroachDB Migration - Can't use pg_advisory_xact_lock()

[SpencerDuball]

Using version 0.23.4:

I am trying to use Kysely with CockroachDB, however when attempting to make a migration I am getting this error:

error: unknown function: pg_advisory_xact_lock(): function undefined

After doing some digging I noticed that CockroachDB has just stubbed functions related to getting a lock in postgres as noted here: cockroachdb/cockroach#13546. They have had this issue open for about 6 years so I doubt they will be fixing it anytime soon, I am not an expert on best practices when it comes to migrations but is it possible to not use a lock in order to make the migrations? I have used Prisma before with CockroachDB and not run into this issue, I wonder if there is a workaround for this or if it would be best to have an adapter specific for CockroachDB?

[igalklebanov]

Hey 👋

There are no plans at this point to expand the built-in dialects.

Take a look at this PR for reference.

We adjusted the dialect API to support @lbguilherme's implementation.

[SpencerDuball]

Ahh fair enough, I probably should have searched for CockroachDB as well before opening this issue. Spent most of my time looking for pg_advisory_xact_lock() and missed to search a little more for CockroachDB in issues too. Actually was just about to submit a PR as a couple line change got this working to only use connection instead of transaction with a lock, having read that linked PR I will leave a comment with the couple line change here in case this is helpful for anyone in the future seeing this issue. Thanks for the response!

File: dialect/postgres/postgres-adapter.js

"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
exports.PostgresAdapter = void 0;
const sql_js_1 = require("../../raw-builder/sql.js");
const dialect_adapter_base_js_1 = require("../dialect-adapter-base.js");
// Random id for our transaction lock.
const LOCK_ID = BigInt('3853314791062309107');
class PostgresAdapter extends dialect_adapter_base_js_1.DialectAdapterBase {
    get supportsTransactionalDdl() {
        return false;
        // CHANGE: Changed the line above from "true" to "false".
    }
    get supportsReturning() {
        return true;
    }
    async acquireMigrationLock(db) {
        // Acquire a transaction level advisory lock.
        // await (0, sql_js_1.sql) `select pg_advisory_xact_lock(${sql_js_1.sql.literal(LOCK_ID)})`.execute(db);
        // CHANGE: Commented out the line above.
    }
    async releaseMigrationLock() {
        // Nothing to do here. `pg_advisory_xact_lock` is automatically released at the
        // end of the transaction and since `supportsTransactionalDdl` true, we know
        // the `db` instance passed to acquireMigrationLock is actually a transaction.
    }
}
exports.PostgresAdapter = PostgresAdapter;

[roi007leaf]

@igalklebanov So in order to work with cockroach using kysely migrator what is needed to change in the code when creating a new db?

const db = new Kysely<Client>({ dialect: new PostgresDialect({ pool: new Pool({ host: 'localhost', password: '', user: 'root', port: 26257, database: 'roi' }) }) });

[SpencerDuball]

@roi007leaf What would need to be changed is the dialect. Currently you are attempting to use PostgresDialect but this will not work. The reason is CockroachDB does not support everything that Postgres does, one thing missing is database locks. The way the PostgresDialect from Kysely works is using this feature so you would need to create or use a different dialect.

TLDR; Either find a dialect someone has created a package for that can do these migrations, or create the package yourself (locally or as an NPM package). If you copy the distribution and then apply the fix outlined above this should work.

[roi007leaf]

@roi007leaf What would need to be changed is the dialect. Currently you are attempting to use PostgresDialect but this will not work. The reason is CockroachDB does not support everything that Postgres does, one thing missing is database locks. The way the PostgresDialect from Kysely works is using this feature so you would need to create or use a different dialect.

TLDR; Either find a dialect someone has created a package for that can do these migrations, or create the package yourself (locally or as an NPM package). If you copy the distribution and then apply the fix outlined above this should work.

Is it possible to write my own dialect without the need to copy the whole repo? Like do you have a small guide that will explain how to actually write my own dialect?

[SpencerDuball]

@roi007leaf Certainly you can, dialect was made for this purpose - there are examples of Kysely’s built in dialects and community created dialects here: https://www.kysely.dev/docs/dialects

Don’t think there is a guide per se, but just take a look at any of these repos (probably best to see Postgres dialect) and use this as reference. The only fix you need to make is outline in the code snippet above.