SQL & Relational Data Modeling — Node.js Course

What you'll learn

A database is not a larger JSON file. Relational design lets the database protect identity, relationships, and invariants while SQL retrieves exactly the rows an API needs. You will model users, tasks, and tags before connecting Node.js.

By the end of this lesson, you'll be able to:

Translate domain relationships into tables and keys
Use constraints to reject invalid state
Write joins, filters, grouping, and aggregates
Choose indexes from real query patterns

Core mental model

Node.js becomes easier when you separate the JavaScript language from the runtime and the operating-system capabilities it exposes. Use this table as a decision guide.

Concept	What it means	Decision rule
Primary key	Stable unique identity for one row	Choose an identifier whose meaning and generation strategy are explicit
Foreign key	Database-enforced reference to another row	Use it whenever orphaned references would violate the model
Constraint	A rule enforced for every write	Put universal data invariants in the database as well as the application
Index	An auxiliary structure that accelerates selected access patterns	Add from measured queries; remember every index costs space and write work

Professional workflow

Build and verify Node.js programs from the terminal in small, observable steps.

Define the relational data model boundary: inputs, outputs, invariants, ownership, and expected failures.
Design the data or message contract before choosing implementation details.
Implement the smallest correct path with dependencies passed explicitly.
Add validation, failure translation, cleanup, and concurrency behavior.
Verify the boundary with realistic data and at least one adversarial case.
Measure or observe the behavior before optimizing or extracting abstractions.

Keep the feedback loop short

Run the smallest useful command after every meaningful change. Read the complete error message before editing again, and keep inputs and outputs visible while you learn.

Guided code lab

Create a constrained task model

Keys and checks make invalid ownership, blank titles, and impossible state combinations unrepresentable at the storage boundary.

001_create_tasks.sql

CREATE TABLE users (
  id uuid PRIMARY KEY,
  email text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE tasks (
  id uuid PRIMARY KEY,
  owner_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title text NOT NULL CHECK (length(trim(title)) BETWEEN 1 AND 200),
  completed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  CHECK (completed_at IS NULL OR completed_at >= created_at)
);

Query a user-facing summary

A left join preserves users with zero tasks. FILTER calculates both counts in one grouped query.

task-summary.sql

SELECT
  u.id,
  u.email,
  count(t.id) AS total_tasks,
  count(t.id) FILTER (WHERE t.completed_at IS NULL) AS open_tasks
FROM users AS u
LEFT JOIN tasks AS t ON t.owner_id = u.id
WHERE u.id = $1
GROUP BY u.id, u.email;

Index the query you actually run

This partial composite index supports listing one owner's open tasks in newest-first order without indexing completed rows.

002_task_indexes.sql

CREATE INDEX tasks_owner_open_created_idx
ON tasks (owner_id, created_at DESC)
WHERE completed_at IS NULL;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM tasks
WHERE owner_id = $1 AND completed_at IS NULL
ORDER BY created_at DESC
LIMIT 20;

Production practice

Contract

Let the database enforce universal identity, relationship, nullability, uniqueness, and range rules; let services enforce workflow rules that need broader context.

Verification

Seed representative and adversarial rows, prove constraints reject invalid writes, and inspect EXPLAIN for the API's most important queries.

Operations

Name migrations and indexes clearly, avoid unbounded result sets, and treat schema changes as reviewed deployable artifacts.

Common failure mode

Adding an index to every column slows writes and consumes memory without guaranteeing useful plans. Index complete query patterns—filters, joins, and ordering—then verify with EXPLAIN.

Independent workshop

Design the relational schema for a multi-user task API with tags and comments.

Your finished workshop must include:

Tables with primary and foreign keys
Constraints for at least five invariants
A normalized many-to-many tag relationship
Three API-facing queries with parameters
Two justified indexes and EXPLAIN notes

Definition of done

Run the happy path and at least two edge cases, keep responsibilities separated, and add a short README explaining how to run the program.

Recap & quick check

Key takeaways

Schemas are executable contracts
Keys protect identity and relationships
Constraints defend every write path
Joins reconstruct related views
Indexes serve queries rather than columns
Schema evolution belongs in migrations

Quick check

1. What prevents a task from referencing a missing user?

2. Why use a left join for user task counts?

3. What should justify an index?

Next: PostgreSQL from Node.js