---
title: "Schema and migrations"
description: "DDL from table definitions, materializedView, effect-orm generate and applying migrations."
---

> Documentation Index
> Fetch the complete documentation index at: https://effect-orm.maple.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Schema and migrations

:::note
The examples on this page use ClickHouse and Postgres until the SQLite versions are written. The query builder is the same in every dialect: import `@maple-dev/effect-orm/sqlite` as `SQLite` instead, and use SQLite's column types and functions ([SQLite specifics](https://effect-orm.maple.dev/sqlite/specifics/overview/)).
:::

Every table is declared with its DDL: `table` from `/clickhouse`, `/postgres`, `/sqlite` or `/mysql` carries the
engine, keys, indexes, and defaults beside the columns the query builder reads. You can ignore
that and manage the database elsewhere, or let three opt-in entry points turn the definitions
into migrations:

| Entry                              | Runs where         | What it does                                                                  |
| ---------------------------------- | ------------------ | ----------------------------------------------------------------------------- |
| `@maple-dev/effect-orm/schema`     | anywhere, pure     | reads `table` / `materializedView` values: DDL rendering, snapshots, the diff |
| `@maple-dev/effect-orm/kit`        | Node or Bun        | `generate` and `check` over a migrations folder, `verifySchema`; the `effect-orm` command |
| `@maple-dev/effect-orm/migrate`    | anywhere Effect runs | applies migrations through a driver you provide, `status`, `verify`        |

