migrate() crashes trying to create migration table inside outer transaction

[IlyaSemenov]

The new migrate() function chokes trying to create the missing migration table when running inside outer transaction. It only works if manually preceded with CREATE TABLE IF NOT EXISTS rake_migration:

import { createBaseTable, testTransaction } from "orchid-orm"
import { makeRakeDbConfig, migrate } from "orchid-orm/migrations"
import { rakeDb } from "orchid-orm/migrations/postgres-js"
import { orchidORM } from "orchid-orm/postgres-js"

export const BaseTable = createBaseTable()

const db = orchidORM({}, {})

const rakeDbConfig = makeRakeDbConfig({
  baseTable: BaseTable,
  basePath: import.meta.url,
  migrations: {
    "0001_initial": getMigrations as any,
  },
  migrationsTable: "rake_migration",
})

const { change } = rakeDb(rakeDbConfig)

async function getMigrations() {
  return [
    change(async (db) => {
      await db.createTable("user", t => ({
        id: t.serial().primaryKey(),
        name: t.varchar(),
      }))
    }),
  ]
}

async function main() {
  await testTransaction.start(db)

  // Uncomment this to work around the issue:
  // await db.$query`CREATE TABLE IF NOT EXISTS ${db.$qb.ref(`rake_migration`)} (version text NOT NULL, name text NOT NULL)`

  // This will crash:
  // PostgresError: current transaction is aborted, commands ignored until end of transaction block
  await migrate(db, rakeDbConfig)

  await testTransaction.close(db)
}

main()

Result:

810 |   }
811 | 
812 |   function ErrorResponse(x) {
813 |     if (query) {
814 |       (query.cursorFn || query.describeFirst) && write(Sync)
815 |       errorResponse = Errors.postgres(parseError(x))
                                   ^
PostgresError: current transaction is aborted, commands ignored until end of transaction block
 severity_local: "ERROR",
   severity: "ERROR",
       file: "postgres.c",
    routine: "exec_simple_query",
       code: "25P02"

      at ErrorResponse (/Users/is/test/node_modules/.bun/postgres@3.4.8/node_modules/postgres/src/connection.js:815:30)
      at handle (/Users/is/test/node_modules/.bun/postgres@3.4.8/node_modules/postgres/src/connection.js:765:5)
      at data (/Users/is/test/node_modules/.bun/postgres@3.4.8/node_modules/postgres/src/connection.js:457:5)
      at emit (node:events:95:22)
      at addChunk (internal:streams/readable:264:47)
      at readableAddChunkPushByteMode (internal:streams/readable:242:18)
      at data (node:net:280:52)

With explicit create table:

Migrating database undefined

(4.3ms) CREATE TABLE "user" (
  "id" serial PRIMARY KEY,
  "name" varchar NOT NULL
)
Migrated file:///Users/is/test/0001_initial

(by the way, I think it shouldn't be writing fake file names.)

With db.$transaction instead of testTransaction:

810 |   }
811 | 
812 |   function ErrorResponse(x) {
813 |     if (query) {
814 |       (query.cursorFn || query.describeFirst) && write(Sync)
815 |       errorResponse = Errors.postgres(parseError(x))
                                   ^
PostgresError: relation "rake_migration" does not exist
 severity_local: "ERROR",
   severity: "ERROR",
   position: "15",
       file: "parse_relation.c",
    routine: "parserOpenTable",
       code: "42P01"

      at ErrorResponse (/Users/is/test/node_modules/.bun/postgres@3.4.8/node_modules/postgres/src/connection.js:815:30)
      at handle (/Users/is/test/node_modules/.bun/postgres@3.4.8/node_modules/postgres/src/connection.js:765:5)
      at data (/Users/is/test/node_modules/.bun/postgres@3.4.8/node_modules/postgres/src/connection.js:457:5)
      at emit (node:events:95:22)
      at addChunk (internal:streams/readable:264:47)
      at readableAddChunkPushByteMode (internal:streams/readable:242:18)
      at data (node:net:280:52)