import PackageManagers from "../../../../components/PackageManagers.astro"; export const pkgs = "@alchemy.run/drizzle drizzle-orm"; Cloud SQL runs Postgres or MySQL for you. A Cloud Run service reaches it through the Cloud SQL socket, with no public IP and no VPC to set up. This page builds a Postgres todo API with Drizzle.
Create the instance
// src/database.ts
import * as Drizzle from "@alchemy.run/drizzle";
import * as GCP from "@alchemy.run/gcp";
import * as Alchemy from "alchemy";
import * as Effect from "effect/Effect";
export const database = Effect.gen(function* () {
const instance = yield* GCP.SQL.Instance("Postgres", {
databaseVersion: "POSTGRES_17",
edition: "ENTERPRISE",
tier: "db-f1-micro",
availabilityType: "ZONAL",
backup: { enabled: false },
dataApiAccess: true,
}); db-f1-micro with no backups is the cheapest Postgres Cloud SQL runs.
Creating an instance takes 5 to 15 minutes.
Add a user
const password = yield* Alchemy.Random("AppPasswordValue", { bytes: 24 });
const user = yield* GCP.SQL.User("AppUser", {
instance: instance.instanceName,
password: password.text,
deletionPolicy: "ABANDON",
}); Alchemy.Random generates the password once and keeps it in state.
ABANDON leaves the user in place on destroy, because Postgres won't
drop a user that owns tables. Deleting the instance removes it.
Keep the password in Secret Manager
The service reads the password from a secret in the instance's region:
const passwordSecret = yield* GCP.SecretManager.LocationsSecret("AppPassword", {
location: instance.region,
}); The example's StorePassword Action writes the password into the secret
at deploy time. Copy
src/password.ts
into your project and yield it from your stack.
Apply migrations at deploy time
const schema = yield* Drizzle.Schema("Schema", {
schema: "./src/schema.ts",
out: "./migrations",
});
const db = yield* GCP.SQL.Database("App", {
instance: instance.instanceName,
migrations: schema,
migrationsUser: user.userName,
migrationsPassword: password.text,
});
return { instance, db, user, password, passwordSecret, schema };
}); Drizzle.Schema regenerates ./migrations whenever src/schema.ts
changes. Each deploy applies the pending ones as migrationsUser
through the Cloud SQL Data API, so your machine never needs a network
path to the database. Set migrationsTransport: "connector" to connect
through the Cloud SQL connector instead.
Connect from Cloud Run
GCP.SQL.Connect mounts the instance's socket into the service and
returns a connection URL. Pass it to Drizzle:
// src/Api.ts
import * as Drizzle from "@alchemy.run/drizzle/Postgres";
import * as GCP from "@alchemy.run/gcp";
import * as Effect from "effect/Effect";
import * as HttpServerResponse from "effect/http/HttpServerResponse";
import { database } from "./database.ts";
import { todos } from "./schema.ts";
export default class Api extends GCP.Function<Api>()(
"Api",
Effect.gen(function* () {
const { schema } = yield* database;
return {
main: import.meta.url,
invokerIamDisabled: true,
env: { SCHEMA_VERSION: schema.snapshotHash },
};
}),
Effect.gen(function* () {
const { instance, db: appDb, user, passwordSecret } = yield* database;
const connect = yield* GCP.SQL.Connect(instance, {
database: appDb,
user,
passwordSecret,
});
const db = yield* Drizzle.Postgres(connect.pipe(Effect.map((info) => info.url)));
return {
fetch: Effect.gen(function* () {
const rows = yield* db.select().from(todos).orderBy(todos.title);
return yield* HttpServerResponse.json(rows);
}).pipe(Effect.orDie),
};
}).pipe(Effect.provide(GCP.SQL.ConnectHttp)),
) {} Binding the database makes the service deploy after its migrations are
applied, and SCHEMA_VERSION rolls out a new revision when the schema
changes.
Connect grants roles/cloudsql.client on this instance only, and
secretAccessor on the password secret.
Register the providers
Drizzle and Random bring their own providers:
// alchemy.run.ts
providers: Layer.mergeAll(
GCP.providers(),
Drizzle.providers(),
Alchemy.RandomProvider(),
), Example
examples/gcp-cloud-sql-drizzle
is the complete todo API, migrations included, with a live test.
Where next
- Drizzle on Postgres — schemas, migrations, and queries.
- SQL reference — every instance prop.