21 Database Concepts Every Backend Engineer Must Know

A practical reference guide covering the core database concepts that show up in interviews and in production — with definitions, real-world use cases, diagrams, and working code in Raw SQL, Prisma (Node.js/TypeScript), and GORM (Go).

Table of Contents

1. Tables & Schema
2. Relationships (1:1, 1:N, N:N) — and Primary/Foreign Keys
3. Joins
4. Normalization
5. Denormalization
6. Constraints
7. Indexes
8. Composite Indexes — Order Matters
9. Transactions
10. ACID
11. Locking: Pessimistic vs Optimistic
12. The N+1 Query Problem
13. Query Optimization
14. Connection Pooling
15. Caching
16. Idempotency Keys
17. Migrations
18. Replication
19. Sharding
20. CAP Theorem
21. Security at the Data Layer

1. Tables & Schema

Definition

A table is a structured collection of rows (records) and columns (fields), each column having a defined data type. The schema is the blueprint that defines what tables exist, what columns they have, and what constraints apply.

Real-World Use Case

An e-commerce platform needs a users table to store customer accounts and an orders table to store purchases. Every backend system — from a to-do app to a banking platform — starts here.

Diagram

Code

CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(120) NOT NULL,
    email      VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);
 
CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    user_id    INTEGER NOT NULL REFERENCES users(id),
    total      NUMERIC(10, 2) NOT NULL,
    status     VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

2. Relationships (1:1, 1:N, N:N) — and Primary/Foreign Keys

Definition

Relationships describe how rows in one table relate to rows in another:

• One-to-One (1:1) — one row in Table A matches exactly one row in Table B (e.g., a user and their profile).
• One-to-Many (1:N) — one row in Table A matches many rows in Table B (e.g., a user and their orders).
• Many-to-Many (N:N) — many rows in Table A relate to many rows in Table B, usually via a join table (e.g., students and courses).

Relationships only work because of two building blocks:

Primary Key (PK) — a column (or set of columns) that uniquely identifies every row in a table. It can never be NULL, and it never changes for the lifetime of that row. Picture a classroom with 500 students all named John — the primary key is what tells them apart, the same way a fingerprint or passport number identifies a person even if their name is common. Every table should have exactly one primary key.

Foreign Key (FK) — a column in one table that stores the primary key value of a row in another table, creating a reference between them. Instead of copying a customer's name, phone number, and address into every single order, an orders table just stores customer_id — a foreign key pointing back to the customers table. The database uses this reference to know exactly who placed the order, without duplicating that customer's data anywhere. Foreign keys are also what a JOIN uses to stitch tables back together (see Joins below), and the database can enforce them, refusing to insert an order for a customer_id that doesn't exist.

Real-World Use Case

A social media app: a User has one Profile (1:1), a User has many Posts (1:N), and Posts have many Tags while Tags belong to many Posts (N:N). In every case, the "many" side holds a foreign key pointing back to the primary key of the "one" side (and N:N relationships need a join table holding two foreign keys).

Diagram

Code

-- 1:1
CREATE TABLE profiles (
    id      SERIAL PRIMARY KEY,       -- PK for this table
    user_id INTEGER UNIQUE NOT NULL   -- FK referencing users.id
            REFERENCES users(id),
    bio     TEXT
);
 
-- 1:N
CREATE TABLE posts (
    id      SERIAL PRIMARY KEY,       -- PK
    user_id INTEGER NOT NULL          -- FK referencing users.id
            REFERENCES users(id),
    title   VARCHAR(200) NOT NULL
);
 
-- N:N via join table (composite PK made of two FKs)
CREATE TABLE tags (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);
 
CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id), -- FK
    tag_id  INTEGER NOT NULL REFERENCES tags(id),   -- FK
    PRIMARY KEY (post_id, tag_id)                   -- composite PK
);

3. Joins

Definition

A join combines rows from two or more tables based on a related column, typically a primary key / foreign key pair. Common types:

• INNER JOIN — only rows with a match in both tables.
• LEFT JOIN — all rows from the left table, matched rows from the right (or NULL).
• RIGHT JOIN — all rows from the right table, matched rows from the left (or NULL).

