Day 1: Spin Up Your First Database & Design the Product Catalog — And Watch the psql Prompt Appear.

Day 1: Spin Up Your First Database & Design the Product Catalog — And Watch the psql Prompt Appear.

Welcome to the first day of building a resilient retail catalog system! Today, we're laying the absolute bedrock: getting a real relational database up and running on your machine and giving it its first job – storing our product information.

Most beginner courses rush past why we use databases, or what guarantees they offer. Here, we start by feeling the problem a database solves, and then we build the solution. You haven't built anything yet, but that's about to change.

The Problem: Data That Disappears (or Gets Messy)

Imagine you're building a simple retail catalog. You need to store product names, descriptions, and prices. In a pinch, you might think, "I'll just save it to a text file!" or "I'll keep it in memory within my application."

This works for about five minutes. What happens if your application crashes? All that in-memory data vanishes. What if two people try to update the same text file at the same time? You get a corrupted mess. How do you find all products under $10 without reading the entire file line by line? It's a nightmare.

This fragility, this lack of structure and persistence, is precisely the problem relational databases were invented to solve. They give us a durable, organized, and concurrently accessible home for our structured data.

The Intuition: A Digital Ledger for Your Business

Think of a traditional library catalog or an old-school accounting ledger. These systems are designed for order:

• Structure: Each item (book, transaction) has specific, consistent fields (title, author, date, amount).

• Durability: Once written, entries are hard to erase or accidentally change. They're on paper, bound.

• Accessibility: You can quickly look up a book by its call number or all transactions for a specific date.

A relational database is essentially a hyper-efficient, digital version of this ledger. It guarantees that your product data, once recorded, stays recorded—even if your computer restarts—and can be found quickly. This core guarantee is what engineers call Durability, one of the "D" in ACID properties (Atomicity, Consistency, Isolation, Durability) that define transactional databases. This is the load-bearing concept of today's lesson.

Component Architecture: Your First Database Server

Today, our architecture is beautifully simple. We're running a single PostgreSQL database server. PostgreSQL is an open-source, robust, and widely-used relational database that powers countless production systems, from small startups to large enterprises.

Component Architecture shows our setup. Your laptop hosts a Docker container running PostgreSQL. For now, our "application" is psql, PostgreSQL's interactive terminal, which connects directly to the database. The crucial part: PostgreSQL stores its data on your laptop's disk, ensuring durability.

We're using Docker for a few key reasons:

1. 1.Isolation: The database runs in its own isolated environment, preventing conflicts with other software on your machine.

2. 2.Reproducibility: Everyone gets the exact same PostgreSQL setup, eliminating "it works on my machine" issues.

3. 3.Ease of Teardown: When you're done, you can remove the container and its data without leaving behind messy files or services.

Designing Your First Table: The products Catalog

Our retail catalog needs to store products. What information defines a product?

• A unique identifier (ID)

• A name

• A description

• A price

In a relational database, we organize this information into tables, which are like spreadsheets. Each row is a record (a single product), and each column is an attribute (ID, name, price).

Here's the SQL (Structured Query Language) to create our products table:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL
);

Let's break down these choices:

• id SERIAL PRIMARY KEY: SERIAL is a PostgreSQL-specific type that automatically assigns a unique, incrementing integer for each new row. PRIMARY KEY means this id is guaranteed to be unique and is the main way to identify a product. This is critical for data integrity.

• name VARCHAR(255) NOT NULL: VARCHAR(255) means a variable-length string up to 255 characters. NOT NULL means every product must have a name. What if a product name is longer? We could use TEXT, which has virtually no length limit, but VARCHAR can be more efficient for shorter, common strings. For a product catalog, 255 characters is a reasonable initial limit.

• description TEXT: TEXT allows for longer, multi-paragraph descriptions without a fixed length limit. It's flexible.

• price NUMERIC(10, 2) NOT NULL: NUMERIC is perfect for monetary values. (10, 2) means it can store up to 10 digits in total, with 2 digits after the decimal point (e.g., 99999999.99). Using NUMERIC avoids the precision issues often associated with FLOAT or DOUBLE types, which are approximations. This is a crucial detail for financial data.

Flowchart illustrates the steps we'll take: starting the database, creating the table, inserting data, and then observing its persistence.

Populating Your Catalog

Once the table is created, we'll add some initial products:

INSERT INTO products (name, description, price) VALUES
('Echo Dot (5th Gen)', 'Our best-sounding Echo Dot yet. Enjoy an improved audio experience...', 49.99),
('Fire TV Stick 4K Max', 'Stream over 1 million movies and TV episodes from Netflix, Prime Video...', 59.99),
('Kindle Paperwhite', 'Now with a 6.8” display and a battery life of up to 10 weeks.', 139.99);

These INSERT statements add rows to our products table. Notice we don't specify the id; SERIAL handles that for us.

The Failure Demo: Proving Durability

This is where the rubber meets the road. We've talked about durability, now let's break something to prove it.

1. 1.We'll start our PostgreSQL database.

2. 2.We'll connect and insert a new product.

3. 3.We'll then deliberately kill the database container. This simulates a server crash, a power outage, or an unexpected process termination.

4. 4.Finally, we'll restart the database and connect again.

State Machine shows the database moving from "stopped" to "running" (empty), then "running" (with data), then back to "stopped", and finally returning to "running" with the data still intact.

Expected Outcome: Even after killing and restarting the database, your newly inserted product will still be there. This is the fundamental promise of a durable database. If this didn't happen, your retail catalog would constantly lose products!

What This Looks Like At Production Scale

On your laptop, we're running a single PostgreSQL instance. In a real-world production system handling hundreds of millions of requests per second, this setup would buckle immediately. We're deliberately cutting corners for simplicity:

• No Replication: Production databases typically have multiple copies (replicas) of data for high availability and read scaling. If the primary database fails, a replica can take over.

• No Backups: We're not configuring automated backups. In production, regular backups are non-negotiable for disaster recovery.

• Single Point of Failure: If your single PostgreSQL instance fails, your entire application goes down.

However, the core concept of durability—that committed data is written to stable storage and survives crashes—is fundamental and applies whether you have one database server or a thousand. We start here, understanding the basics, before tackling the complexities of distributed systems.

Assignment: Expand Your Catalog

Now that you have a working, durable products table, it's time to extend it.

1. 1.Add a new column: Products often belong to categories (e.g., "Electronics", "Books", "Home Goods"). Add a category column to your products table. Choose an appropriate data type (e.g., VARCHAR(100)).

2. 2.Update existing products: Assign categories to the products you've already inserted.

3. 3.Add new products: Insert at least two new products, making sure to include their category.

4. 4.Verify: Query your products table to ensure all products (old and new) have their categories correctly assigned and persist after a database restart.

Solution Hints

• To add a column, look up ALTER TABLE ADD COLUMN.

• To update existing data, use UPDATE products SET category = '...' WHERE id = ...;.

• Remember to restart your database (kill and then start your Docker container) and verify the data persists.