1import * as Cloudflare from "@/Cloudflare";
2import * as Alchemy from "@/index.ts";
3import * as Test from "@/Test/Alchemy";
4import { describe, expect } from "alchemy-test";
5import * as Data from "effect/Data";
6import * as Effect from "effect/Effect";
7import * as Schedule from "effect/Schedule";
8import * as HttpClient from "effect/http/HttpClient";
9import type { HistoryRow } from "./fixtures/sql-migrations/object.ts";
10import SqlMigrationsWorker from "./fixtures/sql-migrations/worker.ts";
11
12type MigratedState = {
13 tag: string;
14 table: string;
15 records: { name: string; hash: string; created_at: number | null }[];
16 boots: number;
17 payloadLength: number;
18 history: HistoryRow[];
19 tables: { name: string }[];
20 users: { name: string; posts: string[] }[];
21};
22
23type CustomState = {
24 tag: string;
25 table: string;
26 names: string[];
27 history: HistoryRow[];
28 tables: { name: string }[];
29 values: string[];
30};
31
32type RollbackState = {
33 error: string | null;
34 history: HistoryRow[];
35 tables: { name: string }[];
36 values: string[];
37 names: string[];
38};
39
40type AdoptionState = {
41 before: HistoryRow[];
42 after: HistoryRow[];
43 adopted: HistoryRow[];
44 repeated: HistoryRow[];
45 names: string[];
46 users: { name: string; posts: string[] }[];
47};
48
49class WorkerNotReady extends Data.TaggedError("WorkerNotReady")<{
50 status: number;
51 message: string;
52}> {}
53
54const migrationNames = ["20240101000000_init", "20240102000000_posts"];
55const seedUsers = [{ name: "seed", posts: ["pending migration"] }];
56
57for (const dev of [true, false]) {
58 const state = dev ? Alchemy.inMemoryState() : Cloudflare.state();
59 describe(
60 `SqlMigrations (${dev ? "local" : "live"})`,
61 { tags: ["provider:cloudflare", "provider:cloudflare:worker"] },
62 () => {
63 const options = {
64 providers: Cloudflare.providers(),
65 state,
66 dev,
67 stage: dev
68 ? Test.defaultStage()
69 : `${Test.defaultStage()}_sql_migrations_live`,
70 };
71 // Leading destroy closes its harness scope; deployment needs a fresh one.
72 const cleanup = Test.make(options);
73 const { test, beforeAll, afterAll, deploy, destroy } = Test.make(options);
74 const Stack = Alchemy.Stack(
75 dev ? "SqlMigrationsLocalStack" : "SqlMigrationsLiveStack",
76 { providers: Cloudflare.providers(), state },
77 Effect.gen(function* () {
78 const worker = yield* SqlMigrationsWorker;
79 return { url: worker.url.as<string>() };
80 }),
81 );
82 cleanup.beforeAll(cleanup.destroy(Stack), { timeout: 120_000 });
83 const stack = beforeAll(
84 Effect.gen(function* () {
85 const deployed = yield* deploy(Stack);
86 expect(deployed.url).toMatch(
87 dev ? /^http:\/\/localhost:\d+/ : /^https:\/\//,
88 );
89 const client = yield* HttpClient.HttpClient;
90 // Readiness never invokes a migration or repeats an application write.
91 yield* Effect.gen(function* () {
92 const response = yield* client.get(`${deployed.url}/health`);
93 const body = yield* response.text;
94 if (response.status !== 200 || body !== "sql-migrations:ready") {
95 return yield* Effect.fail(
96 new WorkerNotReady({
97 status: response.status,
98 message: `GET ${deployed.url}/health: ${response.status}: ${body}`,
99 }),
100 );
101 }
102 }).pipe(
103 Effect.timeout("2 seconds"),
104 Effect.tapError((error) =>
105 Effect.logWarning("SQL Worker readiness", error),
106 ),
107 Effect.retry({ schedule: Schedule.spaced("3 seconds"), times: 10 }),
108 Effect.timeout("45 seconds"),
109 );
110 return deployed;
111 }),
112 { timeout: 120_000 },
113 );
114 afterAll(destroy(Stack), { timeout: 120_000 });
115
116 const json = <A>(method: "GET" | "POST", path: string) =>
117 Effect.gen(function* () {
118 const { url } = yield* stack;
119 const client = yield* HttpClient.HttpClient;
120 const response = yield* method === "GET"
121 ? client.get(`${url}${path}`)
122 : client.post(`${url}${path}`);
123 if (response.status !== 200) {
124 return yield* Effect.fail(
125 new Error(
126 `${method} ${url}${path}: ${response.status}: ${yield* response.text}`,
127 ),
128 );
129 }
130 return (yield* response.json) as A;
131 });
132 const read = (name: string) =>
133 json<MigratedState>("GET", `/state?name=${name}`);
134
135 test(
136 "activation applies modern migrations, relations, and a snapshot larger than 5 KB",
137 Effect.gen(function* () {
138 const state = yield* read("activation");
139 expect(state.tag).toBe("Cloudflare.SqlMigrations");
140 expect(state.table).toBe("__alchemy_migrations");
141 expect(state.boots).toBe(1);
142 expect(state.payloadLength).toBe(6144);
143 expect(state.users).toEqual(seedUsers);
144 expect(state.records.map((record) => record.name)).toEqual(
145 migrationNames,
146 );
147 expect(state.history).toHaveLength(2);
148 expect(
149 state.history.map(({ name, hash, created_at }) => ({
150 name,
151 hash,
152 created_at,
153 })),
154 ).toEqual(state.records);
155 for (const row of state.history) {
156 expect(row.hash).toMatch(/^[a-f0-9]{64}$/);
157 expect(row.applied_at).not.toBeNull();
158 }
159 expect(state.tables.map((table) => table.name)).toContain(
160 "__alchemy_migrations",
161 );
162 expect(state.tables.map((table) => table.name)).not.toContain(
163 "__drizzle_migrations",
164 );
165 }),
166 { tags: [...(dev ? ["local"] : ["live"])], timeout: 90_000 },
167 );
168
169 test(
170 "fresh object names each migrate their own isolated database",
171 Effect.gen(function* () {
172 const first = yield* read("isolation-first");
173 const second = yield* read("isolation-second");
174 expect(first.users).toEqual(seedUsers);
175 expect(second.users).toEqual(seedUsers);
176 expect(first.history.map((row) => row.name)).toEqual(migrationNames);
177 expect(second.history.map((row) => row.name)).toEqual(migrationNames);
178 yield* json("POST", "/users?name=isolation-first&user=only-first");
179 expect((yield* read("isolation-first")).users).toEqual([
180 { name: "only-first", posts: [] },
181 ...seedUsers,
182 ]);
183 expect((yield* read("isolation-second")).users).toEqual(seedUsers);
184 }),
185 { tags: [...(dev ? ["local"] : ["live"])], timeout: 90_000 },
186 );
187
188 test(
189 "repeat application and real object reactivation preserve data and history",
190 Effect.gen(function* () {
191 const before = yield* read("reactivation");
192 yield* json("POST", "/users?name=reactivation&user=preserved");
193 const repeated = yield* json<MigratedState>(
194 "POST",
195 "/repeat?name=reactivation",
196 );
197 expect(repeated.history).toEqual(before.history);
198 expect(repeated.users).toEqual([
199 { name: "preserved", posts: [] },
200 ...seedUsers,
201 ]);
202 const reset = yield* json<{ reset: boolean }>(
203 "POST",
204 "/reset?name=reactivation",
205 );
206 expect(reset.reset).toBe(true);
207 const reactivated = yield* read("reactivation");
208 expect(reactivated.boots).toBe(before.boots + 1);
209 expect(reactivated.history).toEqual(before.history);
210 expect(reactivated.users).toEqual(repeated.users);
211 expect(reactivated.payloadLength).toBe(6144);
212 }),
213 { tags: [...(dev ? ["local"] : ["live"])], timeout: 90_000 },
214 );
215
216 test(
217 "direct activation reads flat SQL files and uses only the custom history table",
218 Effect.gen(function* () {
219 const state = yield* json<CustomState>("GET", "/custom?name=custom");
220 expect(state.tag).toBe("Cloudflare.SqlMigrations");
221 expect(state.table).toBe("fixture_migrations");
222 expect(state.names).toEqual(["0001_values.sql", "0002_values.sql"]);
223 expect(state.history.map((row) => row.name)).toEqual(state.names);
224 expect(state.values).toEqual(["first", "second"]);
225 const tableNames = state.tables.map((table) => table.name);
226 expect(tableNames).toContain("fixture_migrations");
227 expect(tableNames).not.toContain("__alchemy_migrations");
228 expect(tableNames).not.toContain("__drizzle_migrations");
229 const repeated = yield* json<CustomState>(
230 "POST",
231 "/custom/repeat?name=custom",
232 );
233 expect(repeated).toEqual(state);
234 }),
235 { tags: [...(dev ? ["local"] : ["live"])], timeout: 90_000 },
236 );
237
238 test(
239 "failed SQL rolls back DML and DDL without recording the failed migration",
240 Effect.gen(function* () {
241 const failed = yield* json<RollbackState>(
242 "POST",
243 "/rollback?name=rollback",
244 );
245 expect(failed.error).toBe("MigrationError");
246 expect(failed.names).toEqual(["0001_stable.sql", "0002_broken.sql"]);
247 expect(failed.history.map((row) => row.name)).toEqual([
248 "0001_stable.sql",
249 ]);
250 expect(failed.values).toEqual(["committed"]);
251 expect(failed.tables.map((table) => table.name)).not.toContain(
252 "must_roll_back",
253 );
254 // A second explicit attempt must not duplicate the committed predecessor.
255 const repeated = yield* json<RollbackState>(
256 "POST",
257 "/rollback?name=rollback",
258 );
259 expect(repeated).toEqual(failed);
260 }),
261 { tags: [...(dev ? ["local"] : ["live"])], timeout: 90_000 },
262 );
263
264 test(
265 "adopts modern Drizzle history once, freezes its table, and applies only pending SQL",
266 Effect.gen(function* () {
267 const state = yield* json<AdoptionState>(
268 "POST",
269 "/adopt?name=adoption",
270 );
271 expect(state.names).toEqual(migrationNames);
272 expect(state.before).toHaveLength(1);
273 expect(state.before[0]!.name).toBe(migrationNames[0]);
274 expect(state.before[0]!.hash).toBe("");
275 expect(state.before[0]!.applied_at).toMatch(
276 /^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{3}Z$/,
277 );
278 expect(state.after).toEqual(state.before);
279 expect(state.adopted).toHaveLength(2);
280 expect(state.adopted[0]).toEqual(state.before[0]);
281 expect(state.adopted.map((row) => row.name)).toEqual(migrationNames);
282 expect(state.repeated).toEqual(state.adopted);
283 expect(state.users).toEqual(seedUsers);
284 }),
285 { tags: [...(dev ? ["local"] : ["live"])], timeout: 90_000 },
286 );
287
288 for (const empty of [false, true]) {
289 test(
290 `${empty ? "empty migration directory" : "unmatched Drizzle history"} rejects adoption without changing schema or history`,
291 Effect.gen(function* () {
292 const state = yield* json<{
293 error: string | null;
294 before: HistoryRow[];
295 after: HistoryRow[];
296 tables: { name: string }[];
297 users: string[];
298 }>(
299 "POST",
300 `/conflict?name=conflict-${empty}${empty ? "&empty" : ""}`,
301 );
302 expect(state.error).toBe("MigrationHistoryConflictError");
303 expect(state.before).toHaveLength(1);
304 expect(state.before[0]!.name).toBe(migrationNames[0]);
305 expect(state.after).toEqual(state.before);
306 expect(state.users).toEqual(["seed"]);
307 expect(state.tables.map((table) => table.name)).not.toContain(
308 "__alchemy_migrations",
309 );
310 expect(state.tables.map((table) => table.name)).not.toContain(
311 "posts",
312 );
313 }),
314 { tags: [...(dev ? ["local"] : ["live"])], timeout: 90_000 },
315 );
316 }
317 },
318 );
319}