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', '[email protected]');
-- 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.