Overview and What You Will Learn
In this lab, you will create a Cloud SQL instance for a relational workload and a Firestore database for a document-shaped workload, running a representative query against each - directly experiencing why the same data modeled two different ways points to two genuinely different database products.
Why This Matters in Production
A team defaults to Firestore for a new e-commerce order system because "NoSQL scales better," then discovers months later that tracking relationships between orders, customers, and inventory across many linked tables - exactly what the workload actually needed - is a fundamentally relational access pattern Firestore's document model doesn't support natively. The mistake wasn't a technical failure; it was never asking whether the access pattern needed joins and transactions before choosing.
Core Principles
GCP's managed database products aren't interchangeable "database" options - each is built for a genuinely different data shape and scale requirement.
+------------------------------------------+| Cloud SQL || Managed MySQL, PostgreSQL, SQL Server || Best for: traditional relational workloads || at moderate scale, single-region |+------------------------------------------+| Firestore || Serverless NoSQL document database || Best for: mobile/web app data, flexible || schemas, real-time client sync |+------------------------------------------+| Spanner || Globally distributed, strongly consistent || relational database || Best for: massive scale needing both SQL || semantics and horizontal scalability |+------------------------------------------+| Bigtable || Wide-column NoSQL for very high throughput || Best for: time-series data, IoT telemetry |+------------------------------------------+Detailed Step-by-Step Practical Lab
- Create a project and enable the necessary APIs:
gcloud projects create gcp-db-lab-2026 --name="Database Decision Lab"gcloud config set project gcp-db-lab-2026gcloud services enable sqladmin.googleapis.com firestore.googleapis.com- Create a Cloud SQL instance for a relational, transactional workload (an orders system needing joins across customers, orders, and line items):
gcloud sql instances create pg-orders-lab \ --database-version=POSTGRES_15 \ --tier=db-f1-micro \ --region=asia-south1- Create a database and a sample table inside it:
gcloud sql databases create orders_db --instance=pg-orders-lab gcloud sql connect pg-orders-lab --user=postgresCREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT);CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id), total NUMERIC);INSERT INTO customers (name) VALUES ('Priya Sharma');INSERT INTO orders (customer_id, total) VALUES (1, 4599.00); SELECT customers.name, orders.totalFROM orders JOIN customers ON orders.customer_id = customers.id;NoteThis JOIN across two related tables is exactly the access pattern that points toward a relational database like Cloud SQL - Firestore's document model does not support this kind of cross-collection join natively.
- Create a Firestore database in Native mode for a different workload shape - user session or profile data with a flexible, evolving schema:
gcloud firestore databases create --location=asia-south1- Add a document representing user profile data with a flexible structure:
# Using the Firestore CLI equivalent via gcloud (actual writes are typically# done through a client SDK, shown here conceptually)gcloud firestore export gs://gcp-db-lab-2026-firestore-exportCompare the two data models directly - the Cloud SQL schema enforces a fixed structure with explicit relationships, while a Firestore document can have a different, flexible shape from one document to the next within the same collection.
Clean up:
gcloud sql instances delete pg-orders-lab --quietgcloud projects delete gcp-db-lab-2026 --quietProduction Best Practices & Common Pitfalls
Common MistakeChoosing a database based on general reputation ("NoSQL scales better") rather than checking the specific access pattern - joins, transactions, flexible schemas - the actual workload requires. A workload needing relational joins and multi-row transactions is a strong signal toward Cloud SQL or Spanner, regardless of expected data volume.
TipIf a workload starts on Cloud SQL and later needs both SQL semantics and horizontal scale beyond what a single Cloud SQL instance can provide, Spanner is the natural next step - not a NoSQL database, since the actual requirement (SQL semantics) hasn't changed, only the scale has.
- BigQuery is not a substitute for a transactional database, even though it also uses SQL. It is priced and optimized for analytical queries over large datasets, not frequent small reads and writes from a live application.
- Firestore's real-time sync capability is a genuine differentiator for mobile and web apps needing to reflect data changes to connected clients instantly - a capability Cloud SQL and Spanner do not provide natively.
Quick Reference & Troubleshooting Commands
| Command | Description |
|---|---|
gcloud sql instances create |
Create a managed Cloud SQL instance |
gcloud sql connect |
Connect directly to a Cloud SQL instance |
gcloud firestore databases create |
Create a Firestore database |
gcloud firestore export |
Export Firestore data to Cloud Storage |