Databases: Cloud SQL, Firestore & Memorystore

What it is and where it fits 🎯#

Cloud SQL is GCP's managed relational database service, MySQL, PostgreSQL, or SQL Server, running on infrastructure Google patches and backs up for you, the direct analogue of AWS RDS or Azure SQL Database. Firestore is GCP's serverless NoSQL document database, closer to DynamoDB than to anything relational. Memorystore for Redis is a managed in-memory cache/data-store layer, most often sitting in front of one of the other two. This page assumes the project/auth basics from page 01 and the VPC networking primitives (private IP, VPC peering) from page 03, since Cloud SQL's private-IP connectivity pattern depends directly on them.

Core concepts: how an application actually reaches Cloud SQL#

Three real connection paths exist, and they're not interchangeable defaults. The Cloud SQL Auth Proxy (a separate binary, cloud-sql-proxy, not a gcloud subcommand) authenticates via IAM and encrypts the connection without you ever managing a certificate or authorized-network entry, the current recommended default for anything running on GCP compute. A private IP on the same VPC skips the proxy but requires the VPC peering this site's page 03 covers, set up once per network. A public IP with authorized networks is the path gcloud sql connect below uses for quick interactive access, and the one to avoid for anything long-running or automated.

Creating a Cloud SQL instance#

gcloud sql instances create my-instance \
  --database-version=POSTGRES_16 \
  --tier=db-custom-2-8192 --region=us-central1 \
  --storage-type=SSD --storage-size=50GB --storage-auto-increase \
  --backup --backup-start-time=03:00 \
  --availability-type=REGIONAL \
  --require-ssl --deletion-protection

• --tier picks the machine shape; db-custom-<vCPUs>-<MB-RAM> gives you an exact custom shape, or use one of the predefined db-f1-micro/db-g1-small/db-n1-standard-* tiers for a smaller/simpler instance.
• --availability-type=REGIONAL provisions a synchronous standby in a second zone within the region, GCP's equivalent of AWS RDS Multi-AZ, ZONAL (the default) has no automatic failover.
• --deletion-protection blocks gcloud sql instances delete outright until explicitly disabled with gcloud sql instances patch my-instance --no-deletion-protection first, a genuinely useful guardrail for a production instance that shouldn't be one accidental command away from gone.
• --storage-auto-increase grows storage automatically as it fills rather than the instance going read-only at capacity, the safer default for anything you're not actively capacity-planning by hand.

Listing, describing, and connecting#

gcloud sql instances list
gcloud sql instances describe my-instance
gcloud sql instances describe my-instance --format="value(ipAddresses[0].ipAddress)"

gcloud sql connect my-instance --user=postgres --database=my-db   # interactive psql/mysql shell

gcloud sql connect is genuinely convenient for a one-off interactive session, it temporarily adds your current IP to the instance's authorized networks, opens an interactive client, then removes the temporary entry when you exit. It is not the pattern for an application's connection string, an app should use the Cloud SQL Auth Proxy or private IP shown in the diagram above, both of which avoid touching authorized networks at all.

Databases and users#

gcloud sql databases create my-app-db --instance=my-instance --charset=UTF8
gcloud sql databases list --instance=my-instance
gcloud sql databases delete my-app-db --instance=my-instance

gcloud sql users create app-user --instance=my-instance --password=<generated-secret>
gcloud sql users set-password app-user --instance=my-instance --password=<new-secret>
gcloud sql users list --instance=my-instance
gcloud sql users delete app-user --instance=my-instance

A Cloud SQL "instance" is the managed server; "databases" and "users" underneath it map directly onto the underlying engine's own concepts (a MySQL/Postgres database and role), gcloud sql databases/gcloud sql users are just a remote-management wrapper around CREATE DATABASE/CREATE USER so you don't need a live SQL connection just to provision a new schema or credential.

gcloud sql instances patch my-instance --database-flags=cloudsql.iam_authentication=on
gcloud sql users create ci-deployer@my-project-id.iam.gserviceaccount.com \
  --instance=my-instance --type=cloud_iam_service_account

