| 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. |
| 24 | - `Alchemy.Random` `AppPassword` — the user's password, generated once and |
| 25 | kept in state. |
| 26 | - `GCP.SecretManager.LocationsSecret` `AppPassword` — a regional secret in |
| 27 | the instance's region (`location: instance.region`) holding that password. The Data API only reads passwords |
| 28 | from regional secrets in the instance's region. |
| 29 | - `Drizzle.Schema` `Schema` — regenerates `./migrations` with drizzle-kit |
| 30 | when [`src/schema.ts`](./src/schema.ts) changes. |
| 31 | - `Alchemy.Action` `Migrate` ([`src/migrate.ts`](./src/migrate.ts)) — at |
| 32 | deploy time, writes the password as the secret's latest version and |
| 33 | applies pending migrations with `instances.executeSql`, recording them |
| 34 | in `__alchemy_migrations`. |
| 35 | - `GCP.Function` `Api` ([`src/Api.ts`](./src/Api.ts)) — public Cloud Run |
| 36 | service (`invokerIamDisabled: true`) running Drizzle over |
| 37 | `/cloudsql/{connectionName}`. It depends on the `Migrate` output, so it |
| 38 | only rolls out once the schema exists. |
| 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.SQL.ExecuteSql(instance)` | `Migrate` | none — Actions run as the deploying identity | |
| 47 | | `GCP.SecretManager.ReadWriteSecret(secret)` | `Migrate` | none — Actions run as the deploying identity | |
| 48 | |
| 49 | `GCP.SQL.Connect` adds the instance to the service's `cloudsql` volume, so |
| 50 | Cloud Run starts its Cloud SQL connector and the Postgres socket appears |
| 51 | at `/cloudsql/{project}:{region}:{instance}`. Each request reads the |
| 52 | password from Secret Manager and hands Drizzle a socket URL. |
| 53 | |
| 54 | The deploying identity needs `cloudsql.instances.executeSql` (e.g. |
| 55 | `roles/cloudsql.admin`) and access to the secret. |
| 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), `Migrate` |
| 81 | 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. |