Backend developers don't just query data, they own the queries running behind every request their service handles — under real concurrency, real user input, and real load. This path is sequenced around that reality: start with CRUD done safely, then build up to the JOIN, transaction, indexing, and migration decisions that separate an endpoint that works in a demo from one that holds up in production.

1

CRUD Fundamentals for APIs
25 min

Almost every endpoint you'll write maps to SELECT, INSERT, UPDATE, or DELETE underneath. Pulling only the columns a response actually needs — never a reflexive SELECT * — keeps payloads small and avoids accidentally leaking a column your API contract was never meant to expose.
```
SELECT id, name, email
FROM users
WHERE id = ?;
```
Where your database supports it, a native UPSERT / ON CONFLICT is safer than a "SELECT then decide" pattern in application code, which can race under concurrent requests.

Lesson: SELECT Statements Lesson: WHERE Clause Blog: UPSERT explained
2

Parameterized Queries & SQL Injection Prevention
30 min

This is the single most important habit in this whole path. String-concatenating user input directly into a query lets a malicious input change the query's logic entirely. Prepared statements with bound parameters keep user input as data, never executable SQL — no exceptions, no "just this once."
```
-- Never do this:
-- "SELECT * FROM users WHERE email = '" + input + "'"

-- Do this instead (parameterized):
SELECT * FROM users WHERE email = ?;
```
An ORM building queries from bound parameters is safer by default — but a raw or interpolated fragment passed through it can still reopen the exact same injection risk.

Blog: SQL injection prevention best practices
3

JOINs for Relational Data Modeling
35 min

A "user has many orders" API almost always means a foreign-key relationship and a JOIN underneath. INNER JOIN gives you only matched rows; LEFT JOIN is what you reach for when an endpoint needs to include records with no related rows yet — new users with zero orders, products with zero reviews.
```
SELECT u.id, u.name, o.id AS order_id, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
```
If an endpoint is silently missing records it should include, an INNER JOIN where you needed a LEFT JOIN is the most common culprit.

Lesson: JOINs Blog: INNER vs LEFT vs RIGHT JOIN Interview prep: JOIN questions
4

Transactions & ACID in Application Code
30 min

Any endpoint that writes to more than one table — placing an order, transferring a balance — needs those writes wrapped in a transaction. If the second write fails, the transaction rolls back the first one too, instead of leaving your data half-updated for the next request to trip over.
```
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
```
Never hold a transaction open across a slow external API call — it can hold locks and a connection far longer than necessary, blocking other requests behind it.

Lesson: Transactions Blog: Transactions & ACID explained
5

Indexes & API Response-Time Performance
30 min

A missing index on a column your endpoint filters by is one of the most common causes of an API that was fast in testing and slow in production, once the table has real volume. Index the columns your actual WHERE, JOIN, and ORDER BY clauses use most.
```
CREATE INDEX idx_orders_user_id ON orders (user_id);
```
Every index also has to be updated on every write to that table — index deliberately for your real query patterns, not every column "just in case."

Lesson: Indexes & Performance Blog: Index scan vs table scan Blog: Why SQL queries are slow
6

Avoiding the N+1 Query Problem
30 min

Fetch 50 users, then lazily load each one's orders inside a loop, and you've just issued 51 queries for what should have been one or two. This is the N+1 problem — easy to introduce with an ORM's lazy-loaded relationships, and one of the highest-leverage performance bugs to learn to spot.

The fix is almost always the same shape: eager-load the related data with a single JOIN or a batched query, instead of one query per parent row.

Blog: How SQL optimizers actually work Blog: SQL performance anti-patterns
7

Pagination, Sorting & Filtering at Scale
30 min

LIMIT ... OFFSET pagination gets slower the deeper a user pages, because the database still has to scan and discard every skipped row. Keyset (cursor) pagination — filtering WHERE id > last_seen_id — stays fast no matter how deep a user pages.
```
-- Keyset pagination: fast at any depth
SELECT id, name FROM products
WHERE id > ?
ORDER BY id
LIMIT 20;
```
A list endpoint needs a stable, explicit ORDER BY — without one, pagination pages can show duplicate or skipped rows as the underlying data changes between requests.

Blog: Keyset vs offset pagination Blog: LIMIT vs TOP vs FETCH FIRST vs ROWNUM
8

Schema Migrations & Safe Schema Changes
25 min

Adding a NOT NULL column with no default to a large, live table can lock it and take your API down mid-deploy. Backend developers learn to sequence schema changes safely — add nullable first, backfill in batches, tighten the constraint once the backfill is done.

A tracked, versioned migration tool exists to apply schema changes consistently and in order across every environment — never hand-run an untracked ALTER TABLE straight against production.

Lesson: DDL Commands Blog: Auto-increment vs serial vs identity Blog: DELETE vs TRUNCATE vs DROP