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

main
ts 566 lines 19.1 KB
Raw
1 import {
2 diffMigrations,
3 migrationAttributesOf,
4 migrationsAttrs,
5 migrationsInputOf,
6 stampedOf,
7 type MigrationAttributes,
8 type MigrationsInput,
9 } from "@alchemy.run/sql/Migrations";
10 import * as sqladmin from "@distilled.cloud/gcp/sqladmin_v1";
11 import { isResolved } from "alchemy/Diff";
12 import { createPhysicalName } from "alchemy/PhysicalName";
13 import * as Provider from "alchemy/Provider";
14 import { Resource } from "alchemy/Resource";
15 import * as Data from "effect/Data";
16 import * as Effect from "effect/Effect";
17 import type * as Redacted from "effect/Redacted";
18 import * as Schedule from "effect/Schedule";
19 import * as Stream from "effect/Stream";
20 import { GcpEnvironment } from "../Environment.ts";
21 import { ALCHEMY_LABEL_PREFIX, createInternalLabels } from "../Labels.ts";
22 import type { Providers } from "../Providers.ts";
23 import { runDatabaseMigrations, type MigrationsTransport } from "./Migrations.ts";
24 import { recoverIfInstanceMissing, waitForSqlOperation } from "./operations.ts";
25
26 // The SQL Admin API rejects longer database names on every engine.
27 const MAX_NAME_LENGTH = 60;
28
29 const SYSTEM_DATABASES = new Set([
30 "mysql",
31 "information_schema",
32 "performance_schema",
33 "sys",
34 "postgres",
35 "template0",
36 "template1",
37 "master",
38 "model",
39 "msdb",
40 "tempdb",
41 ]);
42
43 export type DatabaseDeletionPolicy = "DELETE" | "ABANDON";
44
45 export type SqlServerDatabaseDetails = {
46 /**
47 * SQL Server compatibility level (e.g. `150`).
48 */
49 compatibilityLevel?: number;
50 /**
51 * SQL Server recovery model (`FULL`, `SIMPLE`, `BULK_LOGGED`).
52 */
53 recoveryModel?: string;
54 };
55
56 export type DatabaseProps = {
57 /**
58 * Cloud SQL instance id (the `{instance}` segment of
59 * `projects/{project}/instances/{instance}`). Full resource names are
60 * accepted and reduced to the last path segment. Immutable — changing
61 * it replaces the database.
62 */
63 instance: string;
64 /**
65 * Database name inside the instance. If omitted, a unique name is
66 * generated from the stack, stage, and logical id. Must start with a
67 * letter and contain only letters, numbers, and underscores (1–60
68 * characters). Immutable — changing it replaces the database.
69 */
70 databaseName?: string;
71 /**
72 * Character set (e.g. `utf8mb4` on MySQL, `UTF8` on PostgreSQL). The
73 * engine may reject in-place charset changes (PostgreSQL).
74 */
75 charset?: string;
76 /**
77 * Collation (e.g. `utf8mb4_unicode_ci` on MySQL, `en_US.UTF8` on
78 * PostgreSQL). The engine may reject in-place collation changes.
79 */
80 collation?: string;
81 /**
82 * SQL Server–only settings. Ignored on MySQL and PostgreSQL.
83 */
84 sqlserverDatabaseDetails?: SqlServerDatabaseDetails;
85 /**
86 * What to do on destroy. `ABANDON` removes the database from state
87 * without calling the Cloud SQL API — useful when the parent instance
88 * is being deleted.
89 * @default "DELETE"
90 */
91 deletionPolicy?: DatabaseDeletionPolicy;
92 /**
93 * SQL migrations to apply on deploy, once the database exists. Accepts a
94 * directory path, a `{ dir, table }` object, or a `Drizzle.Schema`
95 * output. Only pending migrations run; editing or removing an applied
96 * file fails the deploy. PostgreSQL and MySQL instances only.
97 *
98 * Bookkeeping lives in Alchemy's `__alchemy_migrations` table. A
99 * database migrated earlier by drizzle-kit or Prisma is adopted by a
100 * one-way conversion on first deploy.
101 */
102 migrations?: MigrationsInput;
103 /**
104 * How `migrations` reach the database: `"data-api"` (Cloud SQL's
105 * `executeSql`, no network route needed) or `"connector"` (a direct
106 * connection through the Cloud SQL connector, which needs a route to the
107 * instance's IP but no authorized networks).
108 * @default "data-api" when the instance has `dataApiAccess: true`, otherwise "connector"
109 */
110 migrationsTransport?: MigrationsTransport;
111 /**
112 * Built-in database user that `migrations` log in as. It owns the tables
113 * the migrations create.
114 */
115 migrationsUser?: string;
116 /**
117 * Password of `migrationsUser`. The Data API only reads passwords from
118 * Secret Manager, so the `"data-api"` transport stages it in a regional
119 * secret for the duration of the deploy and deletes it afterwards.
120 */
121 migrationsPassword?: string | Redacted.Redacted<string>;
122 };
123
124 export type Database = Resource<
125 "GCP.SQL.Database",
126 DatabaseProps,
127 {
128 /** Database name inside the instance. */
129 databaseName: string;
130 /** Cloud SQL instance id. */
131 instance: string;
132 /** Project id. */
133 project: string;
134 /** Character set currently on the database. */
135 charset: string | undefined;
136 /** Collation currently on the database. */
137 collation: string | undefined;
138 /** SQL Server details, if this is a SQL Server database. */
139 sqlserverDatabaseDetails: SqlServerDatabaseDetails | undefined;
140 /** SQL Admin self-link. */
141 selfLink: string | undefined;
142 /** HTTP etag. */
143 etag: string | undefined;
144 /** Directory of the applied SQL migrations. */
145 migrationsDir: string | undefined;
146 /** Migration bookkeeping table. */
147 migrationsTable: string | undefined;
148 /** Applied migration content hashes. */
149 migrationsHashes: Record<string, string>;
150 },
151 never,
152 Providers
153 >;
154
155 /**
156 * A logical database inside a Cloud SQL instance.
157 *
158 * Cloud SQL databases have no labels field. `list` enumerates non-system
159 * databases on alchemy-labeled instances so `pnpm nuke:gcp` can find
160 * leaked rows without dropping `mysql` / `postgres` / `template1`.
161 *
162 * Changing `instance` or `databaseName` replaces the database. `charset`
163 * and `collation` update in place where the engine allows.
164 *
165 * ### Creating a Database
166 * **Example:** Generated name on a Cloud SQL instance
167 * ```typescript
168 * const instance = yield* GCP.SQL.Instance("AppDb", {
169 * tier: "db-f1-micro",
170 * backup: { enabled: false },
171 * });
172 * const database = yield* GCP.SQL.Database("App", {
173 * instance: instance.instanceName,
174 * });
175 * ```
176 *
177 * **Example:** Explicit name, charset, and collation
178 * ```typescript
179 * const database = yield* GCP.SQL.Database("App", {
180 * instance: instance.instanceName,
181 * databaseName: "app_production",
182 * charset: "utf8mb4",
183 * collation: "utf8mb4_unicode_ci",
184 * });
185 * ```
186 *
187 * ### Migrations
188 * **Example:** Apply migrations over the Data API
189 * ```typescript
190 * const instance = yield* GCP.SQL.Instance("AppDb", {
191 * databaseVersion: "POSTGRES_17",
192 * dataApiAccess: true,
193 * });
194 * const password = yield* Alchemy.Random("AppPassword");
195 * const user = yield* GCP.SQL.User("App", {
196 * instance: instance.instanceName,
197 * password: password.text,
198 * });
199 * const database = yield* GCP.SQL.Database("App", {
200 * instance: instance.instanceName,
201 * migrations: "./migrations",
202 * migrationsUser: user.userName,
203 * migrationsPassword: password.text,
204 * });
205 * ```
206 *
207 * Migrations run as `migrationsUser` through `instances.executeSql`, so
208 * the deploying machine needs no route to the database. The deploying
209 * identity needs `cloudsql.instances.executeSql` and Secret Manager
210 * access: the password is staged in a regional secret while migrations
211 * run.
212 *
213 * **Example:** Apply migrations through the Cloud SQL connector
214 * ```typescript
215 * const database = yield* GCP.SQL.Database("App", {
216 * instance: instance.instanceName,
217 * migrations: "./migrations",
218 * migrationsTransport: "connector",
219 * migrationsUser: user.userName,
220 * migrationsPassword: password.text,
221 * });
222 * ```
223 *
224 * The connector logs in with an ephemeral client certificate from the SQL
225 * Admin API, so the instance needs no authorized networks. The deploying
226 * machine needs a route to its IP: the public IP (the default), or the
227 * private IP from inside its network. Install `pg` (PostgreSQL) or `mysql2`
228 * (MySQL).
229 *
230 * **Example:** Migrations generated by Drizzle
231 * ```typescript
232 * const schema = yield* Drizzle.Schema("Schema", {
233 * schema: "./src/schema.ts",
234 * out: "./migrations",
235 * });
236 * const database = yield* GCP.SQL.Database("App", {
237 * instance: instance.instanceName,
238 * migrations: schema,
239 * migrationsUser: user.userName,
240 * migrationsPassword: password.text,
241 * });
242 * ```
243 *
244 * @resource
245 * @category SQL
246 */
247 export const Database = Resource<Database>("GCP.SQL.Database");
248
249 export class DatabaseNotResolved extends Data.TaggedError("GCP.SQL.DatabaseNotResolved")<{
250 instance: string;
251 databaseName: string;
252 }> {}
253
254 export class DatabaseStillExists extends Data.TaggedError("GCP.SQL.DatabaseStillExists")<{
255 instance: string;
256 databaseName: string;
257 }> {}
258
259 const lastSegment = (value: string) => {
260 const trimmed = value.replace(/\/+$/, "");
261 const parts = trimmed.split("/");
262 return parts[parts.length - 1] || trimmed;
263 };
264
265 const instanceIdOf = (value: string) => lastSegment(value);
266
267 const isUserDatabase = (database: sqladmin.Database) => {
268 const name = (database.name ?? "").toLowerCase();
269 return name.length > 0 && !SYSTEM_DATABASES.has(name);
270 };
271
272 const hasAlchemyInstanceLabels = (labels: Record<string, string | undefined> | null | undefined) =>
273 Object.keys(labels ?? {}).some((key) => key.startsWith(ALCHEMY_LABEL_PREFIX));
274
275 const normalizeText = (value: string | undefined) => (value ?? "").toLowerCase();
276
277 const sqlServerKey = (details: SqlServerDatabaseDetails | undefined) =>
278 JSON.stringify({
279 compatibilityLevel: details?.compatibilityLevel ?? null,
280 recoveryModel: (details?.recoveryModel ?? "").toLowerCase(),
281 });
282
283 const toSqlIdentifier = (name: string) => {
284 let next = name
285 .toLowerCase()
286 .replace(/[^a-z0-9_]/g, "_")
287 .replace(/_+/g, "_")
288 .replace(/^_+|_+$/g, "");
289 if (!/^[a-z]/.test(next)) next = `d${next}`;
290 next = next.slice(0, MAX_NAME_LENGTH).replace(/_+$/g, "");
291 return next.length > 0 ? next : "database";
292 };
293
294 const toDatabaseName = (id: string, databaseName: string | undefined, existing?: string) =>
295 Effect.gen(function* () {
296 if (databaseName !== undefined) return databaseName;
297 if (existing !== undefined) return existing;
298 return toSqlIdentifier(
299 yield* createPhysicalName({
300 id,
301 maxLength: MAX_NAME_LENGTH,
302 lowercase: true,
303 delimiter: "_",
304 }),
305 );
306 });
307
308 const toAttrs = (
309 database: sqladmin.Database,
310 project: string,
311 instance: string,
312 migrations?: Partial<MigrationAttributes>,
313 ): Database["Attributes"] => ({
314 databaseName: database.name ?? "",
315 instance: database.instance ?? instance,
316 project: database.project ?? project,
317 charset: database.charset,
318 collation: database.collation,
319 sqlserverDatabaseDetails: database.sqlserverDatabaseDetails,
320 selfLink: database.selfLink,
321 etag: database.etag,
322 ...migrationAttributesOf(migrations),
323 });
324
325 const getByName = (project: string, instance: string, databaseName: string) =>
326 sqladmin.getDatabases({ project, instance, database: databaseName }).pipe(
327 Effect.catchTag("NotFound", () => Effect.undefined),
328 recoverIfInstanceMissing(project, instance, () => undefined),
329 );
330
331 const waitForOperation = (
332 project: string,
333 operation: sqladmin.Operation,
334 options?: { notFoundOk?: boolean },
335 ) =>
336 waitForSqlOperation(project, operation, {
337 budget: "10 minutes",
338 notFoundOk: options?.notFoundOk,
339 });
340
341 const waitUntilExists = (project: string, instance: string, databaseName: string) =>
342 getByName(project, instance, databaseName).pipe(
343 Effect.flatMap((database) =>
344 database
345 ? Effect.succeed(database)
346 : Effect.fail(new DatabaseNotResolved({ instance, databaseName })),
347 ),
348 Effect.retry({
349 while: (error) => error._tag === "GCP.SQL.DatabaseNotResolved",
350 times: 8,
351 schedule: Schedule.spaced("1 second"),
352 }),
353 );
354
355 const waitUntilGone = (project: string, instance: string, databaseName: string) =>
356 getByName(project, instance, databaseName).pipe(
357 Effect.flatMap((database) =>
358 database === undefined
359 ? Effect.void
360 : Effect.fail(new DatabaseStillExists({ instance, databaseName })),
361 ),
362 Effect.retry({
363 while: (error) => error._tag === "GCP.SQL.DatabaseStillExists",
364 times: 10,
365 schedule: Schedule.spaced("1 second"),
366 }),
367 );
368
369 const toBody = (
370 news: DatabaseProps,
371 databaseName: string,
372 instance: string,
373 project: string,
374 ): sqladmin.Database => ({
375 name: databaseName,
376 instance,
377 project,
378 charset: news.charset,
379 collation: news.collation,
380 sqlserverDatabaseDetails: news.sqlserverDatabaseDetails,
381 });
382
383 export const DatabaseProvider = () =>
384 Provider.succeed(Database, {
385 stables: ["databaseName", "instance", "project", "selfLink"],
386
387 diff: Effect.fn(function* ({ news, olds, output }) {
388 if (!isResolved(news)) return undefined;
389 const previousInstance = olds?.instance ?? output?.instance;
390 const nextInstance = news.instance;
391 const previousName = olds?.databaseName ?? output?.databaseName;
392 const nextName = news.databaseName ?? previousName;
393 const instanceChanged =
394 previousInstance !== undefined &&
395 instanceIdOf(previousInstance) !== instanceIdOf(nextInstance);
396 const nameChanged =
397 previousName !== undefined && nextName !== undefined && previousName !== nextName;
398 if (!instanceChanged && !nameChanged) {
399 if (yield* diffMigrations({ news, output })) {
400 return { action: "update" as const };
401 }
402 return undefined;
403 }
404 return {
405 action: "replace" as const,
406 deleteFirst: false,
407 };
408 }),
409
410 read: Effect.fn(function* ({ id, olds, output }) {
411 const env = yield* GcpEnvironment.current;
412 const instance = instanceIdOf(olds?.instance ?? output?.instance ?? "");
413 if (instance.length === 0) return undefined;
414 const databaseName = yield* toDatabaseName(id, olds?.databaseName, output?.databaseName);
415 const existing = yield* getByName(env.project, instance, databaseName);
416 if (existing === undefined) return undefined;
417 return toAttrs(existing, env.project, instance, output);
418 }),
419
420 list: () =>
421 Effect.gen(function* () {
422 const env = yield* GcpEnvironment.current;
423 const instances = yield* sqladmin.listInstances
424 .items({
425 project: env.project,
426 maxResults: 1000,
427 filter: "instanceType:CLOUD_SQL_INSTANCE",
428 })
429 .pipe(
430 Stream.filter((instance) => hasAlchemyInstanceLabels(instance.settings?.userLabels)),
431 Stream.runCollect,
432 Effect.map((chunk) => Array.from(chunk)),
433 Effect.catchTag("NotFound", () => Effect.succeed([] as sqladmin.DatabaseInstance[])),
434 );
435 const pages = yield* Effect.forEach(
436 instances,
437 (instance) => {
438 const instanceName = instance.name;
439 if (instanceName === undefined || instanceName.length === 0) {
440 return Effect.succeed([] as Database["Attributes"][]);
441 }
442 return sqladmin
443 .listDatabases({
444 project: env.project,
445 instance: instanceName,
446 })
447 .pipe(
448 Effect.map((page) =>
449 (page.items ?? [])
450 .filter(isUserDatabase)
451 .map((database) => toAttrs(database, env.project, instanceName)),
452 ),
453 // The instance may be deleted mid-listing.
454 Effect.catchTag("NotFound", () => Effect.succeed([] as Database["Attributes"][])),
455 recoverIfInstanceMissing(
456 env.project,
457 instanceName,
458 () => [] as Database["Attributes"][],
459 ),
460 );
461 },
462 { concurrency: 4 },
463 );
464 return pages.flat();
465 }),
466
467 reconcile: Effect.fn(function* ({ id, news, output }) {
468 const env = yield* GcpEnvironment.current;
469 const instance = instanceIdOf(news.instance);
470 const databaseName = yield* toDatabaseName(id, news.databaseName, output?.databaseName);
471
472 let current = yield* getByName(env.project, instance, databaseName);
473
474 if (current === undefined) {
475 yield* sqladmin
476 .insertDatabases({
477 project: env.project,
478 instance,
479 body: toBody(news, databaseName, instance, env.project),
480 })
481 .pipe(
482 Effect.flatMap((operation) => waitForOperation(env.project, operation)),
483 Effect.catchTag("Conflict", (error) =>
484 getByName(env.project, instance, databaseName).pipe(
485 Effect.flatMap((existing) => (existing ? Effect.void : Effect.fail(error))),
486 ),
487 ),
488 Effect.retry({
489 while: (error) => error._tag === "Conflict",
490 times: 8,
491 schedule: Schedule.spaced("5 seconds"),
492 }),
493 );
494 current = yield* waitUntilExists(env.project, instance, databaseName);
495 }
496
497 const charsetChanged =
498 news.charset !== undefined &&
499 normalizeText(current.charset) !== normalizeText(news.charset);
500 const collationChanged =
501 news.collation !== undefined &&
502 normalizeText(current.collation) !== normalizeText(news.collation);
503 const sqlServerChanged =
504 news.sqlserverDatabaseDetails !== undefined &&
505 sqlServerKey(current.sqlserverDatabaseDetails) !==
506 sqlServerKey(news.sqlserverDatabaseDetails);
507
508 if (charsetChanged || collationChanged || sqlServerChanged) {
509 const patched = yield* sqladmin.patchDatabases({
510 project: env.project,
511 instance,
512 database: databaseName,
513 body: toBody(news, databaseName, instance, env.project),
514 });
515 yield* waitForOperation(env.project, patched);
516 current = yield* waitUntilExists(env.project, instance, databaseName);
517 }
518
519 // Sync migrations — the shared pipeline skips already-applied ones.
520 const migrationsInput = migrationsInputOf(news);
521 const migrations = migrationsInput
522 ? yield* runDatabaseMigrations({
523 project: env.project,
524 instance,
525 database: databaseName,
526 input: migrationsInput,
527 stamped: stampedOf(output),
528 transport: news.migrationsTransport,
529 user: news.migrationsUser,
530 password: news.migrationsPassword,
531 labels: yield* createInternalLabels(id),
532 })
533 : undefined;
534
535 return toAttrs(
536 current,
537 env.project,
538 instance,
539 migrationsAttrs({ input: migrationsInput, run: migrations, output }),
540 );
541 }),
542
543 delete: Effect.fn(function* ({ olds, output }) {
544 if (olds.deletionPolicy === "ABANDON") return;
545 const project = output.project;
546 const instance = instanceIdOf(output.instance);
547 const databaseName = output.databaseName;
548 yield* sqladmin
549 .deleteDatabases({
550 project,
551 instance,
552 database: databaseName,
553 })
554 .pipe(
555 Effect.flatMap((operation) => waitForOperation(project, operation, { notFoundOk: true })),
556 Effect.catchTag("NotFound", () => Effect.void),
557 recoverIfInstanceMissing(project, instance, () => undefined),
558 Effect.retry({
559 while: (error) => error._tag === "Conflict",
560 times: 8,
561 schedule: Schedule.spaced("5 seconds"),
562 }),
563 );
564 yield* waitUntilGone(project, instance, databaseName);
565 }),
566 });