The best way to control Claude Code, Codex and more from any computer or phone

main
mdx 241 lines 8.15 KB
Raw
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 by name and
101 content hash. An orphaned row fails conversion with
102 `MigrationHistoryConflictError`; an already-applied file that was
103 edited or removed fails with `RewrittenMigrationHistoryError`.
104 Add a forward migration rather than rewriting history. PlanetScale
105 development branches replace (re-fork from parent) instead of
106 failing; production branches always fail.
107 - A **failed** Prisma migration (`finished_at` NULL) blocks adoption
108 until repaired with `prisma migrate resolve`; rolled-back rows are
109 skipped.
110
111 Databases migrated by older versions of Alchemy are upgraded to the
112 current table shape in place, at whatever table name their state
113 recorded — no action needed.
114
115 Each database documents its own mechanics — transactionality and
116 quirks:
117
118 | Database | Mechanics |
119 | --- | --- |
120 | Cloudflare D1 | [D1 migrations](/cloudflare/data/d1#migrations) — batched application (no transactions over HTTP) |
121 | Neon | [Neon migrations](/neon/data/migrations) — applied transactionally on the branch |
122 | PlanetScale | [PlanetScale migrations](/planetscale/data/migrations) — the same contract on Postgres and MySQL branches |
123 | Fly Managed Postgres | [Fly Postgres](/fly/data/postgres#migrations) — applied over the direct URI |
124
125 ## Generated migrations target the same contract
126
127 Generate and commit files with drizzle-kit before deployment:
128
129 ```sh
130 pnpm exec drizzle-kit generate
131 git add src/schema.ts drizzle
132 git commit -m "Add schema migration"
133 ```
134
135 The same directory input accepts handwritten SQL or drizzle-kit's
136 `<timestamp>_<name>/migration.sql` layout; see [Drizzle migrations](/sql/drizzle/migrations).
137 Optional generation automation is documented separately in the
138 [`Drizzle.Schema` reference](/providers/drizzle/schema).
139
140 ## Durable Object migrations
141
142 A Durable Object's SQLite database belongs to one named instance,
143 not to the Worker deployment. `Cloudflare.SqlMigrations` reads and
144 normalizes a migration directory during construction/planning, then
145 embeds its records in the Worker JavaScript. No ORM, SQL imports,
146 `migrations.js` bundle, or migration environment variables are needed.
147
148 Use either flat files such as `migrations/0001_init.sql` or modern
149 drizzle-kit directories such as
150 `drizzle/20260919000000_create_users/migration.sql`. Commit the files
151 before running Alchemy. Paths are relative to the command's current
152 working directory, **not** the source module.
153
154 Load the directory in the outer Effect:
155
156 ```typescript
157 import * as Cloudflare from "@alchemy.run/cloudflare";
158 import * as Effect from "effect/Effect";
159
160 export class Users extends Cloudflare.DurableObject<Users>()(
161 "Users",
162 Effect.gen(function* () {
163 const migrations = yield* Cloudflare.SqlMigrations("./migrations");
164 const state = yield* Cloudflare.DurableObjectState;
165
166 return Effect.gen(function* () {
167 yield* migrations.apply().pipe(Effect.orDie);
168
169 return {
170 listUsers: () =>
171 state.storage.sql.exec("SELECT id, name FROM users").pipe(
172 Effect.flatMap((cursor) => cursor.toArray()),
173 ),
174 };
175 });
176 }),
177 ) {}
178 ```
179
180 `migrations.apply()` requires `RuntimeContext` and the current Durable Object
181 state. Call it in the **inner** Effect before returning the instance's API;
182 it cannot run in the outer construction Effect.
183 Its error channel includes `MigrationError` and
184 `MigrationHistoryConflictError`. Using `.pipe(Effect.orDie)` here
185 prevents activation if the schema cannot be initialized.
186
187 The object form selects a different history table:
188
189 ```typescript
190 const migrations = yield* Cloudflare.SqlMigrations({
191 dir: "./drizzle",
192 table: "app_migrations", // default: "__alchemy_migrations"
193 });
194 ```
195
196 ### Per-instance application
197
198 Each object applies pending files when it activates, not during deployment of
199 the whole namespace. Each file's SQL and history row commit together:
200
201 ```text
202 0001_create_users → commit SQL + history
203 0002_add_email → failure rolls back this file and prevents activation
204 next activation → skip 0001, retry 0002
205 ```
206
207 ### Adopting Drizzle history
208
209 ```text
210 __drizzle_migrations → copy matching history → __alchemy_migrations
211 missing match → MigrationHistoryConflictError
212 ```
213
214 Adoption is one-way: keep the historical SQL files and use only Alchemy's
215 migrator afterward; the old table stays frozen. Changing `migrationsTable`
216 on a `migrations.js` bundle does not convert its history.
217
218 ### Upgrade legacy Drizzle files
219
220 For the legacy `meta/_journal.json` layout:
221
222 ```sh
223 pnpm exec drizzle-kit up
224 git diff -- drizzle
225 git add drizzle
226 git commit -m "Upgrade Drizzle migration layout"
227 ```
228
229 See the [Drizzle guide](/sql/drizzle/migrations#durable-object-migrations)
230 or [runnable example](https://github.com/alchemy-run/alchemy/tree/main/examples/cloudflare-durable-object-sql)
231 for the full setup.
232
233 ## Where next
234
235 - [Postgres](/sql/effect-sql/postgres) /
236 [MySQL](/sql/effect-sql/mysql) / [D1](/sql/effect-sql/d1) — query
237 the migrated database.
238 - [Drizzle migrations](/sql/drizzle/migrations) — generate the
239 files from a schema module instead of writing them by hand.
240 - [Branch from a shared database](/cloudflare/data/branch-from-shared-database)
241 — per-stage databases built on the same contract.