The best way to control Claude Code, Codex and more from any computer or phone
| 1 | --- |
| 2 | title: Cloud SQL |
| 3 | description: Run Postgres on Cloud SQL, connect Cloud Run to it over the Cloud SQL socket with GCP.SQL.Connect, and query it with Drizzle. |
| 4 | --- |
| 5 | |
| 6 | import PackageManagers from "../../../../components/PackageManagers.astro"; |
| 7 | |
| 8 | export const pkgs = "@alchemy.run/drizzle drizzle-orm"; |
| 9 | |
| 10 | Cloud SQL runs Postgres or MySQL for you. A Cloud Run service reaches |
| 11 | it through the Cloud SQL socket, with no public IP and no VPC to set |
| 12 | up. This page builds a Postgres todo API with Drizzle. |
| 13 | |
| 14 | <PackageManagers add={pkgs} /> |
| 15 | |
| 16 | ## Create the instance |
| 17 | |
| 18 | ```typescript |
| 19 | // src/database.ts |
| 20 | import * as Drizzle from "@alchemy.run/drizzle"; |
| 21 | import * as GCP from "@alchemy.run/gcp"; |
| 22 | import * as Alchemy from "alchemy"; |
| 23 | import * as Effect from "effect/Effect"; |
| 24 | |
| 25 | export const database = Effect.gen(function* () { |
| 26 | const instance = yield* GCP.SQL.Instance("Postgres", { |
| 27 | databaseVersion: "POSTGRES_17", |
| 28 | edition: "ENTERPRISE", |
| 29 | tier: "db-f1-micro", |
| 30 | availabilityType: "ZONAL", |
| 31 | backup: { enabled: false }, |
| 32 | dataApiAccess: true, |
| 33 | }); |
| 34 | ``` |
| 35 | |
| 36 | `db-f1-micro` with no backups is the cheapest Postgres Cloud SQL runs. |
| 37 | Creating an instance takes 5 to 15 minutes. |
| 38 | |
| 39 | ## Add a user |
| 40 | |
| 41 | ```typescript |
| 42 | const password = yield* Alchemy.Random("AppPasswordValue", { bytes: 24 }); |
| 43 | const user = yield* GCP.SQL.User("AppUser", { |
| 44 | instance: instance.instanceName, |
| 45 | password: password.text, |
| 46 | deletionPolicy: "ABANDON", |
| 47 | }); |
| 48 | ``` |
| 49 | |
| 50 | `Alchemy.Random` generates the password once and keeps it in state. |
| 51 | `ABANDON` leaves the user in place on destroy, because Postgres won't |
| 52 | drop a user that owns tables. Deleting the instance removes it. |
| 53 | |
| 54 | ## Keep the password in Secret Manager |
| 55 | |
| 56 | The service reads the password from a secret in the instance's region: |
| 57 | |
| 58 | ```typescript |
| 59 | const passwordSecret = yield* GCP.SecretManager.LocationsSecret("AppPassword", { |
| 60 | location: instance.region, |
| 61 | }); |
| 62 | ``` |
| 63 | |
| 64 | The example's `StorePassword` Action writes the password into the secret |
| 65 | at deploy time. Copy |
| 66 | [`src/password.ts`](https://github.com/alchemy-run/alchemy/blob/main/examples/gcp-cloud-sql-drizzle/src/password.ts) |
| 67 | into your project and yield it from your stack. |
| 68 | |
| 69 | ## Apply migrations at deploy time |
| 70 | |
| 71 | ```typescript |
| 72 | const schema = yield* Drizzle.Schema("Schema", { |
| 73 | schema: "./src/schema.ts", |
| 74 | out: "./migrations", |
| 75 | }); |
| 76 | |
| 77 | const db = yield* GCP.SQL.Database("App", { |
| 78 | instance: instance.instanceName, |
| 79 | migrations: schema, |
| 80 | migrationsUser: user.userName, |
| 81 | migrationsPassword: password.text, |
| 82 | }); |
| 83 | |
| 84 | return { instance, db, user, password, passwordSecret, schema }; |
| 85 | }); |
| 86 | ``` |
| 87 | |
| 88 | `Drizzle.Schema` regenerates `./migrations` whenever `src/schema.ts` |
| 89 | changes. Each deploy applies the pending ones as `migrationsUser` |
| 90 | through the Cloud SQL Data API, so your machine never needs a network |
| 91 | path to the database. Set `migrationsTransport: "connector"` to connect |
| 92 | through the Cloud SQL connector instead. |
| 93 | |
| 94 | ## Connect from Cloud Run |
| 95 | |
| 96 | `GCP.SQL.Connect` mounts the instance's socket into the service and |
| 97 | returns a connection URL. Pass it to Drizzle: |
| 98 | |
| 99 | ```typescript |
| 100 | // src/Api.ts |
| 101 | import * as Drizzle from "@alchemy.run/drizzle/Postgres"; |
| 102 | import * as GCP from "@alchemy.run/gcp"; |
| 103 | import * as Effect from "effect/Effect"; |
| 104 | import * as HttpServerResponse from "effect/http/HttpServerResponse"; |
| 105 | import { database } from "./database.ts"; |
| 106 | import { todos } from "./schema.ts"; |
| 107 | |
| 108 | export default class Api extends GCP.Function<Api>()( |
| 109 | "Api", |
| 110 | Effect.gen(function* () { |
| 111 | const { schema } = yield* database; |
| 112 | return { |
| 113 | main: import.meta.url, |
| 114 | invokerIamDisabled: true, |
| 115 | env: { SCHEMA_VERSION: schema.snapshotHash }, |
| 116 | }; |
| 117 | }), |
| 118 | Effect.gen(function* () { |
| 119 | const { instance, db: appDb, user, passwordSecret } = yield* database; |
| 120 | const connect = yield* GCP.SQL.Connect(instance, { |
| 121 | database: appDb, |
| 122 | user, |
| 123 | passwordSecret, |
| 124 | }); |
| 125 | const db = yield* Drizzle.Postgres(connect.pipe(Effect.map((info) => info.url))); |
| 126 | |
| 127 | return { |
| 128 | fetch: Effect.gen(function* () { |
| 129 | const rows = yield* db.select().from(todos).orderBy(todos.title); |
| 130 | return yield* HttpServerResponse.json(rows); |
| 131 | }).pipe(Effect.orDie), |
| 132 | }; |
| 133 | }).pipe(Effect.provide(GCP.SQL.ConnectHttp)), |
| 134 | ) {} |
| 135 | ``` |
| 136 | |
| 137 | Binding the database makes the service deploy after its migrations are |
| 138 | applied, and `SCHEMA_VERSION` rolls out a new revision when the schema |
| 139 | changes. |
| 140 | |
| 141 | `Connect` grants `roles/cloudsql.client` on this instance only, and |
| 142 | `secretAccessor` on the password secret. |
| 143 | |
| 144 | ## Register the providers |
| 145 | |
| 146 | Drizzle and `Random` bring their own providers: |
| 147 | |
| 148 | ```typescript |
| 149 | // alchemy.run.ts |
| 150 | providers: Layer.mergeAll( |
| 151 | GCP.providers(), |
| 152 | Drizzle.providers(), |
| 153 | Alchemy.RandomProvider(), |
| 154 | ), |
| 155 | ``` |
| 156 | |
| 157 | ## Example |
| 158 | |
| 159 | [`examples/gcp-cloud-sql-drizzle`](https://github.com/alchemy-run/alchemy/tree/main/examples/gcp-cloud-sql-drizzle) |
| 160 | is the complete todo API, migrations included, with a live test. |
| 161 | |
| 162 | ## Where next |
| 163 | |
| 164 | - [Drizzle on Postgres](/sql/drizzle/postgres) — schemas, migrations, and queries. |
| 165 | - [SQL reference](/providers/gcp/sql#instance) — every instance prop. |