Drizzle + Node-Postgres + GCP with IAM Auth

getDatabaseDriver.ts

import { z } from "zod";

import { drizzle, type NodePgDatabase } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { Client, Pool } from "pg";
import {
  Connector,
  AuthTypes,
  IpAddressTypes,
} from "@google-cloud/cloud-sql-connector";

export function configurationFromEnv(env: NodeJS.ProcessEnv) {
  const parsable = {
    // my-gcp-project-1024:us-central1:sql-database-name
    instanceConnectionName: env.SQL_CONNECTION_NAME,
    // postgres://10.196.0.13:5432/my-database
    url: env.DB_URL,
    // [email protected] (service account name with ".gserviceaccount.com" stripped from end)
    username: env.DB_USERNAME,
    // my-database
    database: env.DB_NAME,
    // some empty string (but is required, and will fail if empty/null)
    password: env.DB_PASSWORD,
  };
  return DatabaseConnectionConfiguration.parse(parsable);
}
const DatabaseConnectionConfiguration = z.object({
  instanceConnectionName: z.string(),
  url: z.string(),
  username: z.string(),
  database: z.string(),
  password: z.string().optional().default("USING_IAM"),
});
type DatabaseConnectionConfiguration = z.infer<
  typeof DatabaseConnectionConfiguration
>;

export async function runMigrations(env: NodeJS.ProcessEnv) {
  const { clientOpts, userConfig } = await getDriverOptions(env);
  const client = new Client({
    ...clientOpts,
    ...userConfig,
  });
  await client.connect();
  const db = drizzle(client);
  const migrationsFolder =
    env.ENVIRONMENT === "local" ? "drizzle" : "/app/migrations/drizzle";
  await migrate(db, { migrationsFolder });
  await client.end();
}

let instance: NodePgDatabase<Record<string, never>> | null = null;
export async function getDatabaseDriver(env: NodeJS.ProcessEnv) {
  if (instance) return instance;
  const { clientOpts, userConfig } = await getDriverOptions(env);
  const pool = new Pool({
    ...clientOpts,
    ...userConfig,
  });
  instance = drizzle(pool);
  return instance;
}

async function getDriverOptions(env: NodeJS.ProcessEnv) {
  const config = configurationFromEnv(env);

  const connector = new Connector();
  const clientOpts = await connector.getOptions({
    instanceConnectionName: config.instanceConnectionName,
    authType: AuthTypes.IAM,
    ipType: IpAddressTypes.PRIVATE,
  });

  const userConfig = {
    user: config.username,
    database: config.database,
    password: config.password,
  };
  return { clientOpts, userConfig };
}