🟢The Backend

Lesson 70 of 117

30 min

SQL Basics

SELECT, INSERT, UPDATE, DELETE. WHERE, ORDER BY, LIMIT.

SQL is the oldest skill that has somehow refused to become legacy. It was invented in 1974, and in 2026 every serious backend still talks to a database that speaks it. ORMs let you avoid SQL until they don't: the moment you need a join across three tables with a window function, you'll need to actually understand the language. Let's start from zero with Postgres.

The vocabulary

Database - a single named container of data.
Schema - a namespace inside a database (often just public by default).
Table - a typed grid: columns define the shape, rows are the data.
Column - a named cell with a type (int, text, timestamptz, jsonb...).
Row - one record. Sometimes called a tuple.
Primary key - the column(s) that uniquely identify a row. Postgres builds an index on it automatically.

Setting up a tiny schema

We'll work with two tables: users and orders. Every order belongs to a user via a foreign key.

schema.sql

CREATE TABLE users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  amount_cents INTEGER NOT NULL CHECK (amount_cents >= 0),
  status      TEXT NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Types that matter

UUID for IDs (no collisions across services). TEXT not VARCHAR(255) in Postgres. TIMESTAMPTZ always (timezone-aware), never plain TIMESTAMP. Money as INTEGER cents to avoid float weirdness.

INSERT: getting data in

sql

-- Single row
INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada Lovelace');

-- Multiple rows
INSERT INTO users (email, name) VALUES
  ('grace@example.com', 'Grace Hopper'),
  ('alan@example.com',  'Alan Turing');

-- RETURNING: get back what was inserted (super useful)
INSERT INTO users (email, name)
VALUES ('linus@example.com', 'Linus Torvalds')
RETURNING id, created_at;
-- id                                    | created_at
-- 7a2c... -bd34-...                     | 2026-05-24 10:42:11.123+00

RETURNING is Postgres-only magic

Most ORMs use RETURNING under the hood to skip a second round-trip after an insert. MySQL never had it; Postgres has had it forever and you should lean on it.

SELECT: getting data out

sql

-- All columns, all rows
SELECT * FROM users;

-- Specific columns
SELECT id, email FROM users;

-- With a condition
SELECT id, email
FROM users
WHERE email = 'ada@example.com';

-- Multiple conditions
SELECT *
FROM orders
WHERE status = 'pending'
  AND amount_cents > 5000;

Operators in WHERE

sql

WHERE email = 'a@b.com'                   -- equals
WHERE amount_cents <> 0                   -- not equals (or !=)
WHERE created_at > now() - INTERVAL '7 days'
WHERE status IN ('pending', 'paid')
WHERE name LIKE 'Ada%'                    -- starts with
WHERE name ILIKE 'ada%'                   -- case-insensitive
WHERE email IS NULL                       -- NEVER use = NULL
WHERE email IS NOT NULL

NULL is weird

x = NULL is never true, even when x is NULL. Use IS NULL and IS NOT NULL. The classic mistake is filtering with WHERE x <> 'foo'and being surprised that NULL rows didn't come back.

ORDER BY and LIMIT

sql

-- Sort newest first
SELECT * FROM orders
ORDER BY created_at DESC;

-- Page 1 of 20 results
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20;

-- Page 2 (skip the first 20)
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 20;

-- Multi-column sort: status then newest
SELECT * FROM orders
ORDER BY status ASC, created_at DESC;

Always pair LIMIT with ORDER BY

Without an ORDER BY, the database is free to return rows in any order, and "first 20" becomes random. Deterministic pagination means you sort first, then slice.

UPDATE: changing rows

sql

-- Change a single row
UPDATE users
SET name = 'Ada King'
WHERE id = '7a2c...';

-- Change multiple columns at once
UPDATE orders
SET status = 'paid',
    amount_cents = amount_cents + 100
WHERE id = '...';

-- See what changed (Postgres only)
UPDATE users
SET name = 'Updated'
WHERE id = '7a2c...'
RETURNING id, name, created_at;

The most expensive mistake in SQL

UPDATE users SET name = '...' with no WHERE updates every row in the table. The DB will happily do it. Always double-check your WHERE clause before pressing enter. BEGIN; ...; ROLLBACK; is your friend for testing.

DELETE: removing rows

sql

-- Delete one row
DELETE FROM users WHERE id = '7a2c...';

-- Delete many
DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < now() - INTERVAL '90 days';

-- See what was deleted
DELETE FROM orders
WHERE status = 'cancelled'
RETURNING id, amount_cents;

Because orders.user_id has ON DELETE CASCADE, deleting a user automatically deletes all their orders. Without CASCADE, Postgres errors and protects you from orphans.

A realistic end-to-end query

Insert a user, then place an order for them, both in one query using a CTE (Common Table Expression - the WITH keyword):

sql

WITH new_user AS (
  INSERT INTO users (email, name)
  VALUES ('hopper@example.com', 'Grace')
  RETURNING id
)
INSERT INTO orders (user_id, amount_cents, status)
SELECT id, 4999, 'pending' FROM new_user
RETURNING id, user_id, amount_cents;

That's atomic - either both inserts succeed or both fail. You just wrote your first transactional flow without typing the word BEGIN.

Connecting from Node

The standard Postgres driver in Node is pg. For Postgres on Vercel or other serverless setups, @neondatabase/serverless works over HTTP and survives the lack of TCP sockets. For now, here's plain pg:

db.ts

import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

// Parameterized queries - NEVER concat user input into SQL
const { rows } = await pool.query(
  "SELECT id, email FROM users WHERE email = $1",
  ["ada@example.com"]
);

console.log(rows[0]);
// { id: '7a2c-...', email: 'ada@example.com' }

SQL injection: the original sin

Never use string concatenation to build SQL. "WHERE email = '" + req.body.email + "'" lets a malicious user run '; DROP TABLE users; -- as part of your query. Use placeholders ($1, $2...) and let the driver escape values.

Quiz

Quiz1 / 4

How do you check for NULL in Postgres?

Recap

Tables have columns (typed) and rows (data). A primary key uniquely identifies each row.
INSERT / SELECT / UPDATE / DELETE are the four big verbs.
RETURNING hands you back the affected rows - Postgres-only and amazing.
Use IS NULL, never = NULL. Always pair LIMIT with ORDER BY.
Always parameterize queries ($1, $2). String concat = SQL injection.