1import type * as sqladmin from "@distilled.cloud/gcp/sqladmin_v1";
2import * as Alchemy from "alchemy";
3import * as GCP from "alchemy/GCP";
4import {
5 applyMigrations,
6 inlineSqlParams,
7 MigrationError,
8 type SqlExecutor,
9} from "alchemy/SQL/Migrations/index";
10import { RuntimeContext } from "alchemy/RuntimeContext";
11import * as Effect from "effect/Effect";
12import * as FileSystem from "effect/FileSystem";
13import * as Path from "effect/Path";
14import * as Redacted from "effect/Redacted";
15import * as Schedule from "effect/Schedule";
16import { database } from "./database.ts";
17
18/** Rows of the last statement's result, keyed by column name. */
19const rowsOf = (response: sqladmin.SqlInstancesExecuteSqlResponse) => {
20 const result = response.results?.at(-1);
21 const columns = (result?.columns ?? []).map((column) => column.name ?? "");
22 return (result?.rows ?? []).map((row) =>
23 Object.fromEntries(
24 columns.map((name, index) => {
25 const cell = row.values?.[index];
26 return [name, cell?.nullValue ? null : (cell?.value ?? null)];
27 }),
28 ),
29 );
30};
31
32/**
33 * Applies the drizzle-kit migrations in `./migrations` through the Cloud
34 * SQL Data API (`instances.executeSql`) at deploy time — no network path
35 * from the deploying machine to the database is needed. Bookkeeping lives
36 * in `__alchemy_migrations`, so each migration runs exactly once.
37 *
38 * The Data API logs in as a built-in user with a password read from a
39 * regional Secret Manager secret, so the Action first makes sure the
40 * secret's latest version is the user's password.
41 */
42export const migrate = Effect.gen(function* () {
43 const { instance, db, user, password, passwordSecret, schema } =
44 yield* database;
45
46 const Migrate = Alchemy.Action(
47 "Migrate",
48 Effect.gen(function* () {
49 const executeSql = yield* GCP.SQL.ExecuteSql(instance);
50 const secret = yield* GCP.SecretManager.ReadWriteSecret(passwordSecret);
51 const fs = yield* FileSystem.FileSystem;
52 const path = yield* Path.Path;
53
54 return Effect.fn(function* (input: {
55 password: Redacted.Redacted<string>;
56 secretName: string;
57 database: string;
58 user: string;
59 migrationsDir: string;
60 snapshotHash: string;
61 }) {
62 const value = Redacted.value(input.password);
63 if ((yield* secret.access()) !== value) {
64 yield* secret.addVersion(value);
65 }
66
67 // The migrator's executor takes context-free effects; capture the
68 // Action's runtime context once and provide it to each statement.
69 const runtime = yield* RuntimeContext;
70 const run = (sqlStatement: string) =>
71 executeSql({
72 body: {
73 database: input.database,
74 user: input.user,
75 passwordSecretVersion: `${input.secretName}/versions/latest`,
76 sqlStatement,
77 },
78 }).pipe(
79 // A freshly created user can take a few seconds to accept logins.
80 Effect.retry({
81 schedule: Schedule.spaced("5 seconds"),
82 times: 6,
83 }),
84 Effect.mapError(
85 (cause) =>
86 new MigrationError({
87 message: `executeSql failed: ${String(cause)}`,
88 cause,
89 }),
90 ),
91 Effect.flatMap((response) =>
92 response.status?.code
93 ? Effect.fail(
94 new MigrationError({
95 message: `executeSql failed: ${response.status.message}`,
96 }),
97 )
98 : Effect.succeed(response),
99 ),
100 Effect.provideService(RuntimeContext, runtime),
101 );
102
103 const executor: SqlExecutor = {
104 dialect: "postgres",
105 query: (sql, params) =>
106 run(inlineSqlParams(sql, params ?? [], "postgres")).pipe(
107 Effect.map(rowsOf),
108 ),
109 // One request, one transaction. drizzle-kit leaves the last
110 // statement of a migration without its `;`.
111 batch: (statements) =>
112 run(
113 [
114 "BEGIN;",
115 ...statements.map((statement) =>
116 statement.trim().endsWith(";")
117 ? statement.trim()
118 : `${statement.trim()};`,
119 ),
120 "COMMIT;",
121 ].join("\n"),
122 ).pipe(Effect.asVoid),
123 };
124
125 yield* applyMigrations({
126 resolved: { dir: input.migrationsDir, table: "__alchemy_migrations" },
127 executor,
128 }).pipe(
129 Effect.provideService(FileSystem.FileSystem, fs),
130 Effect.provideService(Path.Path, path),
131 );
132 return input.snapshotHash;
133 });
134 }).pipe(
135 Effect.provide([
136 GCP.SQL.ExecuteSqlHttp,
137 GCP.SecretManager.ReadWriteSecretHttp,
138 ]),
139 ),
140 );
141
142 return yield* Migrate({
143 password: password.text,
144 secretName: passwordSecret.name,
145 database: db.databaseName,
146 user: user.userName,
147 migrationsDir: schema.out,
148 snapshotHash: schema.snapshotHash,
149 });
150});