The best way to control Claude Code, Codex and more from any computer or phone
| 1 | import * as crypto from "node:crypto"; |
| 2 | import * as tls from "node:tls"; |
| 3 | import { |
| 4 | makeStatementExecutor, |
| 5 | MigrationError, |
| 6 | migrationError, |
| 7 | probeExecutor, |
| 8 | retryLogin, |
| 9 | rowsFromCells, |
| 10 | runMigrations, |
| 11 | withMySqlExecutor, |
| 12 | withPgExecutor, |
| 13 | type NormalizedMigrationsInput, |
| 14 | type SqlExecutor, |
| 15 | type StampedMigrationsState, |
| 16 | } from "@alchemy.run/sql/Migrations"; |
| 17 | import * as secretmanager from "@distilled.cloud/gcp/secretmanager_v1"; |
| 18 | import * as sqladmin from "@distilled.cloud/gcp/sqladmin_v1"; |
| 19 | import * as Effect from "effect/Effect"; |
| 20 | import * as Redacted from "effect/Redacted"; |
| 21 | |
| 22 | /** |
| 23 | * How migrations reach a Cloud SQL database: |
| 24 | * |
| 25 | * - `"data-api"` — Cloud SQL's `instances.executeSql` over HTTPS. Needs |
| 26 | * `dataApiAccess: true` on the instance; no network route to the |
| 27 | * database. The Data API only reads passwords from Secret Manager, so |
| 28 | * each deploy stages the password in a short-lived regional secret. |
| 29 | * - `"connector"` — a `pg`/`mysql2` connection from the deploying machine |
| 30 | * through the Cloud SQL connector (an ephemeral client certificate from |
| 31 | * the SQL Admin API, TLS to port 3307). Needs a route to the instance's |
| 32 | * public (or private) IP, but no authorized networks. |
| 33 | */ |
| 34 | export type MigrationsTransport = "data-api" | "connector"; |
| 35 | |
| 36 | /** The connector's server-side proxy port. */ |
| 37 | const CONNECTOR_PORT = 3307; |
| 38 | const CONNECT_TIMEOUT_MILLIS = 15_000; |
| 39 | |
| 40 | const dialectOf = ( |
| 41 | instance: string, |
| 42 | databaseVersion: string | undefined, |
| 43 | ): Effect.Effect<"postgres" | "mysql", MigrationError> => { |
| 44 | const version = (databaseVersion ?? "").toUpperCase(); |
| 45 | if (version.startsWith("POSTGRES_")) return Effect.succeed("postgres"); |
| 46 | if (version.startsWith("MYSQL_")) return Effect.succeed("mysql"); |
| 47 | return Effect.fail( |
| 48 | new MigrationError({ |
| 49 | message: `Cloud SQL instance '${instance}' runs ${databaseVersion ?? "an unknown engine"}; migrations support PostgreSQL and MySQL`, |
| 50 | }), |
| 51 | ); |
| 52 | }; |
| 53 | |
| 54 | /** Secret ids allow letters, numbers, `-` and `_`, up to 255 characters. */ |
| 55 | const stagedSecretId = (instance: string, database: string) => |
| 56 | `alchemy-migrations-${instance}-${database}`.replace(/[^A-Za-z0-9_-]/g, "-").slice(0, 255); |
| 57 | |
| 58 | /** |
| 59 | * Write the password into a regional secret in the instance's region for |
| 60 | * the scope of `use`, then delete the secret. The id is deterministic, so a |
| 61 | * secret left behind by a crashed deploy is reused and removed by the next. |
| 62 | */ |
| 63 | const withStagedPassword = <A, E, R>( |
| 64 | options: { |
| 65 | project: string; |
| 66 | region: string; |
| 67 | secretId: string; |
| 68 | password: string; |
| 69 | labels: Record<string, string>; |
| 70 | }, |
| 71 | use: (secretVersion: string) => Effect.Effect<A, E, R>, |
| 72 | ) => { |
| 73 | const parent = `projects/${options.project}/locations/${options.region}`; |
| 74 | const name = `${parent}/secrets/${options.secretId}`; |
| 75 | return Effect.acquireUseRelease( |
| 76 | Effect.gen(function* () { |
| 77 | const data = yield* Effect.sync(() => |
| 78 | Buffer.from(options.password, "utf8").toString("base64"), |
| 79 | ); |
| 80 | yield* secretmanager |
| 81 | .createProjectsLocationsSecrets({ |
| 82 | parent, |
| 83 | secretId: options.secretId, |
| 84 | body: { labels: options.labels }, |
| 85 | }) |
| 86 | .pipe(Effect.catchTag("Conflict", () => Effect.void)); |
| 87 | const version = yield* secretmanager.addVersionProjectsLocationsSecrets({ |
| 88 | parent: name, |
| 89 | body: { payload: { data } }, |
| 90 | }); |
| 91 | return version.name ?? `${name}/versions/latest`; |
| 92 | }).pipe(Effect.mapError(migrationError(`Staging the migrations password in ${name}`))), |
| 93 | use, |
| 94 | () => |
| 95 | secretmanager.deleteProjectsLocationsSecrets({ name }).pipe( |
| 96 | Effect.catchTag("NotFound", () => Effect.void), |
| 97 | Effect.ignore, |
| 98 | ), |
| 99 | ); |
| 100 | }; |
| 101 | |
| 102 | /** |
| 103 | * `instances.executeSql` as a {@link SqlExecutor}: each migration batch is |
| 104 | * one request wrapped in a transaction (MySQL DDL still commits |
| 105 | * implicitly). |
| 106 | */ |
| 107 | const makeDataApiExecutor = Effect.fn(function* (options: { |
| 108 | dialect: "postgres" | "mysql"; |
| 109 | project: string; |
| 110 | instance: string; |
| 111 | database: string; |
| 112 | user: string; |
| 113 | passwordSecretVersion: string; |
| 114 | }) { |
| 115 | const executeSql = yield* sqladmin.executeSqlInstances; |
| 116 | return makeStatementExecutor({ |
| 117 | dialect: options.dialect, |
| 118 | batch: "transaction", |
| 119 | execute: (sqlStatement) => |
| 120 | executeSql({ |
| 121 | project: options.project, |
| 122 | instance: options.instance, |
| 123 | body: { |
| 124 | database: options.database, |
| 125 | user: options.user, |
| 126 | passwordSecretVersion: options.passwordSecretVersion, |
| 127 | sqlStatement, |
| 128 | }, |
| 129 | }).pipe( |
| 130 | Effect.mapError(migrationError(`executeSql on '${options.instance}'`)), |
| 131 | // SQL errors come back inside a 200 response. |
| 132 | Effect.flatMap((response) => { |
| 133 | const failure = response.status?.code |
| 134 | ? response.status |
| 135 | : response.results?.find((result) => result.status?.code)?.status; |
| 136 | if (failure) { |
| 137 | return Effect.fail( |
| 138 | new MigrationError({ |
| 139 | message: `executeSql on '${options.instance}' failed: ${failure.message ?? `code ${failure.code}`}`, |
| 140 | }), |
| 141 | ); |
| 142 | } |
| 143 | const last = response.results?.at(-1); |
| 144 | return Effect.succeed(rowsFromCells(last?.columns, last?.rows)); |
| 145 | }), |
| 146 | ), |
| 147 | }); |
| 148 | }); |
| 149 | |
| 150 | /** |
| 151 | * Open a TLS socket to the instance through the Cloud SQL connector: an |
| 152 | * ephemeral client certificate signed by the SQL Admin API, verified |
| 153 | * against the instance's server CA. The socket is ready for the database |
| 154 | * protocol (no `SSLRequest`, no MySQL TLS upgrade). |
| 155 | */ |
| 156 | const dialConnector = Effect.fn(function* (options: { project: string; instance: string }) { |
| 157 | const settings = yield* sqladmin |
| 158 | .getConnect({ project: options.project, instance: options.instance }) |
| 159 | .pipe(Effect.mapError(migrationError(`Reading connect settings of '${options.instance}'`))); |
| 160 | const address = |
| 161 | settings.ipAddresses?.find((ip) => ip.type === "PRIMARY")?.ipAddress ?? |
| 162 | settings.ipAddresses?.find((ip) => ip.type === "PRIVATE")?.ipAddress; |
| 163 | const serverCa = settings.serverCaCert?.cert; |
| 164 | if (!address || !serverCa) { |
| 165 | return yield* new MigrationError({ |
| 166 | message: `Cloud SQL instance '${options.instance}' has no IP address to connect to`, |
| 167 | }); |
| 168 | } |
| 169 | const keys = yield* Effect.sync(() => |
| 170 | crypto.generateKeyPairSync("rsa", { |
| 171 | modulusLength: 2048, |
| 172 | publicKeyEncoding: { type: "spki", format: "pem" }, |
| 173 | privateKeyEncoding: { type: "pkcs8", format: "pem" }, |
| 174 | }), |
| 175 | ); |
| 176 | const ephemeral = yield* sqladmin |
| 177 | .generateEphemeralCertConnect({ |
| 178 | project: options.project, |
| 179 | instance: options.instance, |
| 180 | body: { public_key: keys.publicKey }, |
| 181 | }) |
| 182 | .pipe( |
| 183 | Effect.mapError(migrationError(`Requesting a client certificate for '${options.instance}'`)), |
| 184 | ); |
| 185 | const clientCert = ephemeral.ephemeralCert?.cert; |
| 186 | if (!clientCert) { |
| 187 | return yield* new MigrationError({ |
| 188 | message: `The SQL Admin API returned no client certificate for '${options.instance}'`, |
| 189 | }); |
| 190 | } |
| 191 | // Per-instance CAs name the instance `project:instance`; shared and |
| 192 | // customer CAs identify it by its DNS name. |
| 193 | const legacyName = `${options.project}:${options.instance}`; |
| 194 | const dnsName = settings.serverCaMode?.includes("CAS") ? settings.dnsName : undefined; |
| 195 | return yield* Effect.callback<tls.TLSSocket, MigrationError>((resume) => { |
| 196 | const socket = tls.connect({ |
| 197 | host: address, |
| 198 | port: CONNECTOR_PORT, |
| 199 | ca: serverCa, |
| 200 | cert: clientCert, |
| 201 | key: keys.privateKey, |
| 202 | servername: dnsName, |
| 203 | timeout: CONNECT_TIMEOUT_MILLIS, |
| 204 | checkServerIdentity: (host, cert) => |
| 205 | dnsName |
| 206 | ? tls.checkServerIdentity(dnsName.replace(/\.$/, ""), cert) |
| 207 | : cert.subject.CN === legacyName |
| 208 | ? undefined |
| 209 | : new Error( |
| 210 | `server certificate names '${[cert.subject.CN].flat().join(", ")}', expected '${legacyName}'`, |
| 211 | ), |
| 212 | }); |
| 213 | const fail = (cause: Error) => { |
| 214 | socket.destroy(); |
| 215 | resume( |
| 216 | Effect.fail( |
| 217 | new MigrationError({ |
| 218 | message: `Failed to connect to '${options.instance}' at ${address}:${CONNECTOR_PORT}: ${cause.message}`, |
| 219 | cause, |
| 220 | }), |
| 221 | ), |
| 222 | ); |
| 223 | }; |
| 224 | socket.once("error", fail); |
| 225 | socket.once("timeout", () => fail(new Error("timed out"))); |
| 226 | socket.once("secureConnect", () => { |
| 227 | socket.off("error", fail); |
| 228 | socket.setTimeout(0); |
| 229 | resume(Effect.succeed(socket)); |
| 230 | }); |
| 231 | return Effect.sync(() => socket.destroy()); |
| 232 | }); |
| 233 | }); |
| 234 | |
| 235 | /** |
| 236 | * A `pg`/`mysql2` session over the Cloud SQL connector for the scope of |
| 237 | * `use`. A new user's login delay is retried on the dial and login, never |
| 238 | * on `use`. |
| 239 | */ |
| 240 | const withConnectorExecutor = <A, E, R>( |
| 241 | options: { |
| 242 | dialect: "postgres" | "mysql"; |
| 243 | project: string; |
| 244 | instance: string; |
| 245 | database: string; |
| 246 | user: string; |
| 247 | password: string; |
| 248 | }, |
| 249 | use: (executor: SqlExecutor) => Effect.Effect<A, E, R>, |
| 250 | ) => |
| 251 | Effect.gen(function* () { |
| 252 | const socket = yield* dialConnector(options); |
| 253 | const credentials = { |
| 254 | database: options.database, |
| 255 | user: options.user, |
| 256 | password: options.password, |
| 257 | }; |
| 258 | return yield* options.dialect === "postgres" |
| 259 | ? withPgExecutor( |
| 260 | { |
| 261 | ...credentials, |
| 262 | // `pg` dials its stream itself; this one is already connected. |
| 263 | stream: () => |
| 264 | Object.assign(socket, { |
| 265 | connect: () => { |
| 266 | process.nextTick(() => socket.emit("connect")); |
| 267 | return socket; |
| 268 | }, |
| 269 | }), |
| 270 | ssl: false, |
| 271 | }, |
| 272 | use, |
| 273 | ) |
| 274 | : withMySqlExecutor({ ...credentials, stream: socket }, use); |
| 275 | }); |
| 276 | |
| 277 | /** |
| 278 | * Apply migrations to a database on a Cloud SQL instance. Call once the |
| 279 | * database exists. |
| 280 | */ |
| 281 | export const runDatabaseMigrations = Effect.fn(function* (options: { |
| 282 | project: string; |
| 283 | instance: string; |
| 284 | database: string; |
| 285 | input: NormalizedMigrationsInput; |
| 286 | stamped: StampedMigrationsState; |
| 287 | transport: MigrationsTransport | undefined; |
| 288 | user: string | undefined; |
| 289 | password: string | Redacted.Redacted<string> | undefined; |
| 290 | labels: Record<string, string>; |
| 291 | }) { |
| 292 | const { project, instance, database } = options; |
| 293 | const live = yield* sqladmin |
| 294 | .getInstances({ project, instance }) |
| 295 | .pipe(Effect.mapError(migrationError(`Reading Cloud SQL instance '${instance}'`))); |
| 296 | const dialect = yield* dialectOf(instance, live.databaseVersion); |
| 297 | if (options.user === undefined || options.password === undefined) { |
| 298 | return yield* new MigrationError({ |
| 299 | message: `Migrations on '${instance}/${database}' need credentials; set migrationsUser and migrationsPassword`, |
| 300 | }); |
| 301 | } |
| 302 | const user = options.user; |
| 303 | const password = Redacted.isRedacted(options.password) |
| 304 | ? Redacted.value(options.password) |
| 305 | : options.password; |
| 306 | const dataApi = live.settings?.dataApiAccess === "ALLOW_DATA_API"; |
| 307 | const transport = options.transport ?? (dataApi ? "data-api" : "connector"); |
| 308 | |
| 309 | if (transport === "data-api") { |
| 310 | if (!dataApi) { |
| 311 | return yield* new MigrationError({ |
| 312 | message: `migrationsTransport "data-api" needs dataApiAccess: true on Cloud SQL instance '${instance}'`, |
| 313 | }); |
| 314 | } |
| 315 | const region = live.region; |
| 316 | if (!region) { |
| 317 | return yield* new MigrationError({ |
| 318 | message: `Cloud SQL instance '${instance}' has no region`, |
| 319 | }); |
| 320 | } |
| 321 | return yield* runMigrations({ |
| 322 | input: options.input, |
| 323 | stamped: options.stamped, |
| 324 | withExecutor: (apply) => |
| 325 | withStagedPassword( |
| 326 | { |
| 327 | project, |
| 328 | region, |
| 329 | secretId: stagedSecretId(instance, database), |
| 330 | password, |
| 331 | labels: options.labels, |
| 332 | }, |
| 333 | (passwordSecretVersion) => |
| 334 | makeDataApiExecutor({ |
| 335 | dialect, |
| 336 | project, |
| 337 | instance, |
| 338 | database, |
| 339 | user, |
| 340 | passwordSecretVersion, |
| 341 | }).pipe(Effect.flatMap(probeExecutor), Effect.flatMap(apply)), |
| 342 | ), |
| 343 | }); |
| 344 | } |
| 345 | |
| 346 | const target = { dialect, project, instance, database, user, password }; |
| 347 | return yield* runMigrations({ |
| 348 | input: options.input, |
| 349 | stamped: options.stamped, |
| 350 | withExecutor: (apply) => |
| 351 | // Logins fail while opening the session, so probe with a session of |
| 352 | // its own; the retry never reruns `apply`. |
| 353 | retryLogin(withConnectorExecutor(target, (executor) => executor.query("SELECT 1"))).pipe( |
| 354 | Effect.andThen(withConnectorExecutor(target, apply)), |
| 355 | ), |
| 356 | }); |
| 357 | }); |