Relational Databases and SQL Fundamentals

Before connecting to a database, you need to understand how relational databases think about data. This conceptual foundation makes every SQL query make sense.

What is a relational database?

A relational database stores data in tables — like spreadsheets with rigid column definitions. Data in one table can reference data in another through relationships.

The Bulletin database has four tables:

users    — id, username, password_hash, created_at
posts    — id, title, body, user_id (→ users), upvotes, created_at
comments — id, body, user_id (→ users), post_id (→ posts), created_at
upvotes  — id, user_id (→ users), post_id (→ posts)

Each row in posts has a user_id that points to a row in users. That’s a relationship — a foreign key.

Tables, rows, and columns

A table is the structure (like a class definition). A row is one record (like an instance). Columns are the fields every row has.

Table: posts
┌────┬──────────────────┬───────────┬─────────┬────────────┐
│ id │ title            │ body      │ user_id │ created_at │
├────┼──────────────────┼───────────┼─────────┼────────────┤
│  1 │ Hello Bulletin   │ First...  │       3 │ 2024-01-15 │
│  2 │ Node.js is great │ I love... │       1 │ 2024-01-16 │
└────┴──────────────────┴───────────┴─────────┴────────────┘

Primary keys

Every row needs a unique identifier — the primary key. In most databases, this is an auto-incrementing integer called id. No two rows can have the same primary key.

Foreign keys

A foreign key is a column in one table that references the primary key of another table. posts.user_id is a foreign key — it must match a valid users.id.

users.id = 3  ←──  posts.user_id = 3  (post belongs to user 3)

Foreign keys enforce referential integrity: you can’t create a post for a user that doesn’t exist.

SQL basics

SQL (Structured Query Language) is how you query and modify data. The four fundamental operations:

-- Read data
SELECT id, title FROM posts WHERE user_id = 3 ORDER BY created_at DESC;

-- Create data
INSERT INTO posts (title, body, user_id) VALUES ('Hello', 'World', 3);

-- Update data
UPDATE posts SET title = 'Updated Title' WHERE id = 1;

-- Delete data
DELETE FROM posts WHERE id = 1;

JOINs

JOINs combine data from multiple tables:

-- Get posts with their author's username
SELECT posts.id, posts.title, users.username
FROM posts
JOIN users ON posts.user_id = users.id
ORDER BY posts.created_at DESC;

This is more efficient than two separate queries — the database does the combining.

Why SQLite?

SQLite stores the entire database in a single file (bulletin.db). No server to install, no network connection. It’s:

• Simple — install a npm package, that’s it
• Portable — the database file travels with your project
• Production-capable — SQLite powers billions of apps (every iOS and Android device has one)
• Perfect for learning — real SQL, real persistence, no infrastructure

Production APIs at scale often use PostgreSQL or MySQL. The SQL you learn here transfers directly.

Exercise

Think through the Bulletin schema:

1. What columns does the users table need?
2. What columns does the posts table need, and what foreign keys?
3. How would you model upvotes — can the same user upvote the same post twice?
4. What columns does comments need?

Write it out before the next lesson, then compare to the schema we’ll create.

Recap

• Relational databases store data in tables with rows and columns.
• Every row has a unique primary key (usually id).
• Foreign keys reference primary keys in other tables, creating relationships.
• SQL is the language for querying (SELECT), creating (INSERT), updating (UPDATE), and deleting (DELETE).
• JOINs combine rows from multiple tables into one result set.
• SQLite stores everything in a single file — perfect for development and learning.