Writing SQL queries is one thing. Understanding why they work, how to structure the data they run against, and what happens when they go wrong in production is a different level of skill entirely.

This handbook picks up where the basics end. It assumes you can write a SELECT query, filter rows with WHERE, group data with GROUP BY, and insert or update records. What it covers is the layer underneath those skills: how to connect data across multiple tables, how to design schemas that hold up over time, how to make the database protect your data through transactions, how to write queries that stay fast as your data grows, and how to avoid the security vulnerability that has topped the OWASP list for nearly two decades.

I spent years working on large-scale storage systems at Meta and Microsoft. The problems in this handbook are not theoretical. Poorly designed schemas cause data integrity failures at any scale. Missing indexes bring down production APIs. SQL injection vulnerabilities appear in funded, modern codebases every day. The concepts here exist because the consequences of getting them wrong are real.

Each section introduces a concept through the problem it solves. By the end, you will have a clear picture of how SQL fits into a production backend, what a SQL developer actually works on day-to-day, and where to go next.

1. SQL joins: connecting tables

A relational database stores data in separate tables on purpose. Users live in a users table. Orders live in an orders table. Products live in a products table. Each table has a focused responsibility and references other tables through key values rather than duplicating their data.

This is the relational model working as intended. The trade-off is that answering a question like “what did each user order?” requires pulling data from more than one table. For example, we need to pull users from the users table, orders from the orders table, and products from the products table. That is what joins are for.

Why is data split across tables?

Consider what happens if you store user information directly in the orders table:

Grace Hopper’s email appears twice. If she updates her email address, you need to find and update every row that contains it. Miss one, and the data is inconsistent. The more orders she places, the worse the problem gets.

The solution is to store users in a users table, assign each one a unique id, and reference that id from the orders table:

Table: users

Table: orders

Now Grace’s email is stored once. A join reconnects these tables when you need data from both. Section 2 covers the design principles behind this kind of split in depth. For now, the key point is that splitting data is deliberate, and joins are how you bring it back together at query time.

INNER JOIN

An INNER JOIN returns only the rows where the join condition matches in both tables. Rows with no match on either side are excluded.

SELECT users.name, users.email, orders.product, orders.amount
FROM orders
INNER JOIN users ON orders.user_id = users.id;

The ON clause defines the condition that connects the two tables: user_id in orders must match id in users. The result includes only orders that have a matching user and only users who have at least one order.

The ON clause defines the condition that connects the two tables: user_id in orders must match id in users. The result includes only orders that have a matching user and only users who have at least one order.

Table aliases

Writing the full table name before every column gets verbose quickly. Table aliases shorten this:

SELECT u.name, u.email, o.product, o.amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

o and u are aliases for orders and users, respectively. They apply for the duration of the query and make column references easier to read, especially when joining more than two tables.

LEFT JOIN

A LEFT JOIN returns all rows from the left table and the matching rows from the right table. Where there is no match in the right table, those columns are returned as NULL.

SELECT u.name, o.product, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

If a user has no orders, they still appear in the result with NULL in the product and amount columns. 

This makes LEFT JOIN useful for finding rows in one table that have no corresponding rows in another:

-- Find users who have never placed an order
SELECT u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_id IS NULL;

This pattern, a LEFT JOIN filtered by IS NULL on the right table, is a standard way to find unmatched rows. It works consistently across PostgreSQL, MySQL, and SQL Server.

Watch out for accidental INNER JOINs: Adding a WHERE condition that filters on a non-NULL value from the right table turns a LEFT JOIN into an effective INNER JOIN. The filter eliminates exactly the unmatched rows LEFT JOIN was meant to preserve. Move such conditions into the ON clause instead.

RIGHT JOIN and FULL OUTER JOIN

A RIGHT JOIN is the mirror of LEFT JOIN: it returns all rows from the right table and matching rows from the left. In practice, most developers rewrite RIGHT JOIN as a LEFT JOIN by swapping the table order, which is easier to read.

A FULL OUTER JOIN returns all rows from both tables, with NULL filling in where there is no match on either side.

SELECT u.name, o.product
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

Dialect note: FULL OUTER JOIN is not supported in MySQL. Simulate it with a UNION of a LEFT JOIN and a RIGHT JOIN. PostgreSQL and SQL Server support it natively.

The Cartesian product mistake

