1import * as sqladmin from "@distilled.cloud/gcp/sqladmin_v1";
2import * as Data from "effect/Data";
3import * as Effect from "effect/Effect";
4import * as Schedule from "effect/Schedule";
5import * as Stream from "effect/Stream";
6import { isResolved } from "../../Diff.ts";
7import { createPhysicalName } from "../../PhysicalName.ts";
8import * as Provider from "../../Provider.ts";
9import { Resource } from "../../Resource.ts";
10import { GcpEnvironment } from "../Environment.ts";
11import { ALCHEMY_LABEL_PREFIX } from "../Labels.ts";
12import type { Providers } from "../Providers.ts";
13import { recoverIfInstanceMissing, waitForSqlOperation } from "./operations.ts";
14
15const MAX_NAME_LENGTH = 63;
16
17const SYSTEM_DATABASES = new Set([
18 "mysql",
19 "information_schema",
20 "performance_schema",
21 "sys",
22 "postgres",
23 "template0",
24 "template1",
25 "master",
26 "model",
27 "msdb",
28 "tempdb",
29]);
30
31export type DatabaseDeletionPolicy = "DELETE" | "ABANDON";
32
33export type SqlServerDatabaseDetails = {
34 /**
35 * SQL Server compatibility level (e.g. `150`).
36 */
37 compatibilityLevel?: number;
38 /**
39 * SQL Server recovery model (`FULL`, `SIMPLE`, `BULK_LOGGED`).
40 */
41 recoveryModel?: string;
42};
43
44export type DatabaseProps = {
45 /**
46 * Cloud SQL instance id (the `{instance}` segment of
47 * `projects/{project}/instances/{instance}`). Full resource names are
48 * accepted and reduced to the last path segment. Immutable — changing
49 * it replaces the database.
50 */
51 instance: string;
52 /**
53 * Database name inside the instance. If omitted, a unique name is
54 * generated from the stack, stage, and logical id. Must start with a
55 * letter and contain only letters, numbers, and underscores (1–63
56 * characters). Immutable — changing it replaces the database.
57 */
58 databaseName?: string;
59 /**
60 * Character set (e.g. `utf8mb4` on MySQL, `UTF8` on PostgreSQL). The
61 * engine may reject in-place charset changes (PostgreSQL).
62 */
63 charset?: string;
64 /**
65 * Collation (e.g. `utf8mb4_unicode_ci` on MySQL, `en_US.UTF8` on
66 * PostgreSQL). The engine may reject in-place collation changes.
67 */
68 collation?: string;
69 /**
70 * SQL Server–only settings. Ignored on MySQL and PostgreSQL.
71 */
72 sqlserverDatabaseDetails?: SqlServerDatabaseDetails;
73 /**
74 * What to do on destroy. `ABANDON` removes the database from state
75 * without calling the Cloud SQL API — useful when the parent instance
76 * is being deleted.
77 * @default "DELETE"
78 */
79 deletionPolicy?: DatabaseDeletionPolicy;
80};
81
82export type Database = Resource<
83 "GCP.SQL.Database",
84 DatabaseProps,
85 {
86 /** Database name inside the instance. */
87 databaseName: string;
88 /** Cloud SQL instance id. */
89 instance: string;
90 /** Project id. */
91 project: string;
92 /** Character set currently on the database. */
93 charset: string | undefined;
94 /** Collation currently on the database. */
95 collation: string | undefined;
96 /** SQL Server details, if this is a SQL Server database. */
97 sqlserverDatabaseDetails: SqlServerDatabaseDetails | undefined;
98 /** SQL Admin self-link. */
99 selfLink: string | undefined;
100 /** HTTP etag. */
101 etag: string | undefined;
102 },
103 never,
104 Providers
105>;
106
107/**
108 * A logical database inside a Cloud SQL instance.
109 *
110 * Cloud SQL databases have no labels field. `list` enumerates non-system
111 * databases on alchemy-labeled instances so `pnpm nuke:gcp` can find
112 * leaked rows without dropping `mysql` / `postgres` / `template1`.
113 *
114 * Changing `instance` or `databaseName` replaces the database. `charset`
115 * and `collation` update in place where the engine allows.
116 *
117 * ### Creating a Database
118 * **Example:** Generated name on a Cloud SQL instance
119 * ```typescript
120 * const instance = yield* GCP.SQL.Instance("AppDb", {
121 * tier: "db-f1-micro",
122 * backupEnabled: false,
123 * });
124 * const database = yield* GCP.SQL.Database("App", {
125 * instance: instance.instanceName,
126 * });
127 * ```
128 *
129 * **Example:** Explicit name, charset, and collation
130 * ```typescript
131 * const database = yield* GCP.SQL.Database("App", {
132 * instance: instance.instanceName,
133 * databaseName: "app_production",
134 * charset: "utf8mb4",
135 * collation: "utf8mb4_unicode_ci",
136 * });
137 * ```
138 *
139 * @resource
140 * @category SQL
141 */
142export const Database = Resource<Database>("GCP.SQL.Database");
143
144export class DatabaseNotResolved extends Data.TaggedError(
145 "GCP.SQL.DatabaseNotResolved",
146)<{
147 instance: string;
148 databaseName: string;
149}> {}
150
151export class DatabaseStillExists extends Data.TaggedError(
152 "GCP.SQL.DatabaseStillExists",
153)<{
154 instance: string;
155 databaseName: string;
156}> {}
157
158const lastSegment = (value: string) => {
159 const trimmed = value.replace(/\/+$/, "");
160 const parts = trimmed.split("/");
161 return parts[parts.length - 1] || trimmed;
162};
163
164const instanceIdOf = (value: string) => lastSegment(value);
165
166const isUserDatabase = (database: sqladmin.Database) => {
167 const name = (database.name ?? "").toLowerCase();
168 return name.length > 0 && !SYSTEM_DATABASES.has(name);
169};
170
171const hasAlchemyInstanceLabels = (
172 labels: Record<string, string | undefined> | null | undefined,
173) =>
174 Object.keys(labels ?? {}).some((key) => key.startsWith(ALCHEMY_LABEL_PREFIX));
175
176const normalizeText = (value: string | undefined) =>
177 (value ?? "").toLowerCase();
178
179const sqlServerKey = (details: SqlServerDatabaseDetails | undefined) =>
180 JSON.stringify({
181 compatibilityLevel: details?.compatibilityLevel ?? null,
182 recoveryModel: (details?.recoveryModel ?? "").toLowerCase(),
183 });
184
185const toSqlIdentifier = (name: string) => {
186 let next = name
187 .toLowerCase()
188 .replace(/[^a-z0-9_]/g, "_")
189 .replace(/_+/g, "_")
190 .replace(/^_+|_+$/g, "");
191 if (!/^[a-z]/.test(next)) next = `d${next}`;
192 next = next.slice(0, MAX_NAME_LENGTH).replace(/_+$/g, "");
193 return next.length > 0 ? next : "database";
194};
195
196const toDatabaseName = (
197 id: string,
198 databaseName: string | undefined,
199 existing?: string,
200) =>
201 Effect.gen(function* () {
202 if (databaseName !== undefined) return databaseName;
203 if (existing !== undefined) return existing;
204 return toSqlIdentifier(
205 yield* createPhysicalName({
206 id,
207 maxLength: MAX_NAME_LENGTH,
208 lowercase: true,
209 delimiter: "_",
210 }),
211 );
212 });
213
214const toAttrs = (
215 database: sqladmin.Database,
216 project: string,
217 instance: string,
218) => ({
219 databaseName: database.name ?? "",
220 instance: database.instance ?? instance,
221 project: database.project ?? project,
222 charset: database.charset,
223 collation: database.collation,
224 sqlserverDatabaseDetails: database.sqlserverDatabaseDetails,
225 selfLink: database.selfLink,
226 etag: database.etag,
227});
228
229const getByName = (project: string, instance: string, databaseName: string) =>
230 sqladmin.getDatabases({ project, instance, database: databaseName }).pipe(
231 Effect.catchTag("NotFound", () => Effect.succeed(undefined)),
232 recoverIfInstanceMissing(project, instance, () => undefined),
233 );
234
235const waitForOperation = (
236 project: string,
237 operation: sqladmin.Operation,
238 options?: { notFoundOk?: boolean },
239) =>
240 waitForSqlOperation(project, operation, {
241 budget: "10 minutes",
242 notFoundOk: options?.notFoundOk,
243 });
244
245const waitUntilExists = (
246 project: string,
247 instance: string,
248 databaseName: string,
249) =>
250 getByName(project, instance, databaseName).pipe(
251 Effect.flatMap((database) =>
252 database
253 ? Effect.succeed(database)
254 : Effect.fail(new DatabaseNotResolved({ instance, databaseName })),
255 ),
256 Effect.retry({
257 while: (error) => error._tag === "GCP.SQL.DatabaseNotResolved",
258 times: 8,
259 schedule: Schedule.spaced("1 second"),
260 }),
261 );
262
263const waitUntilGone = (
264 project: string,
265 instance: string,
266 databaseName: string,
267) =>
268 getByName(project, instance, databaseName).pipe(
269 Effect.flatMap((database) =>
270 database === undefined
271 ? Effect.void
272 : Effect.fail(new DatabaseStillExists({ instance, databaseName })),
273 ),
274 Effect.retry({
275 while: (error) => error._tag === "GCP.SQL.DatabaseStillExists",
276 times: 10,
277 schedule: Schedule.spaced("1 second"),
278 }),
279 );
280
281const toBody = (
282 news: DatabaseProps,
283 databaseName: string,
284 instance: string,
285 project: string,
286): sqladmin.Database => ({
287 name: databaseName,
288 instance,
289 project,
290 charset: news.charset,
291 collation: news.collation,
292 sqlserverDatabaseDetails: news.sqlserverDatabaseDetails,
293});
294
295export const DatabaseProvider = () =>
296 Provider.succeed(Database, {
297 stables: ["databaseName", "instance", "project", "selfLink"],
298
299 diff: Effect.fn(function* ({ news, olds, output }) {
300 if (!isResolved(news)) return undefined;
301 const previousInstance = olds?.instance ?? output?.instance;
302 const nextInstance = news.instance;
303 const previousName = olds?.databaseName ?? output?.databaseName;
304 const nextName = news.databaseName ?? previousName;
305 const instanceChanged =
306 previousInstance !== undefined &&
307 instanceIdOf(previousInstance) !== instanceIdOf(nextInstance);
308 const nameChanged =
309 previousName !== undefined &&
310 nextName !== undefined &&
311 previousName !== nextName;
312 if (!instanceChanged && !nameChanged) return undefined;
313 return {
314 action: "replace" as const,
315 deleteFirst: false,
316 };
317 }),
318
319 read: Effect.fn(function* ({ id, olds, output }) {
320 const env = yield* GcpEnvironment.current;
321 const instance = instanceIdOf(olds?.instance ?? output?.instance ?? "");
322 if (instance.length === 0) return undefined;
323 const databaseName = yield* toDatabaseName(
324 id,
325 olds?.databaseName,
326 output?.databaseName,
327 );
328 const existing = yield* getByName(env.project, instance, databaseName);
329 if (existing === undefined) return undefined;
330 return toAttrs(existing, env.project, instance);
331 }),
332
333 list: () =>
334 Effect.gen(function* () {
335 const env = yield* GcpEnvironment.current;
336 const instances = yield* sqladmin.listInstances
337 .items({
338 project: env.project,
339 maxResults: 1000,
340 filter: "instanceType:CLOUD_SQL_INSTANCE",
341 })
342 .pipe(
343 Stream.filter((instance) =>
344 hasAlchemyInstanceLabels(instance.settings?.userLabels),
345 ),
346 Stream.runCollect,
347 Effect.map((chunk) => Array.from(chunk)),
348 Effect.catchTag("NotFound", () =>
349 Effect.succeed([] as sqladmin.DatabaseInstance[]),
350 ),
351 );
352 const pages = yield* Effect.forEach(
353 instances,
354 (instance) => {
355 const instanceName = instance.name;
356 if (instanceName === undefined || instanceName.length === 0) {
357 return Effect.succeed([] as Database["Attributes"][]);
358 }
359 return sqladmin
360 .listDatabases({
361 project: env.project,
362 instance: instanceName,
363 })
364 .pipe(
365 Effect.map((page) =>
366 (page.items ?? [])
367 .filter(isUserDatabase)
368 .map((database) =>
369 toAttrs(database, env.project, instanceName),
370 ),
371 ),
372 // The instance may be deleted mid-listing.
373 Effect.catchTag("NotFound", () =>
374 Effect.succeed([] as Database["Attributes"][]),
375 ),
376 recoverIfInstanceMissing(
377 env.project,
378 instanceName,
379 () => [] as Database["Attributes"][],
380 ),
381 );
382 },
383 { concurrency: 4 },
384 );
385 return pages.flat();
386 }),
387
388 reconcile: Effect.fn(function* ({ id, news, output }) {
389 const env = yield* GcpEnvironment.current;
390 const instance = instanceIdOf(news.instance);
391 const databaseName = yield* toDatabaseName(
392 id,
393 news.databaseName,
394 output?.databaseName,
395 );
396
397 let current = yield* getByName(env.project, instance, databaseName);
398
399 if (current === undefined) {
400 yield* sqladmin
401 .insertDatabases({
402 project: env.project,
403 instance,
404 body: toBody(news, databaseName, instance, env.project),
405 })
406 .pipe(
407 Effect.flatMap((operation) =>
408 waitForOperation(env.project, operation),
409 ),
410 Effect.catchTag("Conflict", (error) =>
411 getByName(env.project, instance, databaseName).pipe(
412 Effect.flatMap((existing) =>
413 existing ? Effect.void : Effect.fail(error),
414 ),
415 ),
416 ),
417 Effect.retry({
418 while: (error) => error._tag === "Conflict",
419 times: 8,
420 schedule: Schedule.spaced("5 seconds"),
421 }),
422 );
423 current = yield* waitUntilExists(env.project, instance, databaseName);
424 }
425
426 const charsetChanged =
427 news.charset !== undefined &&
428 normalizeText(current.charset) !== normalizeText(news.charset);
429 const collationChanged =
430 news.collation !== undefined &&
431 normalizeText(current.collation) !== normalizeText(news.collation);
432 const sqlServerChanged =
433 news.sqlserverDatabaseDetails !== undefined &&
434 sqlServerKey(current.sqlserverDatabaseDetails) !==
435 sqlServerKey(news.sqlserverDatabaseDetails);
436
437 if (charsetChanged || collationChanged || sqlServerChanged) {
438 const patched = yield* sqladmin.patchDatabases({
439 project: env.project,
440 instance,
441 database: databaseName,
442 body: toBody(news, databaseName, instance, env.project),
443 });
444 yield* waitForOperation(env.project, patched);
445 current = yield* waitUntilExists(env.project, instance, databaseName);
446 }
447
448 return toAttrs(current, env.project, instance);
449 }),
450
451 delete: Effect.fn(function* ({ olds, output }) {
452 if (olds.deletionPolicy === "ABANDON") return;
453 const project = output.project;
454 const instance = instanceIdOf(output.instance);
455 const databaseName = output.databaseName;
456 yield* sqladmin
457 .deleteDatabases({
458 project,
459 instance,
460 database: databaseName,
461 })
462 .pipe(
463 Effect.flatMap((operation) =>
464 waitForOperation(project, operation, { notFoundOk: true }),
465 ),
466 Effect.catchTag("NotFound", () => Effect.void),
467 recoverIfInstanceMissing(project, instance, () => undefined),
468 Effect.retry({
469 while: (error) => error._tag === "Conflict",
470 times: 8,
471 schedule: Schedule.spaced("5 seconds"),
472 }),
473 );
474 yield* waitUntilGone(project, instance, databaseName);
475 }),
476 });