Cloud SQL with Terraform: Private PostgreSQL on Google Cloud

A Cloud SQL instance takes a few lines of Terraform if you accept a public IP and a password in state. A production setup is longer: a VPC, a private services access range and peering connection, an instance with the right edition, tier and backup settings, a user, and somewhere for the application to read that user's password. Several of those settings cannot be changed without recreating the instance, so they are worth getting right the first time.

This guide walks through Terraform for each piece with the Google provider 7.x, the settings that cause problems after the first apply, and how Encore provisions Cloud SQL from the code that uses it.

What a Cloud SQL Terraform setup needs

Network and private services access

Cloud SQL instances live in a Google-managed VPC, so giving one a private IP in your network means you reserve a range for Google's side and create a peering connection, as described in the private services access docs:

variable "project" { type = string }
variable "region" {
  type    = string
  default = "europe-west1"
}
variable "db_password_version" {
  type    = number
  default = 1 # bump to rotate the password
}

provider "google" {
  project = var.project
  region  = var.region
}

resource "google_project_service" "apis" {
  for_each = toset(["sqladmin.googleapis.com", "servicenetworking.googleapis.com", "secretmanager.googleapis.com"])
  service  = each.key
}

resource "google_compute_network" "main" {
  name                    = "main"
  auto_create_subnetworks = false
}

# Private services access: a range Google uses for Cloud SQL's side of the peering.
resource "google_compute_global_address" "sql_range" {
  name          = "cloudsql-range"
  purpose       = "VPC_PEERING"
  address_type  = "INTERNAL"
  prefix_length = 16
  network       = google_compute_network.main.id
}

resource "google_service_networking_connection" "sql" {
  network                 = google_compute_network.main.id
  service                 = "servicenetworking.googleapis.com"
  reserved_peering_ranges = [google_compute_global_address.sql_range.name]
  depends_on              = [google_project_service.apis]
}

# Cloud Run reaches the private IP through Direct VPC egress on this subnet.
resource "google_compute_subnetwork" "run" {
  name          = "run"
  network       = google_compute_network.main.id
  region        = var.region
  ip_cidr_range = "10.10.0.0/24"
}

The reserved range is shared by every Cloud SQL instance and other Google-managed services that use private services access in this network, so size it for growth rather than for the first database. Google requires at least a /24.

The instance and database

resource "google_sql_database_instance" "main" {
  name                = "orders-pg"
  database_version    = "POSTGRES_17"
  region              = var.region
  deletion_protection = true # Terraform-side guard against destroy

  settings {
    edition                     = "ENTERPRISE"
    tier                        = "db-custom-2-7680"
    availability_type           = "REGIONAL" # HA standby in a second zone
    disk_type                   = "PD_SSD"
    disk_autoresize             = true
    deletion_protection_enabled = true # GCP-side guard, also blocks console deletes

    ip_configuration {
      ipv4_enabled    = false
      private_network = google_compute_network.main.id
      ssl_mode        = "ENCRYPTED_ONLY"
    }

    backup_configuration {
      enabled                        = true
      start_time                     = "02:00"
      point_in_time_recovery_enabled = true
      transaction_log_retention_days = 7
    }

    maintenance_window {
      day  = 7 # Sunday
      hour = 3
    }

    database_flags {
      name  = "log_min_duration_statement"
      value = "500"
    }
  }

  depends_on = [google_service_networking_connection.sql]
}

resource "google_sql_database" "orders" {
  name     = "orders"
  instance = google_sql_database_instance.main.name
}

Some of these lines override defaults that cause trouble later:

• edition: for PostgreSQL 16 and later, new instances default to Enterprise Plus, which rejects db-custom-* and shared-core tiers. The editions overview lists which machine types each edition supports.
• Two deletion protections: deletion_protection is a Terraform-only flag that stops terraform destroy, while deletion_protection_enabled is a GCP setting that also blocks deletes from the console and gcloud.
• availability_type = "REGIONAL" adds a standby in a second zone with automatic failover. It roughly doubles the instance cost, which is why staging often uses ZONAL.
• transaction_log_retention_days sets how far back point-in-time recovery reaches. On the Enterprise edition the limit is 7 days.
• ssl_mode = "ENCRYPTED_ONLY" rejects unencrypted connections. Use TRUSTED_CLIENT_CERTIFICATE_REQUIRED only if every client can present a client certificate.

A user whose password stays out of state

A random_password resource and a plain password argument both store the password in Terraform state. With Terraform 1.11 or later, an ephemeral resource and write-only arguments avoid that:

# Generated during apply and never written to state (Terraform 1.11+).
ephemeral "random_password" "db" {
  length  = 32
  special = false
}