Backups, exports, and restores#

gcloud sql backups create --instance=my-instance --description="pre-migration snapshot"
gcloud sql backups list --instance=my-instance
gcloud sql backups restore <backup-id> --restore-instance=my-instance

gcloud sql export sql my-instance gs://my-bucket/backup.sql --database=my-app-db
gcloud sql import sql my-instance gs://my-bucket/backup.sql --database=my-app-db

Automated backups (configured via --backup/--backup-start-time at instance creation) run on a schedule and are what --enable-point-in-time-recovery builds on for restoring to an arbitrary timestamp, not just a backup boundary; gcloud sql backups create is an on-demand one, worth running immediately before a risky schema migration rather than trusting the nightly window happened to land beforehand. export/import round-trip through a Cloud Storage bucket as a portable SQL dump, useful for cloning data into a staging instance or migrating between Cloud SQL and a self-managed database, at the cost of being far slower than a native backup/restore for anything beyond a modest dataset size.

Read replicas and high availability#

gcloud sql instances create my-instance-replica \
  --master-instance-name=my-instance \
  --region=us-east1 --tier=db-custom-2-8192

A read replica is created as its own named instance pointing back at a --master-instance-name, and can live in a different region entirely, useful for serving read traffic closer to a secondary user population, separately from the --availability-type=REGIONAL failover standby shown earlier, which exists purely for availability and isn't a readable, independent endpoint the way a replica is.

Restricting network access#

gcloud sql instances patch my-instance --authorized-networks=203.0.113.0/24
gcloud sql instances patch my-instance --clear-authorized-networks

gcloud sql instances patch my-instance --no-assign-ip --network=projects/my-project/global/networks/my-network

--authorized-networks is CIDR-based allow-listing for the public-IP path, the same shape as a GCE firewall rule's --source-ranges, appropriate only when public IP access is genuinely required (a third-party SaaS tool that can't reach a private VPC, say). --no-assign-ip removes the public IP entirely, the instance is then reachable only via the private-IP path shown in the core-concepts diagram, requiring the referenced VPC to already have Cloud SQL's private services access configured.

Firestore: creating a database and managing indexes#

gcloud firestore databases create --location=nam5 --type=firestore-native
gcloud firestore databases list
gcloud firestore databases describe --database="(default)"

gcloud firestore indexes composite create \
  --collection-group=orders --field-config field-path=customerId,order=ascending \
  --field-config field-path=createdAt,order=descending
gcloud firestore indexes composite list

Firestore's --location is a multi-region or region identifier chosen once at database creation and cannot be changed afterward, the same "get it right the first time" constraint GKE's Autopilot-vs- Standard choice has on page 02. --type=firestore-native is the modern document-model API; the legacy datastore-mode still exists for projects migrating off the older Datastore API, new projects should default to native mode. Composite indexes are required for any query that filters or sorts on more than one field, Firestore rejects such a query outright at request time until the matching index exists, gcloud firestore indexes composite create is how you provision one outside the Console, useful for keeping index definitions in version control alongside the application code that needs them.

Memorystore for Redis#

gcloud redis instances create my-cache \
  --region=us-central1 --tier=standard --size=5 \
  --redis-version=redis_7_2 --network=my-network \
  --enable-auth --transit-encryption-mode=SERVER_AUTHENTICATION

gcloud redis instances describe my-cache --region=us-central1 --format="value(host,port)"
gcloud redis instances list --region=us-central1

--tier=standard provisions a replicated, auto-failover instance (GCP's equivalent of ElastiCache's Multi-AZ Redis); basic is a single node with no failover and no SLA, appropriate only for a cache whose complete, silent loss is genuinely tolerable. Memorystore is reachable only via private IP on the specified VPC by design, there's no public-IP option at all, since an unauthenticated Redis endpoint exposed to the internet is a well-known, actively-scanned-for misconfiguration; --enable-auth adds an application-level AUTH token on top of that network isolation for defense in depth.

Real-world scenario: zero-downtime major-version upgrade rehearsal#