Real-World Use Cases (one per join type)

INNER JOIN — "Only show what matches on both sides." An online store wants a list of products that have actually been ordered at least once, for a "best-sellers" report. A product with zero orders should not appear at all, and an order row can't exist without a real product — so INNER JOIN is exactly right, since it drops anything that has no match on the other side.

LEFT JOIN — "Keep everything on the left, even without a match." A SaaS admin dashboard needs "all registered users and their subscription plan, if they have one." Free-tier users who never subscribed still need to show up in the list (with NULL in the subscription columns) — a LEFT JOIN from users to subscriptions guarantees every user appears, subscribed or not.

RIGHT JOIN — "Keep everything on the right, even without a match." A warehouse system tracks shipments and wants to see every shipment record together with the driver assigned to it — including shipments that were auto-generated by a system but haven't been assigned a driver yet. Running SELECT * FROM drivers RIGHT JOIN shipments ON drivers.id = shipments.driver_id keeps every shipment even when driver_id is NULL. (In practice, most engineers just flip the table order and use LEFT JOIN instead, since it reads more naturally — RIGHT JOIN is rarely used but useful to recognize.)

Diagram

Code

-- INNER JOIN: best-selling products (must have at least one order)
SELECT p.name, COUNT(o.id) AS order_count
FROM products p
INNER JOIN orders o ON o.product_id = p.id
GROUP BY p.name;
 
-- LEFT JOIN: every user, with subscription info if it exists
SELECT u.name, s.plan
FROM users u
LEFT JOIN subscriptions s ON s.user_id = u.id;
 
-- RIGHT JOIN: every shipment, with driver info if assigned
SELECT s.id AS shipment_id, d.name AS driver_name
FROM drivers d
RIGHT JOIN shipments s ON s.driver_id = d.id;

4. Normalization

Definition

Normalization is the process of organizing tables to reduce data redundancy and avoid update anomalies, by splitting data into related tables and enforcing rules called normal forms:

• 1NF — every column holds a single, atomic value; no repeating groups.
• 2NF — every non-key column depends on the whole primary key (matters for composite keys).
• 3NF — no column depends on another non-key column (no "transitive" dependencies) — every column depends only on the key.

Real-World Use Case

An orders table that stores the customer's email on every row duplicates that email across thousands of rows. If the customer updates their email, you'd need to update every row — normalization fixes this by moving the email into a customers table referenced by customer_id.

Diagram

Code

-- Before: denormalized
-- orders(id, product, customer_email)
 
-- After: normalized into two tables
CREATE TABLE customers (
    id    SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
);
 
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    product     VARCHAR(200) NOT NULL,
    customer_id INTEGER NOT NULL REFERENCES customers(id)
);

5. Denormalization

Definition

Denormalization is the deliberate, controlled opposite of normalization: intentionally duplicating some data across tables to avoid expensive joins and speed up reads. It's a trade-off — you gain read performance but take on the responsibility of keeping the duplicated copies in sync.

Real-World Use Case

A social media feed needs to render a post along with the author's name and avatar for every single item, thousands of times a second. Joining posts to users on every single feed render for millions of requests can get expensive. Many high-traffic systems instead store a denormalized snapshot — author_name and author_avatar_url directly on the posts row — accepting that if a user changes their display name, older posts may briefly show the old name until a background job updates them.

Diagram

Code

-- Denormalized: author info duplicated onto the posts table
ALTER TABLE posts ADD COLUMN author_name VARCHAR(120);
ALTER TABLE posts ADD COLUMN author_avatar_url VARCHAR(255);
 
-- Reads no longer need a JOIN
SELECT id, author_name, author_avatar_url FROM posts WHERE id = 42;
 
-- Trade-off: writes must keep the copy in sync
UPDATE posts SET author_name = 'New Name' WHERE user_id = 7;

6. Constraints

Definition

Constraints are rules the database itself enforces on the data, rejecting anything that violates them — so bad data never gets stored in the first place, even if the application layer has a bug. Common constraints:

