The best way to control Claude Code, Codex and more from any computer or phone
| 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 | }); |