A team needs to validate a PostgreSQL 14 to 16 upgrade against production-shaped data before touching the real instance:

gcloud sql instances create prod-pg16-rehearsal \
  --database-version=POSTGRES_16 --tier=db-custom-4-16384 --region=us-central1

gcloud sql export sql prod-instance gs://my-bucket/prod-snapshot.sql --database=app_db
gcloud sql import sql prod-pg16-rehearsal gs://my-bucket/prod-snapshot.sql --database=app_db

# Run the application's real migration + smoke test suite against prod-pg16-rehearsal here

gcloud sql instances delete prod-pg16-rehearsal --quiet   # tear down once validated

Exporting and importing into a fresh instance on the target version, rather than upgrading the production instance directly first, turns a one-way risky operation into a disposable rehearsal, the rehearsal instance costs money only for the hours it exists and can be deleted the moment the migration is validated or found to need more work.

Real-world scenario: locking a Cloud SQL instance down to private IP only#

A security review flagged a Cloud SQL instance still reachable on a public IP months after the application moved onto GKE with private-IP connectivity configured:

• Confirm every consumer has actually migrated off the public IP (check application connection strings and any --authorized-networks entries still in use) before touching the instance
• Run gcloud sql instances patch my-instance --no-assign-ip to remove the public IP entirely
• Confirm private-IP connectivity from a representative pod: gcloud sql instances describe my-instance --format="value(ipAddresses)" should now show only a PRIVATE type entry
• Update any monitoring/alerting that was keyed to the old public IP address

CI/CD integration recipe: running Cloud SQL migrations from GitHub Actions via the Auth Proxy#

# .github/workflows/db-migrate.yml
name: Run DB migrations
on:
  push:
    branches: [main]

permissions:
  id-token: write
  contents: read

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: google-github-actions/auth@v2
        with:
          workload_identity_provider: projects/<project-number>/locations/global/workloadIdentityPools/github-pool/providers/github-provider
          service_account: db-migrator@my-project-id.iam.gserviceaccount.com
      - uses: mattes/gce-cloudsql-proxy-action@v1  # or download cloud-sql-proxy directly
        with:
          instance: my-project-id:us-central1:my-instance
      - run: npm run migrate -- --host=127.0.0.1 --port=5432

The Auth Proxy step authenticates via the same Workload Identity Federation-issued token the earlier auth step already obtained, no separate database password lives in CI secrets at all, only IAM permissions on the db-migrator service account control what the pipeline can reach.

Common pitfalls#

• Reaching for gcloud sql connect from application code or a script. It's built for an interactive human session, temporarily mutating authorized networks; use the Auth Proxy or private IP instead.
• Expecting Firestore's --location to be changeable after creation. It isn't, plan the location before the first firestore databases create, not after data is already flowing in.
• Querying Firestore on two+ fields with no matching composite index provisioned. The query fails outright at request time rather than running slowly, provision the index ahead of shipping the query.
• Deploying Memorystore expecting a public endpoint. There isn't one by design; the application must be on the same VPC (directly or via peering/Serverless VPC Access) to reach it at all.
• Skipping --deletion-protection on anything that holds real data. It costs nothing and blocks exactly one class of accident, an errant gcloud sql instances delete against the wrong instance name.

Exit codes#

0 success, non-zero on any API/validation error, a long-running operation like sql instances create that times out client-side while the instance is still provisioning in the background still exits non-zero even though the instance may finish successfully moments later; gcloud sql operations list --instance=my-instance confirms the operation's real server-side status rather than trusting only the triggering command's exit code.

When to reach for something else#

For a workload that's genuinely serverless-shaped end to end and doesn't need SQL joins or transactions beyond a single document, Firestore (or, for very simple key-value needs, Memorystore alone) avoids managing an instance's sizing and maintenance windows entirely. For declarative, reviewable database provisioning across environments, prefer Terraform's google_sql_database_instance/google_firestore_database/ google_redis_instance resources over a growing shell script of gcloud sql/gcloud firestore/gcloud redis calls, consistent with the IaC guidance on pages 01-03.