1import {
2 applyAlchemyFormat,
3 applyMigrations,
4 readDrizzleDirRecords,
5 readFlatRecords,
6} from "@/SQL/Migrations/index.ts";
7import * as NodeServices from "@effect/platform-node/NodeServices";
8import { Database } from "bun:sqlite";
9import { expect, layer } from "alchemy-test";
10import * as Effect from "effect/Effect";
11import * as Result from "effect/Result";
12import { makeSqliteExecutor, tableNames } from "./sqlite-executor.ts";
13
14const fixture = (name: string) =>
15 new URL(`./fixtures/${name}`, import.meta.url).pathname;
16
17const describe = layer(NodeServices.layer);
18
19const ALCHEMY_COLUMNS = ["id", "hash", "created_at", "name", "applied_at"];
20
21const migrationRows = (db: Database, table = "__alchemy_migrations") =>
22 db
23 .query(
24 `SELECT hash, created_at, name, applied_at FROM ${table} ORDER BY id;`,
25 )
26 .all() as Array<{
27 hash: string;
28 created_at: number | null;
29 name: string;
30 applied_at: string | null;
31 }>;
32
33describe("alchemy format", (it) => {
34 it.effect(
35 "creates the table and applies flat migrations in order",
36 () =>
37 Effect.gen(function* () {
38 const db = new Database(":memory:");
39 const executor = makeSqliteExecutor(db);
40 const records = yield* readFlatRecords(fixture("flat"));
41 yield* applyAlchemyFormat({
42 executor,
43 table: "__alchemy_migrations",
44 records,
45 });
46
47 expect(tableNames(db)).toEqual(
48 expect.arrayContaining(["__alchemy_migrations", "posts", "users"]),
49 );
50 const columns = db
51 .query("PRAGMA table_info(__alchemy_migrations);")
52 .all() as Array<{ name: string }>;
53 expect(columns.map((c) => c.name)).toEqual(ALCHEMY_COLUMNS);
54
55 const rows = migrationRows(db);
56 expect(rows.map((r) => r.name)).toEqual([
57 "0001_users.sql",
58 "0002_posts.sql",
59 ]);
60 expect(rows[0].hash).toMatch(/^[0-9a-f]{64}$/);
61 expect(rows[0].applied_at).toBeTruthy();
62
63 // Idempotent — a replay would throw on the bare CREATE TABLEs.
64 yield* applyAlchemyFormat({
65 executor,
66 table: "__alchemy_migrations",
67 records,
68 });
69 expect(migrationRows(db).length).toBe(2);
70 }),
71 { tags: ["unit", "local"] },
72 );
73
74 it.effect(
75 "applies only pending migrations on subsequent runs",
76 () =>
77 Effect.gen(function* () {
78 const db = new Database(":memory:");
79 const executor = makeSqliteExecutor(db);
80 const records = yield* readFlatRecords(fixture("flat"));
81 yield* applyAlchemyFormat({
82 executor,
83 table: "__alchemy_migrations",
84 records: records.slice(0, 1),
85 });
86 yield* applyAlchemyFormat({
87 executor,
88 table: "__alchemy_migrations",
89 records,
90 });
91 expect(migrationRows(db).map((r) => r.name)).toEqual([
92 "0001_users.sql",
93 "0002_posts.sql",
94 ]);
95 }),
96 { tags: ["unit", "local"] },
97 );
98
99 it.effect(
100 "upgrades the legacy Alchemy 3-column table in place without replaying",
101 () =>
102 Effect.gen(function* () {
103 const db = new Database(":memory:");
104 // A pre-registry deploy: the invented shape, TEXT id.
105 db.run(
106 "CREATE TABLE d1_migrations (id TEXT PRIMARY KEY, name TEXT NOT NULL, applied_at TEXT NOT NULL);",
107 );
108 db.run(
109 "INSERT INTO d1_migrations (id, name, applied_at) VALUES ('00001', '0001_users.sql', '2024-01-01 00:00:00');",
110 );
111 // ...and migration 0001 really ran:
112 db.run(
113 "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
114 );
115
116 const executor = makeSqliteExecutor(db);
117 const records = yield* readFlatRecords(fixture("flat"));
118 // Legacy deploys keep converging against their persisted table.
119 yield* applyAlchemyFormat({
120 executor,
121 table: "d1_migrations",
122 records,
123 });
124
125 const columns = db
126 .query("PRAGMA table_info(d1_migrations);")
127 .all() as Array<{ name: string; type: string }>;
128 expect(columns.map((c) => c.name)).toEqual(ALCHEMY_COLUMNS);
129 const rows = migrationRows(db, "d1_migrations");
130 expect(rows.map((r) => r.name)).toEqual([
131 "0001_users.sql",
132 "0002_posts.sql",
133 ]);
134 // Backfilled from the matching local record, not a placeholder.
135 expect(rows[0].hash).toBe(records[0].hash);
136 // The original applied_at survives the rebuild.
137 expect(rows[0].applied_at).toBe("2024-01-01 00:00:00");
138 expect(tableNames(db)).toContain("posts");
139 }),
140 { tags: ["unit", "local"] },
141 );
142
143 it.effect(
144 "upgrades the oldest 2-column shape, reading names from the primary column",
145 () =>
146 Effect.gen(function* () {
147 const db = new Database(":memory:");
148 db.run(
149 "CREATE TABLE __alchemy_migrations (id TEXT PRIMARY KEY, applied_at TEXT NOT NULL);",
150 );
151 db.run(
152 "INSERT INTO __alchemy_migrations (id, applied_at) VALUES ('0001_users.sql', '2024-01-01 00:00:00');",
153 );
154 db.run(
155 "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
156 );
157
158 const executor = makeSqliteExecutor(db);
159 const records = yield* readFlatRecords(fixture("flat"));
160 yield* applyAlchemyFormat({
161 executor,
162 table: "__alchemy_migrations",
163 records,
164 });
165 expect(migrationRows(db).map((r) => r.name)).toEqual([
166 "0001_users.sql",
167 "0002_posts.sql",
168 ]);
169 }),
170 { tags: ["unit", "local"] },
171 );
172
173 it.effect(
174 "converts a wrangler-shaped table in place when it IS the resolved table",
175 () =>
176 Effect.gen(function* () {
177 const db = new Database(":memory:");
178 db.run(
179 "CREATE TABLE d1_migrations (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE, applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL);",
180 );
181 db.run(
182 "INSERT INTO d1_migrations (name, applied_at) VALUES ('0001_users.sql', '2024-01-01 00:00:00');",
183 );
184 db.run(
185 "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
186 );
187
188 const executor = makeSqliteExecutor(db);
189 const records = yield* readFlatRecords(fixture("flat"));
190 yield* applyAlchemyFormat({
191 executor,
192 table: "d1_migrations",
193 records,
194 });
195 const columns = db
196 .query("PRAGMA table_info(d1_migrations);")
197 .all() as Array<{ name: string }>;
198 expect(columns.map((c) => c.name)).toEqual(ALCHEMY_COLUMNS);
199 expect(migrationRows(db, "d1_migrations").map((r) => r.name)).toEqual([
200 "0001_users.sql",
201 "0002_posts.sql",
202 ]);
203 }),
204 { tags: ["unit", "local"] },
205 );
206
207 it.effect(
208 "legacy rows recorded as dir/migration.sql keep matching directory records",
209 () =>
210 // Pre-registry Alchemy applied drizzle-layout dirs under the flat
211 // key (`<dir>/migration.sql`); current records key them by `<dir>`.
212 Effect.gen(function* () {
213 const db = new Database(":memory:");
214 db.run(
215 "CREATE TABLE d1_migrations (id TEXT PRIMARY KEY, name TEXT NOT NULL, applied_at TEXT NOT NULL);",
216 );
217 db.run(
218 "INSERT INTO d1_migrations (id, name, applied_at) VALUES ('00001', '20240101000000_init/migration.sql', '2024-01-01 00:00:00');",
219 );
220 db.run(
221 "CREATE TABLE users (id integer PRIMARY KEY NOT NULL, name text NOT NULL);",
222 );
223 db.run("CREATE UNIQUE INDEX users_name_unique ON users (name);");
224
225 const executor = makeSqliteExecutor(db);
226 const records = yield* readDrizzleDirRecords(fixture("drizzle-v1"));
227 yield* applyAlchemyFormat({
228 executor,
229 table: "d1_migrations",
230 records,
231 });
232 expect(migrationRows(db, "d1_migrations").map((r) => r.name)).toEqual([
233 "20240101000000_init/migration.sql",
234 "20240102000000_add_posts",
235 ]);
236 expect(tableNames(db)).toContain("posts");
237 }),
238 { tags: ["unit", "local"] },
239 );
240
241 it.effect(
242 "fails the upgrade when a recorded migration matches no local file",
243 () =>
244 Effect.gen(function* () {
245 const db = new Database(":memory:");
246 db.run(
247 "CREATE TABLE __alchemy_migrations (id TEXT PRIMARY KEY, name TEXT NOT NULL, applied_at TEXT NOT NULL);",
248 );
249 db.run(
250 "INSERT INTO __alchemy_migrations (id, name, applied_at) VALUES ('00001', 'zzz_deleted.sql', '2024-01-01 00:00:00');",
251 );
252 const executor = makeSqliteExecutor(db);
253 const records = yield* readFlatRecords(fixture("flat"));
254 const result = yield* Effect.result(
255 applyAlchemyFormat({
256 executor,
257 table: "__alchemy_migrations",
258 records,
259 }),
260 );
261 expect(Result.isFailure(result)).toBe(true);
262 if (Result.isFailure(result)) {
263 expect(result.failure._tag).toBe("MigrationHistoryConflictError");
264 expect(result.failure.message).toContain("zzz_deleted.sql");
265 }
266 }),
267 { tags: ["unit", "local"] },
268 );
269});
270
271describe("one-way conversion from foreign tables", (it) => {
272 it.effect(
273 "adopts a wrangler-migrated database: history copied, table frozen",
274 () =>
275 Effect.gen(function* () {
276 const db = new Database(":memory:");
277 // What `wrangler d1 migrations apply` left behind.
278 db.run(
279 "CREATE TABLE d1_migrations (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE, applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL);",
280 );
281 db.run(
282 "INSERT INTO d1_migrations (name, applied_at) VALUES ('0001_users.sql', '2024-01-01 00:00:00');",
283 );
284 db.run(
285 "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
286 );
287
288 const executor = makeSqliteExecutor(db);
289 const records = yield* readFlatRecords(fixture("flat"));
290 yield* applyAlchemyFormat({
291 executor,
292 table: "__alchemy_migrations",
293 records,
294 });
295
296 // History converted, only the pending migration ran.
297 const rows = migrationRows(db);
298 expect(rows.map((r) => r.name)).toEqual([
299 "0001_users.sql",
300 "0002_posts.sql",
301 ]);
302 expect(rows[0].hash).toBe(records[0].hash);
303 expect(rows[0].applied_at).toBe("2024-01-01 00:00:00");
304
305 // The wrangler table is frozen — never written, never dropped.
306 const wrangler = db
307 .query("SELECT name FROM d1_migrations ORDER BY id;")
308 .all() as Array<{ name: string }>;
309 expect(wrangler.map((r) => r.name)).toEqual(["0001_users.sql"]);
310 }),
311 { tags: ["unit", "local"] },
312 );
313
314 it.effect(
315 "conversion fails when foreign history references a missing local file",
316 () =>
317 Effect.gen(function* () {
318 const db = new Database(":memory:");
319 db.run(
320 "CREATE TABLE d1_migrations (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE, applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL);",
321 );
322 db.run("INSERT INTO d1_migrations (name) VALUES ('zzz_gone.sql');");
323 const executor = makeSqliteExecutor(db);
324 const records = yield* readFlatRecords(fixture("flat"));
325 const result = yield* Effect.result(
326 applyAlchemyFormat({
327 executor,
328 table: "__alchemy_migrations",
329 records,
330 }),
331 );
332 expect(Result.isFailure(result)).toBe(true);
333 if (Result.isFailure(result)) {
334 expect(result.failure._tag).toBe("MigrationHistoryConflictError");
335 }
336 }),
337 { tags: ["unit", "local"] },
338 );
339
340 it.effect(
341 "adopts a state-lost legacy 3-column d1_migrations as a conversion source",
342 () =>
343 // Old Alchemy deploys wrote a 3-column d1_migrations; if state was
344 // lost (adoption), the resolved table is __alchemy_migrations and
345 // the legacy table must be picked up as a source, not replayed.
346 Effect.gen(function* () {
347 const db = new Database(":memory:");
348 db.run(
349 "CREATE TABLE d1_migrations (id TEXT PRIMARY KEY, name TEXT NOT NULL, applied_at TEXT NOT NULL);",
350 );
351 db.run(
352 "INSERT INTO d1_migrations (id, name, applied_at) VALUES ('00001', '0001_users.sql', '2024-01-01 00:00:00');",
353 );
354 db.run(
355 "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
356 );
357
358 const executor = makeSqliteExecutor(db);
359 const records = yield* readFlatRecords(fixture("flat"));
360 yield* applyAlchemyFormat({
361 executor,
362 table: "__alchemy_migrations",
363 records,
364 });
365
366 const rows = migrationRows(db);
367 expect(rows.map((r) => r.name)).toEqual([
368 "0001_users.sql",
369 "0002_posts.sql",
370 ]);
371 expect(rows[0].hash).toBe(records[0].hash);
372 // The legacy table is frozen as a source, still 3 columns.
373 const legacyColumns = db
374 .query("PRAGMA table_info(d1_migrations);")
375 .all() as Array<{ name: string }>;
376 expect(legacyColumns.map((c) => c.name)).toEqual([
377 "id",
378 "name",
379 "applied_at",
380 ]);
381 }),
382 { tags: ["unit", "local"] },
383 );
384
385 it.effect(
386 "prefers drizzle history when multiple foreign sources exist",
387 () =>
388 Effect.gen(function* () {
389 const db = new Database(":memory:");
390 const records = yield* readDrizzleDirRecords(fixture("drizzle-v1"));
391 // Both a drizzle table and a wrangler table exist; drizzle's is
392 // probed first and carries hashes, so it wins.
393 db.run(
394 "CREATE TABLE __drizzle_migrations (id INTEGER PRIMARY KEY, hash text NOT NULL, created_at numeric, name text, applied_at TEXT);",
395 );
396 db.run(
397 `INSERT INTO __drizzle_migrations (hash, created_at, name) VALUES ('${records[0].hash}', 1704067200000, '20240101000000_init');`,
398 );
399 db.run(
400 "CREATE TABLE d1_migrations (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE, applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL);",
401 );
402 db.run(
403 "INSERT INTO d1_migrations (name) VALUES ('20240101000000_init');",
404 );
405 db.run(
406 "CREATE TABLE users (id integer PRIMARY KEY NOT NULL, name text NOT NULL);",
407 );
408 db.run("CREATE UNIQUE INDEX users_name_unique ON users (name);");
409
410 const executor = makeSqliteExecutor(db);
411 yield* applyAlchemyFormat({
412 executor,
413 table: "__alchemy_migrations",
414 records,
415 });
416 const rows = migrationRows(db);
417 expect(rows.map((r) => r.name)).toEqual([
418 "20240101000000_init",
419 "20240102000000_add_posts",
420 ]);
421 // Hash came from drizzle's table (wrangler has none).
422 expect(rows[0].hash).toBe(records[0].hash);
423 }),
424 { tags: ["unit", "local"] },
425 );
426
427 it.effect(
428 "adopts a prisma-migrated database via _prisma_migrations",
429 () =>
430 Effect.gen(function* () {
431 const db = new Database(":memory:");
432 // Prisma's table shape (sqlite rendition), one applied + one
433 // rolled-back migration.
434 db.run(
435 `CREATE TABLE _prisma_migrations (
436 id TEXT PRIMARY KEY,
437 checksum TEXT NOT NULL,
438 finished_at TEXT,
439 migration_name TEXT NOT NULL,
440 logs TEXT,
441 rolled_back_at TEXT,
442 started_at TEXT NOT NULL,
443 applied_steps_count INTEGER NOT NULL DEFAULT 0
444 );`,
445 );
446 const records = yield* readDrizzleDirRecords(fixture("prisma"));
447 db.run(
448 `INSERT INTO _prisma_migrations (id, checksum, finished_at, migration_name, started_at, applied_steps_count)
449 VALUES ('a', '${records[0].hash}', '2024-01-01 00:00:01', '20240101000000_init', '2024-01-01 00:00:00', 1);`,
450 );
451 db.run(
452 `INSERT INTO _prisma_migrations (id, checksum, rolled_back_at, migration_name, started_at)
453 VALUES ('b', 'dead', '2024-01-02 00:00:00', '20240102000000_rolled_back', '2024-01-02 00:00:00');`,
454 );
455 // The applied migration's tables exist (Prisma-dialect SQL in the
456 // fixture is postgres-flavored, so create a stand-in):
457 db.run("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);");
458
459 const executor = makeSqliteExecutor(db);
460 yield* applyAlchemyFormat({
461 executor,
462 // Only the applied row must convert; the rolled-back row is
463 // skipped even though it has no local file.
464 table: "__alchemy_migrations",
465 records: records.slice(0, 1),
466 });
467 const rows = migrationRows(db);
468 expect(rows.map((r) => r.name)).toEqual(["20240101000000_init"]);
469 // Prisma's checksum is sha256 of migration.sql — carried verbatim.
470 expect(rows[0].hash).toBe(records[0].hash);
471 // Frozen source.
472 expect(
473 (
474 db
475 .query("SELECT COUNT(*) AS n FROM _prisma_migrations;")
476 .all() as Array<{ n: number }>
477 )[0].n,
478 ).toBe(2);
479 }),
480 { tags: ["unit", "local"] },
481 );
482
483 it.effect(
484 "a failed prisma migration blocks conversion with guidance",
485 () =>
486 Effect.gen(function* () {
487 const db = new Database(":memory:");
488 db.run(
489 `CREATE TABLE _prisma_migrations (
490 id TEXT PRIMARY KEY,
491 checksum TEXT NOT NULL,
492 finished_at TEXT,
493 migration_name TEXT NOT NULL,
494 logs TEXT,
495 rolled_back_at TEXT,
496 started_at TEXT NOT NULL,
497 applied_steps_count INTEGER NOT NULL DEFAULT 0
498 );`,
499 );
500 db.run(
501 `INSERT INTO _prisma_migrations (id, checksum, migration_name, started_at)
502 VALUES ('a', 'abc', '20240101000000_broken', '2024-01-01 00:00:00');`,
503 );
504 const executor = makeSqliteExecutor(db);
505 const records = yield* readFlatRecords(fixture("flat"));
506 const result = yield* Effect.result(
507 applyAlchemyFormat({
508 executor,
509 table: "__alchemy_migrations",
510 records,
511 }),
512 );
513 expect(Result.isFailure(result)).toBe(true);
514 if (Result.isFailure(result)) {
515 expect(result.failure._tag).toBe("MigrationError");
516 expect(result.failure.message).toContain("prisma migrate resolve");
517 }
518 }),
519 { tags: ["unit", "local"] },
520 );
521});
522
523describe("registry apply", (it) => {
524 it.effect(
525 "keys directory-layout dirs by directory name",
526 () =>
527 Effect.gen(function* () {
528 const db = new Database(":memory:");
529 const executor = makeSqliteExecutor(db);
530 yield* applyMigrations({
531 resolved: {
532 dir: fixture("drizzle-v1"),
533 table: "__alchemy_migrations",
534 },
535 executor,
536 });
537 expect(migrationRows(db).map((r) => r.name)).toEqual([
538 "20240101000000_init",
539 "20240102000000_add_posts",
540 ]);
541 }),
542 { tags: ["unit", "local"] },
543 );
544
545 it.effect(
546 "rejects drizzle-v0 layouts",
547 () =>
548 Effect.gen(function* () {
549 const db = new Database(":memory:");
550 const executor = makeSqliteExecutor(db);
551 const result = yield* Effect.result(
552 applyMigrations({
553 resolved: {
554 dir: fixture("drizzle-v0"),
555 table: "__alchemy_migrations",
556 },
557 executor,
558 }),
559 );
560 expect(Result.isFailure(result)).toBe(true);
561 if (Result.isFailure(result)) {
562 expect(result.failure._tag).toBe("DrizzleV0LayoutError");
563 }
564 }),
565 { tags: ["unit", "local"] },
566 );
567});