Connecting Local Backend to Cloud Databases

This guide explains how developers can connect their local backend to the development database using Cloud SQL Auth Proxy.

Quick Start

# Run the proxy (downloads automatically on first run)
npm run db:connect

# Or directly:
./local-scripts/connect-dev-db.sh

The script will:

1. Download Cloud SQL Auth Proxy if not present
2. Fetch the database password from Secret Manager
3. Display connection settings you can copy to your .env
4. Start the proxy on port 15431

Prerequisites

1. Google Cloud CLI installed and authenticated:

   gcloud auth login
   gcloud auth application-default login

2. Access permissions:

   • Cloud SQL Client role on eli-health-dev project
   • Access to postgres-root-password-ca secret

Connection Settings

Once the proxy is running, use these settings:

Setting	Value
Host	localhost
Port	15431
User	root
Password	(shown by script, from Secret Manager)
Database	postgres

For .env file

The script outputs ready-to-use environment variables:

POSTGRES_HOST=localhost
POSTGRES_PORT=15431
POSTGRES_USER=root
POSTGRES_PASSWORD=<from Secret Manager>
POSTGRES_DB=postgres

Or as a single DATABASE_URL:

DATABASE_URL=postgresql://root:<password>@localhost:15431/postgres?sslmode=prefer

Why Cloud SQL Proxy?

Instead of relaxing database security (which would create inconsistency between dev/staging/production), we use Cloud SQL Auth Proxy:

• Keeps security consistent across all environments
• Encrypted connection - the proxy handles SSL/TLS to Cloud SQL
• Simple local connection - connect to localhost with standard PostgreSQL clients
• Password from Secret Manager - no hardcoded credentials

Workflow Example

# Terminal 1: Start the proxy
cd eli-backend-api
./local-scripts/connect-dev-db.sh

# Terminal 2: Run your backend
# Copy the POSTGRES_* vars shown by the script to your .env
npm run start:backend

Troubleshooting

"Could not fetch password from Secret Manager"

You need access to the secret:

gcloud secrets versions access latest \
  --secret=postgres-root-password-ca \
  --project=eli-health-dev

If this fails, ask for Secret Manager Secret Accessor role on the project.

"No application default credentials found"

Run:

gcloud auth application-default login

"Connection refused" on localhost

1. Make sure the proxy is still running (check the terminal)
2. Verify nothing else is using port 15431:

   lsof -i :15431

DataGrip / Database Client Connection

Use the same settings shown by the script:

• Host: localhost
• Port: 15431
• User: root
• Password: (from script output)
• Database: postgres
• JDBC URL: Add ?sslmode=prefer to the URL

Security Notes

• The proxy only listens on 127.0.0.1 (localhost only)
• Password is fetched from Secret Manager at runtime
• The actual connection to Cloud SQL is always encrypted
• The .db-proxy/ folder (contains the binary) is gitignored