Relational Databases

Tables, SQL, and ACID — the foundation of structured data storage

What Makes Them "Relational"?

Relational databases store data in tables (rows and columns), enforce relationships between tables via foreign keys, and use SQL (Structured Query Language) to create, read, update, and delete data. They've been the backbone of web applications since the 1990s.

The "relational" in relational database doesn't mean "tables are related to each other" (though they are). It comes from the mathematical concept of a relation — a set of tuples (rows) that share the same attributes (columns). In practical terms:

  • Tables define a structure (columns with types)
  • Rows are individual records that conform to that structure
  • Foreign keys link rows in one table to rows in another
  • Constraints enforce data integrity (NOT NULL, UNIQUE, CHECK)
  • ACID transactions guarantee that operations either fully succeed or fully fail

PostgreSQL as Our Primary Example

We use PostgreSQL (often called "Postgres") in this course. It's open-source, standards-compliant, and one of the most capable relational databases available. Other popular options include MySQL/MariaDB, SQLite, and Microsoft SQL Server — they all use SQL with minor dialect differences.

Defining a Table: The Stories Schema

Here's the table we'll use throughout our tutorials — a stories table for tracking work items (like a simple project management tool):

CREATE TABLE stories ( id SERIAL PRIMARY KEY, title VARCHAR(200) NOT NULL, description TEXT, priority VARCHAR(10) DEFAULT 'medium', status VARCHAR(20) DEFAULT 'todo', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

Let's break down each part:

Column Type Constraints Purpose
id SERIAL PRIMARY KEY Auto-incrementing unique identifier
title VARCHAR(200) NOT NULL Story title, required, max 200 chars
description TEXT (none — nullable) Optional longer description
priority VARCHAR(10) DEFAULT 'medium' low, medium, or high
status VARCHAR(20) DEFAULT 'todo' todo, in-progress, or done
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP Auto-set when row is created

SQL Basics Mapped to CRUD

SQL maps directly to the same CRUD operations you already know from REST:

CRUD Operation SQL Statement HTTP Method REST Endpoint
Create INSERT INTO POST /api/stories
Read (all) SELECT * FROM GET /api/stories
Read (one) SELECT * FROM ... WHERE id = ? GET /api/stories/:id
Update UPDATE ... SET ... WHERE id = ? PUT / PATCH /api/stories/:id
Delete DELETE FROM ... WHERE id = ? DELETE /api/stories/:id

CRUD SQL Examples

Here are the fundamental SQL operations you'll use in every database-backed application:

-- CREATE: Insert a new story INSERT INTO stories (title, description, priority) VALUES ('Build login page', 'Add username/password auth', 'high') RETURNING *; -- READ: Get all stories SELECT * FROM stories ORDER BY created_at DESC; -- READ: Get one story by ID SELECT * FROM stories WHERE id = 1; -- READ: Filter stories SELECT * FROM stories WHERE status = 'todo' AND priority = 'high'; -- UPDATE: Change a story's status UPDATE stories SET status = 'in-progress' WHERE id = 1 RETURNING *; -- DELETE: Remove a story DELETE FROM stories WHERE id = 1 RETURNING *;