1import * as run from "@distilled.cloud/gcp/run_v2";
2import * as secretmanager from "@distilled.cloud/gcp/secretmanager_v1";
3import * as sqladmin from "@distilled.cloud/gcp/sqladmin_v1";
4import * as Alchemy from "alchemy";
5import * as GCP from "alchemy/GCP";
6import * as Test from "alchemy/Test/Bun";
7import { describe, expect } from "bun:test";
8import * as Effect from "effect/Effect";
9import * as Layer from "effect/Layer";
10import * as FetchHttpClient from "effect/http/FetchHttpClient";
11import * as Schedule from "effect/Schedule";
12import * as HttpClient from "effect/http/HttpClient";
13import * as HttpClientRequest from "effect/http/HttpClientRequest";
14import { spawnSync } from "node:child_process";
15import Stack, { providers } from "../alchemy.run.ts";
16
17const { test, beforeAll, afterAll, deploy, destroy } = Test.make({
18 providers: providers(),
19 state: Alchemy.localState(),
20});
21
22const { getWhenReady } = Test;
23
24// Out-of-band calls to the Google APIs resolve the same stored credentials
25// the deploy uses, so the test runs against the configured profile.
26// Distilled routes the regional secret to its regional endpoint.
27const GcpHttp = Layer.mergeAll(
28 GCP.GcpAuth,
29 GCP.fromAuthProvider(),
30 FetchHttpClient.layer,
31);
32
33// The service is built from `main`, which needs a local image build.
34const dockerAvailable =
35 spawnSync("docker", ["info"], { stdio: "ignore", timeout: 15_000 }).status ===
36 0;
37
38// Deploy, tests and destroy all sit behind the same Docker guard.
39describe.skipIf(!dockerAvailable)("gcp-cloud-sql-drizzle", () => {
40 // Creating a Cloud SQL instance takes 5–10 minutes (and deleting one a
41 // few more); everything else is bounded well below that.
42 const stack = beforeAll(deploy(Stack), { timeout: 1_500_000 });
43
44 afterAll.skipIf(!!process.env.NO_DESTROY)(
45 Effect.gen(function* () {
46 const outputs = yield* stack;
47 yield* destroy(Stack);
48 if (outputs === undefined) return;
49
50 const project = outputs.connectionName?.split(":")[0] ?? "";
51 const instance = yield* sqladmin
52 .getInstances({ project, instance: outputs.instanceName })
53 .pipe(
54 Effect.map(() => "present" as const),
55 Effect.catchTag("NotFound", () => Effect.succeed("gone" as const)),
56 Effect.orDie,
57 Effect.provide(GcpHttp),
58 );
59 expect(instance).toBe("gone");
60
61 const secret = yield* secretmanager
62 .getProjectsLocationsSecrets({ name: outputs.passwordSecretName })
63 .pipe(
64 Effect.map(() => "present" as const),
65 Effect.catchTag("NotFound", () => Effect.succeed("gone" as const)),
66 Effect.orDie,
67 Effect.provide(GcpHttp),
68 );
69 expect(secret).toBe("gone");
70
71 const service = yield* run
72 .getProjectsLocationsServices({ name: outputs.serviceName })
73 .pipe(
74 Effect.map(() => "present" as const),
75 Effect.catchTag("NotFound", () => Effect.succeed("gone" as const)),
76 Effect.orDie,
77 Effect.provide(GcpHttp),
78 );
79 expect(service).toBe("gone");
80 }),
81 { timeout: 1_200_000 },
82 );
83
84 const baseUrlOf = (url: string | undefined) => {
85 if (url === undefined) throw new Error("the service has no URL");
86 return url.replace(/\/+$/, "");
87 };
88
89 const request = (
90 method: "GET" | "POST" | "PATCH" | "DELETE",
91 url: string,
92 body?: unknown,
93 ) =>
94 HttpClient.execute(
95 body === undefined
96 ? HttpClientRequest.make(method)(url)
97 : HttpClientRequest.make(method)(url).pipe(
98 HttpClientRequest.bodyJsonUnsafe(body),
99 ),
100 );
101
102 /** Query the database directly through the Cloud SQL Data API. */
103 const executeSql = (
104 outputs: {
105 instanceName: string;
106 connectionName: string | undefined;
107 databaseName: string;
108 userName: string;
109 passwordSecretName: string;
110 },
111 sqlStatement: string,
112 ) =>
113 sqladmin
114 .executeSqlInstances({
115 project: outputs.connectionName?.split(":")[0] ?? "",
116 instance: outputs.instanceName,
117 body: {
118 database: outputs.databaseName,
119 user: outputs.userName,
120 passwordSecretVersion: `${outputs.passwordSecretName}/versions/latest`,
121 sqlStatement,
122 },
123 })
124 .pipe(
125 Effect.map((response) => {
126 const result = response.results?.at(-1);
127 const columns = (result?.columns ?? []).map((c) => c.name ?? "");
128 return (result?.rows ?? []).map((row) =>
129 Object.fromEntries(
130 columns.map((name, index) => [
131 name,
132 row.values?.[index]?.nullValue
133 ? null
134 : row.values?.[index]?.value,
135 ]),
136 ),
137 );
138 }),
139 Effect.orDie,
140 Effect.provide(GcpHttp),
141 );
142
143 test(
144 "applies the drizzle migration at deploy time",
145 Effect.gen(function* () {
146 const outputs = yield* stack;
147 const applied = yield* executeSql(
148 outputs,
149 "SELECT name FROM __alchemy_migrations",
150 );
151 expect(applied).toHaveLength(1);
152 expect(String(applied[0]?.name)).toMatch(/^\d{14}_/);
153 const columns = yield* executeSql(
154 outputs,
155 "SELECT column_name FROM information_schema.columns WHERE table_name = 'todos' ORDER BY column_name",
156 );
157 expect(columns.map((row) => row.column_name)).toEqual([
158 "done",
159 "id",
160 "title",
161 ]);
162 }),
163 { timeout: 120_000 },
164 );
165
166 test(
167 "serves CRUD over Drizzle on the Cloud SQL socket",
168 Effect.gen(function* () {
169 const outputs = yield* stack;
170 const baseUrl = baseUrlOf(outputs.url);
171 yield* getWhenReady(`${baseUrl}/`);
172
173 const id = "aaaaaaaa-0000-4000-8000-000000000001";
174 // The project-level cloudsql.client grant (under an IAM Condition) can
175 // take a few minutes to reach a fresh service account; until then the
176 // socket refuses the connection and the service answers 500.
177 const empty = yield* request("DELETE", `${baseUrl}/todos/${id}`).pipe(
178 Effect.repeat({
179 schedule: Schedule.spaced("10 seconds"),
180 until: (response) => response.status !== 500,
181 times: 42,
182 }),
183 );
184 expect(empty.status).toBe(204);
185
186 const created = yield* request("POST", `${baseUrl}/todos`, {
187 id,
188 title: "Ship the Cloud SQL guide",
189 });
190 expect(created.status).toBe(201);
191 expect(yield* created.json).toEqual([
192 { id, title: "Ship the Cloud SQL guide", done: false },
193 ]);
194
195 // The row is really in Cloud SQL.
196 const rows = yield* executeSql(
197 outputs,
198 `SELECT id, title, done FROM todos WHERE id = '${id}'`,
199 );
200 expect(rows).toEqual([
201 { id, title: "Ship the Cloud SQL guide", done: "false" },
202 ]);
203
204 const updated = yield* request("PATCH", `${baseUrl}/todos/${id}`, {
205 done: true,
206 });
207 expect(updated.status).toBe(200);
208 expect(yield* updated.json).toEqual({
209 id,
210 title: "Ship the Cloud SQL guide",
211 done: true,
212 });
213
214 const listed = yield* request("GET", `${baseUrl}/todos`);
215 expect(listed.status).toBe(200);
216 expect(yield* listed.json).toEqual([
217 { id, title: "Ship the Cloud SQL guide", done: true },
218 ]);
219
220 const invalid = yield* request("POST", `${baseUrl}/todos`, {
221 id: "not-a-uuid",
222 title: "",
223 });
224 expect(invalid.status).toBe(400);
225
226 const deleted = yield* request("DELETE", `${baseUrl}/todos/${id}`);
227 expect(deleted.status).toBe(204);
228 const gone = yield* request("GET", `${baseUrl}/todos/${id}`);
229 expect(gone.status).toBe(404);
230 expect(
231 yield* executeSql(outputs, `SELECT id FROM todos WHERE id = '${id}'`),
232 ).toEqual([]);
233 }),
234 { timeout: 600_000 },
235 );
236});