The ON clause is not optional. Without it, the database returns every possible combination of rows from both tables, called a Cartesian product.

-- Missing ON clause: every user paired with every order
SELECT u.name, o.product
FROM users u
INNER JOIN orders o;

If users has 1,000 rows and orders has 10,000, this returns 10,000,000 rows. At scale, it can bring a database to a halt. If you intentionally need every combination of two tables, use CROSS JOIN, which makes the intent explicit.

Self joins

A self join connects a table to itself. A common example is an employees table where each row has a manager_id referencing another employee’s id:

SELECT e.name AS employee, m.name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;

Both e and m refer to the same employees table. The aliases distinguish the two instances in the query.

Joining more than two tables

Joins chains together. Each JOIN clause adds another table. The example below assumes orders has a product_id column referencing a products table:

-- products table: id, name, category
-- orders.product_id references products.id

SELECT u.name, o.product, p.category, o.amount
FROM orders o
INNER JOIN users u    ON o.user_id    = u.id
INNER JOIN products p ON o.product_id = p.id
WHERE u.name = 'Grace Hopper';

Logically, joins happen before the WHERE clause filters the results, although modern databases may optimize execution to avoid unnecessary work. This means columns from all joined tables are available to WHERE, GROUP BY, and HAVING without special handling.

2. Structuring your data: schema design and normalization

Joins exist because data is split across tables. That split is not arbitrary. It follows a set of design principles called normalization, which determine which data belongs in which table and how tables relate to each other. A well-normalized schema makes joins predictable, data consistent, and application bugs less likely. A poorly normalized schema makes all three worse.

This section starts with a broken schema and fixes it step by step.

Start with the problem

Suppose you are building an e-commerce application, and you create a single table to store everything:

Three things are wrong here, and each one causes a specific class of problem.

• Repeated data: Grace Hopper’s email and city appear in every row. If she moves or changes her email, every row needs updating. One missed row leaves the data inconsistent.
• Multiple values in one cell: product_names and product_prices store comma-separated lists. SQL has no reliable way to filter, sort, or aggregate individual values inside a cell. Querying “all orders that include a Keyboard” requires string parsing, which is slow and fragile.
• Mixed concerns: Customer data, order data, and product data are all in one table. Changes to one type of data ripple through rows that have nothing to do with that change.

These problems have names and solutions. The process of eliminating them is called normalization.

First normal form: one value per cell

A table is in First Normal Form (1NF) when every cell contains a single, indivisible value and every row is unique. Every row should also have a primary key.

The product_names and product_prices columns violate 1NF. Fix this by giving each product its own row:

Each cell now holds one value. However, order_id alone is no longer the primary key here; a composite key of order_id and product_name is now required to uniquely identify a row. Furthermore, the repeated customer data has gotten worse: Grace Hopper’s information appears three times.

Second normal form: remove partial dependencies

A table is in Second Normal Form (2NF) when it is in 1NF and every non-key column depends on the entire primary key, not just part of it.

In the table above, customer_name, customer_email, and city depend only on the customer, not on the specific order or product. They are partially dependent on the key. The fix is to move customer data into its own table:

Table: customers

Table: order_items

Customer data is now stored once. Updating Grace’s email requires changing one row in one table. The customer_id in order_items is what a join uses to reconnect the data at query time.

Third normal form: remove transitive dependencies

A table is in Third Normal Form (3NF) when it is in 2NF and every non-key column depends directly on the primary key, not on another non-key column.

In the customers table, city describes where the customer lives. If you later want to store more detail about cities, such as country or timezone, that data would depend on city, not on customer_id. That is a transitive dependency.

For a simple application, storing city directly on the customer is often the right call. As requirements grow, splitting it out becomes worthwhile:

Table: cities

Table: customers

Every column in customers now depends directly on customer_id. City details live in one place and can be extended without touching customer rows.

Foreign keys and referential integrity

When one table references another through an id column, a foreign key constraint tells the database to enforce that relationship. If order_items.customer_id is declared as a foreign key referencing customers.customer_id, the database rejects any attempt to insert an order for a customer that does not exist.

CREATE TABLE order_items (
  order_id      INT,
  customer_id   INT REFERENCES customers(customer_id),
  product_name  VARCHAR(200) NOT NULL,
  product_price NUMERIC(10, 2) NOT NULL
);

