Expose your Postgres database on Cloud SQL easily with Cloud Run

The PostgREST project provides a CRUD API on top of your postgres database.
What better way to explore your data than with an OpenAPI running on Cloud Run?

Overview

Thanks to Cloud Run easy integration with Cloud SQL and Secret Manager it's very easy to connect all we need!

Your data in Cloud SQL
↕️
VPC Connector
↕️
SQL Connection
↕️
Cloud Run service

Content of this repo 🕵🏻

This repo serves as a demo, it will:

• Create a Cloud SQL instance with Postgres in a private VPC.
• Set the postgres password and store it in Secret Manager
• Initialize a Cloud Run Service, with a VPC Connector, a SQL Connection to the SQL instance, and mounting the password from Secret
• Finally, output the Service Endpoint and optional gcloud commands to populate the database

/!\ Permission: the resulting Cloud Run service is available publicly for ease of access

Exemple of deployed interface ⤵️

Exemple generated openapi spec: openapi.json

.
├── cloud_run
│   ├── cloud_run.tf
│   ├── outputs.tf
│   └── variables.tf
│
├── cloud_sql
│   ├── outputs.tf
│   ├── postgres.tf
│   └── variables.tf
│
├── secret_manager
│   ├── outputs.tf
│   ├── secret_manager.tf
│   └── variables.tf
│
└── main.tf
├── terraform.tfvars
└── [backend.tf]

Deploy 🚀

Authenticate first using gcloud's ADC

gcloud auth application-default login

Fill in your own GCP project id in the terraform.tfvars file.
[Optionnaly, uncomment the backend.tf file and fill in your GCS bucket name if you want a remote state.]

Deploy!

tf init
tf plan -var-files=terraform.tfvars -out tfplan

# (the tf apply will start immediately,
#+ you better review the tf plan output first)
tf apply tfplan

Go grab a coffee while Terraform is doing its magic ☕️ ✨

Come back to Terraform outputting gcloud commands to populate the db and the service URL to inspect your data! 🔍 👀

Cleanup 🧹

Set the deletion_protection field in cloud_sql/postgres.tf to false on the SQL Database instance:

  resource "google_sql_database_instance" "postgrest" {
      (...)
      deletion_protection = true -> false
      (...)
  }

Apply the change

tf apply -var-file=terraform.tfvars \
  -target module.postgrest_database.google_sql_database_instance.postgrest

Confirm you want to delete all resources

tf destroy