clover's git

the-knife

1	---
2	title: Migrations
3	description: Commit ordered SQL files and apply them on database deploy or Durable Object activation, tracked in one Alchemy-owned table.
4	---
5
6	Migrations without an ORM: commit `.sql` files to a directory and
7	wire it into the database resource with `migrations`. Each deploy
8	applies the pending files — there is nothing else to run. For
9	[Durable Objects](#durable-object-migrations), load the files during
10	construction and apply them per instance at activation instead.
11
12	## Write the migrations
13
14	```sql
15	-- migrations/0001_init.sql
16	CREATE TABLE users (
17	id INTEGER PRIMARY KEY AUTOINCREMENT,
18	name TEXT NOT NULL,
19	email TEXT NOT NULL UNIQUE
20	);
21	```
22
23	```sql
24	-- migrations/0002_posts.sql
25	CREATE TABLE posts (
26	id INTEGER PRIMARY KEY AUTOINCREMENT,
27	user_id INTEGER NOT NULL REFERENCES users(id),
28	title TEXT NOT NULL
29	);
30	```
31
32	## Wire the directory in
33
34	```typescript
35	const db = yield* Cloudflare.D1.Database("app-db", {
36	migrations: "./migrations",
37	});
38	```
39
40	The same prop exists on `Neon.Project`, `Neon.Branch`,
41	`Fly.Postgres`, and the PlanetScale databases and branches.
42
43	## How files are applied
44
45	- The directory is scanned recursively for `.sql` files.
46	- Files are ordered by numeric prefix (`0001_init.sql`,
47	`0002_posts.sql`), then by name.
48	- Each file is content-hashed and applied files are recorded, so a
49	deploy only runs what's new — an unchanged directory is a noop.
50	- Application is ordered and stops on the first failure; the failed
51	file is retried on the next deploy.
52
53	## The tracking table
54
55	Applied migrations are recorded in one Alchemy-owned table,
56	`__alchemy_migrations`, on every database:
57
58	```sql
59	CREATE TABLE IF NOT EXISTS "__alchemy_migrations" (
60	id SERIAL PRIMARY KEY, -- INTEGER on SQLite
61	hash text NOT NULL, -- sha256 of the file
62	created_at bigint, -- millis from a timestamp prefix, if any
63	name text, -- the applied-detection key
64	applied_at timestamp with time zone DEFAULT now()
65	);
66	```
67
68	Detection is by `name`, so renaming an applied file is a schema
69	change, not a cosmetic one.
70
71	To put the bookkeeping in a differently-named table, use the object
72	form of the prop:
73
74	```typescript
75	const db = yield* Cloudflare.D1.Database("app-db", {
76	migrations: {
77	dir: "./migrations",
78	table: "my_migrations", // default: "__alchemy_migrations"
79	},
80	});
81	```
82
83	## Adopting an existing database
84
85	A database previously migrated with **drizzle-kit**, **Prisma**, or
86	**wrangler** needs no baselining. On the first deploy, Alchemy finds
87	the old tool's tracking table — `__drizzle_migrations` (in the
88	`drizzle` schema on Postgres), `_prisma_migrations`, or
89	`d1_migrations` — copies its applied history into
90	`__alchemy_migrations` once, and leaves the old table **frozen**:
91	never written, never dropped. Only migrations the old tool hadn't
92	applied yet actually run.
93
94	This is a one-way move. After adoption, Alchemy's table is the only
95	bookkeeping — the old tool's table stops reflecting reality, so stop
96	running its migrate command.
97
98	Two guard rails:
99
100	- Every recorded row must match a local migration file; an orphaned
101	row fails the deploy with `MigrationHistoryConflictError` — it
102	means migrations were applied that your checkout doesn't have.
103	- A **failed** Prisma migration (`finished_at` NULL) blocks adoption
104	until repaired with `prisma migrate resolve`; rolled-back rows are
105	skipped.
106
107	Databases migrated by older versions of Alchemy are upgraded to the
108	current table shape in place, at whatever table name their state
109	recorded — no action needed.
110
111	Each database documents its own mechanics — transactionality and
112	quirks:
113
114	| Database | Mechanics |
115	| --- | --- |
116	| Cloudflare D1 | [D1 migrations](/cloudflare/data/d1#migrations) — batched application (no transactions over HTTP) |
117	| Neon | [Neon migrations](/neon/data/migrations) — applied transactionally on the branch |
118	| PlanetScale | [PlanetScale migrations](/planetscale/data/migrations) — the same contract on Postgres and MySQL branches |
119	| Fly Managed Postgres | [Fly Postgres](/fly/data/postgres#migrations) — applied over the direct URI |
120
121	## Generated migrations target the same contract
122
123	Generate and commit files with drizzle-kit before deployment:
124
125	```sh
126	pnpm exec drizzle-kit generate
127	git add src/schema.ts drizzle
128	git commit -m "Add schema migration"
129	```
130
131	The same directory input accepts handwritten SQL or drizzle-kit's
132	`<timestamp>_<name>/migration.sql` layout; see [Drizzle migrations](/sql/drizzle/migrations).
133	Optional generation automation is documented separately in the
134	[`Drizzle.Schema` reference](/providers/drizzle/schema).
135
136	## Durable Object migrations
137
138	A Durable Object's SQLite database belongs to one named instance,
139	not to the Worker deployment. `Cloudflare.SqlMigrations` reads and
140	normalizes a migration directory during construction/planning, then
141	embeds its records in the Worker JavaScript. No ORM, SQL imports,
142	`migrations.js` bundle, or migration environment variables are needed.
143
144	Use either flat files such as `migrations/0001_init.sql` or modern
145	drizzle-kit directories such as
146	`drizzle/20260919000000_create_users/migration.sql`. Commit the files
147	before running Alchemy. Paths are relative to the command's current
148	working directory, **not** the source module.
149
150	Load the directory in the outer Effect:
151
152	```typescript
153	import * as Cloudflare from "alchemy/Cloudflare";
154	import * as Effect from "effect/Effect";
155
156	export class Users extends Cloudflare.DurableObject<Users>()(
157	"Users",
158	Effect.gen(function* () {
159	const migrations = yield* Cloudflare.SqlMigrations("./migrations");
160	const state = yield* Cloudflare.DurableObjectState;
161
162	return Effect.gen(function* () {
163	yield* migrations.apply().pipe(Effect.orDie);
164
165	return {
166	listUsers: () =>
167	state.storage.sql.exec("SELECT id, name FROM users").pipe(
168	Effect.flatMap((cursor) => cursor.toArray()),
169	),
170	};
171	});
172	}),
173	) {}
174	```
175
176	`migrations.apply()` requires `RuntimeContext` and the current Durable Object
177	state. Call it in the **inner** Effect before returning the instance's API;
178	it cannot run in the outer construction Effect.
179	Its error channel includes `MigrationError` and
180	`MigrationHistoryConflictError`. Using `.pipe(Effect.orDie)` here
181	prevents activation if the schema cannot be initialized.
182
183	The object form selects a different history table:
184
185	```typescript
186	const migrations = yield* Cloudflare.SqlMigrations({
187	dir: "./drizzle",
188	table: "app_migrations", // default: "__alchemy_migrations"
189	});
190	```
191
192	### Per-instance application
193
194	Each object applies pending files when it activates, not during deployment of
195	the whole namespace. Each file's SQL and history row commit together:
196
197	```text
198	0001_create_users → commit SQL + history
199	0002_add_email → failure rolls back this file and prevents activation
200	next activation → skip 0001, retry 0002
201	```
202
203	### Adopting Drizzle history
204
205	```text
206	__drizzle_migrations → copy matching history → __alchemy_migrations
207	missing match → MigrationHistoryConflictError
208	```
209
210	Adoption is one-way: keep the historical SQL files and use only Alchemy's
211	migrator afterward; the old table stays frozen. Changing `migrationsTable`
212	on a `migrations.js` bundle does not convert its history.
213
214	### Upgrade legacy Drizzle files
215
216	For the legacy `meta/_journal.json` layout:
217
218	```sh
219	pnpm exec drizzle-kit up
220	git diff -- drizzle
221	git add drizzle
222	git commit -m "Upgrade Drizzle migration layout"
223	```
224
225	See the [Drizzle guide](/sql/drizzle/migrations#durable-object-migrations)
226	or [runnable example](https://github.com/alchemy-run/alchemy/tree/main/examples/cloudflare-durable-object-sql)
227	for the full setup.
228
229	## Where next
230
231	- [Postgres](/sql/effect-sql/postgres) /
232	[MySQL](/sql/effect-sql/mysql) / [D1](/sql/effect-sql/d1) — query
233	the migrated database.
234	- [Drizzle migrations](/sql/drizzle/migrations) — generate the
235	files from a schema module instead of writing them by hand.
236	- [Branch from a shared database](/cloudflare/data/branch-from-shared-database)
237	— per-stage databases built on the same contract.