Both ClickHouse and Postgres. The model follows drizzle-kit (a committed snapshot per migration,
an offline `generate`, data-loss confirmations by prompt or by hints). Snapshots, the branch
check and folder loading are shared; table definitions, the diff, the DDL, the runtime and
drift detection are per database, because the two differ where it matters: ClickHouse DDL is
not transactional and cannot change most things in place, and Postgres DDL is and can. The
sections below describe ClickHouse first; [Postgres](#postgres) covers what differs.
[SQLite](#sqlite) and [MySQL](#mysql) have `generate` only: another tool applies their migrations for now.

## Defining tables

`CH.table` returns a `Table`, so every query API accepts it. Columns are the usual column
types, or `CH.column(type, options)` for a default, a codec, or a comment. Keys, TTL, defaults,
and index expressions are SQL strings or DSL callbacks. `/schema` only reads these values: it
renders them, snapshots them, and diffs them.

```ts title="migrations-schema.ts"
import { Effect } from "effect"
import * as CH from "@maple-dev/effect-orm/clickhouse"
import * as S from "@maple-dev/effect-orm/schema"

export const Requests = CH.table("requests", {
	columns: {
		OrgId: CH.custom("LowCardinality(String)", CH.string.schema),
		Timestamp: CH.dateTime,
		Route: CH.string,
		Status: CH.column(CH.uint16, { default: 200 }),
	},
	engine: CH.engine.mergeTree(),
	orderBy: ["OrgId", "Route", "Timestamp"],
	partitionBy: "toDate(Timestamp)",
	ttl: CH.ttlAfterDays("toDate(Timestamp)", 30),
	indexes: [CH.index("idx_status", ($) => $.Status, "set(100)")],
	tenantColumn: "OrgId",
})

export const RoutesHourly = CH.table("routes_hourly", {
	columns: { OrgId: CH.string, Hour: CH.dateTime, Route: CH.string, Requests: CH.uint64 },
	engine: CH.engine.summingMergeTree(),
	orderBy: ["OrgId", "Hour", "Route"],
})

// The body is a DSL query. An output column the target lacks, or of another
// type, is a type error here rather than a failed insert later.
export const RoutesHourlyMv = CH.materializedView("routes_hourly_mv", {
	to: RoutesHourly,
	as: CH.from(Requests)
		.select(($) => ({ OrgId: $.OrgId, Hour: CH.toStartOfHour($.Timestamp), Route: $.Route, Requests: CH.count() }))
		.groupBy("OrgId", "Hour", "Route"),
})

export const ddl = Effect.map(S.entitiesOf([Requests, RoutesHourly, RoutesHourlyMv]), (entities) => S.renderSchema(entities))
```

A definition that cannot become DDL (a MergeTree without `orderBy`, a name that is not a plain
identifier, a view writing to a table outside the schema) never throws: it records the problem
on its `problems`, and `S.entitiesOf` fails with a `SchemaDefinitionError` listing every
problem in the schema. `CH.dateTime64` renders as `DateTime64`, which ClickHouse reads as
`DateTime64(3)`; declare another precision with `CH.custom`.

Write engines as the plain family. Replicated engines and `ON CLUSTER` are render options
(`{ replicated: {}, cluster: "main" }`), so one schema serves a single server, a cluster, and
ClickHouse Cloud.

A table declared with `external: true` (a system table, a table function, a table another tool
migrates) carries no DDL: `S.isSchemaObject` is false for it, so `generate` skips it and
`entitiesOf` does not take it. See
[External tables](https://effect-orm.maple.dev/sqlite/queries/tables-and-types/#external-tables).

## Generating migrations

Create `effect-orm.config.ts`:

```ts
import { defineConfig } from "@maple-dev/effect-orm/kit"

export default defineConfig({
	schema: "./src/schema.ts",
	out: "./migrations",
})
```

Then `effect-orm generate --name add_status` (with Bun, or Node with type stripping) imports the
schema modules, diffs them against the newest snapshot in `out`, and writes
`migrations/<YYYYMMDDHHMMSS>_add_status/` holding `migration.json` and `snapshot.json`. It never
connects to a database. The plan it prints labels each statement:

- `metadata`: a schema change with no data rewrite.
- `ingest gap`: a materialized view is dropped and recreated. Inserts in between are not
  materialized by it. A view's body is fixed at creation, so this is the only way to change one.
- `destructive`: a table or column is dropped.
- `backfill`: rows are copied into a table (see [Backfills](#backfills)). Never generated.

Drops need confirmation. In a terminal, `generate` asks. Without one, it exits with status 2,
writes nothing, and prints the hints to pass back:

```sh
effect-orm generate --hints '[{"type":"confirm_data_loss","kind":"column","entity":"requests.Route"}]'
```

Changes ClickHouse cannot make with `ALTER` (engine, sorting key, partition key, primary key,
column type) are reported and nothing is written. They need a table rebuild, which `generate`
does not write yet. Renames are not detected yet either: a rename reads as a drop plus an add,
and the drop asks for confirmation, so it never loses data silently.

`effect-orm generate --custom` writes an empty `migration.sql` for statements you write by hand
(separate them with a line holding `--> statement-breakpoint`). Its snapshot copies its parent's.

### Backfills

A table or view created with history to fill needs an `INSERT ... SELECT`, which `generate` cannot
infer. Add a `backfill` op by hand to the generated `migration.json`, after the ops it depends on:

```json
{
	"op": "backfill",
	"backfill": {
		"target": "totals",
		"columns": ["OrgId", "Name", "Count"],
		"from": "events",
		"timeColumn": "Timestamp",
		"select": "OrgId, Name, sum(Count)",
		"groupBy": "OrgId, Name",
		"windowDays": 1
	}
}
```

It runs as one statement per window of `windowDays` (default 1) on `timeColumn`, over the
source's `min` to `max` when the migration runs. Windows are aligned to the epoch, not to the
data, and each is journaled under its start date (`0.2026-10-01`). A resumed run skips the
windows it finished, even after TTL has moved the lower bound, and adds any the source has
grown into. Each window compares the raw column (`prefer_column_name_to_alias = 1`), so a
`select` aliasing `Timestamp` to itself still prunes partitions. With `groupBy`, use windows at
least as coarse as the grain, so no group is split across two windows.

`effect-orm check` validates the folder: every snapshot id matches its contents, every parent
exists and sorts earlier, and branches merged from different pull requests touch different
tables. Independent branches are fine: the next `generate` records both as parents. Branches that
change the same table conflict; delete one migration and generate it again on top of the other.
Run `check` in CI.

### Starting from an existing database

A ClickHouse database that predates effect-orm does not need its tables written by hand.
`effect-orm pull` reads the database the config's `driver` connects to and writes a schema
module, one `CH.table` per table; then `generate --baseline` starts the history from it:

```sh
effect-orm pull                         # writes the config's schema module; --out <path> for another
effect-orm generate --baseline          # a migration that runs nothing, with this schema as its snapshot
effect-orm migrate                      # runs no statement; records the baseline as applied
```

The module is written so that its definitions yield exactly what the database holds: engine
and its arguments, keys as declared, partition key, TTL, settings, comments, column defaults
(`DEFAULT`, `MATERIALIZED`, `ALIAS`), codecs and skipping indexes, with every expression as the
server's own text. The first `generate` after the baseline finds nothing to do. A `Replicated`
or `Shared` engine is written as its plain family; replication is a render option.

Column types map to the built-ins that write the same SQL (`LowCardinality(Nullable(String))` is
`CH.lowCardinality(CH.nullable(CH.string))`). A type without one becomes `CH.custom` with the
schema its JSON value decodes with (`UUID` and `Date` as strings, `Decimal` and `Int8` as
numbers, an `Enum8` as its labels), and a type the generator does not know (`Tuple`, `JSON`, a
`Map` keyed by numbers) becomes `CH.untyped`. The file's header lists what to review:

- **Materialized views** are not generated: a view's body is a query-DSL value, which SQL text
  cannot be turned back into. The header quotes each body; write it with `CH.materializedView`.
- **Views, dictionaries, `Distributed`, `Kafka` and other non-MergeTree tables** are written as
  `external: true`: queryable, never migrated.
- A table with a name that is not a plain identifier (`my-col`), or a MergeTree declared in the
  old `MergeTree(date, key, granularity)` syntax, is written as external too.
- **Not modeled, so left out:** `SAMPLE BY`, projections, constraints and `EPHEMERAL` columns.
  Column-level TTLs are not read at all.
- A `Decimal` wider than 15 digits decodes to a number, which rounds; the header flags each one.
- A user who may not read `system.data_skipping_indices` gets no indexes, and a note saying so.

`--tables a,b` writes only those tables; `--force` overwrites an existing file. `verify` reports
the views and materialized views as `unexpected` until the schema declares them. Postgres is
not supported yet.

## Applying migrations

The library opens no connection. Give it a `MigrationDriver`, usually built from the
`SqlClient` you query with. ClickHouse DDL has to go through the client's `asCommand`.

```ts title="migrations-run.ts"
import { ClickhouseClient } from "@effect/sql-clickhouse"
import { Effect, Layer } from "effect"
import * as Migrate from "@maple-dev/effect-orm/migrate"

const Driver = Layer.effect(
	Migrate.MigrationDriver,
	Effect.gen(function* () {
		const sql = yield* ClickhouseClient.ClickhouseClient
		return Migrate.fromSqlClient(sql, { command: sql.asCommand })
	}),
)

export const program = Effect.gen(function* () {
	const migrations = yield* Migrate.fromRecord({
		"20261003120000_init": {
			kind: "sql",
			migration: "CREATE TABLE IF NOT EXISTS t (x UInt8) ENGINE = MergeTree ORDER BY x",
		},
	})
	const applied = yield* Migrate.run({ migrations, strict: true })
	const { drift } = yield* Migrate.verify(migrations)
	return { applied, drift }
}).pipe(
	Effect.provide(Driver),
	Effect.provide(ClickhouseClient.layer({ url: "http://localhost:8123" })),
)
```

`Migrate.fromFileSystem(dir)` reads a migrations folder through Effect's `FileSystem`; add a
`driver` layer to the config and the CLI runs `effect-orm migrate`, `status`, and `verify`.

How a run behaves:

- **Order** follows the snapshots' parent links, then names.
- **Pending** means not in the ledger, by name. An applied migration's hash is checked; with
  `strict` a changed file fails with `MigrateHashMismatch`, otherwise it logs a warning.
- **Each statement is journaled** in `_effect_orm_migration_steps` after it finishes, and the
  migration row in `_effect_orm_migrations` is written last. A run that fails partway leaves
  the migration `partial`; the next run skips the finished statements and resumes. A finished
  statement whose SQL has since changed fails the run with `MigrateStepChanged` instead of being
  skipped: edit only the failed statement and those after it.
- **Uncertain statements are never repeated.** `started` is journaled before a statement runs. If
  the process dies, or the `done` row cannot be written, the statement may or may not have run, and
  the next run stops there with `MigrateStepUncertain` (status `uncertain`). Check the database,
  then record what happened: `Migrate.resolveStep` or `effect-orm resolve <migration> <step>
  --ran | --not-ran`. A statement the server rejected is journaled `failed` and simply runs again.
- **A lease** in `_effect_orm_migration_lease` stops a second run while one is active. It is best
  effort: two runs starting in the same instant can both proceed. Serialize deploys if that
  matters.
- **No rollback.** Write a new migration. Prefer expand, then contract: add the new shape, move
  readers, and drop the old shape in a later migration.

`Migrate.verify` compares the database with the snapshot of the **last applied** migration, so a
database that is behind is reported as behind (`status`), not as drifted. The server normalizes
both sides (`formatQuery`, `defaultValueOfTypeName`). It checks tables, engine family, keys,
columns, skipping indexes, and view targets and bodies; it does not check TTL, codecs, settings,
or comments yet. `effect-orm verify` exits 3 when it finds drift.

### Checking the definitions against a database

`verify` needs the migrations folder and the ledger. To check the table definitions against a
database that another tool migrates, or in CI after a deploy, compare it with the definitions
directly:

```sh
effect-orm verify --schema          # exit 0: no drift; exit 3: drift, one line per difference
effect-orm verify --schema --json   # {"drift":[{"entity":"users.email","problem":"not_null",...}]}
```

```text
Drift against the schema modules:
  users.email: type (expected text, actual character varying(10))
  users.name: missing
  users.users_email_unq: missing
```

It reads the config's `schema` modules and `driver`, and nothing in `out`. The comparison is the
one `verify` makes, with the same checks and the same limits, against a snapshot of the
definitions as they stand instead of the last applied one. So a database that is behind reports
the missing parts as drift; use plain `verify` where the ledger exists. The ledger tables are
never reported. ClickHouse and Postgres only: effect-orm has no SQLite catalog reader yet, so
with `dialect: "sqlite"` (SQLite or D1) it fails with a `KitError` (`unsupported`) before it
connects. In code, `verifySchema(config, cwd)` from `/kit` does the same, and
`Migrate.verifySnapshot(snapshot)` compares the database with any snapshot (a SQLite snapshot
fails with `MigrateSourceError`):

```ts title="migrations-verify-schema.ts"
import { Effect } from "effect"
import * as PG from "@maple-dev/effect-orm/postgres"
import * as Migrate from "@maple-dev/effect-orm/migrate"
import * as S from "@maple-dev/effect-orm/schema"

const Users = PG.table("users", {
	columns: { id: PG.text, email: PG.text },
	primaryKey: ["id"],
})

export const drift = Effect.gen(function* () {
	const entities = yield* S.pgEntitiesOf([Users])
	const snapshot = yield* S.makeSnapshot(entities, [S.ORIGIN_ID], "postgres")
	return yield* Migrate.verifySnapshot(snapshot)
})
```

### Running one step at a time

`run` applies everything in one process. An orchestrator that runs each statement as its own
durable step (a workflow engine, a job queue) uses the parts `run` is built from:

```ts
const pending = yield* Migrate.pendingMigrations({ migrations, strict: true })
for (const migration of pending) {
	const plan = yield* Migrate.planMigration(migration) // reads backfill bounds; plain data
	for (const step of plan.steps) if (!step.done) yield* Migrate.applyStep(plan.name, step)
	yield* Migrate.completeMigration(migration)
}
```

Plan a migration only once the ones before it are applied: a backfill reads its source when it
is planned. These calls take no lease, so the orchestrator must run one migration at a time.

_(Effect's own `ClickhouseMigrator` creates its ledger with a statement ClickHouse 26.8
rejects, and inserts ledger rows before running each migration. That is why this package has
its own runner.)_

## Postgres

Set `dialect: "postgres"` in the config and define tables with `PG.table`. `generate`, `check`,
`migrate`, `status` and `verify` then work as above, with the differences below.

```ts title="migrations-postgres.ts"
import { Effect } from "effect"
import * as PG from "@maple-dev/effect-orm/postgres"
import * as S from "@maple-dev/effect-orm/schema"

export const Dashboards = PG.table("dashboards", {
	columns: {
		org_id: PG.text,
		id: PG.text,
		status: PG.column(PG.text, { default: "open" }),
		created_at: PG.column(PG.timestamptz, { defaultExpr: "now()" }),
		archived_at: PG.nullable(PG.timestamptz),
	},
	primaryKey: ["org_id", "id"],
	indexes: [PG.index("dashboards_open_idx", ["org_id"], { where: ($) => $.archived_at.isNull() })],
	tenantColumn: "org_id",
})

export const Shares = PG.table("dashboard_shares", {
	columns: { org_id: PG.text, id: PG.text, dashboard_id: PG.text, widget_id: PG.nullable(PG.text), revoked_at: PG.nullable(PG.timestamptz) },
	primaryKey: ["org_id", "id"],
	indexes: [
		// At most one live share per dashboard and widget: a partial unique index on an expression.
		PG.uniqueIndex("dashboard_shares_live_unq", ($) => [$.org_id, $.dashboard_id, PG.coalesce($.widget_id, PG.lit(""))], {
			where: "revoked_at is null",
		}),
	],
	foreignKeys: [
		PG.foreignKey({ columns: ["org_id", "dashboard_id"], references: Dashboards, foreignColumns: ["org_id", "id"], onDelete: "cascade" }),
	],
})

export const ddl = Effect.map(S.pgEntitiesOf([Dashboards, Shares]), (entities) => S.renderPgSchema(entities))
```

**Definitions.** A column is `NOT NULL` unless its type is `PG.nullable(...)`. `PG.column(type,
options)` adds a `default` (a value of the column's type), a `defaultExpr` (SQL or a DSL
expression) or an `identity` (`"always"` or `"by default"`); any of them makes the column
optional on insert. `primaryKey` takes column names, or `{ columns, name }`; the default name is
`<table>_pkey`. Indexes are `PG.index` / `PG.uniqueIndex` over column names or expressions,
with `where` for a partial index and `using` for the access method. A foreign key without a
`name` gets drizzle-orm's, `<table>_<columns>_<foreign table>_<foreign columns>_fk`, shortened
with drizzle-kit's hash to `<table>_<hash>_fk` when it would pass 63 characters. Types are
stored as Postgres names them (`int4` is `integer`), so snapshots compare with the catalog and
with drizzle-kit.

**Unique constraints.** `uniqueConstraints: [PG.unique(name, columns, { nullsNotDistinct? })]`
on a table, or `unique` in `PG.column` for one column, declares a `UNIQUE` constraint: `ALTER
TABLE ... ADD CONSTRAINT name UNIQUE (columns)`, which is what drizzle-kit writes for `unique()`
and `.unique()`. It is not a unique index: Postgres backs it with an index of the same name, but
drops it with `DROP CONSTRAINT`, and a foreign key may reference its columns. `unique: true`
names the constraint `<table>_<column>_unique`, drizzle-orm's name; `unique: "name"` or `unique:
{ name, nullsNotDistinct }` gives another (drizzle-kit v1 names a column's unnamed `.unique()`
`<table>_<column>_key`, so say so when adopting one). `nullsNotDistinct` needs Postgres 15.
`onConflictDoNothing` / `onConflictDoUpdate` take the constraint's columns as `target`, or `{
constraint: name }`.

**Enums.** `PG.enumType(name, labels)` is a column type and a schema object; see
[Enums](https://effect-orm.maple.dev/postgres/specifics/overview/#enums). Its `CREATE TYPE` comes before every table, and is skipped when
the type already exists (Postgres has no `IF NOT EXISTS` for it), so an enum created by hand
behind `PG.custom` can be declared with `enumType` later.

Check constraints, generated columns, views, sequences and tables in other schemas are not
modeled yet; write them in a `--custom` migration, and declare a view you query as an
[external table](https://effect-orm.maple.dev/sqlite/queries/tables-and-types/#external-tables).

**Generating.** Postgres changes a column's type, nullability, default or identity in place
(`ALTER COLUMN`), and a primary key, an index, a unique constraint or a foreign key by dropping
and re-creating it. A type change is labeled `rewrite`: Postgres rewrites the table under an
exclusive lock. Drops need confirmation, dropping an enum type included (`"kind": "enum"`), and
renames of tables and columns still read as a drop and an add. Enum types are created first and
dropped last, unique constraints are added before the foreign keys that reference them, and
turning a `uniqueIndex` into a `unique` of the same name drops the index before adding the
constraint. Generated files carry `"dialect": "postgres"`. With `emit: "sql"` in the config,
`generate` writes the rendered statements to `migration.sql` instead, each under a comment
naming its label, so a tool that applies drizzle-kit folders applies the migration too.

Enum labels change as far as Postgres can change them in place:

- **A new label** is `ALTER TYPE ... ADD VALUE`, with `BEFORE` the label it precedes, so the
  order matches the definition.
- **A renamed label**, the same labels in the same order but one replaced in place, is `ALTER
  TYPE ... RENAME VALUE`. Rows holding the old label then hold the new one, so it asks for a
  hint first: `{"type":"confirm_rename","kind":"enum_value","entity":"channel_type.direct","to":"dm"}`.
- **Removing or reordering labels** needs a new type and every column moved to it. `generate`
  reports it as unsupported and writes nothing; write that rebuild with `--custom`.

A migration is one transaction, and Postgres does not let a transaction use a label it added
(`unsafe use of new value`). So `generate` refuses a migration that adds a label and also
uses it, in a default or a partial index's predicate: add the label, generate, then use it and
generate again. Before Postgres 12, `ADD VALUE` cannot run in a transaction at all, so a
migration that adds a label needs Postgres 12 or later.

Moving an enum from `PG.custom("public.channel_type", ...)` to `PG.enumType("channel_type",
...)` generates a `create_enum`, skipped since the type exists, and an `alter_column` per column
from `public.channel_type` to `channel_type`, the same type under its unqualified name, which
leaves the data as it is.

**Applying.** Each migration runs in one transaction with its ledger row, under a
transaction-scoped advisory lock, so concurrent deploys wait for each other rather than
racing. A failed statement rolls the whole migration back and the next run starts it from the
top: there is no step journal, no lease, no `partial` or `uncertain` state, and nothing for
`resolve` to do. The driver needs a transaction, which `Migrate.fromSqlClient` provides from
`SqlClient.withTransaction`. A statement Postgres refuses inside a transaction (`CREATE INDEX
CONCURRENTLY`, `ALTER TYPE ... ADD VALUE` before Postgres 12) cannot be in a migration yet.

**Drift.** `verify` builds the expected schema in a scratch schema, inside a transaction it
always rolls back, and reads both catalogs with the same queries, so Postgres deparses both
sides and `'open'` matches the stored `'open'::text`. The scratch schema is searched before the
current one, so a type of the current schema resolves in both, and a column of it reads
`channel_type`, its default `'public'::channel_type`, on both sides, however the definition
qualified it. It checks enum types (labels, in order, read from `pg_enum`), tables, columns
(type, `NOT NULL`, default, identity), primary keys, unique constraints (columns, `NULLS NOT
DISTINCT`), indexes (uniqueness, method, keys, predicate) and foreign keys in the current
schema. A unique constraint is never read as an index, nor an index as a constraint. An enum
the snapshot does not declare is reported only if no column uses it, so a `PG.custom` enum still
verifies. `Migrate.verify(migrations, { ignoreTables })` skips tables another tool owns.

### Adopting a drizzle-kit folder

A drizzle-kit (v1) folder already has the layout `migrate` reads: `<timestamp>_<name>/migration.sql`
split on `--> statement-breakpoint`. Its `snapshot.json` files are recognized as drizzle-kit's
and set aside, so the folder runs as it is. Adoption is two steps:

1. `effect-orm generate --baseline --from-drizzle` writes a migration that runs nothing, whose
   snapshot is drizzle-kit's last one converted to entities: enums, tables, columns (enum columns
   included), keys, indexes, unique constraints and foreign keys. Anything the conversion cannot
   model (sequences, views, check constraints, policies, tables in other schemas) is listed and
   nothing is written. Without `--from-drizzle` the snapshot comes from your
   `PG.table` definitions instead. Migrations before the baseline are legacy: they run, but
   nothing diffs against them, and a plain `generate` refuses to run until a baseline exists.
2. On a database drizzle-kit (or anything else) already migrated, `effect-orm baseline <name>`
   (`Migrate.baseline`) records the baseline and every migration before it as applied, without
   running them. A fresh database, such as a test's, simply runs everything.

The first `generate` after the baseline diffs your `PG.table` definitions against what
drizzle-kit recorded, so every place they disagree (a constraint name, a default) shows up as an
op to accept or fix. `verify` against the baseline also finds objects the database has and
drizzle-kit's snapshot does not, such as a table a hand-written migration created and nothing
dropped.

## SQLite

Set `dialect: "sqlite"` and export `SQLite.table` values with their DDL from the schema module:

```ts title="sqlite-kit-config.ts"
import { defineConfig } from "@maple-dev/effect-orm/kit"

export default defineConfig({ dialect: "sqlite", schema: "./src/db/schema.ts", out: "./migrations" })
```

`effect-orm generate` writes `<timestamp>_<name>/migration.sql` and `snapshot.json`, the
drizzle-kit 1.0 layout. Statements are split by `--> statement-breakpoint`, each under a comment
naming its label. A SQLite migration is always SQL; there is no `migration.json` for it. Tools
that apply drizzle-kit folders apply it as it is, for example Alchemy's
`Cloudflare.D1.Database({ migrations: "./migrations" })`, which runs each folder's statements as one
D1 batch. `snapshot.json` is for `generate` and `check`; those tools ignore it.

What each change becomes:

| Change | SQL |
| --- | --- |
| New table, dropped table | `CREATE TABLE`, `DROP TABLE` (confirm the data loss) |
| New column | `ALTER TABLE ... ADD COLUMN`, when SQLite allows it: the column is nullable or has a literal default, and is not in the primary key or a UNIQUE constraint. Otherwise a rebuild |
| Dropped column | `ALTER TABLE ... DROP COLUMN` (confirm the data loss), unless it is in the primary key or a UNIQUE constraint. Otherwise a rebuild |
| A column's type, nullability or default; the primary key; a UNIQUE column | A table rebuild. A new type also needs `confirm_data_loss` for the column: the copy converts values by SQLite's type affinity (`'007'` to `7`) |
| New or changed index | `DROP INDEX`, `CREATE [UNIQUE] INDEX` |
| Renamed table or column | `ALTER TABLE ... RENAME`, only with a `confirm_rename` hint |

A rebuild first checks the live catalog for tables that reference it through a foreign key,
including keys created by another tool and self-references. Any inbound key refuses the rebuild
before rows are copied or the old table is dropped, even if its child table is empty. The error
names `foreign_key_references_prevent_rebuild`. Use a custom migration with a tested plan that
preserves those dependencies instead.

With no inbound keys, the rebuild follows [SQLite's procedure](https://www.sqlite.org/lang_altertable.html#otheralter)
the way drizzle-kit writes it: `PRAGMA foreign_keys=OFF`, `CREATE TABLE "__new_<table>"` with the
new definition, `INSERT INTO "__new_<table>" (...) SELECT ...` for every column both tables
have, `DROP TABLE`, `ALTER TABLE "__new_<table>" RENAME TO "<table>"`, `PRAGMA foreign_keys=ON`,
then every index of the table again. A column made NOT NULL that has a default is copied as
`COALESCE(<column>, <default>)`, so existing NULLs take the default. A copy fails when the rows
do not fit the new table, such as a column made NOT NULL over existing NULLs with no default, or
a new UNIQUE constraint over duplicate values. A column a rebuild leaves out needs the same
`confirm_data_loss` hint as a dropped column.

:::caution
`PRAGMA foreign_keys=OFF` does nothing inside a transaction, and D1 and most migrators run each
migration in one. Without the guard, the rebuild's `DROP TABLE` would fire `ON DELETE CASCADE`
or `ON DELETE SET NULL` in referencing tables. Running outside a transaction is not an option
for a D1 batch, and `defer_foreign_keys` does not prevent those actions.
:::

Apply a migration's statements as one transaction (one `batch` on D1), and roll back on failure.
The guard uses a short-lived `__effect_orm_rebuild_guard_<table>` table with a NOT NULL assertion,
so it still works with `ignore_check_constraints` enabled. It drops the guard before a safe
rebuild; a refused transaction rolls it back. A pre-existing table with that name causes an
error without reusing or dropping it. This guard is in newly generated SQL: review or regenerate
unapplied rebuilds generated by older versions. It checks inbound foreign keys; it does not
preserve unmodelled triggers, views or other constraints.

A rebuild renumbers rowids: the copy may read the old rows in any order. A table whose rows are
referenced by rowid (rather than by a declared key) should declare an `integer` primary key, which
is the rowid and is copied.

`ALTER TABLE ... ADD COLUMN` always appends, so a column declared between two existing ones is
last in the database and first-to-last order differs from a fresh create. Name columns in
`INSERT` and `SELECT`, as the builder does.

A rename is never guessed, except by case: SQLite compares table and column names without case,
so `Url` to `url` (or `links` to `Links`) is the same name, renamed with no hint and no data lost.
Any other rename without a hint reads as a drop plus an add, and the drop asks for confirmation.
Pass the rename instead:

```json
[
	{ "type": "confirm_rename", "kind": "table", "entity": "links", "to": "urls" },
	{ "type": "confirm_rename", "kind": "column", "entity": "urls.url", "to": "target" }
]
```

`entity` is the old table name, or `<table>.<old column>` with the table's new name. Table
renames run first. Indexes keep their names and follow the rename.

### Adopting a drizzle-kit SQLite folder

`effect-orm generate --baseline --from-drizzle` reads drizzle-kit 1.0's last `snapshot.json`
(version 7, `dialect: "sqlite"`): tables, columns, primary keys, indexes and UNIQUE
constraints. Foreign keys, checks, views, generated and AUTOINCREMENT columns and partial
indexes are listed, and nothing is written. The baseline runs nothing. A later `generate`
compares your `SQLite.table` definitions with it; when they declare the same tables, it writes
nothing. The tests prove this with a folder drizzle-kit 1.0.0-rc.5 generated.

drizzle-kit 1.0 leaves `NOT NULL` out of a primary key column (`"id" text PRIMARY KEY`), so
SQLite then accepts a NULL key in a `text` key column. Its snapshot marks the column
`notNull: false`, but drizzle-kit treats a key column as not null. The baseline reads it as not
null too, so a `SQLite.table` key column (always `NOT NULL`) matches it and no rebuild is
written. The database keeps accepting a NULL key until that table is rebuilt for another change.

The drizzle-kit 0.x layout (`meta/_journal.json` and flat `.sql` files) is not read. Run
`drizzle-kit up` to convert it first.

`/migrate` does not run SQLite folders yet: `run`, `status`, `verify` and `baseline` fail with a
`MigrateSourceError` for one. Keep applying them with the tool you use now.

## MySQL

Set `dialect: "mysql"` and export `MySQL.table` values with their DDL from the schema module. One
folder serves MySQL and MariaDB: the DDL is the same.

```ts title="mysql-kit-config.ts"
import { defineConfig } from "@maple-dev/effect-orm/kit"

export default defineConfig({ dialect: "mysql", schema: "./src/db/schema.ts", out: "./migrations" })
```

`effect-orm generate` writes `<timestamp>_<name>/migration.sql` and `snapshot.json`
(`"dialect": "mysql"`), the drizzle-kit 1.0 layout, as for SQLite: one statement per
`--> statement-breakpoint`, each under a comment naming its label. A MySQL migration is always SQL.

MySQL alters tables in place, so every change is an `ALTER TABLE` of its own:

| Change | SQL |
| --- | --- |
| New table, dropped table | `CREATE TABLE`, `DROP TABLE` (confirm the data loss) |
| New column | `ALTER TABLE ... ADD COLUMN` |
| Dropped column | `ALTER TABLE ... DROP COLUMN` (confirm the data loss) |
| A column's type, nullability or default | `ALTER TABLE ... MODIFY COLUMN` with the whole new definition. A new type also needs `confirm_data_loss` for the column: MySQL converts (or refuses) the values |
| A column made NOT NULL that has a default | `MODIFY COLUMN` to the new type, `UPDATE ... SET <column> = <default> WHERE <column> IS NULL`, then `MODIFY COLUMN ... NOT NULL` |
| The primary key | `ALTER TABLE ... DROP PRIMARY KEY` before the column changes, `ADD PRIMARY KEY (...)` after |
| New or changed UNIQUE column, index | `DROP INDEX ... ON`, then `ADD CONSTRAINT ... UNIQUE` or `CREATE [UNIQUE] INDEX` after the column changes |
| Renamed table or column | `RENAME TABLE`, or `CHANGE COLUMN <old> <new definition>`, only with a `confirm_rename` hint |

A column is renamed with `CHANGE COLUMN` rather than `RENAME COLUMN`: MariaDB names a JSON
column's automatic `CHECK (json_valid(...))` after the column, and keeps the old name through a
`RENAME COLUMN`. MySQL compares column names without case, so a column renamed by case only
(`Url` to `url`) is renamed with no hint. Table names are compared as written. The hints take the
same form as for [SQLite](#sqlite).

:::caution
MySQL DDL is not transactional: each statement commits on its own, so a migration that fails
part way leaves the statements before the failure applied. A `MODIFY COLUMN` over rows the new
definition cannot hold fails: a NOT NULL with no default over NULLs, a narrower type over values
that do not fit, a new UNIQUE over duplicates. Read the plan before applying it.
:::

`ALTER TABLE ... ADD COLUMN` appends, so a column declared between two existing ones is last in
the database. A new `NOT NULL` column with no default takes the type's implicit default in
existing rows (`0`, `''`, a JSON `null` on MySQL), and a `datetime` fails on MySQL, while MariaDB
writes a zero date that `datetime` refuses to decode. Give it a default, or add it nullable first.

`--from-drizzle` reads only Postgres and SQLite snapshots. To adopt a MySQL database, declare its
tables and run `generate --baseline`. `/migrate` does not run MySQL folders yet: `run`, `status`,
`verify` and `baseline` fail with a `MigrateSourceError` for one, and `verify --schema` refuses a
MySQL config. Apply the folder with a tool that reads drizzle-kit folders.

The fuzzer generates pairs of random schemas (types, nullability, defaults, keys, UNIQUE columns,
indexes, table and column renames), applies the migration between them to seeded rows on MySQL 8.4
and MariaDB 11.4, and checks that `information_schema` matches a fresh create and that the rows
kept their values.

Source: https://effect-orm.maple.dev/sqlite/database/migrations/index.mdx