Insert a ticket
INSERT INTO tickets (title, description, status, assignee_id)
VALUES ($1, $2, 'open', $3)
RETURNING id, created_at;$1/$2/$3 are parameter placeholders — the driver sends values separately from the SQL text.
Back to week 2
Topic
SQL CRUD
Definition
CRUD refers to the four row-level SQL statements — INSERT, SELECT, UPDATE, and DELETE — that create, read, update, and delete rows in a table.
In simpler words
INSERT adds a row, SELECT reads rows, UPDATE changes existing rows, and DELETE removes rows — everything an API endpoint eventually does to the database.
Before TypeORM writes any of this for you, know what the actual SQL looks like — it is what a Repository call or QueryBuilder produces under the hood.
After this you can
- Write a parameterized INSERT and SELECT against the tickets table
- Filter, sort, and paginate a SELECT with WHERE / ORDER BY / LIMIT / OFFSET
- Explain why raw string-concatenated SQL is dangerous
INSERT and SELECT
Definition
INSERT adds one or more new rows to a table with explicit column values, and SELECT retrieves rows that match a query, optionally filtered and ordered.
In simpler words
These are the “create” and “read” of CRUD — every ticket that exists got there through an INSERT, and every list view is a SELECT.
INSERT lists the columns you are providing; omitted columns fall back to their DEFAULT or must be nullable.
SELECT with a WHERE clause returns only matching rows; without one, it returns every row in the table.
Select open tickets for a user
SELECT id, title, status
FROM tickets
WHERE assignee_id = $1 AND status = 'open'
ORDER BY created_at DESC;Filter first, then sort — this is exactly what a ticket list endpoint needs.
UPDATE and DELETE
Definition
UPDATE modifies the column values of rows matching a condition, and DELETE removes rows matching a condition; both affect zero rows if the condition matches nothing.
In simpler words
These are the “update” and “delete” of CRUD — always paired with a WHERE clause, or every row in the table is affected.
UPDATE ... SET column = value WHERE id = $1 changes exactly one row when id is a primary key.
A DELETE without a WHERE clause removes every row in the table — this is the single most common catastrophic SQL mistake.
Update ticket status
UPDATE tickets
SET status = 'done', updated_at = now()
WHERE id = $1;Bump updated_at manually in raw SQL — TypeORM’s @UpdateDateColumn does this for you.
Delete one ticket
DELETE FROM tickets WHERE id = $1;Always confirm the WHERE clause before running a DELETE against a real database.
Parameters beat string concatenation
Definition
A parameterized query sends SQL text and values separately, so Postgres treats the values purely as data and never as SQL syntax.
In simpler words
Building SQL by concatenating a string with a variable lets user input change the meaning of the query — that is SQL injection.
Never build SQL by inserting a request value directly into a string; always use placeholders ($1, $2, …) with a values array.
This is exactly why class-validator DTOs and TypeORM parameter binding both matter — untrusted input should never become SQL syntax.
Injection risk vs safe query
-- Wrong: string concatenation
const sql = "SELECT * FROM tickets WHERE title = '" + input + "'";
-- Right: parameterized
client.query('SELECT * FROM tickets WHERE title = $1', [input]);A crafted input like x'; DROP TABLE tickets; -- can rewrite the wrong query entirely.
Keep in mind
- Every UPDATE and DELETE needs a WHERE clause you have double-checked.
- RETURNING lets INSERT/UPDATE hand back the row without a second SELECT.
- Repository.save/find and QueryBuilder both compile down to exactly these four statements.
Test
Check your understanding
At least 10 questions — mix of concept, syntax, practical, and logic. Score ≥ 80% (enforced by the API) to save progress.
Checking your session…
10 questions · concept 3 · syntax 2 · practical 3 · logic 2