resource "google_sql_user" "app" {
  name                = "orders_app"
  instance            = google_sql_database_instance.main.name
  password_wo         = ephemeral.random_password.db.result
  password_wo_version = var.db_password_version
}

resource "google_secret_manager_secret" "db_password" {
  secret_id = "orders-db-password"
  replication {
    auto {}
  }
  depends_on = [google_project_service.apis]
}

resource "google_secret_manager_secret_version" "db_password" {
  secret                 = google_secret_manager_secret.db_password.id
  secret_data_wo         = ephemeral.random_password.db.result
  secret_data_wo_version = var.db_password_version
}

The ephemeral value is generated during each run, and the write-only arguments only send it on creation and when db_password_version changes, so the database and Secret Manager receive the same password together, and rotating it means bumping that variable. The whole configuration in this guide passes terraform validate with Terraform 1.13 and the Google provider 7.x. An alternative is IAM database authentication with a google_sql_user of type CLOUD_IAM_SERVICE_ACCOUNT, which removes the password entirely in exchange for using the Cloud SQL connectors or Auth Proxy in the app.

What still takes work after the first apply

• Connecting from Cloud Run needs Direct VPC egress on the run subnet, a service account with roles/cloudsql.client if you use the connector, and the password mounted from Secret Manager.
• Migrations run from somewhere with network access to a private IP, usually a CI job with VPC access or a Cloud Run job.
• Local development needs its own Postgres, plus a way to keep schema and seed data in step with what production runs.
• Every new database a service needs repeats the database, user and secret resources, and the grant statements live outside Terraform.

How does Encore provision Cloud SQL from code?

Encore is an infrastructure-from-code platform for TypeScript and Go: you declare a database in the service that owns it, and Encore provisions Cloud SQL, the networking and the credentials in your own GCP project, or RDS in AWS. According to the GCP infrastructure docs, databases are placed in private subnets, with daily backups kept for 7 days and point-in-time recovery.

Set up the app

Here is an inventory service that owns its database, where the SQLDatabase declaration replaces the instance, database, user and secret resources above:

// inventory/inventory.ts
import { api, APIError } from "encore.dev/api";
import { SQLDatabase } from "encore.dev/storage/sqldb";

// Cloud SQL for PostgreSQL on GCP, Postgres on your laptop with `encore run`.
const db = new SQLDatabase("inventory", { migrations: "./migrations" });

interface Item {
  sku: string;
  quantity: number;
}

export const get = api(
  { expose: true, method: "GET", path: "/items/:sku" },
  async ({ sku }: { sku: string }): Promise<Item> => {
    const row = await db.queryRow<Item>`
      SELECT sku, quantity FROM items WHERE sku = ${sku}
    `;
    if (!row) throw APIError.notFound("unknown sku");
    return row;
  },
);

export const adjust = api(
  { expose: true, method: "POST", path: "/items/:sku/adjust" },
  async ({ sku, delta }: { sku: string; delta: number }): Promise<Item> => {
    const row = await db.queryRow<Item>`
      UPDATE items SET quantity = quantity + ${delta}
      WHERE sku = ${sku} AND quantity + ${delta} >= 0
      RETURNING sku, quantity
    `;
    if (!row) throw APIError.failedPrecondition("not enough stock or unknown sku");
    return row;
  },
);

-- inventory/migrations/1_create_items.up.sql
CREATE TABLE items (
  sku TEXT PRIMARY KEY,
  quantity INTEGER NOT NULL CHECK (quantity >= 0)
);

encore run starts Postgres locally and applies the migrations, and the database docs cover transactions, encore db shell and connecting with an external tool through encore db proxy. On deploy, Encore applies the same migrations to Cloud SQL as part of the deployment, per the infrastructure management docs. An existing instance can be imported rather than recreated.

Each preview environment is an isolated environment with its own database, so a pull request that changes the schema, including one opened by a coding agent, is tested against that database instead of the shared staging one.

Best fit: TypeScript and Go teams on GCP who want private Cloud SQL with backups and per-service credentials, created from the code that uses the database.

Good to know

• Instance names cannot be reused for about a week after deletion, so a destroy and recreate with the same name fails. Add a random suffix if you expect to recreate instances.
• disk_autoresize grows storage when it fills up but never shrinks it, so a one-off bulk import leaves you paying for the larger disk.
• db-f1-micro and db-g1-small are not covered by the Cloud SQL SLA, so keep them to development projects.

Related guides

• How to connect Cloud Run to Cloud SQL
• Cloud Run with Terraform
• Amazon RDS with Terraform
• Google Secret Manager with Cloud Run
• Local Postgres for development