import {
diffMigrations,
migrationAttributesOf,
migrationsAttrs,
migrationsInputOf,
stampedOf,
type MigrationAttributes,
type MigrationsInput,
} from "@alchemy.run/sql/Migrations";
import * as sqladmin from "@distilled.cloud/gcp/sqladmin_v1";
import { isResolved } from "alchemy/Diff";
import { createPhysicalName } from "alchemy/PhysicalName";
import * as Provider from "alchemy/Provider";
import { Resource } from "alchemy/Resource";
import * as Data from "effect/Data";
import * as Effect from "effect/Effect";
import type * as Redacted from "effect/Redacted";
import * as Schedule from "effect/Schedule";
import * as Stream from "effect/Stream";
import { GcpEnvironment } from "../Environment.ts";
import { ALCHEMY_LABEL_PREFIX, createInternalLabels } from "../Labels.ts";
import type { Providers } from "../Providers.ts";
import { runDatabaseMigrations, type MigrationsTransport } from "./Migrations.ts";
import { recoverIfInstanceMissing, waitForSqlOperation } from "./operations.ts";
// The SQL Admin API rejects longer database names on every engine.
const MAX_NAME_LENGTH = 60;
const SYSTEM_DATABASES = new Set([
"mysql",
"information_schema",
"performance_schema",
"sys",
"postgres",
"template0",
"template1",
"master",
"model",
"msdb",
"tempdb",
]);
export type DatabaseDeletionPolicy = "DELETE" | "ABANDON";
export type SqlServerDatabaseDetails = {
/**
* SQL Server compatibility level (e.g. `150`).
*/
compatibilityLevel?: number;
/**
* SQL Server recovery model (`FULL`, `SIMPLE`, `BULK_LOGGED`).
*/
recoveryModel?: string;
};
export type DatabaseProps = {
/**
* Cloud SQL instance id (the `{instance}` segment of
* `projects/{project}/instances/{instance}`). Full resource names are
* accepted and reduced to the last path segment. Immutable — changing
* it replaces the database.
*/
instance: string;
/**
* Database name inside the instance. If omitted, a unique name is
* generated from the stack, stage, and logical id. Must start with a
* letter and contain only letters, numbers, and underscores (1–60
* characters). Immutable — changing it replaces the database.
*/
databaseName?: string;
/**
* Character set (e.g. `utf8mb4` on MySQL, `UTF8` on PostgreSQL). The
* engine may reject in-place charset changes (PostgreSQL).
*/
charset?: string;
/**
* Collation (e.g. `utf8mb4_unicode_ci` on MySQL, `en_US.UTF8` on
* PostgreSQL). The engine may reject in-place collation changes.
*/
collation?: string;
/**
* SQL Server–only settings. Ignored on MySQL and PostgreSQL.
*/
sqlserverDatabaseDetails?: SqlServerDatabaseDetails;
/**
* What to do on destroy. `ABANDON` removes the database from state
* without calling the Cloud SQL API — useful when the parent instance
* is being deleted.
* @default "DELETE"
*/
deletionPolicy?: DatabaseDeletionPolicy;
/**
* SQL migrations to apply on deploy, once the database exists. Accepts a
* directory path, a `{ dir, table }` object, or a `Drizzle.Schema`
* output. Only pending migrations run; editing or removing an applied
* file fails the deploy. PostgreSQL and MySQL instances only.
*
* Bookkeeping lives in Alchemy's `__alchemy_migrations` table. A
* database migrated earlier by drizzle-kit or Prisma is adopted by a
* one-way conversion on first deploy.
*/
migrations?: MigrationsInput;
/**
* How `migrations` reach the database: `"data-api"` (Cloud SQL's
* `executeSql`, no network route needed) or `"connector"` (a direct
* connection through the Cloud SQL connector, which needs a route to the
* instance's IP but no authorized networks).
* @default "data-api" when the instance has `dataApiAccess: true`, otherwise "connector"
*/
migrationsTransport?: MigrationsTransport;
/**
* Built-in database user that `migrations` log in as. It owns the tables
* the migrations create.
*/
migrationsUser?: string;
/**
* Password of `migrationsUser`. The Data API only reads passwords from
* Secret Manager, so the `"data-api"` transport stages it in a regional
* secret for the duration of the deploy and deletes it afterwards.
*/
migrationsPassword?: string | Redacted.Redacted<string>;
};
export type Database = Resource<
"GCP.SQL.Database",
DatabaseProps,
{
/** Database name inside the instance. */
databaseName: string;
/** Cloud SQL instance id. */
instance: string;
/** Project id. */
project: string;
/** Character set currently on the database. */
charset: string | undefined;
/** Collation currently on the database. */
collation: string | undefined;
/** SQL Server details, if this is a SQL Server database. */
sqlserverDatabaseDetails: SqlServerDatabaseDetails | undefined;
/** SQL Admin self-link. */
selfLink: string | undefined;
/** HTTP etag. */
etag: string | undefined;
/** Directory of the applied SQL migrations. */
migrationsDir: string | undefined;
/** Migration bookkeeping table. */
migrationsTable: string | undefined;
/** Applied migration content hashes. */
migrationsHashes: Record<string, string>;
},
never,
Providers
>;
/**
* A logical database inside a Cloud SQL instance.
*
* Cloud SQL databases have no labels field. `list` enumerates non-system
* databases on alchemy-labeled instances so `pnpm nuke:gcp` can find
* leaked rows without dropping `mysql` / `postgres` / `template1`.
*
* Changing `instance` or `databaseName` replaces the database. `charset`
* and `collation` update in place where the engine allows.
*
* ### Creating a Database
* **Example:** Generated name on a Cloud SQL instance
* ```typescript
* const instance = yield* GCP.SQL.Instance("AppDb", {
* tier: "db-f1-micro",
* backup: { enabled: false },
* });
* const database = yield* GCP.SQL.Database("App", {
* instance: instance.instanceName,
* });
* ```
*
* **Example:** Explicit name, charset, and collation
* ```typescript
* const database = yield* GCP.SQL.Database("App", {
* instance: instance.instanceName,
* databaseName: "app_production",
* charset: "utf8mb4",
* collation: "utf8mb4_unicode_ci",
* });
* ```
*
* ### Migrations
* **Example:** Apply migrations over the Data API
* ```typescript
* const instance = yield* GCP.SQL.Instance("AppDb", {
* databaseVersion: "POSTGRES_17",
* dataApiAccess: true,
* });
* const password = yield* Alchemy.Random("AppPassword");
* const user = yield* GCP.SQL.User("App", {
* instance: instance.instanceName,
* password: password.text,
* });
* const database = yield* GCP.SQL.Database("App", {
* instance: instance.instanceName,
* migrations: "./migrations",
* migrationsUser: user.userName,
* migrationsPassword: password.text,
* });
* ```
*
* Migrations run as `migrationsUser` through `instances.executeSql`, so
* the deploying machine needs no route to the database. The deploying
* identity needs `cloudsql.instances.executeSql` and Secret Manager
* access: the password is staged in a regional secret while migrations
* run.
*
* **Example:** Apply migrations through the Cloud SQL connector
* ```typescript
* const database = yield* GCP.SQL.Database("App", {
* instance: instance.instanceName,
* migrations: "./migrations",
* migrationsTransport: "connector",
* migrationsUser: user.userName,
* migrationsPassword: password.text,
* });
* ```
*
* The connector logs in with an ephemeral client certificate from the SQL
* Admin API, so the instance needs no authorized networks. The deploying
* machine needs a route to its IP: the public IP (the default), or the
* private IP from inside its network. Install `pg` (PostgreSQL) or `mysql2`
* (MySQL).
*
* **Example:** Migrations generated by Drizzle
* ```typescript
* const schema = yield* Drizzle.Schema("Schema", {
* schema: "./src/schema.ts",
* out: "./migrations",
* });
* const database = yield* GCP.SQL.Database("App", {
* instance: instance.instanceName,
* migrations: schema,
* migrationsUser: user.userName,
* migrationsPassword: password.text,
* });
* ```
*
* @resource
* @category SQL
*/
export const Database = Resource<Database>("GCP.SQL.Database");
export class DatabaseNotResolved extends Data.TaggedError("GCP.SQL.DatabaseNotResolved")<{
instance: string;
databaseName: string;
}> {}
export class DatabaseStillExists extends Data.TaggedError("GCP.SQL.DatabaseStillExists")<{
instance: string;
databaseName: string;
}> {}
const lastSegment = (value: string) => {
const trimmed = value.replace(/\/+$/, "");
const parts = trimmed.split("/");
return parts[parts.length - 1] || trimmed;
};
const instanceIdOf = (value: string) => lastSegment(value);
const isUserDatabase = (database: sqladmin.Database) => {
const name = (database.name ?? "").toLowerCase();
return name.length > 0 && !SYSTEM_DATABASES.has(name);
};
const hasAlchemyInstanceLabels = (labels: Record<string, string | undefined> | null | undefined) =>
Object.keys(labels ?? {}).some((key) => key.startsWith(ALCHEMY_LABEL_PREFIX));
const normalizeText = (value: string | undefined) => (value ?? "").toLowerCase();
const sqlServerKey = (details: SqlServerDatabaseDetails | undefined) =>
JSON.stringify({
compatibilityLevel: details?.compatibilityLevel ?? null,
recoveryModel: (details?.recoveryModel ?? "").toLowerCase(),
});
const toSqlIdentifier = (name: string) => {
let next = name
.toLowerCase()
.replace(/[^a-z0-9_]/g, "_")
.replace(/_+/g, "_")
.replace(/^_+|_+$/g, "");
if (!/^[a-z]/.test(next)) next = `d${next}`;
next = next.slice(0, MAX_NAME_LENGTH).replace(/_+$/g, "");
return next.length > 0 ? next : "database";
};
const toDatabaseName = (id: string, databaseName: string | undefined, existing?: string) =>
Effect.gen(function* () {
if (databaseName !== undefined) return databaseName;
if (existing !== undefined) return existing;
return toSqlIdentifier(
yield* createPhysicalName({
id,
maxLength: MAX_NAME_LENGTH,
lowercase: true,
delimiter: "_",
}),
);
});
const toAttrs = (
database: sqladmin.Database,
project: string,
instance: string,
migrations?: Partial<MigrationAttributes>,
): Database["Attributes"] => ({
databaseName: database.name ?? "",
instance: database.instance ?? instance,
project: database.project ?? project,
charset: database.charset,
collation: database.collation,
sqlserverDatabaseDetails: database.sqlserverDatabaseDetails,
selfLink: database.selfLink,
etag: database.etag,
...migrationAttributesOf(migrations),
});
const getByName = (project: string, instance: string, databaseName: string) =>
sqladmin.getDatabases({ project, instance, database: databaseName }).pipe(
Effect.catchTag("NotFound", () => Effect.undefined),
recoverIfInstanceMissing(project, instance, () => undefined),
);
const waitForOperation = (
project: string,
operation: sqladmin.Operation,
options?: { notFoundOk?: boolean },
) =>
waitForSqlOperation(project, operation, {
budget: "10 minutes",
notFoundOk: options?.notFoundOk,
});
const waitUntilExists = (project: string, instance: string, databaseName: string) =>
getByName(project, instance, databaseName).pipe(
Effect.flatMap((database) =>
database
? Effect.succeed(database)
: Effect.fail(new DatabaseNotResolved({ instance, databaseName })),
),
Effect.retry({
while: (error) => error._tag === "GCP.SQL.DatabaseNotResolved",
times: 8,
schedule: Schedule.spaced("1 second"),
}),
);
const waitUntilGone = (project: string, instance: string, databaseName: string) =>
getByName(project, instance, databaseName).pipe(
Effect.flatMap((database) =>
database === undefined
? Effect.void
: Effect.fail(new DatabaseStillExists({ instance, databaseName })),
),
Effect.retry({
while: (error) => error._tag === "GCP.SQL.DatabaseStillExists",
times: 10,
schedule: Schedule.spaced("1 second"),
}),
);
const toBody = (
news: DatabaseProps,
databaseName: string,
instance: string,
project: string,
): sqladmin.Database => ({
name: databaseName,
instance,
project,
charset: news.charset,
collation: news.collation,
sqlserverDatabaseDetails: news.sqlserverDatabaseDetails,
});
export const DatabaseProvider = () =>
Provider.succeed(Database, {
stables: ["databaseName", "instance", "project", "selfLink"],
diff: Effect.fn(function* ({ news, olds, output }) {
if (!isResolved(news)) return undefined;
const previousInstance = olds?.instance ?? output?.instance;
const nextInstance = news.instance;
const previousName = olds?.databaseName ?? output?.databaseName;
const nextName = news.databaseName ?? previousName;
const instanceChanged =
previousInstance !== undefined &&
instanceIdOf(previousInstance) !== instanceIdOf(nextInstance);
const nameChanged =
previousName !== undefined && nextName !== undefined && previousName !== nextName;
if (!instanceChanged && !nameChanged) {
if (yield* diffMigrations({ news, output })) {
return { action: "update" as const };
}
return undefined;
}
return {
action: "replace" as const,
deleteFirst: false,
};
}),
read: Effect.fn(function* ({ id, olds, output }) {
const env = yield* GcpEnvironment.current;
const instance = instanceIdOf(olds?.instance ?? output?.instance ?? "");
if (instance.length === 0) return undefined;
const databaseName = yield* toDatabaseName(id, olds?.databaseName, output?.databaseName);
const existing = yield* getByName(env.project, instance, databaseName);
if (existing === undefined) return undefined;
return toAttrs(existing, env.project, instance, output);
}),
list: () =>
Effect.gen(function* () {
const env = yield* GcpEnvironment.current;
const instances = yield* sqladmin.listInstances
.items({
project: env.project,
maxResults: 1000,
filter: "instanceType:CLOUD_SQL_INSTANCE",
})
.pipe(
Stream.filter((instance) => hasAlchemyInstanceLabels(instance.settings?.userLabels)),
Stream.runCollect,
Effect.map((chunk) => Array.from(chunk)),
Effect.catchTag("NotFound", () => Effect.succeed([] as sqladmin.DatabaseInstance[])),
);
const pages = yield* Effect.forEach(
instances,
(instance) => {
const instanceName = instance.name;
if (instanceName === undefined || instanceName.length === 0) {
return Effect.succeed([] as Database["Attributes"][]);
}
return sqladmin
.listDatabases({
project: env.project,
instance: instanceName,
})
.pipe(
Effect.map((page) =>
(page.items ?? [])
.filter(isUserDatabase)
.map((database) => toAttrs(database, env.project, instanceName)),
),
// The instance may be deleted mid-listing.
Effect.catchTag("NotFound", () => Effect.succeed([] as Database["Attributes"][])),
recoverIfInstanceMissing(
env.project,
instanceName,
() => [] as Database["Attributes"][],
),
);
},
{ concurrency: 4 },
);
return pages.flat();
}),
reconcile: Effect.fn(function* ({ id, news, output }) {
const env = yield* GcpEnvironment.current;
const instance = instanceIdOf(news.instance);
const databaseName = yield* toDatabaseName(id, news.databaseName, output?.databaseName);
let current = yield* getByName(env.project, instance, databaseName);
if (current === undefined) {
yield* sqladmin
.insertDatabases({
project: env.project,
instance,
body: toBody(news, databaseName, instance, env.project),
})
.pipe(
Effect.flatMap((operation) => waitForOperation(env.project, operation)),
Effect.catchTag("Conflict", (error) =>
getByName(env.project, instance, databaseName).pipe(
Effect.flatMap((existing) => (existing ? Effect.void : Effect.fail(error))),
),
),
Effect.retry({
while: (error) => error._tag === "Conflict",
times: 8,
schedule: Schedule.spaced("5 seconds"),
}),
);
current = yield* waitUntilExists(env.project, instance, databaseName);
}
const charsetChanged =
news.charset !== undefined &&
normalizeText(current.charset) !== normalizeText(news.charset);
const collationChanged =
news.collation !== undefined &&
normalizeText(current.collation) !== normalizeText(news.collation);
const sqlServerChanged =
news.sqlserverDatabaseDetails !== undefined &&
sqlServerKey(current.sqlserverDatabaseDetails) !==
sqlServerKey(news.sqlserverDatabaseDetails);
if (charsetChanged || collationChanged || sqlServerChanged) {
const patched = yield* sqladmin.patchDatabases({
project: env.project,
instance,
database: databaseName,
body: toBody(news, databaseName, instance, env.project),
});
yield* waitForOperation(env.project, patched);
current = yield* waitUntilExists(env.project, instance, databaseName);
}
// Sync migrations — the shared pipeline skips already-applied ones.
const migrationsInput = migrationsInputOf(news);
const migrations = migrationsInput
? yield* runDatabaseMigrations({
project: env.project,
instance,
database: databaseName,
input: migrationsInput,
stamped: stampedOf(output),
transport: news.migrationsTransport,
user: news.migrationsUser,
password: news.migrationsPassword,
labels: yield* createInternalLabels(id),
})
: undefined;
return toAttrs(
current,
env.project,
instance,
migrationsAttrs({ input: migrationsInput, run: migrations, output }),
);
}),
delete: Effect.fn(function* ({ olds, output }) {
if (olds.deletionPolicy === "ABANDON") return;
const project = output.project;
const instance = instanceIdOf(output.instance);
const databaseName = output.databaseName;
yield* sqladmin
.deleteDatabases({
project,
instance,
database: databaseName,
})
.pipe(
Effect.flatMap((operation) => waitForOperation(project, operation, { notFoundOk: true })),
Effect.catchTag("NotFound", () => Effect.void),
recoverIfInstanceMissing(project, instance, () => undefined),
Effect.retry({
while: (error) => error._tag === "Conflict",
times: 8,
schedule: Schedule.spaced("5 seconds"),
}),
);
yield* waitUntilGone(project, instance, databaseName);
}),
});