Migration failure for postgresql comment after table create

I'm currently running migrations on local machine against one postgres container with two schemas, one for dev and one for integration tests. The migrations run fine against the dev schema, but does not run correctly for my test schema.

Would appreciate any help if this is user error 🙏

Here is how we've defined the migrator:

  const schema = process.env.DB_SCHEMA ?? "public";
  const db = new Kysely<DB>({
    log(event) {
      if (event.level === "query") {
        console.log(event.query.sql);
        console.log(event.query.parameters);
      }
    },
    dialect: new PostgresDialect({
      pool: new Pool({
        connectionString: process.env.DB_CONNECTION_STRING,
        ssl: process.env.DB_CA_CERT
          ? {
              rejectUnauthorized: true,
              ca: process.env.DB_CA_CERT,
            }
          : undefined,
      }),
    }),
  }).withSchema(schema);

  const migrator = new Migrator({
    db,
    migrationTableSchema: schema,
    provider: new FileMigrationProvider({
      fs,
      path,
      // This needs to be an absolute path.
      migrationFolder: path.join(__dirname, "."),
    }),
  });

I have a migration file that creates a table and then adds comments using raw SQL.

  await db.schema
    .createTable("workout")
    .addColumn("id", "uuid", (col) => col.primaryKey())
    .addColumn("name", "text")
    .addColumn("program_id", "uuid", (col) =>
      col.references("account_program.id").onDelete("cascade"),
    )
    .addColumn("start_date", "timestamptz")
    .$call(withAuditColumns)
    .execute();
  await sql`COMMENT ON TABLE workout IS 'A program created for a client'`.execute(
    db,
  );
  await sql`COMMENT ON COLUMN workout.name IS 'Name for program'`.execute(db);
  await sql`COMMENT ON COLUMN workout.program_id IS 'Program containing workout'`.execute(
    db,
  );

The migration fails on the last COMMENT ON stating the program_id does not exist.

failed to migrate
error: column "program_id" of relation "workout" does not exist
    at /Users/tday/repos/server/node_modules/pg/lib/client.js:526:17
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PostgresConnection.executeQuery (/Users/tday/repos/server/node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.js:72:28)
    at async PostgresConnection.connection.executeQuery (/Users/tday/repos/server/node_modules/kysely/dist/cjs/driver/runtime-driver.js:92:24)
    at async /Users/tday/repos/server/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:37:28
    at async SingleConnectionProvider.#run (/Users/tday/repos/server/node_modules/kysely/dist/cjs/driver/single-connection-provider.js:26:16)
    at async DefaultQueryExecutor.executeQuery (/Users/tday/repos/server/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:36:16)
    at async Object.up (/Users/tday/repos/server/src/platform/postgres/migrations/20240409_112555_program_design.ts:61:3)
    at async Migrator.#migrateUp (/Users/tday/repos/server/node_modules/kysely/dist/cjs/migration/migrator.js:479:17)
    at async run (/Users/tday/repos/server/node_modules/kysely/dist/cjs/migration/migrator.js:352:28)
    at PostgresConnection.executeQuery (/Users/tday/repos/server/node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.js:92:69)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PostgresConnection.connection.executeQuery (/Users/tday/repos/server/node_modules/kysely/dist/cjs/driver/runtime-driver.js:92:24)
    at async /Users/tday/repos/server/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:37:28
    at async SingleConnectionProvider.#run (/Users/tday/repos/server/node_modules/kysely/dist/cjs/driver/single-connection-provider.js:26:16)
    at async DefaultQueryExecutor.executeQuery (/Users/tday/repos/server/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:36:16)
    at async Object.up (/Users/tday/repos/server/src/platform/postgres/migrations/20240409_112555_program_design.ts:61:3)
    at async Migrator.#migrateUp (/Users/tday/repos/server/node_modules/kysely/dist/cjs/migration/migrator.js:479:17)
    at async run (/Users/tday/repos/server/node_modules/kysely/dist/cjs/migration/migrator.js:352:28)
    at async /Users/tday/repos/server/node_modules/kysely/dist/cjs/kysely.js:418:32 {
  length: 137,
  severity: 'ERROR',
  code: '42703',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'objectaddress.c',
  line: '1559',
  routine: 'get_object_address_attribute'
}

If I log queries, then I see the CREATE TABLE issued as expected, but a rollback where the COMMENT ON is attempted

create table "test"."workout" ("id" uuid primary key, "name" text, "program_id" uuid references "test"."account_program" ("id") on delete cascade, "start_date" timestamptz, "created_by" varchar not null, "created_at" timestamptz default CURRENT_TIMESTAMP not null, "updated_at" timestamptz default CURRENT_TIMESTAMP not null)
[]
COMMENT ON TABLE workout IS 'A program created for a client'
[]
COMMENT ON COLUMN workout.name IS 'Name for program'
[]
rollback

kysely-org / kysely

Migration failure for postgresql comment after table create #1069