• NOT NULL — the column can never be empty.
• UNIQUE — no two rows can have the same value in this column.
• CHECK — the value must satisfy a condition (e.g., price > 0).
• FOREIGN KEY — the value must match an existing row in another table.

Real-World Use Case

A checkout API has a bug that occasionally sends a negative quantity when a race condition happens. Without a CHECK constraint, that bad row would just get written silently, and someone would discover it weeks later during a finance audit. A CHECK (quantity > 0) constraint makes the database reject the insert immediately, surfacing the bug right away instead of corrupting data quietly.

Diagram

Code

CREATE TABLE order_items (
    id          SERIAL PRIMARY KEY,
    order_id    INTEGER NOT NULL REFERENCES orders(id), -- FOREIGN KEY
    sku         VARCHAR(50) NOT NULL UNIQUE,             -- UNIQUE + NOT NULL
    quantity    INTEGER NOT NULL CHECK (quantity > 0),   -- CHECK
    unit_price  NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0)
);

7. Indexes

Definition

An index is a separate, sorted data structure the database keeps alongside a table so it can find rows without scanning every single one — the same way a book's alphabetical index lets you jump straight to a topic instead of reading every page.

A few terms that come up constantly when discussing indexes:

• B-tree (short for "balanced tree") is the data structure most databases use for a standard index. It keeps values sorted in a tree shape where each lookup eliminates roughly half the remaining rows, similar to how you'd search a phone book by repeatedly splitting it in half rather than reading it front to back.
• Big-O notation (O(...)) is a shorthand for how the amount of work grows as the number of rows (n) grows — it describes the shape of the slowdown, not an exact time.
  • O(n) ("linear time") means work grows in direct proportion to the number of rows: double the rows, double the work. A full table scan (checking every row one by one) is O(n).
  • O(log n) ("logarithmic time") means work grows much slower than the number of rows — doubling the rows barely increases the work, because each step eliminates half of what's left. A B-tree index lookup is O(log n): searching 1,000,000 rows takes only around 20 comparison steps instead of up to 1,000,000.

Real-World Use Case

A users table with 5 million rows and a login endpoint that runs WHERE email = ? on every request. Without an index on email, every login triggers a full table scan (O(n) — checking millions of rows one by one). Add a B-tree index, and lookups drop to O(log n) — roughly 22 comparisons instead of 5 million — making logins near-instant.

Diagram

Code

CREATE INDEX idx_users_email ON users(email);
 
-- Confirm the database is actually using the index
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'x@mail.com';
-- Look for "Index Scan" (fast) instead of "Seq Scan" (slow, full table scan)

When to add an index (and when not to)

✅ Do index: primary keys (usually automatic), foreign keys, columns used often in WHERE, JOIN, or ORDER BY, and columns with many distinct values (high cardinality — e.g. email).

❌ Don't index: tiny tables (a few hundred rows, where a scan is already instant), columns updated very frequently (every index adds write overhead), columns with very few distinct values (like a boolean), or every column "just in case" — each extra index slows down every INSERT/UPDATE on that table.

8. Composite Indexes — Order Matters

Definition

A composite index covers more than one column, e.g. (status, created_at). The columns are stored in that exact order inside the B-tree, so the index only helps efficiently when your query's filter follows the same left-to-right order — it does not equally speed up every combination of those columns.

Real-World Use Case

A support-ticket system frequently runs WHERE status = 'open' ORDER BY created_at DESC to show the newest open tickets first. An index on (status, created_at) handles that query beautifully — it narrows straight to the 'open' tickets, already sorted by date. But a different query that filters only by created_at (with no status filter) can't make good use of that same index, because the index is sorted by status first; the database would still have to hunt across the whole structure. Put the most selective / most-frequently-filtered column first.

Diagram

Code

-- Column order matches the query pattern: filter by status first, then sort by date
CREATE INDEX idx_tickets_status_created ON tickets(status, created_at);
 
-- ✅ Uses the index well
SELECT * FROM tickets WHERE status = 'open' ORDER BY created_at DESC;
 
