Continuation — Tables, CRUD, and Foreign Keys

I've been building with React and Supabase for a while now.

I'm currently in Phase 3 of my 16-week Express.js roadmap — the Database + Auth phase — and PostgreSQL was the first thing on the list. Here's what I learned.

Creating a table

Before you write a single query, you need to define the shape of your data. In PostgreSQL that looks like this:

sql

CREATE TABLE clients (

  id SERIAL PRIMARY KEY,

  name VARCHAR(100) NOT NULL,

  email VARCHAR(150) UNIQUE NOT NULL,

  budget NUMERIC(12, 2),

  status VARCHAR(20) DEFAULT 'lead',

  created_at TIMESTAMP DEFAULT NOW()

);

A few things worth noting:

SERIAL is PostgreSQL handling your ID auto-increment for you. You never insert it manually.

DEFAULT means if you don't supply a value, PostgreSQL fills it in. status defaults to 'lead', created_at defaults to the current timestamp.

NOT NULL makes a field required. UNIQUE means no two rows can share that value — useful for emails.

CRUD in plain SQL

Once your table exists, the four operations you'll use constantly are INSERT, SELECT, UPDATE, and DELETE.

sql-- Create

INSERT INTO clients (name, email, budget)

VALUES ('Apex Realty', '[email protected]', 750000.00);



-- Read

SELECT * FROM clients WHERE status = 'active' ORDER BY budget DESC;



-- Update

UPDATE clients

SET status = 'active'

WHERE id = 1;



-- Delete

DELETE FROM clients WHERE id = 1;

The one rule that will save you from a painful mistake: always use WHERE on UPDATE and DELETE. Without it, you're modifying or wiping every single row in the table.

Relating tables with foreign keys

A real app has multiple tables that reference each other. A project belongs to a client. An invoice belongs to a project. That relationship is enforced with a foreign key.

sql

CREATE TABLE projects (

  id SERIAL PRIMARY KEY,

  client_id INT REFERENCES clients(id),

  title VARCHAR(150) NOT NULL,

  price NUMERIC(12, 2),

  created_at TIMESTAMP DEFAULT NOW()

);

client_id INT REFERENCES clients(id) tells PostgreSQL that every value in that column must exist as an ID in the clients table. You can't insert a project with a client_id that doesn't exist. The database enforces the integrity for you.

When inserting, you supply the ID manually:

sql

INSERT INTO projects (client_id, title, price)

VALUES (1, 'Apex Realty Website Redesign', 750000.00);

PostgreSQL doesn't guess which client a project belongs to. You always pass it explicitly.

What's next

Prisma, which gives you all of this through a JavaScript API without writing raw SQL by hand.

Building in public. Week 3 of 16.