The REFERENCES clause creates the foreign key. Without it, customer_id is just a number. The database has no way to know it is supposed to correspond to a real customer, and nothing prevents orphaned rows.The ON DELETE option controls what happens when a referenced row is deleted:

CREATE TABLE order_items (
  order_id      INT,
  customer_id   INT REFERENCES customers(customer_id) ON DELETE CASCADE,
  product_name  VARCHAR(200) NOT NULL,
  product_price NUMERIC(10, 2) NOT NULL
);

ON DELETE CASCADE automatically deletes all order items when the referenced customer is deleted. ON DELETE RESTRICT (the PostgreSQL default) prevents deletion if any order items reference that customer. Choose based on what the data model requires.

Dialect note: The inline REFERENCES syntax is PostgreSQL standard. In MySQL, inline REFERENCES without an explicit FOREIGN KEY keyword may be silently ignored. Use the explicit syntax for reliable cross-dialect behavior: FOREIGN KEY (customer_id) REFERENCES customers(customer_id).

When to denormalize

Normalization reduces redundancy and protects data integrity. There are situations where deliberate denormalization is the right trade-off.

At high read volumes, joining five or six tables on every query adds latency. Some teams store derived or summarized data directly in a table to avoid the join cost at query time. A reporting table that stores pre-aggregated sales totals by day is a common example. The data is redundant by design, but the read performance benefit outweighs the cost of keeping it in sync.

The key word is deliberate. Denormalization that happens because someone did not know better is a source of bugs. Denormalization that happens because a team understood the trade-off and made a conscious choice is a valid engineering decision.

3. Transactions and data integrity

A transaction is a group of write operations that the database treats as a single unit. Either all of them succeed, or none of them do. If anything fails midway, the database rolls back to the state it was in before the transaction started.

This guarantee is not just about error handling. It is about what it means to have correct data.

The problem without transactions

Consider a bank transfer. Moving $500 from account A to account B requires two writes:

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

Without a transaction, these are two independent operations. If the first succeeds and the second fails, account A loses $500 and account B receives nothing. The money disappears. The database is in a state that never should have existed.

This is not a rare edge case. Network interruptions, application crashes, and constraint violations all happen in production. Any multi-step write sequence that must succeed or fail together needs a transaction.

BEGIN, COMMIT, and ROLLBACK

A transaction starts with BEGIN and ends with either COMMIT or ROLLBACK:

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;

UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

COMMIT makes all changes permanent. ROLLBACK undoes everything since BEGIN. If the application crashes between BEGIN and COMMIT, the database automatically rolls back the incomplete transaction on recovery.

You can also roll back manually when something does not look right:

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;

-- Check the result before committing
SELECT balance FROM accounts WHERE id = 1;

-- If the balance went negative, undo everything
ROLLBACK;

Dialect note: 

• PostgreSQL uses BEGIN. 
• MySQL uses START TRANSACTION or BEGIN.
• SQL Server uses BEGIN TRANSACTION (or BEGIN TRAN). Just using BEGIN is reserved for code blocks, not transactions.

ACID: What the database guarantees

The guarantees a transactional database provides are described by four properties, abbreviated as ACID:

• Atomicity: A transaction is all or nothing. Partial success is not a valid outcome.
• Consistency: A transaction brings the database from one valid state to another. Constraints, foreign keys, and data types are enforced throughout. If a write would violate a constraint, the entire transaction is rejected.
• Isolation: Concurrent transactions do not interfere with each other. A transaction running in parallel does not see uncommitted changes from another transaction. The exact behavior depends on the isolation level, which is configurable.
• Durability: Once a transaction is committed, the changes survive a system crash. The database writes committed data to disk before acknowledging success.

Most developers work with the defaults and never need to configure isolation levels directly. What matters is knowing the guarantee exists: a committed transaction will not silently disappear.

Transactions in the application code

In a real backend, transactions are typically managed by the database driver or ORM, not by writing raw SQL. Here is what a transaction looks like in Node.js using the pg library:

const client = await pool.connect();

try {
  await client.query('BEGIN');

  await client.query(
    'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
    [500, 1]
  );

  await client.query(
    'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
    [500, 2]
  );

  await client.query('COMMIT');
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  client.release();
}

The try/catch/finally pattern ensures the connection is always released back to the pool, even if the transaction fails. The ROLLBACK in the catch block ensures partial writes are never committed.

When to use transactions

