SQL and relational databases
Tables, keys, CRUD, JOIN, relationships and parameterized queries.
Updated
What a relational database is
A relational database (PostgreSQL, MySQL, SQLite) keeps data in tables: each table has columns with fixed types, and each row is one item. The tables are linked to each other through keys — hence "relational".
users posts
id | name | email id | user_id | title
---+-------+------------ ---+---------+---------
1 | Ana | ana@x.com 1 | 1 | Hello
2 | John | john@x.com 2 | 1 | Next 16
SQL is the language you use to query and change this data. Even if you use an ORM, SQL is what actually runs — it's worth reading it fluently. This app uses PostgreSQL on Neon.
The keys
| Key | Role |
|---|---|
| primary key | uniquely identifies a row (id) |
| foreign key | a column that points to another table's key (posts.user_id → users.id) |
| unique | a value that can't repeat (email) |
| index | a structure that makes lookups on a column fast |
The 4 operations (CRUD)
SELECT id, title FROM posts WHERE user_id = 1 ORDER BY created_at DESC LIMIT 10;
INSERT INTO posts (user_id, title) VALUES (1, 'Hello') RETURNING id;
UPDATE posts SET title = 'Hello!' WHERE id = 1;
DELETE FROM posts WHERE id = 1;UPDATE and DELETE without a WHERE affect every row. It's the first thing you check.
SELECT piece by piece
| Clause | Role | The JS equivalent |
|---|---|---|
SELECT col1, col2 |
which columns | map |
FROM table |
from which table | the array |
WHERE cond |
which rows | filter |
ORDER BY col DESC |
the order | toSorted |
LIMIT n OFFSET m |
how many, from where | slice |
GROUP BY col + COUNT(*) |
grouping and aggregation | Object.groupBy + length |
JOIN — combining tables
SELECT posts.title, users.name
FROM posts
JOIN users ON users.id = posts.user_id;| Kind | Keeps |
|---|---|
JOIN (inner) |
only the rows that have a match in both tables |
LEFT JOIN |
every row from the left; the right = NULL if there's no match |
Relationships — the kinds
| Relationship | Example | How it's modeled |
|---|---|---|
| one-to-many | one user → many posts | posts.user_id |
| many-to-many | posts ↔ tags | a join table post_tags (post_id, tag_id) |
| one-to-one | user → profile | profiles.user_id unique |
SQL injection — the golden rule
Never paste user input directly into the SQL text:
sql(`SELECT * FROM users WHERE email = '${email}'`) // ✗ email = "' OR '1'='1" → every user
sql`SELECT * FROM users WHERE email = ${email}` // ✓ a parameter — sent separately from the queryThe sql`...` template from @neondatabase/serverless (used in this app), ORMs and query builders send values as parameters ($1, $2), never as text.
Summary
- Tables with typed columns, linked through primary / foreign keys.
SELECT … WHERE … ORDER BY … LIMIT,INSERT,UPDATE … WHERE,DELETE … WHERE;JOINfor relationships.- Always parameterized queries — never input pasted into the text.