1import * as Cloudflare from "@/Cloudflare";
2import { CloudflareEnvironment } from "@/Cloudflare/CloudflareEnvironment";
3import { hashMigrations } from "@/SQL/SqlFile.ts";
4import { State } from "@/State";
5import * as Test from "@/Test/Alchemy";
6import * as d1 from "@distilled.cloud/cloudflare/d1";
7import { expect } from "alchemy-test";
8import * as Data from "effect/Data";
9import * as Duration from "effect/Duration";
10import * as Effect from "effect/Effect";
11import * as FileSystem from "effect/FileSystem";
12import * as Path from "effect/Path";
13import { MinimumLogLevel } from "effect/References";
14import * as Schedule from "effect/Schedule";
15
16const { test } = Test.make({ providers: Cloudflare.providers() });
17
18const logLevel = Effect.provideService(
19 MinimumLogLevel,
20 process.env.DEBUG ? "Debug" : "Info",
21);
22
23test.provider(
24 "create and delete database with default props",
25 (stack) =>
26 Effect.gen(function* () {
27 const { accountId } = yield* yield* CloudflareEnvironment;
28
29 yield* stack.destroy();
30
31 const database = yield* stack.deploy(
32 Effect.gen(function* () {
33 return yield* Cloudflare.D1.Database("DefaultDatabase");
34 }),
35 );
36
37 expect(database.databaseName).toBeDefined();
38 expect(database.databaseId).toBeDefined();
39
40 const actualDatabase = yield* d1.getDatabase({
41 accountId,
42 databaseId: database.databaseId,
43 });
44 expect(actualDatabase.uuid).toEqual(database.databaseId);
45
46 yield* stack.destroy();
47
48 yield* waitForDatabaseToBeDeleted(database.databaseId, accountId);
49 }).pipe(logLevel),
50 { tags: ["provider:cloudflare", "provider:cloudflare:d1", "live"] },
51);
52
53test.provider(
54 "create, update, delete database",
55 (stack) =>
56 Effect.gen(function* () {
57 const { accountId } = yield* yield* CloudflareEnvironment;
58
59 yield* stack.destroy();
60
61 const database = yield* stack.deploy(
62 Effect.gen(function* () {
63 return yield* Cloudflare.D1.Database("TestDatabase", {
64 readReplication: { mode: "disabled" },
65 });
66 }),
67 );
68
69 const actualDatabase = yield* d1.getDatabase({
70 accountId,
71 databaseId: database.databaseId,
72 });
73 expect(actualDatabase.uuid).toEqual(database.databaseId);
74
75 const updatedDatabase = yield* stack.deploy(
76 Effect.gen(function* () {
77 return yield* Cloudflare.D1.Database("TestDatabase", {
78 readReplication: { mode: "auto" },
79 });
80 }),
81 );
82
83 expect(updatedDatabase.databaseId).toEqual(database.databaseId);
84
85 const actualUpdatedDatabase = yield* d1.getDatabase({
86 accountId,
87 databaseId: updatedDatabase.databaseId,
88 });
89 expect(actualUpdatedDatabase.readReplication?.mode).toEqual("auto");
90
91 yield* stack.destroy();
92
93 yield* waitForDatabaseToBeDeleted(database.databaseId, accountId);
94 }).pipe(logLevel),
95 { tags: ["provider:cloudflare", "provider:cloudflare:d1", "live"] },
96);
97
98test.provider(
99 "applies migrations from migrationsDir",
100 (stack) =>
101 Effect.gen(function* () {
102 const { accountId } = yield* yield* CloudflareEnvironment;
103 const fs = yield* FileSystem.FileSystem;
104 const path = yield* Path.Path;
105 const migrationsDir = yield* fs.makeTempDirectory({
106 prefix: "alchemy-d1-migrations-",
107 });
108
109 yield* fs.writeFileString(
110 path.join(migrationsDir, "0001_users.sql"),
111 "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
112 );
113 yield* fs.writeFileString(
114 path.join(migrationsDir, "0002_posts.sql"),
115 "CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL);",
116 );
117
118 yield* stack.destroy();
119
120 const database = yield* stack.deploy(
121 Effect.gen(function* () {
122 return yield* Cloudflare.D1.Database("MigrationDatabase", {
123 migrations: migrationsDir,
124 });
125 }),
126 );
127
128 expect(database.migrationsDir).toEqual(migrationsDir);
129 expect(database.migrationsTable).toEqual("__alchemy_migrations");
130 expect(Object.keys(database.migrationsHashes).sort()).toEqual([
131 "0001_users.sql",
132 "0002_posts.sql",
133 ]);
134
135 const tables = yield* listTables(accountId, database.databaseId);
136 expect(tables).toContain("users");
137 expect(tables).toContain("posts");
138 expect(tables).toContain("__alchemy_migrations");
139
140 // Alchemy's shape: INTEGER ids, name-keyed, hashed.
141 const applied = yield* queryAll<{
142 id: number;
143 name: string;
144 hash: string;
145 }>(
146 accountId,
147 database.databaseId,
148 "SELECT id, name, hash FROM __alchemy_migrations ORDER BY id;",
149 );
150 expect(applied.map((r) => ({ id: r.id, name: r.name }))).toEqual([
151 { id: 1, name: "0001_users.sql" },
152 { id: 2, name: "0002_posts.sql" },
153 ]);
154 expect(applied[0].hash).toMatch(/^[0-9a-f]{64}$/);
155
156 // Adding a new migration on update should apply only the new one and the
157 // sequential id should continue from where it left off.
158 yield* fs.writeFileString(
159 path.join(migrationsDir, "0003_comments.sql"),
160 "CREATE TABLE comments (id INTEGER PRIMARY KEY, body TEXT NOT NULL);",
161 );
162
163 const updated = yield* stack.deploy(
164 Effect.gen(function* () {
165 return yield* Cloudflare.D1.Database("MigrationDatabase", {
166 migrations: migrationsDir,
167 });
168 }),
169 );
170 expect(updated.databaseId).toEqual(database.databaseId);
171
172 const tablesAfter = yield* listTables(accountId, database.databaseId);
173 expect(tablesAfter).toContain("comments");
174
175 const appliedAfter = yield* queryAll<{ id: number; name: string }>(
176 accountId,
177 database.databaseId,
178 "SELECT id, name FROM __alchemy_migrations ORDER BY id;",
179 );
180 expect(appliedAfter).toEqual([
181 { id: 1, name: "0001_users.sql" },
182 { id: 2, name: "0002_posts.sql" },
183 { id: 3, name: "0003_comments.sql" },
184 ]);
185
186 yield* stack.destroy();
187 yield* waitForDatabaseToBeDeleted(database.databaseId, accountId);
188 }).pipe(logLevel),
189 { tags: ["provider:cloudflare", "provider:cloudflare:d1", "live"] },
190);
191
192test.provider(
193 "applies migrations using a custom migrationsTable",
194 (stack) =>
195 Effect.gen(function* () {
196 const { accountId } = yield* yield* CloudflareEnvironment;
197 const fs = yield* FileSystem.FileSystem;
198 const path = yield* Path.Path;
199 const migrationsDir = yield* fs.makeTempDirectory({
200 prefix: "alchemy-d1-custom-migrations-",
201 });
202 yield* fs.writeFileString(
203 path.join(migrationsDir, "0001_create.sql"),
204 "CREATE TABLE test_migrations_table (id INTEGER PRIMARY KEY, name TEXT);",
205 );
206
207 yield* stack.destroy();
208
209 const database = yield* stack.deploy(
210 Effect.gen(function* () {
211 return yield* Cloudflare.D1.Database("CustomMigrationsTableDb", {
212 migrations: {
213 dir: migrationsDir,
214 table: "custom_migration_tracking",
215 },
216 });
217 }),
218 );
219
220 expect(database.migrationsTable).toEqual("custom_migration_tracking");
221
222 const tables = yield* listTables(accountId, database.databaseId);
223 expect(tables).toContain("custom_migration_tracking");
224 expect(tables).toContain("test_migrations_table");
225 // The default table must NOT be created when a custom one is configured.
226 expect(tables).not.toContain("__alchemy_migrations");
227
228 yield* stack.destroy();
229 yield* waitForDatabaseToBeDeleted(database.databaseId, accountId);
230 }).pipe(logLevel),
231 { tags: ["provider:cloudflare", "provider:cloudflare:d1", "live"] },
232);
233
234/**
235 * Adopting a drizzle-kit-migrated database: the user ran `drizzle-kit
236 * migrate` before Alchemy ever saw the database, leaving history in
237 * `__drizzle_migrations`. First deploy performs the one-way conversion —
238 * history copies into `__alchemy_migrations` (validated against local
239 * files, hashes carried verbatim) and drizzle's table is frozen, never
240 * written or dropped.
241 */
242test.provider(
243 "adopts a drizzle-kit-migrated database via one-way conversion",
244 (stack) =>
245 Effect.gen(function* () {
246 const { accountId } = yield* yield* CloudflareEnvironment;
247 const fs = yield* FileSystem.FileSystem;
248 const path = yield* Path.Path;
249 const migrationsDir = yield* fs.makeTempDirectory({
250 prefix: "alchemy-d1-drizzle-",
251 });
252 const initSql =
253 "CREATE TABLE users (id integer PRIMARY KEY NOT NULL, name text NOT NULL);\n--> statement-breakpoint\nCREATE UNIQUE INDEX users_name_unique ON users (name);";
254 yield* fs.makeDirectory(path.join(migrationsDir, "20240101000000_init"));
255 yield* fs.writeFileString(
256 path.join(migrationsDir, "20240101000000_init", "migration.sql"),
257 initSql,
258 );
259 const [initRecord] = yield* hashMigrations(migrationsDir).pipe(
260 Effect.map((hashes) => Object.values(hashes)),
261 );
262
263 yield* stack.destroy();
264
265 // Phase 1: what `drizzle-kit migrate` left behind — deploy without
266 // migrations, then hand-write drizzle's table + the applied schema.
267 const seeded = yield* stack.deploy(
268 Effect.gen(function* () {
269 return yield* Cloudflare.D1.Database("DrizzleAdoptionDb");
270 }),
271 );
272 yield* execSql(
273 accountId,
274 seeded.databaseId,
275 "CREATE TABLE users (id integer PRIMARY KEY NOT NULL, name text NOT NULL);",
276 );
277 yield* execSql(
278 accountId,
279 seeded.databaseId,
280 "CREATE UNIQUE INDEX users_name_unique ON users (name);",
281 );
282 yield* execSql(
283 accountId,
284 seeded.databaseId,
285 `CREATE TABLE __drizzle_migrations (
286 id INTEGER PRIMARY KEY,
287 hash text NOT NULL,
288 created_at numeric,
289 name text,
290 applied_at TEXT
291 );`,
292 );
293 yield* execSql(
294 accountId,
295 seeded.databaseId,
296 `INSERT INTO __drizzle_migrations (hash, created_at, name, applied_at) VALUES
297 ('${initRecord}', 1704067200000, '20240101000000_init', '2024-01-01T00:00:00.000Z');`,
298 );
299
300 // Phase 2: first Alchemy deploy with migrations + a new pending one.
301 yield* fs.makeDirectory(path.join(migrationsDir, "20240102000000_posts"));
302 yield* fs.writeFileString(
303 path.join(migrationsDir, "20240102000000_posts", "migration.sql"),
304 "CREATE TABLE posts (id integer PRIMARY KEY NOT NULL, title text NOT NULL);",
305 );
306 const database = yield* stack.deploy(
307 Effect.gen(function* () {
308 return yield* Cloudflare.D1.Database("DrizzleAdoptionDb", {
309 migrations: migrationsDir,
310 });
311 }),
312 );
313 expect(database.databaseId).toEqual(seeded.databaseId);
314 expect(database.migrationsTable).toEqual("__alchemy_migrations");
315
316 // History converted (hash carried verbatim), only the pending
317 // migration ran (a replay of init's bare CREATE TABLE would fail).
318 const applied = yield* queryAll<{ name: string; hash: string }>(
319 accountId,
320 database.databaseId,
321 "SELECT name, hash FROM __alchemy_migrations ORDER BY id;",
322 );
323 expect(applied.map((r) => r.name)).toEqual([
324 "20240101000000_init",
325 "20240102000000_posts",
326 ]);
327 expect(applied[0].hash).toBe(initRecord);
328 const tables = yield* listTables(accountId, database.databaseId);
329 expect(tables).toContain("posts");
330
331 // One-way: drizzle's table is frozen, not dropped, not extended.
332 const frozen = yield* queryAll<{ name: string }>(
333 accountId,
334 database.databaseId,
335 "SELECT name FROM __drizzle_migrations ORDER BY id;",
336 );
337 expect(frozen.map((r) => r.name)).toEqual(["20240101000000_init"]);
338
339 yield* stack.destroy();
340 yield* waitForDatabaseToBeDeleted(database.databaseId, accountId);
341 }).pipe(logLevel),
342 { tags: ["provider:cloudflare", "provider:cloudflare:d1", "live"] },
343);
344
345/**
346 * Adopting a wrangler-migrated database: `wrangler d1 migrations apply`
347 * left its real table behind; the first Alchemy deploy converts that
348 * history into `__alchemy_migrations` (hashes backfilled from local
349 * files) and freezes wrangler's table.
350 */
351test.provider(
352 "adopts a wrangler-migrated database via one-way conversion",
353 (stack) =>
354 Effect.gen(function* () {
355 const { accountId } = yield* yield* CloudflareEnvironment;
356 const fs = yield* FileSystem.FileSystem;
357 const path = yield* Path.Path;
358 const migrationsDir = yield* fs.makeTempDirectory({
359 prefix: "alchemy-d1-wrangler-",
360 });
361 yield* fs.writeFileString(
362 path.join(migrationsDir, "0001_users.sql"),
363 "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
364 );
365 yield* fs.writeFileString(
366 path.join(migrationsDir, "0002_posts.sql"),
367 "CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL);",
368 );
369
370 yield* stack.destroy();
371
372 // Phase 1: what `wrangler d1 migrations apply` left behind.
373 const seeded = yield* stack.deploy(
374 Effect.gen(function* () {
375 return yield* Cloudflare.D1.Database("WranglerAdoptionDb");
376 }),
377 );
378 yield* execSql(
379 accountId,
380 seeded.databaseId,
381 "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
382 );
383 yield* execSql(
384 accountId,
385 seeded.databaseId,
386 `CREATE TABLE d1_migrations(
387 id INTEGER PRIMARY KEY AUTOINCREMENT,
388 name TEXT UNIQUE,
389 applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
390 );`,
391 );
392 yield* execSql(
393 accountId,
394 seeded.databaseId,
395 "INSERT INTO d1_migrations (name) VALUES ('0001_users.sql');",
396 );
397
398 // Phase 2: first Alchemy deploy — converts, applies only 0002.
399 const database = yield* stack.deploy(
400 Effect.gen(function* () {
401 return yield* Cloudflare.D1.Database("WranglerAdoptionDb", {
402 migrations: migrationsDir,
403 });
404 }),
405 );
406 expect(database.databaseId).toEqual(seeded.databaseId);
407 expect(database.migrationsTable).toEqual("__alchemy_migrations");
408
409 const applied = yield* queryAll<{ name: string; hash: string }>(
410 accountId,
411 database.databaseId,
412 "SELECT name, hash FROM __alchemy_migrations ORDER BY id;",
413 );
414 expect(applied.map((r) => r.name)).toEqual([
415 "0001_users.sql",
416 "0002_posts.sql",
417 ]);
418 expect(applied[0].hash).toMatch(/^[0-9a-f]{64}$/);
419 const tables = yield* listTables(accountId, database.databaseId);
420 expect(tables).toContain("posts");
421
422 // wrangler's table is frozen.
423 const frozen = yield* queryAll<{ name: string }>(
424 accountId,
425 database.databaseId,
426 "SELECT name FROM d1_migrations ORDER BY id;",
427 );
428 expect(frozen.map((r) => r.name)).toEqual(["0001_users.sql"]);
429
430 yield* stack.destroy();
431 yield* waitForDatabaseToBeDeleted(database.databaseId, accountId);
432 }).pipe(logLevel),
433 { tags: ["provider:cloudflare", "provider:cloudflare:d1", "live"] },
434);
435
436/**
437 * True roll-forward: state row AND physical table are rewritten to exactly
438 * what pre-registry Alchemy persisted (3-column `id TEXT PK` table,
439 * `migrationsHashes` + `migrationsTable` in state), then a normal deploy
440 * runs on top. The persisted table name keeps winning (legacy deploys stay
441 * on `d1_migrations`), the table upgrades in place to Alchemy's shape, and
442 * nothing replays (0001/0002's bare CREATE TABLEs would fail).
443 */
444test.provider(
445 "rolls forward from pre-registry state and 3-column table without replaying",
446 (stack) =>
447 Effect.gen(function* () {
448 const { accountId } = yield* yield* CloudflareEnvironment;
449 const fs = yield* FileSystem.FileSystem;
450 const path = yield* Path.Path;
451 const migrationsDir = yield* fs.makeTempDirectory({
452 prefix: "alchemy-d1-rollforward-",
453 });
454 yield* fs.writeFileString(
455 path.join(migrationsDir, "0001_users.sql"),
456 "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
457 );
458 yield* fs.writeFileString(
459 path.join(migrationsDir, "0002_posts.sql"),
460 "CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL);",
461 );
462
463 yield* stack.destroy();
464
465 // Phase 1: deploy so the database, tables, and state exist.
466 const deployed = yield* stack.deploy(
467 Effect.gen(function* () {
468 return yield* Cloudflare.D1.Database("RollForwardDb", {
469 migrations: migrationsDir,
470 });
471 }),
472 );
473
474 // Phase 2: rewrite BOTH artifacts to the pre-registry shape.
475 // 2a. The physical table: drop the modern one, recreate the old
476 // 3-column `id TEXT PK` shape under the old default name.
477 yield* execSql(
478 accountId,
479 deployed.databaseId,
480 "DROP TABLE __alchemy_migrations;",
481 );
482 yield* execSql(
483 accountId,
484 deployed.databaseId,
485 `CREATE TABLE d1_migrations (
486 id TEXT PRIMARY KEY,
487 name TEXT NOT NULL,
488 applied_at TEXT NOT NULL
489 );`,
490 );
491 yield* execSql(
492 accountId,
493 deployed.databaseId,
494 `INSERT INTO d1_migrations (id, name, applied_at) VALUES
495 ('00001', '0001_users.sql', '2024-01-01 00:00:00'),
496 ('00002', '0002_posts.sql', '2024-01-01 00:01:00');`,
497 );
498 // 2b. The state row: exactly what old code persisted — table name
499 // pinned to d1_migrations (the old D1 default).
500 yield* Effect.gen(function* () {
501 const state = yield* yield* State;
502 const row = yield* state.get({
503 stack: stack.name,
504 stage: stack.stage,
505 fqn: "RollForwardDb",
506 });
507 const attr = {
508 ...(row as any).attr,
509 migrationsTable: "d1_migrations",
510 };
511 yield* state.set({
512 stack: stack.name,
513 stage: stack.stage,
514 fqn: "RollForwardDb",
515 value: { ...(row as any), attr },
516 });
517 }).pipe(Effect.provide(stack.state));
518
519 // Phase 3: add a migration and redeploy on top of the legacy state.
520 yield* fs.writeFileString(
521 path.join(migrationsDir, "0003_comments.sql"),
522 "CREATE TABLE comments (id INTEGER PRIMARY KEY, body TEXT NOT NULL);",
523 );
524 const rolled = yield* stack.deploy(
525 Effect.gen(function* () {
526 return yield* Cloudflare.D1.Database("RollForwardDb", {
527 migrations: migrationsDir,
528 });
529 }),
530 );
531 expect(rolled.databaseId).toEqual(deployed.databaseId);
532 // The persisted table name keeps winning for legacy deploys.
533 expect(rolled.migrationsTable).toEqual("d1_migrations");
534
535 const columns = yield* queryAll<{ name: string; type: string }>(
536 accountId,
537 deployed.databaseId,
538 "PRAGMA table_info(d1_migrations);",
539 );
540 // Upgraded in place to Alchemy's shape.
541 expect(columns.map((c) => c.name)).toEqual([
542 "id",
543 "hash",
544 "created_at",
545 "name",
546 "applied_at",
547 ]);
548 expect(columns.find((c) => c.name === "id")?.type).toBe("INTEGER");
549 const rows = yield* queryAll<{ id: number; name: string }>(
550 accountId,
551 deployed.databaseId,
552 "SELECT id, name FROM d1_migrations ORDER BY id;",
553 );
554 expect(rows).toEqual([
555 { id: 1, name: "0001_users.sql" },
556 { id: 2, name: "0002_posts.sql" },
557 { id: 3, name: "0003_comments.sql" },
558 ]);
559 const tables = yield* listTables(accountId, deployed.databaseId);
560 expect(tables).toContain("comments");
561
562 yield* stack.destroy();
563 yield* waitForDatabaseToBeDeleted(deployed.databaseId, accountId);
564 }).pipe(logLevel),
565 { tags: ["provider:cloudflare", "provider:cloudflare:d1", "live"] },
566);
567
568/**
569 * The Seth-shaped roll-forward: a drizzle-LAYOUT directory whose history
570 * was applied by pre-registry Alchemy under flat keys
571 * (`<dir>/migration.sql`) in the legacy table. The persisted table name
572 * must keep winning — starting a second bookkeeping table would replay
573 * history into a live database.
574 */
575test.provider(
576 "rolls forward legacy state over a drizzle-layout dir without switching tables",
577 (stack) =>
578 Effect.gen(function* () {
579 const { accountId } = yield* yield* CloudflareEnvironment;
580 const fs = yield* FileSystem.FileSystem;
581 const path = yield* Path.Path;
582 const migrationsDir = yield* fs.makeTempDirectory({
583 prefix: "alchemy-d1-rollforward-drizzle-",
584 });
585 yield* fs.makeDirectory(path.join(migrationsDir, "20240101000000_init"));
586 yield* fs.writeFileString(
587 path.join(migrationsDir, "20240101000000_init", "migration.sql"),
588 "CREATE TABLE users (id integer PRIMARY KEY NOT NULL, name text NOT NULL);",
589 );
590
591 yield* stack.destroy();
592
593 // Phase 1: deploy WITHOUT migrations, then hand-write what old
594 // Alchemy left behind: migration 1 really ran, recorded under its
595 // flat path key in the legacy 3-column table.
596 const deployed = yield* stack.deploy(
597 Effect.gen(function* () {
598 return yield* Cloudflare.D1.Database("RollForwardDrizzleDb");
599 }),
600 );
601 yield* execSql(
602 accountId,
603 deployed.databaseId,
604 "CREATE TABLE users (id integer PRIMARY KEY NOT NULL, name text NOT NULL);",
605 );
606 yield* execSql(
607 accountId,
608 deployed.databaseId,
609 `CREATE TABLE d1_migrations (
610 id TEXT PRIMARY KEY,
611 name TEXT NOT NULL,
612 applied_at TEXT NOT NULL
613 );`,
614 );
615 yield* execSql(
616 accountId,
617 deployed.databaseId,
618 `INSERT INTO d1_migrations (id, name, applied_at) VALUES
619 ('00001', '20240101000000_init/migration.sql', '2024-01-01 00:00:00');`,
620 );
621 // Old-style state: dir + table + hashes, no format stamp.
622 const legacyHashes = yield* hashMigrations(migrationsDir);
623 yield* Effect.gen(function* () {
624 const state = yield* yield* State;
625 const row = yield* state.get({
626 stack: stack.name,
627 stage: stack.stage,
628 fqn: "RollForwardDrizzleDb",
629 });
630 const attr = {
631 ...(row as any).attr,
632 migrationsDir,
633 migrationsTable: "d1_migrations",
634 migrationsHashes: legacyHashes,
635 };
636 yield* state.set({
637 stack: stack.name,
638 stage: stack.stage,
639 fqn: "RollForwardDrizzleDb",
640 value: { ...(row as any), attr },
641 });
642 }).pipe(Effect.provide(stack.state));
643
644 // Phase 2: add a second drizzle-layout migration and redeploy WITH
645 // migrations configured.
646 yield* fs.makeDirectory(path.join(migrationsDir, "20240102000000_posts"));
647 yield* fs.writeFileString(
648 path.join(migrationsDir, "20240102000000_posts", "migration.sql"),
649 "CREATE TABLE posts (id integer PRIMARY KEY NOT NULL, title text NOT NULL);",
650 );
651 const rolled = yield* stack.deploy(
652 Effect.gen(function* () {
653 return yield* Cloudflare.D1.Database("RollForwardDrizzleDb", {
654 migrations: migrationsDir,
655 });
656 }),
657 );
658 expect(rolled.databaseId).toEqual(deployed.databaseId);
659 // The persisted table name keeps winning; no second bookkeeping
660 // table appears.
661 expect(rolled.migrationsTable).toEqual("d1_migrations");
662
663 const tables = yield* listTables(accountId, deployed.databaseId);
664 expect(tables).not.toContain("__drizzle_migrations");
665 expect(tables).toContain("posts");
666
667 // Migration 1 was not replayed (bare CREATE TABLE would fail);
668 // migration 2 was recorded under the same flat-path convention.
669 const rows = yield* queryAll<{ name: string }>(
670 accountId,
671 deployed.databaseId,
672 "SELECT name FROM d1_migrations ORDER BY id;",
673 );
674 expect(rows.map((r) => r.name)).toEqual([
675 // The legacy row keeps its flat key; new records use the current
676 // directory-name convention. Aliasing keeps both recognized.
677 "20240101000000_init/migration.sql",
678 "20240102000000_posts",
679 ]);
680
681 yield* stack.destroy();
682 yield* waitForDatabaseToBeDeleted(deployed.databaseId, accountId);
683 }).pipe(logLevel),
684 { tags: ["provider:cloudflare", "provider:cloudflare:d1", "live"] },
685);
686
687/**
688 * State-lost adoption of the OLDEST pre-registry deploys: a 2-column
689 * `d1_migrations` exists but state carries no table name. The legacy table
690 * is treated as a conversion source — history copies into
691 * `__alchemy_migrations` and the old table is frozen, exactly like a
692 * foreign tool's table.
693 */
694test.provider(
695 "converts a legacy 2-column d1_migrations table into __alchemy_migrations",
696 (stack) =>
697 Effect.gen(function* () {
698 const { accountId } = yield* yield* CloudflareEnvironment;
699 const fs = yield* FileSystem.FileSystem;
700 const path = yield* Path.Path;
701 const migrationsDir = yield* fs.makeTempDirectory({
702 prefix: "alchemy-d1-legacy-",
703 });
704 // History validation is strict (drizzle's own upgrade policy): every
705 // recorded row must match a local file, so the two "previously
706 // applied" migrations exist in the dir. Their bare CREATE TABLEs
707 // would fail if replayed, which is exactly what the upgrade must not
708 // do.
709 yield* fs.writeFileString(
710 path.join(migrationsDir, "0000_initial_setup.sql"),
711 "CREATE TABLE settings (id INTEGER PRIMARY KEY, value TEXT);",
712 );
713 yield* fs.writeFileString(
714 path.join(migrationsDir, "0001_add_indexes.sql"),
715 "CREATE INDEX settings_value_idx ON settings (value);",
716 );
717 yield* fs.writeFileString(
718 path.join(migrationsDir, "0002_create_users.sql"),
719 "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
720 );
721 yield* fs.writeFileString(
722 path.join(migrationsDir, "0003_create_posts.sql"),
723 "CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL);",
724 );
725
726 yield* stack.destroy();
727
728 // Step 1: deploy the database without migrations so we can seed a legacy
729 // 2-column d1_migrations table on it before re-deploying with a
730 // migrationsDir.
731 const seeded = yield* stack.deploy(
732 Effect.gen(function* () {
733 return yield* Cloudflare.D1.Database("LegacyMigrationDb");
734 }),
735 );
736
737 yield* execSql(
738 accountId,
739 seeded.databaseId,
740 `CREATE TABLE d1_migrations (
741 id TEXT PRIMARY KEY,
742 applied_at TEXT NOT NULL
743 );`,
744 );
745 yield* execSql(
746 accountId,
747 seeded.databaseId,
748 `INSERT INTO d1_migrations (id, applied_at) VALUES
749 ('0000_initial_setup.sql', datetime('now', '-1 day')),
750 ('0001_add_indexes.sql', datetime('now', '-1 hour'));`,
751 );
752
753 // Step 2: deploy again with a migrationsDir. No table name is
754 // persisted in state, so the legacy table becomes a conversion
755 // source: history copies into __alchemy_migrations, only the two
756 // pending migrations run (0000/0001's SQL would fail if replayed
757 // out of order against the frozen history's assumptions).
758 const upgraded = yield* stack.deploy(
759 Effect.gen(function* () {
760 return yield* Cloudflare.D1.Database("LegacyMigrationDb", {
761 migrations: migrationsDir,
762 });
763 }),
764 );
765 expect(upgraded.databaseId).toEqual(seeded.databaseId);
766 expect(upgraded.migrationsTable).toEqual("__alchemy_migrations");
767
768 const columns = yield* queryAll<{ name: string; type: string }>(
769 accountId,
770 seeded.databaseId,
771 "PRAGMA table_info(__alchemy_migrations);",
772 );
773 expect(columns.map((c) => c.name)).toEqual([
774 "id",
775 "hash",
776 "created_at",
777 "name",
778 "applied_at",
779 ]);
780
781 const records = yield* queryAll<{ id: number; name: string }>(
782 accountId,
783 seeded.databaseId,
784 "SELECT id, name FROM __alchemy_migrations ORDER BY id;",
785 );
786 expect(records.map((r) => r.name)).toEqual([
787 "0000_initial_setup.sql",
788 "0001_add_indexes.sql",
789 "0002_create_users.sql",
790 "0003_create_posts.sql",
791 ]);
792 // Ids are sequential ordinals preserving apply order.
793 expect(records.map((r) => r.id)).toEqual(records.map((_, i) => i + 1));
794
795 // The legacy table is frozen, never dropped or rewritten.
796 const frozen = yield* queryAll<{ id: string }>(
797 accountId,
798 seeded.databaseId,
799 "SELECT id FROM d1_migrations ORDER BY id;",
800 );
801 expect(frozen.map((r) => r.id)).toEqual([
802 "0000_initial_setup.sql",
803 "0001_add_indexes.sql",
804 ]);
805
806 yield* stack.destroy();
807 yield* waitForDatabaseToBeDeleted(seeded.databaseId, accountId);
808 }).pipe(logLevel),
809 { tags: ["provider:cloudflare", "provider:cloudflare:d1", "live"] },
810);
811
812test.provider(
813 "imports SQL files via importFiles",
814 (stack) =>
815 Effect.gen(function* () {
816 const { accountId } = yield* yield* CloudflareEnvironment;
817 const fs = yield* FileSystem.FileSystem;
818 const path = yield* Path.Path;
819 const dir = yield* fs.makeTempDirectory({
820 prefix: "alchemy-d1-imports-",
821 });
822 const importPath = path.join(dir, "seed.sql");
823
824 yield* fs.writeFileString(
825 importPath,
826 [
827 "CREATE TABLE widgets (id INTEGER PRIMARY KEY, label TEXT NOT NULL);",
828 "INSERT INTO widgets (id, label) VALUES (1, 'one');",
829 "INSERT INTO widgets (id, label) VALUES (2, 'two');",
830 ].join("\n"),
831 );
832
833 yield* stack.destroy();
834
835 const database = yield* stack.deploy(
836 Effect.gen(function* () {
837 return yield* Cloudflare.D1.Database("ImportDatabase", {
838 importFiles: [importPath],
839 });
840 }),
841 );
842
843 expect(database.importHashes[importPath]).toBeDefined();
844
845 const widgets = yield* getResults<{ id: number; label: string }>(
846 accountId,
847 database.databaseId,
848 "SELECT id, label FROM widgets ORDER BY id;",
849 );
850 expect(widgets).toEqual([
851 { id: 1, label: "one" },
852 { id: 2, label: "two" },
853 ]);
854
855 yield* stack.destroy();
856 yield* waitForDatabaseToBeDeleted(database.databaseId, accountId);
857 }).pipe(logLevel),
858 { tags: ["provider:cloudflare", "provider:cloudflare:d1", "live"] },
859);
860
861test.provider(
862 "clones a database by databaseId",
863 (stack) =>
864 Effect.gen(function* () {
865 const { accountId } = yield* yield* CloudflareEnvironment;
866 const fs = yield* FileSystem.FileSystem;
867 const path = yield* Path.Path;
868 const dir = yield* fs.makeTempDirectory({
869 prefix: "alchemy-d1-clone-id-",
870 });
871 const seedPath = path.join(dir, "seed.sql");
872
873 yield* fs.writeFileString(
874 seedPath,
875 [
876 "CREATE TABLE colors (id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
877 "INSERT INTO colors (id, name) VALUES (1, 'red'), (2, 'green'), (3, 'blue');",
878 ].join("\n"),
879 );
880
881 yield* stack.destroy();
882
883 const { source, target } = yield* stack.deploy(
884 Effect.gen(function* () {
885 const source = yield* Cloudflare.D1.Database("CloneByIdSource", {
886 importFiles: [seedPath],
887 });
888 const target = yield* Cloudflare.D1.Database("CloneByIdTarget", {
889 clone: { databaseId: source.databaseId },
890 });
891 return { source, target };
892 }),
893 );
894
895 expect(target.databaseId).not.toEqual(source.databaseId);
896
897 const targetColors = yield* getResults<{ id: number; name: string }>(
898 accountId,
899 target.databaseId,
900 "SELECT id, name FROM colors ORDER BY id;",
901 );
902 expect(targetColors).toEqual([
903 { id: 1, name: "red" },
904 { id: 2, name: "green" },
905 { id: 3, name: "blue" },
906 ]);
907
908 yield* stack.destroy();
909 yield* waitForDatabaseToBeDeleted(source.databaseId, accountId);
910 yield* waitForDatabaseToBeDeleted(target.databaseId, accountId);
911 }).pipe(logLevel),
912 { tags: ["provider:cloudflare", "provider:cloudflare:d1", "live"] },
913);
914
915test.provider(
916 "clones a database by name lookup",
917 (stack) =>
918 Effect.gen(function* () {
919 const { accountId } = yield* yield* CloudflareEnvironment;
920 const fs = yield* FileSystem.FileSystem;
921 const path = yield* Path.Path;
922 const dir = yield* fs.makeTempDirectory({
923 prefix: "alchemy-d1-clone-name-",
924 });
925 const seedPath = path.join(dir, "seed.sql");
926
927 yield* fs.writeFileString(
928 seedPath,
929 [
930 "CREATE TABLE animals (id INTEGER PRIMARY KEY, kind TEXT NOT NULL);",
931 "INSERT INTO animals (id, kind) VALUES (1, 'cat'), (2, 'dog');",
932 ].join("\n"),
933 );
934
935 yield* stack.destroy();
936
937 const { source, target } = yield* stack.deploy(
938 Effect.gen(function* () {
939 const source = yield* Cloudflare.D1.Database("CloneByNameSource", {
940 importFiles: [seedPath],
941 });
942 const target = yield* Cloudflare.D1.Database("CloneByNameTarget", {
943 clone: { name: source.databaseName },
944 });
945 return { source, target };
946 }),
947 );
948
949 expect(target.databaseId).not.toEqual(source.databaseId);
950
951 const animals = yield* getResults<{ id: number; kind: string }>(
952 accountId,
953 target.databaseId,
954 "SELECT id, kind FROM animals ORDER BY id;",
955 );
956 expect(animals).toEqual([
957 { id: 1, kind: "cat" },
958 { id: 2, kind: "dog" },
959 ]);
960
961 yield* stack.destroy();
962 yield* waitForDatabaseToBeDeleted(source.databaseId, accountId);
963 yield* waitForDatabaseToBeDeleted(target.databaseId, accountId);
964 }).pipe(logLevel),
965 { tags: ["provider:cloudflare", "provider:cloudflare:d1", "live"] },
966);
967
968test.provider(
969 "clones a database by passing the source resource directly",
970 (stack) =>
971 Effect.gen(function* () {
972 const { accountId } = yield* yield* CloudflareEnvironment;
973 const fs = yield* FileSystem.FileSystem;
974 const path = yield* Path.Path;
975 const dir = yield* fs.makeTempDirectory({
976 prefix: "alchemy-d1-clone-direct-",
977 });
978 const seedPath = path.join(dir, "seed.sql");
979
980 yield* fs.writeFileString(
981 seedPath,
982 [
983 "CREATE TABLE shapes (id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
984 "INSERT INTO shapes (id, name) VALUES (1, 'square'), (2, 'circle');",
985 ].join("\n"),
986 );
987
988 yield* stack.destroy();
989
990 const { source, target } = yield* stack.deploy(
991 Effect.gen(function* () {
992 const source = yield* Cloudflare.D1.Database("CloneDirectSource", {
993 importFiles: [seedPath],
994 });
995 const target = yield* Cloudflare.D1.Database("CloneDirectTarget", {
996 clone: source,
997 });
998 return { source, target };
999 }),
1000 );
1001
1002 expect(target.databaseId).not.toEqual(source.databaseId);
1003
1004 const shapes = yield* getResults<{ id: number; name: string }>(
1005 accountId,
1006 target.databaseId,
1007 "SELECT id, name FROM shapes ORDER BY id;",
1008 );
1009 expect(shapes).toEqual([
1010 { id: 1, name: "square" },
1011 { id: 2, name: "circle" },
1012 ]);
1013
1014 yield* stack.destroy();
1015 yield* waitForDatabaseToBeDeleted(source.databaseId, accountId);
1016 yield* waitForDatabaseToBeDeleted(target.databaseId, accountId);
1017 }).pipe(logLevel),
1018 { tags: ["provider:cloudflare", "provider:cloudflare:d1", "live"] },
1019);
1020
1021const queryAll = Effect.fn(function* <T>(
1022 accountId: string,
1023 databaseId: string,
1024 sql: string,
1025) {
1026 const queryDb = yield* d1.queryDatabase;
1027 const result = yield* queryDb({ accountId, databaseId, sql });
1028 return (result.result[0]?.results ?? []) as T[];
1029});
1030
1031const execSql = (accountId: string, databaseId: string, sql: string) =>
1032 queryAll<unknown>(accountId, databaseId, sql);
1033
1034const listTables = Effect.fn(function* (accountId: string, databaseId: string) {
1035 const rows = yield* queryAll<{ name: string }>(
1036 accountId,
1037 databaseId,
1038 "SELECT name FROM sqlite_master WHERE type='table';",
1039 );
1040 return rows.map((r) => r.name);
1041});
1042
1043/**
1044 * D1 query results are eventually consistent following an import/clone, so
1045 * retry until we see at least one row (matches v1's `getResults` helper).
1046 */
1047const getResults = Effect.fn(function* <T>(
1048 accountId: string,
1049 databaseId: string,
1050 sql: string,
1051) {
1052 return yield* queryAll<T>(accountId, databaseId, sql).pipe(
1053 Effect.flatMap((rows) =>
1054 rows.length > 0
1055 ? Effect.succeed(rows)
1056 : Effect.fail(new EmptyResults({ sql })),
1057 ),
1058 Effect.retry({
1059 while: (e) => e instanceof EmptyResults,
1060 schedule: Schedule.max([
1061 Schedule.spaced(Duration.seconds(1)),
1062 Schedule.recurs(10),
1063 ]),
1064 }),
1065 Effect.orDie,
1066 );
1067});
1068
1069// Engine-level adoption: D1 databases have no ownership signal (Cloudflare
1070// doesn't expose tags on D1), so a name match in `read` is treated as silent
1071// adoption.
1072test.provider(
1073 "existing database (matching name) is silently adopted without --adopt",
1074 (stack) =>
1075 Effect.gen(function* () {
1076 const { accountId } = yield* yield* CloudflareEnvironment;
1077
1078 yield* stack.destroy();
1079
1080 // Phase 1: deploy normally so a real D1 database exists. No explicit
1081 // `name` — the engine generates a random-suffixed physical name
1082 // (collision-free across concurrent runs); the deploy output hands
1083 // back the real name, which pins the database's identity for the
1084 // adoption phase below.
1085 const initial = yield* stack.deploy(
1086 Effect.gen(function* () {
1087 return yield* Cloudflare.D1.Database("AdoptableDatabase");
1088 }),
1089 );
1090 const databaseName = initial.databaseName;
1091 const initialId = initial.databaseId;
1092
1093 // Phase 2: wipe local state — the database stays on Cloudflare.
1094 yield* Effect.gen(function* () {
1095 const state = yield* yield* State;
1096 yield* state.delete({
1097 stack: stack.name,
1098 stage: stack.stage,
1099 fqn: "AdoptableDatabase",
1100 });
1101 }).pipe(Effect.provide(stack.state));
1102
1103 // Phase 3: redeploy without `adopt(true)`. The engine calls
1104 // `provider.read`, which lists databases by name and returns plain
1105 // attrs — silent adoption.
1106 const adopted = yield* stack.deploy(
1107 Effect.gen(function* () {
1108 return yield* Cloudflare.D1.Database("AdoptableDatabase", {
1109 name: databaseName,
1110 });
1111 }),
1112 );
1113
1114 // Same physical database — adoption, not re-creation.
1115 expect(adopted.databaseId).toEqual(initialId);
1116 expect(adopted.databaseName).toEqual(databaseName);
1117
1118 const persisted = yield* Effect.gen(function* () {
1119 const state = yield* yield* State;
1120 return yield* state.get({
1121 stack: stack.name,
1122 stage: stack.stage,
1123 fqn: "AdoptableDatabase",
1124 });
1125 }).pipe(Effect.provide(stack.state));
1126
1127 expect((persisted as any)?.attr).toMatchObject({
1128 databaseId: initialId,
1129 databaseName,
1130 });
1131
1132 yield* stack.destroy();
1133 yield* waitForDatabaseToBeDeleted(initialId, accountId);
1134 }).pipe(logLevel),
1135 { tags: ["provider:cloudflare", "provider:cloudflare:d1", "live"] },
1136);
1137
1138const waitForDatabaseToBeDeleted = Effect.fn(function* (
1139 databaseId: string,
1140 accountId: string,
1141) {
1142 yield* d1
1143 .getDatabase({
1144 accountId,
1145 databaseId,
1146 })
1147 .pipe(
1148 Effect.flatMap(() => Effect.fail(new DatabaseStillExists())),
1149 Effect.retry({
1150 while: (e): e is DatabaseStillExists =>
1151 e instanceof DatabaseStillExists,
1152 schedule: Schedule.exponential(100),
1153 }),
1154 Effect.catchTag("DatabaseNotFound", () => Effect.void),
1155 );
1156});
1157
1158class DatabaseStillExists extends Data.TaggedError("DatabaseStillExists") {}
1159class EmptyResults extends Data.TaggedError("EmptyResults")<{
1160 sql: string;
1161}> {}