Continuation — Tables, CRUD, and Foreign Keys

#webdev #javascript #programming #database

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', 'contact@apexrealty.com', 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.