Any sequence of writes that must succeed or fail together needs a transaction. Common cases include:

• Transferring a value between two rows (money, inventory, credits)
• Creating a parent record and its child records together
• Updating multiple related tables as part of one user action
• Deleting a record and all its dependencies in a specific order

Single-row writes that do not depend on other writes do not need an explicit transaction. The database wraps each statement in its own implicit transaction automatically.

4. Indexes and query performance

An index is a separate data structure that the database maintains alongside a table to make certain queries faster. Without an index, finding a specific row means scanning every row in the table from top to bottom. With an index on the right column, the database can jump directly to the rows that match.

The trade-off is real: indexes speed up reads but slow down writes. Every INSERT, UPDATE, or DELETE on an indexed column requires the index to be updated as well. Knowing when to add an index and when not to is as important as knowing how indexes work.

How an index works

The most common index type is a B-tree index. The database builds a sorted tree structure over the indexed column. When a query filters or sorts on that column, the database traverses the tree to find matching values rather than reading the entire table.

-- Without an index: full table scan
SELECT * FROM users WHERE email = 'grace@example.com';

-- Create an index on the email column
CREATE INDEX idx_users_email ON users(email);

-- Now the same query uses the index
SELECT * FROM users WHERE email = 'grace@example.com';

The query looks identical. The difference is what happens inside the database. Without the index, it reads every row. With the index, it jumps directly to the matching rows. On a table with millions of rows, this is the difference between a query that takes seconds and one that takes milliseconds.

When indexes help and when they do not

Indexes help when:

• A column appears frequently in WHERE clauses
• A column is used in JOIN conditions
• A column is used in ORDER BY on large result sets
• A column has high cardinality (many distinct values, like email or user ID)

Indexes do not help, and can hurt, when:

• A column has low cardinality (few distinct values, like a boolean or a status with three options). The database may ignore the index entirely because a full scan is faster.
• A table is small. The overhead of using an index is not worth it when a full scan takes microseconds anyway.
• A column is wrapped in a function in the WHERE clause. This is one of the most common performance mistakes and deserves its own explanation.

Functions in WHERE clauses kill indexes

Wrapping a column in a function in a WHERE clause prevents the database from using an index on that column. The index stores raw values, not computed ones.

-- This cannot use an index on created_at
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- This can use an index on created_at
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

Both queries return the same rows. The second one rewrites the condition so the column appears alone on one side of the comparison, allowing the index to do its job. This pattern, called a sargable predicate, applies to any function applied to an indexed column: LOWER(), UPPER(), DATE(), CAST(), and others.

-- Cannot use an index on email
SELECT * FROM users WHERE LOWER(email) = 'grace@example.com';

-- Can use an index on email
SELECT * FROM users WHERE email = 'grace@example.com';

If you need case-insensitive matching on a column, store the data in a consistent case and match against that, or use a function-based index if your database supports it.

Composite indexes

An index can cover more than one column. A composite index is useful when queries filter on multiple columns together:

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

This index helps queries that filter on user_id alone, or on user_id and status together. It does not help queries that filter on status alone. The order of columns in a composite index matters: the index is most useful when queries filter on a left-to-right prefix of the indexed columns.

Reading EXPLAIN output

Every major database provides an EXPLAIN command that shows how the query optimizer plans to execute a query. Reading it tells you whether indexes are being used and where the expensive operations are.

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

In PostgreSQL, EXPLAIN ANALYZE runs the query and shows actual execution times alongside the plan:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

The two key things to look for in the output are:

• Sequential scan vs. index scan: A Seq Scan means the database is reading every row. An Index Scan or Index Only Scan means it is using an index. A Seq Scan on a large table with a WHERE clause on a high-cardinality column is a signal that an index is missing.
• Cost and row estimates: The planner estimates the cost and number of rows for each operation. Large discrepancies between estimated and actual rows (shown in EXPLAIN ANALYZE) often indicate that table statistics are stale and need refreshing with ANALYZE.

The N+1 query problem

One of the most common performance issues in applications using an ORM is the N+1 query problem. It happens when the application runs one query to fetch a list of records, then runs one additional query for each record to fetch related data.

// Fetch all orders
const orders = await Order.findAll();

// For each order, fetch the user separately
for (const order of orders) {
  const user = await User.findOne({ where: { id: order.userId } });
  console.log(user.name, order.amount);
}

