Overview
In troubleshooting scenarios, we may need to connect to a live CloudSQL instance. We can impersonate the service account running our application code and connect to the database via the Cloud SQL proxy.
Goal: Connect to a live, Cloud SQL instance to simulate runtime access as if it were our application.
Authenticating your terminal
Authenticate your terminal instance with either of the following two scripts:
gcloud auth login # OR
gcloud auth application-default login
After you complete the login flow with your browser, set your project environment with either:
gcloud config set project api-platform-staging-1 # Staging
gcloud config set project api-platform-421220 # Production
Start up the Cloud SQL proxy
The Cloud SQL proxy is a utility installed on a VM that lives within our GCP project and allows us to securely connect to the database instance. We can connect to this "jump box" VM using an IAP tunnel which authenticates against our individual Google accounts.
In a terminal window, start the IAP tunnel using:
gcloud compute start-iap-tunnel cloudsql-bastion 5432 --local-host-port=localhost:5432 --zone=us-central1-a
We'll need to leave this tunnel running for as long as we want to connect to it. Once it's up, let's open another terminal instance.
Impersonate the runtime service account
To ensure that we are viewing the database with the same eyes as our application, we will use a method called impersonation to assume the role of the service account when connecting to the database. We can do this by using the GCloud CLI to retrieve an access token mapped to the service account.
Let's impersonate the application’s runtime service account and get a temporary access token. This token expires in 1 hour. Use either:
gcloud auth print-access-token --impersonate-service-account=storefront-backend@api-platform-staging-1.iam.gserviceaccount.com # Staging
gcloud auth print-access-token --impersonate-service-account=storefront-backend@api-platform-421220.iam.gserviceaccount.com # Production
These tokens will be really long strings. We'll use these in the next step to set up our connection string.
Setting up our application for connectivity
My preferred way of connecting to the database is using Drizzle Studio, which provides a simple yet versatile interface for interacting with the database. We can set this up using .env and run our application as usual to establish the connection:
Configure a DATABASE_URL environment variable in .env:
Staging:
DATABASE_URL=postgresql://storefront-backend@api-platform-staging-1.iam:[REPLACE_ME_WITH_TOKEN_FROM_ABOVE]@localhost:5432/postgres
Production:
DATABASE_URL=postgresql://storefront-backend@api-platform-421220.iam:[REPLACE_ME_WITH_TOKEN_FROM_ABOVE]@localhost:5432/postgres
Run any script that imports db from ~/lib/drizzle Examples:
bun run dev
bun run db:studio
bun run scripts/list-events.ts