In the previous lessons, you explored how Firestore — a flexible NoSQL document database — lets you store and query data with nested objects and arrays. This approach is great for scalability and evolving schemas, but it can become challenging when your application needs to manage complex relationships between entities.

Imagine building an e-commerce platform with customers, products, orders, and order items. A customer can place multiple orders, each order can contain multiple products, and each product can appear in many orders. In Firestore, you might duplicate customer information in every order document. If a customer updates their email, you’d need to find and update every order — missing one could lead to inconsistent data. You could also split data into separate collections, but then you’d need to manually join data in your application code.

This is where Google Cloud SQL comes in. Cloud SQL is a fully managed relational database service for MySQL, PostgreSQL, and SQL Server on Google Cloud. Unlike NoSQL databases, Cloud SQL uses a relational model: data is organized into tables with strictly defined columns, and relationships between tables are enforced through foreign keys. Each customer, product, and order is stored only once, and relationships are maintained by referencing IDs. When a customer’s email changes, you update a single row, and all related data remains consistent.

The relational model also provides ACID transactions, which guarantee that complex operations involving multiple tables either complete entirely or not at all. For example, when a customer places an order, you can create the order, add order items, and update inventory in a single transaction — ensuring data consistency.

In this lesson, you’ll learn how Cloud SQL’s relational model uses tables, foreign keys, and JOINs to represent complex data relationships. You’ll see when structured relationships and guaranteed consistency are more important than the flexibility and scalability of NoSQL databases.

The relational model organizes data into tables, where each table represents a specific type of entity. Unlike Firestore documents, every row in a relational table has the same columns, and the structure is enforced by a schema.

For our e-commerce example, you would create four tables: customers, products, orders, and order_items. Here are their definitions using MySQL syntax (supported by Cloud SQL):

Let’s break down the SQL syntax:

• customer_id INT PRIMARY KEY AUTO_INCREMENT: This column holds integer values, uniquely identifies each row, and automatically generates a new sequential number for each new customer.

A JOIN is a SQL operation that combines rows from two or more tables based on related columns. In Cloud SQL, you use JOIN queries to efficiently retrieve and analyze data across tables.

The most common type is an INNER JOIN, which returns only rows with matches in both tables. A LEFT JOIN returns all rows from the left table and matching rows from the right table, using nulls where there's no match. This is useful for including entities even if they don't have related data. RIGHT JOIN works like LEFT JOIN but keeps all rows from the right table instead, and FULL OUTER JOIN keeps all rows from both tables. Note that MySQL (one of the Cloud SQL database engines) does not support FULL OUTER JOIN, though PostgreSQL does. In practice, most analytical queries can be accomplished with INNER and LEFT JOINs, which we'll focus on here.

Here are some example queries for our e-commerce schema:

ACID transactions in Cloud SQL ensure that operations maintain data integrity, even when multiple changes must happen together or when multiple users access the database at the same time. ACID stands for:

• Atomicity: All operations in a transaction complete together or none do. If any step fails, the entire transaction is rolled back.
• Consistency: Transactions maintain all constraints, such as foreign keys and unique constraints.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Once committed, changes are permanent, even in the event of a crash.

Let's look at a concrete problem: placing an order while ensuring sufficient inventory. Without proper transaction handling, you could have a race condition:

1. Transaction A reads: "Product has 10 units in stock"
2. Transaction B reads: "Product has 10 units in stock"
3. Transaction A places an order for 8 units
4. Transaction B places an order for 8 units
5. Both transactions succeed, but you've oversold by 6 units!

Both transactions saw 10 units available, but by executing concurrently, they created invalid data. This is where SELECT ... FOR UPDATE comes in. This SQL statement locks the selected rows, preventing other transactions from reading or modifying them until the current transaction completes. It guarantees that only one transaction at a time can check and update inventory for a product.

Here's how you would use a transaction in Cloud SQL (MySQL) to place an order while ensuring inventory is available:

The %s placeholders in these queries are used by MySQL's Python connector (). Instead of building SQL strings by concatenating values (like ), you use placeholders and pass values separately as a tuple. This prevents , where malicious users could insert SQL commands into your queries. For example, if a user provided as input, string concatenation would execute the DROP command, deleting your entire products table. With parameterized queries, the database treats the entire input as a safe data value, not executable code. Note that is specific to MySQL's Python connector — PostgreSQL's uses the same syntax, but other databases like SQLite use as the placeholder. All SQL client libraries support parameterized queries — you pass values as a tuple or list, and the library safely escapes and inserts them.

Choose Cloud SQL when:

• Your data has structured relationships that must be enforced (customers → orders → products)
• Updates must be consistent across multiple entities (order creation + inventory decrease)
• You need complex analytical queries spanning multiple entities
• Data accuracy is critical (financial transactions, inventory management)

Choose Firestore when:

• Your schema needs to evolve frequently without downtime
• Your access patterns are primarily key-based lookups (get user by ID)
• You need to scale to very high request rates with simple queries
• Eventual consistency is acceptable

Here’s a comparison for our e-commerce example:

In the Firestore approach, each order document might duplicate customer information for fast lookups, but this makes updates and analytics more complex. In the Cloud SQL approach, customer information is stored once and referenced by ID, making updates and complex queries easier and more reliable.

The choice depends on whether you value consistency and complex queries more than flexibility and raw performance for simple operations. Many applications use both: transactional data in Cloud SQL for consistency, and Firestore for flexible, scalable access patterns.

You now understand two database models available on Google Cloud:

• Firestore: A NoSQL document database for flexible, hierarchical data with evolving schemas and rapid development.
• Cloud SQL: A fully managed relational database for structured data, complex relationships, and guaranteed consistency.

These are not competing alternatives, but complementary tools for different scenarios. Use Firestore when your schema needs flexibility, your access patterns are primarily key-based lookups, and eventual consistency is acceptable. Use Cloud SQL when relationships between entities are complex, data consistency is critical, and you need sophisticated analytical queries with JOINs.

The key differences come down to:

• Schema flexibility vs enforcement: Firestore lets your schema evolve without migrations; Cloud SQL enforces structure and relationships.
• Duplication vs normalization: Firestore often duplicates data for fast access; Cloud SQL normalizes data and uses JOINs.
• Eventual vs immediate consistency: Firestore offers eventual consistency; Cloud SQL provides ACID transactions.

To get started with Cloud SQL, you create an instance, choose your database engine (MySQL, PostgreSQL, or SQL Server), and connect using standard SQL clients or libraries. You can then define schemas with foreign keys, insert data, and run queries as shown in this lesson.

In the upcoming practice exercises, you'll define relational schemas with foreign keys, insert sample data, write JOIN queries, and work with ACID transactions. Pay attention to how foreign keys prevent invalid data, how JOINs simplify complex queries, and how transactions guarantee consistency.