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

main
md 104 lines 4.93 KB
Rendered Raw
1 # gcp-cloud-sql-drizzle
2
3 A todo API on Cloud Run backed by Cloud SQL for PostgreSQL, queried with
4 Drizzle. The service reaches the database over Cloud Run's built-in Cloud
5 SQL Unix socket — no authorized networks, no VPC — and the Drizzle
6 migrations are applied at deploy time through the Cloud SQL Data API.
7
8 | Method | Path | Body |
9 | -------- | --------------- | ---------------------------------------- |
10 | `GET` | `/todos` | — |
11 | `POST` | `/todos` | `{ "id": "<uuid>", "title": "Ship it" }` |
12 | `GET` | `/todos/<uuid>` | — |
13 | `PATCH` | `/todos/<uuid>` | `{ "done": true }` |
14 | `DELETE` | `/todos/<uuid>` | — |
15
16 Invalid bodies and ids get `400`.
17
18 ## Architecture
19
20 - `GCP.SQL.Instance` `Postgres` — PostgreSQL 17, `db-f1-micro`, zonal, no
21 backups, Data API enabled. No authorized networks.
22 - `GCP.SQL.Database` `App` and `GCP.SQL.User` `App` — the application
23 database and its built-in login. Each deploy applies pending migrations
24 from `./migrations` through the Cloud SQL Data API, logged in as that
25 user, recording them in `__alchemy_migrations`.
26 - `Alchemy.Random` `AppPassword` — the user's password, generated once and
27 kept in state.
28 - `GCP.SecretManager.LocationsSecret` `AppPassword` — a regional secret in
29 the instance's region holding that password, for the service to read at
30 runtime.
31 - `Drizzle.Schema` `Schema` — regenerates `./migrations` with drizzle-kit
32 when [`src/schema.ts`](./src/schema.ts) changes.
33 - `Alchemy.Action` `StorePassword` ([`src/password.ts`](./src/password.ts)) —
34 at deploy time, writes the password as the secret's latest version.
35 - `GCP.Function` `Api` ([`src/Api.ts`](./src/Api.ts)) — public Cloud Run
36 service (`invokerIamDisabled: true`) running Drizzle over
37 `/cloudsql/{connectionName}`. It binds the database, so it only rolls
38 out once the migrations are applied.
39
40 ## Bindings
41
42 | Binding | Where | Grants |
43 | ------------------------------------------------ | ------------- | -------------------------------------------------------------------------------------------------------- |
44 | `GCP.SQL.Connect(instance, { … })` | `Api` | `roles/cloudsql.client` on the project under an IAM Condition naming only `Postgres`; mounts its socket |
45 | ↳ reads `passwordSecret` | `Api` | `roles/secretmanager.secretAccessor` on `AppPassword` only |
46 | `GCP.SecretManager.ReadWriteSecret(secret)` | `StorePassword` | none — Actions run as the deploying identity |
47
48 `GCP.SQL.Connect` adds the instance to the service's `cloudsql` volume, so
49 Cloud Run starts its Cloud SQL connector and the Postgres socket appears
50 at `/cloudsql/{project}:{region}:{instance}`. Each request reads the
51 password from Secret Manager and hands Drizzle a socket URL.
52
53 The deploying identity needs `cloudsql.instances.executeSql` (e.g.
54 `roles/cloudsql.admin`) and Secret Manager access: migrations stage the
55 password in a short-lived regional secret while they run.
56
57 ## Deploy
58
59 Credentials come from your alchemy profile: run `alchemy profile` once and pick GCP (*Service account JSON* for a key file, or *Stored* for an access token or key kept in `~/.alchemy/credentials`, plus a default region), then deploy with `--profile <name>`.
60
61 ```sh
62 pnpm deploy --profile <name>
63 ```
64
65 Creating the Cloud SQL instance takes 5–10 minutes. The stack prints the
66 service `url`.
67
68 ```sh
69 curl -X POST "$URL/todos" -H 'content-type: application/json' \
70 -d '{"id":"aaaaaaaa-0000-4000-8000-000000000001","title":"Ship it"}'
71 curl "$URL/todos"
72 ```
73
74 A fresh service account's `cloudsql.client` grant can take a few minutes
75 to propagate; until then requests answer `500`.
76
77 ## Changing the schema
78
79 Edit [`src/schema.ts`](./src/schema.ts) and deploy. `Drizzle.Schema`
80 writes a new migration under `./migrations` (commit it), the `App`
81 database applies it, and the service rolls a new revision.
82
83 ## Test
84
85 ```sh
86 ALCHEMY_PROFILE=<name> bun test
87 ```
88
89 Deploys the stack, checks through the Data API that the migration was
90 applied, drives create / read / update / list / delete over HTTP while
91 checking the rows directly in Cloud SQL, then destroys the stack and
92 checks that the instance, secret, and service are gone. Needs
93 Docker (the service image is built locally). Allow about 20 minutes.
94
95 ## Destroy
96
97 ```sh
98 pnpm destroy --profile <name>
99 ```
100
101 Deletes the service, its image repository and service account, the
102 secret, the user, the database, and the instance (with its data). Cloud
103 SQL keeps a deleted instance's name reserved for about a week, so the
104 next deploy gets a freshly generated name.