Paths/Backend Engineer/Relational Databases / Schemas & Indexes
Design Tables With Constraints
Application code has bugs, constraints do not. Write the schema with the keys, the nullability and the foreign keys spelled out.
Validator
IDLE — checks run live as you type commands; submit when done
01Requirements
live checks- Directory ~/backend/db exists○
- File ~/backend/db/schema.sql exists○
- The schema declares a primary key and a foreign key○
- The schema marks the required columns as NOT NULL○
02Walkthrough
5 steps- 01
Create the db directory
Schema files live next to the code that queries them.
mkdir -p ~/backend/db - 02
Write the users table
A primary key is not optional, and an email is not optional either - the database should refuse the bad row before your code sees it.
echo CREATE TABLE users (\n id BIGSERIAL PRIMARY KEY,\n email TEXT NOT NULL UNIQUE,\n created_at TIMESTAMPTZ NOT NULL DEFAULT now()\n); > ~/backend/db/schema.sql - 03
Add the orders table
The foreign key is the relationship. Without it, an order can point at a user that never existed.
echo '\nCREATE TABLE orders (\n id BIGSERIAL PRIMARY KEY,\n user_id BIGINT NOT NULL,\n status TEXT NOT NULL,\n amount NUMERIC NOT NULL,\n created_at TIMESTAMPTZ NOT NULL DEFAULT now(),\n FOREIGN KEY (user_id) REFERENCES users(id)\n);' >> ~/backend/db/schema.sql - 04
Verify the schema
cat prints the schema. Every rule you enforce here is a bug you will not debug.
cat ~/backend/db/schema.sql - 05
Submit for validation
The validator checks the keys, the nullability and the foreign key.
03Stuck?
0 of 3 revealedTake a hint without losing the lab — each one points at the thing to look at, not at the answer.
04AI Tutor
guides, never solvesTell me what you're stuck on — I'll nudge, not solve. Which requirement are you looking at?
04Notebook
saved locally in this browserlearner@lab — ubuntu:24.04
Session is disposable
↑/↓ recalls commands · Ctrl+Enter submits · type help for the command list