-- ❌ Can't use this index efficiently — created_at isn't the leftmost column
SELECT * FROM tickets WHERE created_at > '2026-01-01';

9. Transactions

Definition

A transaction groups multiple database writes so they succeed or fail together — "all or nothing." If anything inside the transaction fails, the database rolls back every change made so far, as if none of it ever happened.

Real-World Use Case

A bank transfer: debit account A by $100 and credit account B by $100. If the credit fails after the debit succeeds, money vanishes. Wrapping both statements in a transaction guarantees either both happen or neither does.

Diagram

Code

BEGIN;
 
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
 
COMMIT; -- or ROLLBACK if something fails

10. ACID

Definition

ACID is the set of four guarantees that a properly implemented transaction provides. It's a property of transactions, not a separate mechanism:

• Atomicity — all operations in the transaction happen, or none do.
• Consistency — the database only ever moves from one valid state to another, respecting all constraints and rules.
• Isolation — concurrent transactions don't see each other's uncommitted changes.
• Durability — once a transaction is committed, the change survives even a crash right after (it's been written to durable storage).

Real-World Use Case

Two customers try to book the last seat on a flight at the same second. Isolation ensures one transaction's in-progress booking doesn't leak into the other's view before it commits. Consistency ensures the seat-count constraint (never negative) always holds. Atomicity ensures a half-finished booking (payment charged, seat not reserved) can never persist. Durability ensures that once the booking is confirmed, a server crash a millisecond later doesn't un-book it.

Diagram

Code

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Isolation, tunable
 
UPDATE seats SET booked = true WHERE id = 42 AND booked = false; -- Consistency: constraint enforced
-- If 0 rows affected, the seat was already taken — application must ROLLBACK (Atomicity)
 
COMMIT; -- Durability: guaranteed once this returns successfully

11. Locking: Pessimistic vs Optimistic

Definition

Locking controls concurrent access to the same row:

• Pessimistic locking — lock the row up front (SELECT ... FOR UPDATE); any other transaction trying to touch it must wait.
• Optimistic locking — don't lock; instead track a version column. On update, check the version hasn't changed; if it has, reject and retry.

Real-World Use Case

Pessimistic: booking the last seat on a flight — you want to lock that row so two people can't both book it simultaneously. Optimistic: editing a shared document field where conflicts are rare — locking every read would be wasteful, so you just detect conflicts on write.

Diagram

Code

-- Pessimistic locking
BEGIN;
SELECT * FROM seats WHERE id = 42 FOR UPDATE; -- locks the row
UPDATE seats SET booked = true WHERE id = 42;
COMMIT;
 
-- Optimistic locking
UPDATE documents
SET content = 'new text', version = version + 1
WHERE id = 7 AND version = 3; -- fails silently (0 rows) if version moved on

12. The N+1 Query Problem

Definition

The N+1 problem happens when you fetch N parent rows with one query, then run one additional query per row to fetch related data — turning what could be 1-2 queries into N+1 queries.

Real-World Use Case

Fetching a list of 100 blog posts and, for each post, separately querying its author — that's 1 query for posts + 100 queries for authors = 101 queries, instead of a single join or a single batched follow-up query.

Diagram

Code

-- ❌ N+1: 1 query for posts, then 1 per post for the author
SELECT * FROM posts;
-- then in a loop: SELECT * FROM users WHERE id = ?;
 
-- ✅ Fixed option 1: one query with a JOIN
SELECT p.*, u.name AS author_name
FROM posts p
JOIN users u ON u.id = p.user_id;
 
-- ✅ Fixed option 2: one batched follow-up query (better for very large result sets)
SELECT * FROM posts;
SELECT * FROM users WHERE id IN (1, 2, 3, /* ...all author ids from above */);

13. Query Optimization

Definition

Query optimization is the broader practice of making the database do only the work it actually needs to: fetching fewer columns, filtering in the database instead of in application code, checking existence without loading full rows, and batching writes instead of issuing them one at a time.

Real-World Use Case

An admin endpoint lists all 2 million users, but the frontend only needs id, name, and email. Running SELECT * FROM users pulls every column — including large ones like a stored profile bio or photo URL — across the network for no reason, and doing the filtering in application code after loading everything into memory makes it dramatically worse. Selecting only the needed columns and filtering with WHERE in the database itself keeps both the query and the network payload small.

Diagram

Code

-- ❌ Fetch everything, filter later
SELECT * FROM users;
 
-- ✅ Select only needed columns, filter and limit in the database
SELECT id, name, email FROM users WHERE active = true LIMIT 20;
 
-- ✅ Existence check without loading a full row
SELECT EXISTS(SELECT 1 FROM users WHERE email = 'x@mail.com');
 
-- ✅ Bulk insert instead of one-row-at-a-time inserts
INSERT INTO products (name, price) VALUES
  ('Product 1', 10.00),
  ('Product 2', 20.00),
  ('Product 3', 30.00);

14. Connection Pooling

Definition

A connection pool maintains a fixed set of already-open database connections that the application borrows and returns, instead of opening (and closing) a new connection for every request — which is expensive.

Real-World Use Case

A web API handling thousands of requests per second can't afford to open a fresh TCP + auth handshake to Postgres for every request. A pool of, say, 20 warm connections gets borrowed and returned, and the pool size also caps how many concurrent connections hit the database, protecting it from overload.

Diagram

Code

// node-postgres example
const { Pool } = require("pg");
const pool = new Pool({
  host: "localhost",
  max: 20, // max connections in the pool
  idleTimeoutMillis: 30000,
});
const { rows } = await pool.query("SELECT * FROM users WHERE id = $1", [1]);

15. Caching

Definition

Caching stores frequently accessed ("hot") results in fast memory (e.g., Redis) so subsequent reads skip the database entirely. On a cache hit, return immediately; on a miss, query the database, store the result in the cache, then return it.

Real-World Use Case

A product page viewed thousands of times a minute doesn't need to hit Postgres every time — cache the product data in Redis with a short TTL, and only the first request (or a periodic refresh) touches the database.

Diagram

Code

const cacheKey = `product:${id}`;
let product = await redis.get(cacheKey);
 
if (!product) {
  const { rows } = await pool.query("SELECT * FROM products WHERE id = $1", [
    id,
  ]);
  product = rows[0];
  await redis.set(cacheKey, JSON.stringify(product), "EX", 300); // 5 min TTL
} else {
  product = JSON.parse(product);
}

16. Idempotency Keys

Definition

An idempotency key is a unique identifier the client attaches to a request. If the same key arrives more than once (e.g., because of a retry after a timeout), the server recognizes it and returns the original result instead of processing the request again. This is what makes an operation "idempotent" — safe to repeat without changing the outcome beyond the first successful call.

Real-World Use Case

A user taps "Withdraw ₦50,000" on a slow connection. Nothing appears to happen, so they tap it two more times. Without protection, the backend could process all three taps and withdraw ₦150,000. If the client sends the same idempotency key on every retry, the server can detect the duplicate before touching the balance a second time, and simply returns the result of the original withdrawal.

Diagram

Code

CREATE TABLE idempotency_keys (
    key         VARCHAR(64) PRIMARY KEY,
    response    JSONB NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT now()
);
 
-- On each request: check first, then insert-and-process inside one transaction
BEGIN;
SELECT response FROM idempotency_keys WHERE key = 'abc123'; -- if found, return it and STOP
 
-- Not found: process the withdrawal, then record the key with its result
UPDATE accounts SET balance = balance - 50000 WHERE id = 1;
INSERT INTO idempotency_keys (key, response) VALUES ('abc123', '{"status":"success"}');
COMMIT;

17. Migrations

Definition

Migrations are versioned scripts that track how a database schema evolves over time (adding tables, columns, indexes, etc.), stored in source control alongside your code — instead of one giant, unversioned schema file. Changing application code is relatively cheap; changing a schema after millions of rows already exist is not, so migrations are how teams apply schema changes safely, in order, and reversibly.

Real-World Use Case

A team of five engineers is actively developing a product. Migrations let each engineer apply schema changes in the same order, roll back a bad change, and keep staging/production in sync with what's in the codebase.

Diagram

Code

-- 001_create_users.sql
CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(120));
 
