Skip to content

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.

Terminal window
pnpm add https://pkg.alchemy.run/@alchemy.run/drizzle/a870ed4 drizzle-orm

Create the instance

Section titled “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

Section titled “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

Section titled “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

Section titled “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

Section titled “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

Section titled “Register the providers”

Drizzle and Random bring their own providers:

alchemy.run.ts
providers: Layer.mergeAll(
  GCP.providers(),
  Drizzle.providers(),
  Alchemy.RandomProvider(),
),

Example

Section titled “Example”

examples/gcp-cloud-sql-drizzle is the complete todo API, migrations included, with a live test.

Where next

Section titled “Where next”