If there are 100 orders, this runs 101 queries: one for the list and one per order. The fix is to use a join at the database level and fetch everything in one or two queries:

// Fetch all orders with their users in a single query
const orders = await Order.findAll({
  include: [{ model: User }]
});

The ORM generates a JOIN query or a highly optimized batched query (e.g., using WHERE IN (...)) behind the scenes. One round trip to the database instead of 101. At scale, the N+1 problem is one of the fastest ways to bring an otherwise well-written application to its knees.

Practical indexing rules

A few guidelines that hold in most situations:

• Index foreign key columns. Joins on unindexed foreign keys require full table scans on the joined table.
• Index columns that appear in WHERE clauses on large tables.
• Do not index every column. Each index adds write overhead and storage cost.
• Drop indexes that are never used. Most databases track index usage statistics.
• Run EXPLAIN ANALYZE before and after adding an index to confirm it is used and that it actually helps.

5. SQL injection: what it is and how to prevent it

SQL injection has appeared on the OWASP Top 10 list of critical web application vulnerabilities for over two decades. In 2025, researchers identified a record volume of these flaws, with SQL injection (CWE-89) rising to become the #2 most dangerous software weakness in the world. 

Even in 2026, it shows up in modern codebases, on funded startups, and in applications built with TypeScript, Docker, and current frameworks. The reason it persists is not that developers do not know about it. It is that the mistake is easy to make and the consequences are not visible until something goes wrong.

How it works

SQL injection happens when user-supplied input is concatenated directly into a SQL query string. The database cannot tell the difference between the query structure you wrote and the data the user provided. An attacker can supply input that changes the query’s logic entirely.

Here is a login endpoint written in Node.js that is vulnerable:

app.post('/login', async (req, res) => {
  const { email, password } = req.body;

  const query = `
    SELECT * FROM users
    WHERE email = '${email}'
    AND password = '${password}'
  `;

  const result = await pool.query(query);

  if (result.rows.length > 0) {
    res.json({ success: true });
  } else {
    res.json({ success: false });
  }
});

If a user submits a normal email and password, the query works as intended:

SELECT * FROM users
WHERE email = 'grace@example.com'
AND password = 'correctpassword'

But if an attacker submits ' OR '1'='1' -- as the email:

SELECT * FROM users
WHERE email = '' OR '1'='1' --' AND password = ''

The -- is a SQL comment. Everything after it is ignored by the database, including the AND password = '' condition. Without the comment, SQL’s operator precedence means AND binds before OR, so the injection would only work if the password field happened to be empty. The comment removes that dependency entirely. '1'='1' is always true, so this query returns every row in the users table. The attacker is logged in without valid credentials. With more targeted input, they can extract the entire database, modify records, or delete data.

The fix: parameterized queries

The solution is to separate the query structure from the data. Parameterized queries (also called prepared statements) send the SQL and the values to the database separately. The database treats the values as data, not as executable code, regardless of what they contain.

Here is the same endpoint rewritten safely:

app.post('/login', async (req, res) => {
  const { email, password } = req.body;

  const query = `
    SELECT * FROM users
    WHERE email = $1
    AND password = $2
  `;

  const result = await pool.query(query, [email, password]);

  if (result.rows.length > 0) {
    res.json({ success: true });
  } else {
    res.json({ success: false });
  }
});

The $1 and $2 are placeholders. The values are passed separately as an array. No matter what the user submits in email or password, the database will never interpret it as SQL. An attacker submitting ' OR '1'='1' -- will simply get a failed login, because the database looks for a user whose email is literally the string ' OR '1'='1' --.

Dialect note: $1, $2 placeholders are PostgreSQL syntax. MySQL uses ? instead. The concept is identical; only the placeholder syntax differs.

Passwords and hashing

The login example above compares passwords directly in the database query. In practice, passwords should never be stored as plain text or compared directly. Passwords should be hashed using a dedicated library like bcrypt, and comparison should happen in application code against the stored hash, not in SQL. The SQL injection fix is necessary but not sufficient for a secure login endpoint.

Why ORMs help but do not eliminate the risk

ORMs like Prisma, Sequelize, and TypeORM generate parameterized queries by default when you use their standard query builders. For the vast majority of operations, you are protected automatically.

The risk reappears in specific situations every ORM supports: raw query escape hatches. Developers reach for raw queries when the ORM’s query builder cannot express a complex condition, or when they want better performance on a specific operation. If those raw queries concatenate user input, they are just as vulnerable as hand-written SQL.