-- 002_add_email.sql
ALTER TABLE users ADD COLUMN email VARCHAR(255) UNIQUE;
 
-- 003_create_orders.sql
CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id));

18. Replication

Definition

Replication keeps live copies of the database in sync across multiple servers. Writes go to the primary, which replicates changes to one or more replicas; reads can be spread across replicas to reduce load on the primary.

Real-World Use Case

A high-traffic news site has far more reads (article views) than writes (new articles). Sending all reads to replicas and writes to the primary lets the system scale read throughput horizontally without touching the primary's write capacity.

Diagram

Code

// Two connection pools: one to primary, one to a replica
const writePool = new Pool({ host: "primary.db.internal" });
const readPool = new Pool({ host: "replica.db.internal" });
 
await writePool.query(
  "INSERT INTO orders (user_id, total) VALUES ($1, $2)",
  [1, 99.99]
);
const { rows } = await readPool.query(
  "SELECT * FROM orders WHERE user_id = $1",
  [1]
);

19. Sharding

Definition

Sharding splits data across multiple independent databases when it no longer fits (or performs well) on one. A shard key determines which shard a given row lives on, often via a hashing or range-based router.

Real-World Use Case

A messaging app with billions of messages shards by user_id — all of a given user's messages live on the same shard (e.g., shard = hash(user_id) % N), spreading storage and query load across many smaller databases instead of one massive one. The trade-off: a query that needs data across many users (e.g. "find every message containing X across the whole app") now has to fan out across every shard instead of running once.

