Spanner Free Trial: Global SQL Database Hands-On
What you build: a free trial Cloud Spanner instance with interleaved tables, real duplicate and orphan errors, a unique index, a Python app saving an order and points in one transaction, and a stale r

✍️ Translate this blog into Hindi, Tamil or Telugu
Cloud Spanner is Google's globally distributed SQL database: it speaks SQL with real transactions like PostgreSQL or MySQL, but it scales across machines and regions without you sharding anything. It is the database behind very large Google services, and it used to be out of reach for learners because of its price. The Spanner free trial instance changes that: 90 days, 10 GB, no charge. In this lab you create one, design two tables the Spanner way (interleaved), load and query data, hit the two errors every Spanner beginner meets, add an index, write a small Python app that saves an order and its loyalty points in one transaction, and read data "as it was a minute ago".
Who it is for: developers and data beginners who know basic SQL. Time: about 1 hour. Cost: free. The free trial instance costs nothing for 90 days, and we delete it at the end.
Every command below was run for real by the CareerByteCode Team on a fresh Google Cloud project, and every output is the real result we got.
⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโน40,000Start learning →Part A: Spanner in plain English

- Instance: the compute and storage that run your databases, in one region (
regional-us-east1) or several (nam3,nam-eur-asia1). Normal instances are sized in processing units; the free trial instance is a special small one with 10 GB for 90 days, one per project. - Database: tables, indexes and their data, inside an instance.
- Primary key: every table must have one, and Spanner stores rows in key order, split into ranges across servers. Choosing the key well is the main design decision.
- Interleaved table: a child table stored inside its parent.
Ordersis interleaved inCustomers, so customer 1's orders sit right next to customer 1, and reading a customer with their orders is one fast read. Real-life example: a filing cabinet where each customer's folder holds their own order slips, instead of one giant drawer of all slips. - Transaction: several reads and writes that succeed or fail together. Spanner gives strong (externally consistent) transactions, even across regions.
- Commit timestamp: Spanner can stamp a row with the exact moment it was committed (
PENDING_COMMIT_TIMESTAMP()). - Stale read: reading the data as it was at an earlier moment. It never blocks writers, which is great for reports.
Before you start
- A Google Cloud account with billing (the free trial works) and a project. One free Spanner instance per project, ever (see Troubleshooting), so use a project that has not had one.
- Open Cloud Shell (the
>_icon at the top right of the console) and set your project:
export PROJECT_ID="your-project-id"
gcloud config set project $PROJECT_ID
gcloud services enable spanner.googleapis.com- Make a folder
~/lab194with two files:schema.sql(Step 3) andshop_app.py(Step 8). All commands run in Cloud Shell.
Part B: the lab
Step 1: where can Spanner run?
Why: an instance configuration decides where your data lives. A regional config keeps it in one region (with copies across zones); multi-region configs like nam3 or nam-eur-asia1 keep copies across regions so the database survives a whole region going down.
# Where can Spanner run? An instance config = the region (or regions) that hold your data
gcloud spanner instance-configs list --format="table(name.basename(), displayName)" | grep -E "NAME|us-east1|nam3|nam-eur" | head -5NAME DISPLAY_NAME
nam-eur-asia1 United States, Europe, and Asia (Iowa/Oklahoma/Belgium/Taiwan)
nam-eur-asia3 (Iowa/South Carolina/Belgium/Netherlands/Taiwan/Oklahoma)
nam3 United States (Northern Virginia/South Carolina)
regional-us-east1 us-east1Step 2: create the free trial instance
Why: --instance-type=free-instance asks for the free trial. expireBehavior: REMOVE_AFTER_GRACE_PERIOD means that after 90 days (plus a grace period) it is removed unless you upgrade it to a paid instance.
# Create the FREE TRIAL instance (90 days, 10 GB, one per project)
gcloud spanner instances create shop-trial --config=regional-us-east1 --instance-type=free-instance --description="Free trial for the lab" 2>&1 | tail -1
gcloud spanner instances describe shop-trial --format="yaml(displayName, config.basename(), instanceType, state, freeInstanceMetadata.expireBehavior)"......done.
config: regional-us-east1
displayName: Free trial for the lab
freeInstanceMetadata:
expireBehavior: REMOVE_AFTER_GRACE_PERIOD
instanceType: FREE_INSTANCE
state: READYStep 3: a database with interleaved tables
Why: Customers has the key CustomerId. Orders has the key (CustomerId, OrderId), starting with the parent's key, and INTERLEAVE IN PARENT Customers ON DELETE CASCADE stores orders inside their customer and deletes them with it. allow_commit_timestamp lets Spanner fill CreatedAt with the commit time.
# A database with two tables: Orders is INTERLEAVED in Customers (each customer's orders are stored next to it)
cat schema.sql
gcloud spanner databases create shopdb --instance=shop-trial --ddl-file=schema.sql 2>&1 | tail -1
gcloud spanner databases ddl describe shopdb --instance=shop-trial | grep -E "CREATE|INTERLEAVE"CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
Name STRING(100) NOT NULL,
Email STRING(200) NOT NULL,
City STRING(50),
Points INT64 NOT NULL DEFAULT (0),
) PRIMARY KEY (CustomerId);
CREATE TABLE Orders (
CustomerId INT64 NOT NULL,
OrderId INT64 NOT NULL,
Item STRING(100) NOT NULL,
Amount NUMERIC NOT NULL,
CreatedAt TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp = true),
) PRIMARY KEY (CustomerId, OrderId),
INTERLEAVE IN PARENT Customers ON DELETE CASCADE;
.....done.
CREATE TABLE Customers (
CREATE TABLE Orders (
INTERLEAVE IN PARENT Customers ON DELETE CASCADE;
Step 4: insert data with SQL
Why: Spanner supports normal INSERT, UPDATE and DELETE (DML). PENDING_COMMIT_TIMESTAMP() is replaced by the real commit time.
# Insert data with SQL (DML)
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="INSERT INTO Customers (CustomerId, Name, Email, City) VALUES (1, 'Asha', 'asha@example.com', 'Chennai'), (2, 'Ravi', 'ravi@example.com', 'Pune'), (3, 'Meera', 'meera@example.com', 'Kochi')"
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="INSERT INTO Orders (CustomerId, OrderId, Item, Amount, CreatedAt) VALUES (1, 101, 'Laptop stand', 1499, PENDING_COMMIT_TIMESTAMP()), (1, 102, 'USB-C cable', 399, PENDING_COMMIT_TIMESTAMP()), (2, 201, 'Keyboard', 2499, PENDING_COMMIT_TIMESTAMP())"Statement modified 3 rows
Statement modified 3 rowsStep 5: query it
Why: a join and a group-by, exactly as in other SQL databases. Kochi has a customer with no orders, so LEFT JOIN with COALESCE shows 0.
# Query it: a join, and the totals per city
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="SELECT c.Name, o.OrderId, o.Item, o.Amount FROM Customers c JOIN Orders o ON o.CustomerId = c.CustomerId ORDER BY o.OrderId"
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="SELECT c.City, COUNT(o.OrderId) AS orders, COALESCE(SUM(o.Amount), 0) AS total FROM Customers c LEFT JOIN Orders o ON o.CustomerId = c.CustomerId GROUP BY c.City ORDER BY total DESC"Name OrderId Item Amount
Asha 101 Laptop stand 1499
Asha 102 USB-C cable 399
Ravi 201 Keyboard 2499
City orders total
Pune 1 2499
Chennai 2 1898
Kochi 0 0
Break it: a duplicate key and a missing parent
Two errors every Spanner beginner meets: inserting a row whose key already exists, and inserting a child row (an order) whose parent (customer 99) does not exist.
# Break it: a duplicate key, and an order for a customer that does not exist
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="INSERT INTO Customers (CustomerId, Name, Email) VALUES (1, 'Copy', 'copy@example.com')" 2>&1 | grep -iE "error|exists" | head -2
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="INSERT INTO Orders (CustomerId, OrderId, Item, Amount, CreatedAt) VALUES (99, 901, 'Ghost order', 100, PENDING_COMMIT_TIMESTAMP())" 2>&1 | grep -iE "error|parent|not found" | head -2
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="SELECT COUNT(*) AS customers FROM Customers"ERROR: (gcloud.spanner.databases.execute-sql) ALREADY_EXISTS: Row [1] in table Customers already exists
ERROR: (gcloud.spanner.databases.execute-sql) NOT_FOUND: Parent row for row [99,901] in table Orders is missing. Row cannot be written. This command is authenticated as lab-shell@your-project-id.iam.gserviceaccount.com which is the active account specified by the [core/account] property.
customers
3ALREADY_EXISTS: the primary key must be unique. UseINSERT OR UPDATE(orUPDATE) when you mean "change it".NOT_FOUND: Parent row ... is missing: an interleaved child cannot exist without its parent. This is a feature: it protects your data from orphan orders.- The table still has 3 customers: neither bad insert changed anything.
Step 6: a secondary index
Why: rows are stored in key order (CustomerId), so finding a customer by email would read every row. A secondary index on Email makes it a direct lookup, and UNIQUE also stops two customers from sharing an email. @{FORCE_INDEX=...} tells Spanner to use it. Building an index on an existing table takes a little while (the long line of dots), even on a tiny table.
# A secondary index: find a customer by email without reading the whole table, and keep emails unique
gcloud spanner databases ddl update shopdb --instance=shop-trial --ddl="CREATE UNIQUE INDEX CustomersByEmail ON Customers(Email)" 2>&1 | tail -1
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="SELECT CustomerId, Name FROM Customers@{FORCE_INDEX=CustomersByEmail} WHERE Email = 'ravi@example.com'"
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="INSERT INTO Customers (CustomerId, Name, Email) VALUES (4, 'Ravi K', 'ravi@example.com')" 2>&1 | grep -iE "error|unique|exists" | head -2..........done.
CustomerId Name
2 Ravi
ERROR: (gcloud.spanner.databases.execute-sql) ALREADY_EXISTS: Unique index violation on index CustomersByEmail at index key [ravi@example.com,2]. It conflicts with row [2] in table Customers.The last insert was refused by the unique index: ravi@example.com is already taken by customer 2. (That refused insert is why this step ends with exit code 1.)
Step 7: a Python app with one transaction
Why: this is what Spanner is for. shop_app.py places an order and adds loyalty points (1 point per 100 of the amount) inside run_in_transaction: both writes happen, or neither does. It signs in with your Cloud Shell login (on a server: a service account), so there is no password or key file.
# shop_app.py: a tiny app on Cloud Spanner. It places an order and adds loyalty points in ONE read-write transaction,
# then reads the customer back. Sign-in: the VM's service account (no key file, no password).
import sys
from google.cloud import spanner
INSTANCE, DATABASE = "shop-trial", "shopdb"
db = spanner.Client().instance(INSTANCE).database(DATABASE)
def place_order(customer_id, order_id, item, amount):
def work(tx):
rows = list(tx.execute_sql("SELECT Points FROM Customers WHERE CustomerId = @c",
params={"c": customer_id}, param_types={"c": spanner.param_types.INT64}))
if not rows:
raise ValueError(f"customer {customer_id} does not exist")
tx.insert("Orders", columns=("CustomerId", "OrderId", "Item", "Amount", "CreatedAt"),
values=[(customer_id, order_id, item, amount, spanner.COMMIT_TIMESTAMP)])
new_points = rows[0][0] + int(amount) // 100
tx.update("Customers", columns=("CustomerId", "Points"), values=[(customer_id, new_points)])
return new_points
return db.run_in_transaction(work)
if __name__ == "__main__":
cid, oid, item, amount = int(sys.argv[1]), int(sys.argv[2]), sys.argv[3], sys.argv[4]
try:
points = place_order(cid, oid, item, amount)
print(f"order {oid} saved for customer {cid}; points now {points}")
except Exception as e:
print(f"order {oid} NOT saved: {type(e).__name__}: {str(e).splitlines()[0][:120]}")
with db.snapshot() as snap:
sql = ("SELECT c.Name, c.Points, COUNT(o.OrderId) FROM Customers c LEFT JOIN Orders o ON o.CustomerId = c.CustomerId "
"WHERE c.CustomerId = @c GROUP BY c.Name, c.Points")
for r in snap.execute_sql(sql, params={"c": cid}, param_types={"c": spanner.param_types.INT64}):
print(f"{r[0]}: {r[1]} points, {r[2]} orders")We place one good order, one for a customer that does not exist, and the same order twice:
🧪⭐ Recommended by CareerByteCode · ByteLabs project bundleCloud and DevOps Realtime Starterโน499Build the projects →# A small Python app (on the Cloud Shell VM): an order and its loyalty points saved in ONE transaction
export GOOGLE_CLOUD_PROJECT=$PROJECT_ID # the project the app uses (Cloud Shell sets this for you)
python3 -m venv .venv && . .venv/bin/activate && pip install -q google-cloud-spanner 2>&1 | grep -iv "notice" | tail -1
grep -nE "^def|run_in_transaction|tx\.(insert|update)" shop_app.py
python3 shop_app.py 1 103 "Monitor arm" 3200
python3 shop_app.py 99 902 "Ghost order" 500
python3 shop_app.py 1 103 "Monitor arm again" 320010:def place_order(customer_id, order_id, item, amount):
16: tx.insert("Orders", columns=("CustomerId", "OrderId", "Item", "Amount", "CreatedAt"),
19: tx.update("Customers", columns=("CustomerId", "Points"), values=[(customer_id, new_points)])
21: return db.run_in_transaction(work)
order 103 saved for customer 1; points now 32
Asha: 32 points, 3 orders
order 902 NOT saved: ValueError: customer 99 does not exist
order 103 NOT saved: AlreadyExists: 409 Row [1,103] in table Orders already exists, request_id = 1.11740438615885963494.EXTERNAL_IP
Asha: 32 points, 3 orders
- Order 103 (3,200) was saved and Asha got 32 points, in one transaction.
- The order for customer 99 was refused by our own check inside the transaction, so nothing was written.
- Sending order 103 again failed with
AlreadyExists, and the transaction rolled back: Asha still has 32 points and 3 orders, not 64 points. Without a transaction you could easily end up with points for an order that was never saved.
Step 8: stale reads
Why: a stale read asks for the data as it was at a moment in the past. Spanner keeps old versions for a while (1 hour by default), so a report can read a consistent picture without blocking anyone who is writing.
# Stale reads: read the data as it was 60 seconds ago (reports that must not slow down writes)
gcloud spanner databases execute-sql shopdb --instance=shop-trial --read-timestamp=$(date -u -d "-60 seconds" +%Y-%m-%dT%H:%M:%SZ) --sql="SELECT CustomerId, Points FROM Customers WHERE CustomerId = 1"
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="SELECT CustomerId, Points FROM Customers WHERE CustomerId = 1"CustomerId Points
1 0
CustomerId Points
1 3260 seconds ago Asha had 0 points; now she has 32. Same table, same query, two moments in time.
POC: prove it works
- A free trial Spanner instance (
FREE_INSTANCE,READY) inus-east1. - Two tables with
Ordersinterleaved inCustomers, loaded with SQL and queried with a join and a group-by. - Spanner refused a duplicate key, an orphan order and a duplicate email (unique index).
- A Python app saved an order and its points in one transaction, and rolled back cleanly on a duplicate.
- A stale read returned the data as it was 60 seconds earlier.
Quiz task: check what you learned
- Why does the
Orderskey start withCustomerId? (Answer: an interleaved child's key must begin with its parent's key, so it is stored inside the parent.) - You need to find orders by
Itemquickly. What do you add? *(Answer:CREATE INDEX OrdersByItem ON Orders(Item).)* - An insert says
Parent row ... is missing. What does it mean? (Answer: the parent customer does not exist; insert the customer first.) - Why use a transaction for "save order + add points"? (Answer: both must happen or neither; a failure in the middle must not leave points without an order.)
- How many free trial instances can one project have? (Answer: one per project, ever.)
Clean up (delete everything)
Why: the free instance is free, but it still counts as your one free instance and holds data. Delete the database, then the instance.
# Clean up: delete the database and the free instance
gcloud spanner databases delete shopdb --instance=shop-trial --quiet 2>&1 | tail -1
gcloud spanner instances delete shop-trial --quiet 2>&1 | tail -1
echo "Spanner instances left: $(gcloud spanner instances list --format='value(name)' | wc -l)"Spanner instances left: 0
FAQ
When should I choose Spanner instead of Cloud SQL? Choose Cloud SQL (MySQL or PostgreSQL) for most apps: it is cheaper and familiar. Choose Spanner when you need more scale than one machine, very high availability (99.999 percent in multi-region configs) or one consistent database across regions.
Does Spanner speak PostgreSQL? Yes: a database can use the PostgreSQL dialect, so many PostgreSQL tools and drivers work. This lab used GoogleSQL, Spanner's default dialect.
Why not use an auto-increment ID as the key? Ever-growing keys send every new row to the same server (a "hotspot"). Spanner recommends keys that spread out, such as a UUID or a bit-reversed sequence. Our tiny lab used small numbers for readability.
What happens after the 90 days? You can upgrade the free instance to a paid one and keep the data. Otherwise it is removed after a grace period.
How do I see a query plan? Add --query-mode=PROFILE to execute-sql, or open the query in Spanner Studio in the console and look at the plan and statistics.
Can I back up a free trial instance? Free trial instances have limits (for example on backups and size). Upgrade to a paid instance for production features.
Troubleshooting
| You see | Why | Fix |
|---|---|---|
FAILED_PRECONDITION: Free instances are limited to 1 per project lifecycle. | The project already had a free instance, even a deleted one | Use another project (we hit this on our second run) |
ALREADY_EXISTS: Row [...] already exists | The primary key is taken | Use another key, or INSERT OR UPDATE / UPDATE |
NOT_FOUND: Parent row for row [...] is missing | Interleaved child without a parent | Insert the parent row first |
Unique index violation on index ... | A UNIQUE index already has that value | Use another value, or update the existing row |
Python: 403 ... spanner permission | Your account or the service account has no Spanner role | Grant roles/spanner.databaseUser (data) or roles/spanner.admin |
| Index creation takes long | Spanner backfills the index online | Wait; check with gcloud spanner operations list --instance=... |
What next
- A simpler, schemaless database: Firestore Hands-On: A NoSQL Database for Apps.
- Analytics SQL on huge data: BigQuery for Beginners: Query Billions of Rows for Free.
- Least-privilege roles for your app: Google Cloud IAM for Beginners: Roles, Members and Least Privilege.
- More hands-on posts: ByteBlog. Practice in ByteLabs and see the courses.
Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.
⭐ Keep learning with CareerByteCode
Hand-picked to go deeper on this topicGet CareerByteCode Team's next blog by email
One email a week when they publish. Add the weekly Best of ByteBlog too. No spam, unsubscribe with one click.
We email you once to confirm. By subscribing you agree to the privacy policy.
Related blogs
CareerByteCode TeamBigQuery ML: Train a Model with Just SQL
What you build: a churn prediction model written in SQL with BigQuery ML, scored and used for predictions, in your own Google Cloud account. Every step tested.
#beginners
CareerByteCode TeamCloud SQL for PostgreSQL: Create, Connect and Back Up
What you build: a Cloud SQL for PostgreSQL 17 instance with private IP only, a database and user, real data, an on-demand backup and a real restore, plus the Auth Proxy. Every step tested.
#bigquery
CareerByteCode TeamData Engineering Project for Your Resume on Google Cloud
What you build: a daily batch pipeline on Cloud Storage and BigQuery with layers, data quality checks, quarantine and idempotent re-runs. Every step tested.
#bigquery
CareerByteCode TeamRedis Caching: Make Your API 10x Faster
What you build: an API on a slow 1-million-row database with and without a Redis cache on a free VM, load-tested (about 20x more requests per second), plus stale data and a full cache broken and fixed
💬 Comments (0)
Top · NewestSign in to comment