gcp-cloud-sql-drizzle#

A todo API on Cloud Run backed by Cloud SQL for PostgreSQL, queried with Drizzle. The service reaches the database over Cloud Run's built-in Cloud SQL Unix socket — no authorized networks, no VPC — and the Drizzle migrations are applied at deploy time through the Cloud SQL Data API.

Method	Path	Body
GET	/todos	—
POST	/todos	{ "id": "<uuid>", "title": "Ship it" }
GET	/todos/<uuid>	—
PATCH	/todos/<uuid>	{ "done": true }
DELETE	/todos/<uuid>	—

Invalid bodies and ids get 400.

Architecture#

• GCP.SQL.Instance Postgres — PostgreSQL 17, db-f1-micro, zonal, no backups, Data API enabled. No authorized networks.
• GCP.SQL.Database App and GCP.SQL.User App — the application database and its built-in login. Each deploy applies pending migrations from ./migrations through the Cloud SQL Data API, logged in as that user, recording them in __alchemy_migrations.
• Alchemy.Random AppPassword — the user's password, generated once and kept in state.
• GCP.SecretManager.LocationsSecret AppPassword — a regional secret in the instance's region holding that password, for the service to read at runtime.
• Drizzle.Schema Schema — regenerates ./migrations with drizzle-kit when src/schema.ts changes.
• Alchemy.Action StorePassword (src/password.ts) — at deploy time, writes the password as the secret's latest version.
• GCP.Function Api (src/Api.ts) — public Cloud Run service (invokerIamDisabled: true) running Drizzle over /cloudsql/{connectionName}. It binds the database, so it only rolls out once the migrations are applied.

Bindings#

Binding	Where	Grants
GCP.SQL.Connect(instance, { … })	Api	roles/cloudsql.client on the project under an IAM Condition naming only Postgres; mounts its socket
↳ reads passwordSecret	Api	roles/secretmanager.secretAccessor on AppPassword only
GCP.SecretManager.ReadWriteSecret(secret)	StorePassword	none — Actions run as the deploying identity

GCP.SQL.Connect adds the instance to the service's cloudsql volume, so Cloud Run starts its Cloud SQL connector and the Postgres socket appears at /cloudsql/{project}:{region}:{instance}. Each request reads the password from Secret Manager and hands Drizzle a socket URL.

The deploying identity needs cloudsql.instances.executeSql (e.g. roles/cloudsql.admin) and Secret Manager access: migrations stage the password in a short-lived regional secret while they run.

Deploy#

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>.

pnpm deploy --profile <name>

Creating the Cloud SQL instance takes 5–10 minutes. The stack prints the service url.

curl -X POST "$URL/todos" -H 'content-type: application/json' \
  -d '{"id":"aaaaaaaa-0000-4000-8000-000000000001","title":"Ship it"}'
curl "$URL/todos"

A fresh service account's cloudsql.client grant can take a few minutes to propagate; until then requests answer 500.

Changing the schema#

Edit src/schema.ts and deploy. Drizzle.Schema writes a new migration under ./migrations (commit it), the App database applies it, and the service rolls a new revision.

Test#

ALCHEMY_PROFILE=<name> bun test

Deploys the stack, checks through the Data API that the migration was applied, drives create / read / update / list / delete over HTTP while checking the rows directly in Cloud SQL, then destroys the stack and checks that the instance, secret, and service are gone. Needs Docker (the service image is built locally). Allow about 20 minutes.

Destroy#

pnpm destroy --profile <name>

Deletes the service, its image repository and service account, the secret, the user, the database, and the instance (with its data). Cloud SQL keeps a deleted instance's name reserved for about a week, so the next deploy gets a freshly generated name.