Diagram

Code

function getShardConnection(userId) {
  const shardId = userId % 3; // simple hash-based routing
  return shardPools[shardId];
}
 
const pool = getShardConnection(userId);
await pool.query("SELECT * FROM messages WHERE user_id = $1", [userId]);

20. CAP Theorem

Definition

The CAP theorem says that when a distributed database splits across multiple servers and part of the network fails to communicate (a partition), you can only fully guarantee two of these three things at once — and in practice, since partitions can always happen, the real choice is between C and A:

• Consistency — every server that responds shows the same, most up-to-date data.
• Availability — the system keeps responding to requests, even if some servers are unreachable.
• Partition Tolerance — the system keeps working despite servers losing communication with each other.

Real-World Use Case

A banking app runs across multiple data centers. If two servers briefly lose contact with each other, the system has to choose: keep answering balance requests immediately even if one server's copy might be slightly stale (favor Availability), or refuse to answer until every server agrees on the latest number (favor Consistency). For a bank balance, showing the wrong number is dangerous, so financial systems typically favor consistency. A social media "like count," on the other hand, can tolerate being off by a few for a moment, so those systems typically favor availability and let the count catch up shortly after.

Diagram

Code

-- Postgres: require at least one replica to confirm before COMMIT returns (favors Consistency)
ALTER SYSTEM SET synchronous_standby_names = 'replica_1';

21. Security at the Data Layer

Definition

Instead of relying entirely on application code to check permissions, modern databases can enforce access rules themselves. Row-Level Security (RLS) is the most common form: the database attaches a policy to a table so that a query only ever sees the rows it's allowed to see — no matter what the application code does or forgets to do.

Real-World Use Case

A multi-tenant SaaS product stores every customer's data in the same invoices table, distinguished by a tenant_id column. If a developer forgets a WHERE tenant_id = ? clause in one query somewhere in a large codebase, that bug could leak one customer's invoices to another. With Row-Level Security enabled on the invoices table, the database itself refuses to return rows outside the current tenant's context — even if the application query forgot the filter.

Diagram

Code

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON invoices
    USING (tenant_id = current_setting('app.current_tenant')::int);
 
-- Set per connection/session before running queries
SET app.current_tenant = '42';
 
-- This query only ever sees tenant 42's rows, even without a WHERE clause
SELECT * FROM invoices;

Quick Reference Table

Guide compiled for backend engineers preparing for interviews and production systems. Concepts apply across relational databases (PostgreSQL, MySQL) regardless of the ORM you use.