// Safe: Sequelize query builder, parameterized by default
const user = await User.findOne({ where: { email: req.body.email } });

// Vulnerable: raw query with string concatenation
const user = await sequelize.query(
  `SELECT * FROM users WHERE email = '${req.body.email}'`
);

// Safe: raw query with parameterization
const user = await sequelize.query(
  'SELECT * FROM users WHERE email = $1',
  { bind: [req.body.email] }
);

The rule is simple: whenever you write raw SQL in application code, use parameterized queries. No exceptions.

Dynamic identifiers are a separate problem

Parameterized queries protect values. They do not protect column names, table names, or ORDER BY directions, because those are SQL identifiers, not values, and they cannot be parameterized.

If your application allows users to choose which column to sort by, and you put that directly into the query, you have an injection risk even with parameterized values:

// Vulnerable: user controls the column name
const sortColumn = req.query.sort; // could be anything
const query = `SELECT * FROM products ORDER BY ${sortColumn}`;

The fix for dynamic identifiers is an allowlist: check the user-supplied value against a list of columns that are actually valid, and reject anything not on it.

const ALLOWED_SORT_COLUMNS = ['name', 'price', 'created_at'];

const sortColumn = req.query.sort;

if (!ALLOWED_SORT_COLUMNS.includes(sortColumn)) {
  return res.status(400).json({ error: 'Invalid sort column' });
}

const query = `SELECT * FROM products ORDER BY ${sortColumn}`;

This is a case where ORMs do not save you. Dynamic identifiers require explicit allowlist validation in application code.

6. SQL in the real world

The previous sections covered SQL as a language and as a set of engineering decisions. This section covers how it fits into a production backend: how applications connect to databases, what ORMs actually do, when raw SQL is the better choice, and what working with SQL looks like day to day.

Connection pooling

A database connection is expensive to establish. It involves a network handshake, authentication, and session setup. Opening a new connection for every query in a web application would be slow and would exhaust the database’s connection limit under any meaningful traffic.

Connection pooling solves this by maintaining a pool of open connections that the application reuses across requests. When a query needs to run, it borrows a connection from the pool, executes the query, and returns the connection when done.

As the architecture above shows, the connection pool acts as a dedicated traffic manager between your backend API and the database. Rather than building this layer from scratch, modern backend drivers provide it out of the box. For example, if you are working in a Node.js environment using the pg driver, initializing and using a pool looks like this:

import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

const result = await pool.query(
  'SELECT * FROM users WHERE id = $1',
  [userId]
);

The max setting controls how many connections the pool maintains. Setting it too high exhausts the database’s connection limit. Setting it too low creates a bottleneck under concurrent load. A reasonable starting point for most applications is 10 to 20 connections per application instance, adjusted based on the database server’s own max_connections setting and the number of running application instances.

Most ORMs manage a connection pool internally. If you are using Prisma, Sequelize, or TypeORM, the pool is configured at initialization, and you typically do not interact with it directly. The concept matters when you are debugging connection exhaustion issues or tuning for high traffic.

Raw SQL vs. ORMs

ORMs abstract the database layer. Instead of writing SQL, you work with model objects and method calls, and the ORM generates the queries behind the scenes. The trade-offs are real in both directions.

What ORMs give you:

• Parameterized queries by default, reducing SQL injection risk
• A typed interface to the database in languages like TypeScript
• Automatic schema migrations in some tools (Prisma, Django ORM)
• Less boilerplate for standard CRUD operations

What ORMs hide from you:

• The SQL being generated. A query that looks simple in ORM syntax can produce a slow or unexpected query underneath.
• Join complexity. ORMs sometimes generate multiple queries where one join would suffice, leading to N+1 problems as covered in Section 4.
• Database-specific features. Window functions, CTEs, and advanced index hints often require dropping to raw SQL.

The practical answer for most teams is to use an ORM for standard operations and write raw SQL when the ORM cannot express what you need or when you need precise control over the query. Using both in the same codebase is normal and expected.

Subqueries and CTEs

Two SQL features that become important in real-world queries are subqueries and common table expressions (CTEs). Both allow you to break a complex query into smaller, readable steps.

A subquery is a query nested inside another query:

