Skip to main content

Cloud SQL Quick Start Guide

⚡ Fast deployment for experienced users

Prerequisites (30 seconds)​

# Verify authentication
gcloud auth list  # Should show: 1@az1.ai (ACTIVE)
gcloud config get-value project  # Should be: serene-voltage-464305-n2

# If not set:
gcloud config set project serene-voltage-464305-n2

Deployment (2 commands, 20 minutes)​

# Navigate to project
cd /Users/halcasteel/PROJECTS/coditect-rollout-master/submodules/coditect-cloud-backend

# Run automated deployment
./deployment/deploy-cloud-sql.sh

What it does:

  1. Enables GCP APIs (SQL Admin, Service Networking, Secret Manager)
  2. Creates Cloud SQL instance (coditect-dev-db, PostgreSQL 15, db-f1-micro)
  3. Configures private IP (VPC peering)
  4. Stores passwords in Secret Manager
  5. Creates Kubernetes secret (coditect-app namespace)

Output: Connection details + manual steps

Manual Steps (3 commands, 5 minutes)​

After deployment script completes:

# 1. Create database and user
gcloud sql connect coditect-dev-db --user=postgres --project=serene-voltage-464305-n2 < /tmp/create_db_user.sql

# 2. Apply schema
gcloud sql connect coditect-dev-db --user=postgres --database=coditect_dev --project=serene-voltage-464305-n2 < database/migrations/001_initial_schema.sql

# 3. Apply RLS policies
gcloud sql connect coditect-dev-db --user=postgres --database=coditect_dev --project=serene-voltage-464305-n2 < database/rls_policies.sql

Passwords: Stored in Secret Manager, script provides them during execution

Verification (1 command, 1 minute)​

# Test connection from GKE
kubectl run psql-test --rm -it --image=postgres:15 --namespace=coditect-app -- \
  psql "postgresql://coditect_app:$(kubectl get secret coditect-db-credentials -n coditect-app -o jsonpath='{.data.password}' | base64 -d)@$(kubectl get secret coditect-db-credentials -n coditect-app -o jsonpath='{.data.host}' | base64 -d):5432/coditect_dev?sslmode=require"

# Inside psql:
SELECT version();  -- Should show: PostgreSQL 15.x
\dt coditect_shared.*  -- Should list 4 tables
\q

Connection Details​

Instance Name: coditect-dev-db Database: coditect_dev User: coditect_app Private IP: (auto-assigned, see Kubernetes secret) Port: 5432

Connection String:

postgresql://coditect_app:<password>@<private-ip>:5432/coditect_dev?sslmode=require

Kubernetes Secret: coditect-db-credentials (namespace: coditect-app)

Environment Variables:

kubectl get secret coditect-db-credentials -n coditect-app -o yaml

Troubleshooting​

API not enabled:

gcloud services enable sqladmin.googleapis.com --project=serene-voltage-464305-n2

Connection refused:

# Check instance status
gcloud sql instances describe coditect-dev-db --project=serene-voltage-464305-n2

# Get private IP
gcloud sql instances describe coditect-dev-db --format="value(ipAddresses[0].ipAddress)" --project=serene-voltage-464305-n2

# Test from GKE pod
kubectl run curl-test --rm -it --image=curlimages/curl --namespace=coditect-app -- sh
nc -zv <private-ip> 5432

Password forgotten:

# Root password
gcloud secrets versions access latest --secret="coditect-db-root-password" --project=serene-voltage-464305-n2

# App password
gcloud secrets versions access latest --secret="coditect-db-app-password" --project=serene-voltage-464305-n2

Rollback​

# Create backup first
gcloud sql backups create --instance=coditect-dev-db --project=serene-voltage-464305-n2

# Delete instance
gcloud sql instances delete coditect-dev-db --project=serene-voltage-464305-n2

# Clean up secrets
gcloud secrets delete coditect-db-root-password --project=serene-voltage-464305-n2
gcloud secrets delete coditect-db-app-password --project=serene-voltage-464305-n2

# Clean up Kubernetes
kubectl delete secret coditect-db-credentials --namespace=coditect-app

Documentation​

Full Guide: deployment/gcp-cloud-sql-setup.md Summary: deployment/deployment-summary.md Checkpoint: ../../CHECKPOINT-CLOUD-SQL-DEPLOYMENT-READY.md

Cost​

Monthly: ~$10.30 (development tier)

  • Instance: $7.60 (db-f1-micro)
  • Storage: $1.70 (10 GB SSD)
  • Backups: $1.00 (7 days)

Next Steps​

  1. ✅ Cloud SQL deployed
  2. ⏸️ FastAPI backend integration (SQLAlchemy models)
  3. ⏸️ API endpoints development
  4. ⏸️ Testing and security hardening

Total Time: ~25 minutes (automated + manual + verification) Status: ✅ Production-ready deployment package