Database Migration System#

Better Auth's migration system generates and applies incremental database schema changes — new tables, new columns, and new indexes — through getMigrations(config) in packages/better-auth/src/db/get-migration.ts. This function is the single entry point used by both the better-auth migrate CLI command (direct execution) and the generate command (SQL file output) via the thin wrapper generateKyselySchema in packages/cli/src/generators/kysely.ts.

The migration system is Kysely-only. Other adapters (Prisma, Drizzle) use dedicated schema generators. If a non-Kysely adapter is detected, getMigrations logs an error and exits 1.

Pipeline Overview#

getSchema(config)
      │
      ▼
db.introspection.getTables() ←── live DB state
      │
      ▼
  Diff tables/columns
      │
      ├── toBeCreated → CREATE TABLE builders
      └── toBeAdded → ALTER TABLE builders
                             │
                             ▼
                      deferredIndexes → CREATE INDEX builders
                             │
                             ▼
                      compileMigrations() / runMigrations()

1. Schema discovery — getSchema(config) calls getAuthTables(config) from @better-auth/core/db to collect the union of all core and plugin table definitions.

2. Table diffing — db.introspection.getTables() introspects the live DB; tables are split into toBeCreated (missing) and toBeAdded (present but with missing columns) 2.

3. CREATE TABLE generation — For each toBeCreated entry, Kysely's schema.createTable() is called. Columns receive NOT NULL, foreign-key references, and — when field.unique is set — an inline .unique() constraint 3.

4. ALTER TABLE generation — For each toBeAdded entry, schema.alterTable().addColumn() is called with the same column-level constraints 4.

5. Deferred indexes — CREATE INDEX / CREATE UNIQUE INDEX builders are collected in deferredIndexes and appended to the migration list after all table/column statements. This guarantees all referenced tables and columns exist before any index is created 5 6.

6. SQL compilation — compileMigrations() calls .compile().sql on every builder and joins them with ;\n\n 7.

Type Mapping#

getType(field, fieldName) translates DBFieldAttribute.type to a dialect-specific Kysely ColumnDataType. Key rules:

• MySQL string fields become varchar(255) when unique, sortable, or index is set; otherwise text 8.
• date fields map to timestamptz (Postgres), timestamp(3) (MySQL), date (SQLite), datetime2(3) (MSSQL) 9.
• id / foreignKeyId fields honour advanced.database.generateId to emit uuid, integer GENERATED BY DEFAULT AS IDENTITY (Postgres), or varchar(36) 10.
• PostgreSQL migrations additionally filter tables to the active search_path schema 11.

Compound / Multi-Column Indexes#

Introduced in PR #10402, tables can now declare named multi-column indexes in an indexes array alongside fields:

schema: {
  compoundIdentity: {
    fields: { issuer: { type: "string" }, providerSubject: { type: "string" } },
    indexes: [
      { fields: ["issuer", "providerSubject"], name: "compound_identity_uidx", unique: true }
    ]
  }
}

Each entry conforms to DBTableIndex (in packages/core/src/db/type.ts):

Validation rules 12:

• json, string[], number[] fields cannot be indexed.
• Unique indexes require all constituent fields to be non-nullable.
• MySQL/SQL Server deployments receive a blocking error (with remediation) if any VARCHAR column in the index exceeds dialect byte limits (3 072 bytes MySQL, 1 700 bytes SQL Server).

For MongoDB, the adapter creates indexes lazily before the first write that enforces them, using a per-collection deduplication cache 12.

Known Issue: Redundant Unique Indexes (CREATE TABLE path)#

When a field carries both unique: true and index: true, the Kysely CREATE TABLE path currently emits two redundant uniqueness mechanisms 13:

1. Inline column constraint — .unique() at line 526–528 (unconditional when field.unique is true)
2. Deferred unique index — CREATE UNIQUE INDEX pushed at lines 543–551 (triggered when field.index && field.unique)

A concrete trigger is the built-in organization.slug field, which carries { unique: true, index: true } 13. The ALTER TABLE path guards against this correctly — it only pushes a deferred index via the field.index check at lines 438–447 and does not redundantly set .unique() when field.index is true. The Drizzle generator received the equivalent CREATE TABLE fix in PR #10333. As of this writing, the CREATE TABLE path in get-migration.ts has not received the same guard — the minimal fix is if (field.unique && !field.index) at line 526.

Key Files#