SELECT name, email
FROM users
WHERE id IN (
  SELECT user_id
  FROM orders
  GROUP BY user_id
  HAVING COUNT(*) > (
    SELECT AVG(order_count)
    FROM (
      SELECT COUNT(*) AS order_count
      FROM orders
      GROUP BY user_id
    ) AS counts
  )
);

Subqueries work, but become difficult to read when nested deeply. A CTE rewrites the same logic with named, readable steps:

WITH order_counts AS (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
),
avg_count AS (
  SELECT AVG(order_count) AS avg_orders
  FROM order_counts
)
SELECT u.name, u.email
FROM users u
JOIN order_counts oc ON u.id = oc.user_id
JOIN avg_count ac ON oc.order_count > ac.avg_orders;

The WITH clause defines named result sets you can reference in the main query. Each step is named and readable. CTEs do not always improve performance over subqueries, but they almost always improve readability, which matters when maintaining queries over time.

What a SQL developer does day to day

The term “SQL developer” covers a wide range of roles. In most backend positions, SQL is one layer of a larger system rather than the entire job. Here is what working closely with SQL actually involves:

• Writing and optimizing queries: The majority of the work is writing queries that are correct, readable, and efficient. This means understanding when to add an index, when a join produces unexpected results, and when an ORM is generating something slow underneath a simple method call.
• Schema migrations: As applications evolve, schemas change. Adding columns, renaming tables, and adding indexes in production without downtime all require careful planning. Tools like Flyway, Liquibase, and Prisma Migrate manage migration history and make rollbacks predictable.
• Debugging production issues: Slow query logs, connection pool exhaustion, lock contention, and unexpected query plans all appear in production. Diagnosing them requires reading execution plans, reviewing schema design, and understanding how the database handles concurrent writes.
• Data integrity: Enforcing constraints, writing transactions correctly, and making sure application code does not leave the database in an inconsistent state are ongoing responsibilities across the lifetime of a codebase.

SQL projects to build

The fastest way to move from knowing SQL to being comfortable with it in a real backend is to build something that uses it under realistic conditions. Three projects that cover the concepts in this handbook:

• An order management system: A schema with users, products, orders, and order items exercises normalization, foreign keys, joins, and aggregation. Add an endpoint that returns a customer’s full order history with per-order totals, and you will write most of what this handbook covers.
• A budget tracker: A schema with accounts, categories, and transactions exercises transactions (the SQL kind), balances that must stay consistent across writes, and aggregate queries for monthly summaries. Add a constraint that prevents account balances from going negative, and you have a realistic data integrity challenge.
• A simple analytics dashboard backend: Build API endpoints that return aggregated data: top products by revenue, user signups by week, average order value by category. This forces you to write non-trivial GROUP BY queries, think about index coverage, and understand what the ORM is generating underneath.

Resources for a deeper dive

The official PostgreSQL documentation remains the most thorough reference for everything covered in this guide, including detailed coverage of indexes, execution plans, transaction isolation levels, and foreign key behavior. The OWASP SQL Injection Prevention Cheat Sheet is the authoritative reference on parameterized queries, allowlists, and secure database access patterns.

For execution plan analysis, the official PostgreSQL EXPLAIN documentation covers every field in the output in detail. Explain.depesz.com is a widely used tool for pasting and visualizing PostgreSQL execution plans in a more readable format.

For readers building on MySQL or SQL Server, the MySQL documentation on optimization and the Microsoft SQL Server execution plan documentation are the corresponding primary references.

Readers who learn best by writing, reading, and running code should try structured interactive courses, which can provide a head start and help build momentum. Here are a few recommendations:

• Grokking the SQL Interview Patterns: An intermediate course that teaches you to recognize repeatable SQL patterns and apply them to complex, real-world query problems. Covers LeetCode-style challenges, business case studies, and performance optimization techniques used across data-centric roles including data engineering and analytics.
• The Art of PostgreSQL: An advanced course focused on PostgreSQL specifically, covering SQL integration, window functions, data modeling, additional data types, and extensions. Builds directly on the concepts in this guide and goes significantly deeper into PostgreSQL’s capabilities.
• SQL Interview Preparation: Advanced Level: An advanced MySQL-focused course covering transactions, isolation behavior, stored routines, CTEs, window functions, query optimization, and execution plan analysis. A strong fit if your stack uses MySQL and you want to prepare for senior-level SQL interviews or deepen your production SQL skills.