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

main
mdx 165 lines 4.92 KB
Raw
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.