Databases — Here's What Actually Clicked

I've been a frontend developer for a while. React, Supabase, Cloudinary — I've used them. But I never actually understood what was happening inside the database. I was just clicking buttons in the Supabase dashboard hoping for the best.
Today that changed. I sat down and wrote raw SQL for the first time. No ORM, no abstraction. Just me and Postgres.
Here's what I learned.

Tables are not spreadsheets

Well, they look like spreadsheets. But the difference is Postgres enforces rules on your data that Excel never would.
When you create a table, every column has a type. And Postgres holds you to it strictly.

sql
CREATE TABLE clients (
  id         UUID      PRIMARY KEY DEFAULT gen_random_uuid(),
  name       TEXT      NOT NULL,
  email      TEXT      UNIQUE NOT NULL,
  phone      TEXT,
  brand_info JSONB,
  created_at TIMESTAMP DEFAULT now()
);

NOT NULL means the field is required. UNIQUE means no two rows can share that value. DEFAULT means Postgres fills it in automatically if you don't provide it.

These constraints catch bad data before it ever touches your application. That's the point.

Relationships are just foreign keys

I was confused about this before. How do you connect two tables?
You add a column to one table that points to the primary key of another. That's it. That column is called a foreign key.

sql
CREATE TABLE projects (
  id         UUID      PRIMARY KEY DEFAULT gen_random_uuid(),
  name       TEXT      NOT NULL,
  status     TEXT,
  stage      TEXT,
  budget     DECIMAL,
  client_id  UUID      REFERENCES clients(id),
  created_at TIMESTAMP DEFAULT now()
);

client_id REFERENCES clients(id) tells Postgres: whatever value goes in this column must exist in the clients table. If it doesn't, the insert gets rejected. The database enforces the relationship, not your application code.

Order matters on inserts

This one bit me. I tried to insert a project before inserting the client it belonged to. Postgres rejected it immediately — the client_id I was referencing didn't exist yet.
The rule: always insert the parent record first.

sql-- First, the client

INSERT INTO clients (id, name, phone, email) 
VALUES (gen_random_uuid(), 'Jeffrey', '09012345678', '[e-mail]');

-- Then, the project — using the client's UUID

INSERT INTO projects (id, name, status, stage, budget, client_id) 
VALUES (gen_random_uuid(), 'Velto', 'ongoing', 'development', 500.00, 'your-client-uuid-here');

JOIN pulls data across tables

The whole point of relationships is being able to query related data together. That's what JOIN does.

sql
SELECT 
  clients.name AS client_name,
  clients.email,
  projects.name AS project_name,
  projects.status
FROM projects
JOIN clients ON projects.client_id = clients.id;

Notice the AS aliases — both tables had a name column. Without aliases, Postgres would just show one of them. Aliasing gives each column a unique label in the result.

What I'm building toward

This is Phase 3 of my 16-week Express.js roadmap. The end goal is a Velto Client Portal — client login, project tracking, file uploads, invoices, and Paystack integration.

Understanding raw SQL first, before Prisma abstracts it away, is what makes the difference between a developer who debugs confidently and one who guesses and prays.

Next up: Prisma ORM — same concepts, cleaner syntax, and proper migrations.