Feature Request: Support per-schema migration tables

[normanaranez]

Description:
Problem:
Currently, Kysely uses a single global migration table for all schemas. In multi-schema or multi-tenant setups, this makes it difficult to manage migrations independently for each schema.

Proposed Solution:
Allow specifying a schema for the migration table, for example via a migrationSchema option in defineConfig.

Example Usage:

import { PostgresDialect } from 'kysely'
import { defineConfig } from 'kysely-ctl'
import { Pool } from 'pg'

export default defineConfig({
  destroyOnExit: true,
  dialect: new PostgresDialect({
    pool: new Pool({
      host: 'localhost',
      database: 'recording',
      user: 'postgres',
      password: 'postgres',
      port: 5432,
    }),
  }),
  migrations: {
    migrationFolder: "migrations",
    migrationSchema: "tenant1", // <- proposed new option
  },
})

Benefits:

Enables independent migration tracking per schema.

Avoids conflicts when multiple schemas evolve differently.

Makes multi-tenant or modular database setups easier to manage.

Additional Context:
This is similar to how some ORMs allow schema-specific migration tables, which is very useful for projects with multiple tenants or schema-per-module setups.

[igalklebanov]

Hey 👋

Currently, Kysely uses a single global migration table for all schemas.

This is inaccurate. You can pass the migrator a table and schema name.

Allow specifying a schema for the migration table, for example via a migrationSchema option in defineConfig.

You can already achieve this niche use case.

Pass a migrator factory:

migrations: {
  migrator: (db) => new Migrator({
    db: db.withSchema(process.env.SCHEMA!), // if all changes happen in that single schema, otherwise pass `db` as-is.
    migrationTableSchema: process.env.SCHEMA,
    // ...
  })
}