Cloud SQL

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.

pnpm

pnpm add @alchemy.run/drizzle@latest drizzle-orm

bun

bun add @alchemy.run/drizzle@latest drizzle-orm

npm

npm install @alchemy.run/drizzle@latest drizzle-orm

yarn

yarn add @alchemy.run/drizzle@latest drizzle-orm

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",
    backupEnabled: 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 database and a user

  const db = yield* GCP.SQL.Database("App", {
    instance: instance.instanceName,
  });

  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,
  });

  const schema = yield* Drizzle.Schema("Schema", {
    schema: "./src/schema.ts",
    out: "./migrations",
  });

  return { instance, db, user, password, passwordSecret, schema };
});

Drizzle.Schema regenerates ./migrations whenever src/schema.ts changes.

Apply migrations at deploy time

The example’s migrate Action writes the password into the secret and applies the migrations through the Cloud SQL Data API, so your machine never needs a network path to the database. Copy src/migrate.ts into your project.

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 { migrate } from "./migrate.ts";
import { todos } from "./schema.ts";

export default class Api extends GCP.Function<Api>()(
  "Api",
  Effect.gen(function* () {
    const schemaVersion = yield* migrate;
    return {
      main: import.meta.url,
      invokerIamDisabled: true,
      env: { SCHEMA_VERSION: schemaVersion },
    };
  }),
  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)),
) {}

Reading migrate in the props makes the service deploy after the schema exists, and roll out a new revision when it 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.