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

main
ts 232 lines 9.08 KB
Raw
1 import * as crypto from "node:crypto";
2 import {
3 MigrationError,
4 migrationError,
5 runMigrations,
6 splitSqlScript,
7 type MigrationRecord,
8 type NormalizedMigrationsInput,
9 type SqlExecutor,
10 type StampedMigrationsState,
11 } from "@alchemy.run/sql/Migrations";
12 import * as spanner from "@distilled.cloud/gcp/spanner_v1";
13 import * as Effect from "effect/Effect";
14 import * as Result from "effect/Result";
15 import { waitForOperation } from "../Operation.ts";
16
17 /**
18 * Spanner names must start with a letter, so the bookkeeping table drops
19 * the default `__alchemy_migrations` prefix.
20 */
21 export const SPANNER_MIGRATIONS_TABLE = "alchemy_migrations";
22
23 type SpannerDialect = "googlesql" | "postgres";
24
25 const DDL = /^(?:CREATE|ALTER|DROP|RENAME|GRANT|REVOKE|ANALYZE)\b/i;
26
27 /** Consecutive statements of one kind: DDL goes to `updateDdl`, DML to a transaction. */
28 const groupStatements = (statements: ReadonlyArray<string>) => {
29 const groups: Array<{ ddl: boolean; statements: string[] }> = [];
30 for (const statement of statements) {
31 const ddl = DDL.test(statement);
32 const last = groups.at(-1);
33 if (last && last.ddl === ddl) last.statements.push(statement);
34 else groups.push({ ddl, statements: [statement] });
35 }
36 return groups;
37 };
38
39 /** Attempts per DDL statement whose earlier operations failed. */
40 const MAX_DDL_ATTEMPTS = 20;
41
42 /**
43 * Spanner as a {@link SqlExecutor} over one session. Spanner cannot commit
44 * DDL with DML, so DDL runs as `updateDdl` operations and each migration's
45 * trailing DML commits in one transaction with its bookkeeping row.
46 *
47 * A migration's DDL runs one statement per operation, named after the
48 * migration, the statement's position, and its text. A deploy after a
49 * crash finds the operations it already started and waits for them
50 * instead of rerunning their statements; after a failed statement is
51 * fixed, the statements before it are found applied and only the fixed
52 * one runs.
53 */
54 const makeSpannerExecutor = Effect.fn(function* (options: {
55 database: string;
56 dialect: SpannerDialect;
57 session: string;
58 }) {
59 const { database, dialect, session } = options;
60 const executeSql = yield* spanner.executeSqlProjectsInstancesDatabasesSessions;
61 const executeBatchDml = yield* spanner.executeBatchDmlProjectsInstancesDatabasesSessions;
62 const commit = yield* spanner.commitProjectsInstancesDatabasesSessions;
63 const updateDdl = yield* spanner.updateDdlProjectsInstancesDatabases;
64 const getOperation = yield* spanner.getProjectsInstancesDatabasesOperations;
65
66 const awaitDdl = (operation: spanner.Operation) =>
67 waitForOperation(operation, (name) => getOperation({ name }), {
68 budget: "30 minutes",
69 interval: "2 seconds",
70 }).pipe(
71 Effect.asVoid,
72 Effect.mapError((cause) =>
73 cause._tag === "GCP.OperationFailed"
74 ? new MigrationError({
75 message: `Spanner DDL on ${database} failed: ${cause.message}`,
76 cause,
77 })
78 : migrationError(`Spanner DDL on ${database}`)(cause),
79 ),
80 );
81
82 const runDdl = (statements: ReadonlyArray<string>) =>
83 updateDdl({ database, body: { statements: [...statements] } }).pipe(
84 Effect.mapError(migrationError(`Spanner DDL on ${database}`)),
85 Effect.flatMap(awaitDdl),
86 );
87
88 /**
89 * Run one DDL statement exactly once under deterministic operation ids
90 * `{base}_0`, `{base}_1`, …: an operation a previous deploy started is
91 * awaited, not resubmitted. A single statement applies atomically, so an
92 * earlier attempt that failed changed nothing and the next attempt id
93 * runs it again; a failure of this deploy's own attempt fails the deploy.
94 */
95 const runDdlOnce = (statement: string, base: string) =>
96 Effect.gen(function* () {
97 for (let attempt = 0; attempt < MAX_DDL_ATTEMPTS; attempt++) {
98 const operationId = `${base}_${attempt}`;
99 const submitted = yield* updateDdl({
100 database,
101 body: { statements: [statement], operationId },
102 }).pipe(
103 Effect.map((operation) => ({ operation, replayed: false })),
104 Effect.catchTag("DdlOperationIdExists", () =>
105 getOperation({ name: `${database}/operations/${operationId}` }).pipe(
106 Effect.map((operation) => ({ operation, replayed: true })),
107 ),
108 ),
109 Effect.mapError(migrationError(`Spanner DDL on ${database}`)),
110 );
111 const outcome = yield* Effect.result(awaitDdl(submitted.operation));
112 if (Result.isSuccess(outcome)) return;
113 if (!submitted.replayed) return yield* outcome.failure;
114 }
115 return yield* new MigrationError({
116 message: `Spanner DDL on ${database} failed ${MAX_DDL_ATTEMPTS} times: ${statement}`,
117 });
118 });
119
120 const runDml = (statements: ReadonlyArray<string>) =>
121 Effect.gen(function* () {
122 const result = yield* executeBatchDml({
123 session,
124 body: {
125 transaction: { begin: { readWrite: {} } },
126 seqno: "1",
127 statements: statements.map((sql) => ({ sql })),
128 },
129 }).pipe(Effect.mapError(migrationError(`Spanner DML on ${database}`)));
130 if (result.status?.code) {
131 return yield* new MigrationError({
132 message: `Spanner DML on ${database} failed: ${result.status.message ?? `code ${result.status.code}`}`,
133 });
134 }
135 const transactionId = result.resultSets?.[0]?.metadata?.transaction?.id;
136 if (!transactionId) {
137 return yield* new MigrationError({
138 message: `Spanner returned no transaction for DML on ${database}`,
139 });
140 }
141 yield* commit({ session, body: { transactionId } }).pipe(
142 Effect.mapError(migrationError(`Committing DML on ${database}`)),
143 );
144 });
145
146 const splitAll = (statements: ReadonlyArray<string>) =>
147 statements.flatMap((statement) => splitSqlScript(statement, dialect));
148
149 const runGroups = (statements: ReadonlyArray<string>) =>
150 Effect.forEach(
151 groupStatements(splitAll(statements)),
152 (group) => (group.ddl ? runDdl(group.statements) : runDml(group.statements)),
153 { discard: true },
154 );
155
156 const operationBaseOf = (record: MigrationRecord, index: number, statement: string) =>
157 Effect.sync(
158 () =>
159 `alchemy_${crypto.createHash("sha256").update(`${record.name}\n${index}\n${statement}`).digest("hex").slice(0, 32)}`,
160 );
161
162 return {
163 dialect,
164 transactionalDdl: false,
165 ...(dialect === "postgres"
166 ? { migrationTableId: "spanner" as const, defaultSchema: "public" }
167 : {}),
168 query: (sql) =>
169 executeSql({ session, body: { sql: splitAll([sql])[0] ?? sql } }).pipe(
170 Effect.mapError(migrationError(`Spanner query on ${database}`)),
171 Effect.map((result) => {
172 const fields = (result.metadata?.rowType?.fields ?? []).map((field) => field.name ?? "");
173 return (result.rows ?? []).map((row) =>
174 Object.fromEntries(
175 fields.map((name, index) => [name, (row as ReadonlyArray<unknown>)[index] ?? null]),
176 ),
177 );
178 }),
179 ),
180 batch: runGroups,
181 applyMigration: (record, bookkeeping) =>
182 Effect.gen(function* () {
183 const groups = groupStatements(splitAll(record.statements));
184 const dml = groups.findIndex((group) => !group.ddl);
185 if (dml >= 0 && dml < groups.length - 1) {
186 return yield* new MigrationError({
187 message: `Spanner migration ${record.name} runs DML between DDL statements. Spanner can't commit DDL and DML together, so put the DML after the migration's DDL or in its own migration.`,
188 });
189 }
190 let index = 0;
191 for (const group of groups) {
192 if (!group.ddl) continue;
193 for (const statement of group.statements) {
194 yield* runDdlOnce(statement, yield* operationBaseOf(record, index++, statement));
195 }
196 }
197 // The trailing DML commits with the bookkeeping row: exactly once.
198 const trailing = groups.at(-1);
199 yield* runDml([...(trailing && !trailing.ddl ? trailing.statements : []), bookkeeping]);
200 }),
201 } satisfies SqlExecutor;
202 });
203
204 /**
205 * Apply migrations to a Spanner database. Call once the database is ready.
206 */
207 export const runDatabaseMigrations = Effect.fn(function* (options: {
208 database: string;
209 dialect: SpannerDialect;
210 input: NormalizedMigrationsInput;
211 stamped: StampedMigrationsState;
212 }) {
213 const createSession = yield* spanner.createProjectsInstancesDatabasesSessions;
214 const deleteSession = yield* spanner.deleteProjectsInstancesDatabasesSessions;
215 return yield* runMigrations({
216 input: { ...options.input, table: options.input.table ?? SPANNER_MIGRATIONS_TABLE },
217 stamped: options.stamped,
218 withExecutor: (apply) =>
219 Effect.acquireUseRelease(
220 createSession({ database: options.database, body: {} }).pipe(
221 Effect.mapError(migrationError(`Opening a session on ${options.database}`)),
222 ),
223 (session) =>
224 makeSpannerExecutor({
225 database: options.database,
226 dialect: options.dialect,
227 session: session.name ?? "",
228 }).pipe(Effect.flatMap(apply)),
229 (session) => deleteSession({ name: session.name ?? "" }).pipe(Effect.ignore),
230 ),
231 });
232 });