SQL & Database Engineering

This skill provides standards, patterns, and procedures for database schema design, writing performant SQL, query debugging, and database engineering best practices. It is database-engine-agnostic with notes on engine-specific behavior where critical.

Guiding Principles

SELECT *

EXPLAIN ANALYZE

1. Database Schema Architecture Design

Naming Conventions

snake_case

order_items

user_addresses

snake_case

first_name

created_at

id

<table_singular>_id

id

user_id

<referenced_table_singular>_id

user_id

order_id

idx_<table>_<columns>

idx_orders_user_id_status

uq_<table>_<columns>

uq_users_email

chk_<table>_<description>

chk_orders_total_positive

<table1>_<table2>

products_tags

roles_users

Standard Columns

Every table MUST include:

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- or BIGSERIAL for auto-increment
    -- ... domain columns ...
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

When soft deletes are required by business rules:

deleted_at TIMESTAMP WITH TIME ZONE DEFAULT NULL

Primary Key Strategy

UUID v4

UUID v7

ULID

BIGSERIAL

IDENTITY

Rule: Never expose auto-increment IDs in public APIs. Use UUIDs or ULIDs for external identifiers.

Data Types — Choose Precisely

NUMERIC(19,4)

DECIMAL(19,4)

FLOAT

DOUBLE

TIMESTAMP WITH TIME ZONE

TIMESTAMP

BOOLEAN

TINYINT

CHAR(1)

VARCHAR(n)

TEXT

TEXT

VARCHAR(10000)

VARCHAR

JSONB

JSON

INET

VARCHAR(45)

VARCHAR(15)

Enum Handling

Prefer CHECK constraints over native ENUM types for portability and ease of migration:

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CONSTRAINT chk_orders_status CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

Adding a new enum value is a simple

ALTER TABLE ... DROP CONSTRAINT

ADD CONSTRAINT

Schema Design Checklist

Schema design checklist:
- [ ] Every table has a primary key (UUID or BIGSERIAL)
- [ ] Every table has created_at and updated_at columns
- [ ] All foreign keys have corresponding indexes
- [ ] All columns have appropriate NOT NULL constraints
- [ ] Unique constraints are defined where business rules require uniqueness
- [ ] CHECK constraints enforce valid value ranges and enums
- [ ] Naming follows snake_case conventions consistently
- [ ] Data types are chosen precisely (no FLOAT for money, no TEXT for emails)
- [ ] Soft delete (deleted_at) is used only where business rules require it
- [ ] No business logic is embedded in database triggers or stored procedures (keep logic in the application layer)

2. Normalisation Strategies

Normal Forms Reference

tags: "php,sql,go"

tags

order_items(order_id, product_id, product_name)

product_name

product_id

product_name

products

employees(id, department_id, department_name)

department_name

department_id

id

department_name

departments

When to Use Each Level

Target 3NF by default. This eliminates redundancy while keeping the schema manageable.

Use 2NF only as an intermediate step when refactoring legacy schemas — never as a design target.

Use BCNF when you have composite primary keys with overlapping candidate keys (uncommon in application databases).

Practical Normalisation Example

Unnormalised (0NF):

orders:
| order_id | customer_name | customer_email      | items                          |
|----------|---------------|---------------------|--------------------------------|
| 1        | John Doe      | john@example.com    | Widget x2, Gadget x1           |

1NF — Atomic values, no repeating groups:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    customer_email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id),
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0)
);

2NF — Remove partial dependencies (product details depend on product, not order):

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(19,4) NOT NULL
);

CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id),
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(19,4) NOT NULL  -- snapshot of price at order time
);

3NF — Remove transitive dependencies (customer depends on customer, not order):

CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

Strategic Denormalisation

Denormalise only when you have measured performance data (via

EXPLAIN ANALYZE

orders.total_amount

order_items

order_items.unit_price

orders.customer_country

customers.country

Rules for denormalisation:

• Always document why the denormalisation exists (code comment + migration description).
• Ensure the denormalised data is kept in sync (application-level updates, triggers as last resort, or materialised view refresh).
• Treat denormalised data as a cache — the normalised source remains the source of truth.

3. Relationships & Foreign Keys

Relationship Types

One-to-Many (most common):

-- A customer has many orders
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Many-to-Many (junction table):

-- Products can have many tags, tags can belong to many products
CREATE TABLE products_tags (
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    tag_id BIGINT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, tag_id)
);

CREATE INDEX idx_products_tags_tag_id ON products_tags(tag_id);

One-to-One:

-- Each user has exactly one profile
CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    bio TEXT,
    avatar_url VARCHAR(500)
);

ON DELETE / ON UPDATE Actions

RESTRICT

CASCADE

SET NULL

SET DEFAULT

NO ACTION

Rule: Default to

RESTRICT

CASCADE

Self-Referencing Relationships

-- Categories with parent-child hierarchy
CREATE TABLE categories (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id BIGINT REFERENCES categories(id) ON DELETE SET NULL
);

CREATE INDEX idx_categories_parent_id ON categories(parent_id);

For deep hierarchies (trees), consider:

• Adjacency list (above) — simple, good for shallow trees
• Materialised path —

  path VARCHAR(500)

  storing

  /1/5/12/

  — good for read-heavy trees
• Nested sets — complex to maintain but fast for subtree queries
• Closure table — separate table storing all ancestor-descendant pairs — balanced read/write performance

4. Indexes

Index Types

CREATE INDEX idx ON table(column)

=

<

>

BETWEEN

ORDER BY

CREATE INDEX idx ON table USING hash(column)

CREATE INDEX idx ON table USING gin(column)

CREATE INDEX idx ON table USING gist(column)

CREATE INDEX idx ON table USING brin(column)

Index Strategy Rules

Always index:

• Foreign key columns (not auto-indexed in PostgreSQL/MySQL InnoDB)
• Columns frequently used in

  WHERE

  clauses
• Columns used in

  ORDER BY

  on large tables
• Columns used in

  JOIN

  conditions

Consider composite indexes for:

• Queries that filter on multiple columns together
• Covering indexes that satisfy a query entirely from the index

Avoid over-indexing:

• Each index adds overhead to

  INSERT

  ,

  UPDATE

  , and

  DELETE

  operations
• Indexes consume storage
• Review and remove unused indexes periodically

Composite Index Column Order

The order of columns in a composite index matters. Place columns in this priority:

1. Equality conditions first (

   WHERE status = 'active'

   )
2. Range conditions second (

   WHERE created_at > '2025-01-01'

   )
3. Sort columns last (

   ORDER BY created_at DESC

   )

-- Query: WHERE status = 'active' AND created_at > '2025-01-01' ORDER BY created_at DESC
-- Optimal index:
CREATE INDEX idx_orders_status_created_at ON orders(status, created_at DESC);

Partial Indexes

Index only the rows you actually query:

-- Only index active orders (if most queries filter for active)
CREATE INDEX idx_orders_active ON orders(customer_id, created_at)
    WHERE deleted_at IS NULL;

Unique Indexes

Use unique indexes to enforce business constraints:

-- Email must be unique, but only for non-deleted users
CREATE UNIQUE INDEX uq_users_email_active ON users(email)
    WHERE deleted_at IS NULL;

Index Monitoring

Periodically check for unused indexes:

-- PostgreSQL: find unused indexes
SELECT
    schemaname, relname AS table_name, indexrelname AS index_name,
    idx_scan AS times_used, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Non-Blocking Index Operations (CONCURRENTLY)

Standard

CREATE INDEX

DROP INDEX

Always use

CONCURRENTLY

-- Creating an index without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

-- Dropping an index without blocking writes
DROP INDEX CONCURRENTLY idx_orders_customer_id;

Key constraints and caveats:

CONCURRENTLY

ALGORITHM=INPLACE, LOCK=NONE

WITH (ONLINE = ON)

ONLINE

CREATE INDEX CONCURRENTLY

DROP INDEX CONCURRENTLY

BEGIN

COMMIT

disable_ddl_transaction!

atomic = False

CREATE INDEX CONCURRENTLY

\d table_name

CREATE UNIQUE INDEX CONCURRENTLY

When to skip

CONCURRENTLY

• Initial schema setup or empty tables (no live traffic to block)
• During a maintenance window with no active connections
• Test/development environments

5. Writing Performant SQL Queries

Query Writing Rules

SELECT id, name, email FROM users

SELECT * FROM users

WHERE id = $1

WHERE id = ?

WHERE id = '

'

LIMIT 100

SELECT

WHERE

WHERE EXISTS (SELECT 1 FROM ...)

WHERE id IN (SELECT id FROM ...)

WHERE created_at >= '2025-01-01'

WHERE DATE(created_at) = '2025-01-01'

UNION ALL

UNION

JOIN Optimisation

Join Types and When to Use

INNER JOIN

LEFT JOIN

RIGHT JOIN

CROSS JOIN

LATERAL JOIN

Join Performance Guidelines

-- GOOD: Join on indexed columns, filter early
SELECT o.id, o.created_at, c.name
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'active'
  AND o.created_at >= '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

-- BAD: Joining on expressions, filtering late
SELECT *
FROM orders o
INNER JOIN customers c ON LOWER(c.email) = LOWER(o.customer_email)
WHERE YEAR(o.created_at) = 2025;

Rules:

• Always join on indexed columns (typically primary keys and foreign keys).
• Place the most restrictive

  WHERE

  conditions on the driving table to reduce the row set early.
• Avoid joining on computed expressions — create a persisted computed column or a functional index if needed.
• When joining many tables, consider whether a subquery or CTE might be clearer and equally performant.

Pagination Strategies

Offset-based pagination (simple but degrades on large offsets):

SELECT id, name, email
FROM users
WHERE deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 20 OFFSET 1000;
-- Database must scan and discard 1000 rows

Keyset/cursor-based pagination (performant at any depth):

-- First page
SELECT id, name, email, created_at
FROM users
WHERE deleted_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page (use last row's values as cursor)
SELECT id, name, email, created_at
FROM users
WHERE deleted_at IS NULL
  AND (created_at, id) < ('2025-02-01T10:00:00Z', '550e8400-e29b-41d4-a716-446655440000')
ORDER BY created_at DESC, id DESC
LIMIT 20;

Rule: Use offset-based pagination for admin/back-office UIs with moderate data volumes (< 100k rows). Use keyset pagination for APIs, infinite scrolling, and large datasets.

Aggregation Best Practices

-- GOOD: Filter before aggregating
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
WHERE status = 'completed'
  AND created_at >= '2025-01-01'
GROUP BY customer_id
HAVING COUNT(*) > 5
ORDER BY total_spent DESC;

-- BAD: Aggregating everything then filtering in application code
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id;
-- Then filtering in PHP/Node/Java... wasted database work

Common Table Expressions (CTEs)

Use CTEs for readability. Be aware that in some databases (PostgreSQL < 12), CTEs act as optimisation fences:

-- Readable multi-step query
WITH active_customers AS (
    SELECT id, name
    FROM customers
    WHERE status = 'active'
      AND deleted_at IS NULL
),
recent_orders AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY customer_id
)
SELECT ac.id, ac.name, COALESCE(ro.order_count, 0) AS recent_orders
FROM active_customers ac
LEFT JOIN recent_orders ro ON ro.customer_id = ac.id
ORDER BY recent_orders DESC;

Window Functions

Use window functions for ranking, running totals, and per-group calculations without collapsing rows:

-- Rank orders by total within each customer
SELECT
    customer_id,
    id AS order_id,
    total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank,
    SUM(total) OVER (PARTITION BY customer_id) AS customer_total
FROM orders
WHERE status = 'completed';

Batch Operations

For large data modifications, batch to avoid long locks and transaction log bloat:

-- Instead of one massive DELETE:
-- DELETE FROM logs WHERE created_at < '2024-01-01';  -- locks millions of rows

-- Batch delete:
DO $$
DECLARE
    rows_deleted INT;
BEGIN
    LOOP
        DELETE FROM logs
        WHERE id IN (
            SELECT id FROM logs
            WHERE created_at < '2024-01-01'
            LIMIT 5000
        );
        GET DIAGNOSTICS rows_deleted = ROW_COUNT;
        EXIT WHEN rows_deleted = 0;
        COMMIT;
    END LOOP;
END $$;

6. Transactions

ACID Properties

Transaction Isolation Levels

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

*PostgreSQL's

REPEATABLE READ

Rule: Use

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

Transaction Best Practices

-- GOOD: Short, focused transaction
BEGIN;
    UPDATE accounts SET balance = balance - 100.00 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100.00 WHERE id = 2;
    INSERT INTO transactions (from_id, to_id, amount) VALUES (1, 2, 100.00);
COMMIT;

Rules:

• Keep transactions as short as possible. Long transactions hold locks and block other operations.
• Never perform external I/O (HTTP calls, file operations) inside a transaction.
• Always handle transaction failures: catch exceptions, rollback, and optionally retry.
• Use

  SAVEPOINT

  for partial rollbacks within a larger transaction when needed.
• Acquire locks in a consistent order across all transactions to prevent deadlocks (e.g., always lock by ascending ID).

Savepoints

BEGIN;
    INSERT INTO orders (customer_id, total) VALUES (1, 250.00);
    SAVEPOINT before_items;

    INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 99, 1);
    -- If this fails:
    ROLLBACK TO SAVEPOINT before_items;
    -- Order is still inserted, items are rolled back

COMMIT;

7. Locking Mechanics

Lock Types

SELECT ... FOR SHARE

SELECT ... FOR UPDATE

LOCK TABLE ... IN <mode> MODE

pg_advisory_lock(key)

Optimistic Locking

Optimistic locking assumes conflicts are rare. It checks for conflicts at write time using a version column:

-- Schema
ALTER TABLE products ADD COLUMN version INT NOT NULL DEFAULT 1;

-- Read: fetch current version
SELECT id, name, price, stock, version FROM products WHERE id = 42;
-- Application receives: version = 3

-- Update: include version check
UPDATE products
SET stock = stock - 1, version = version + 1, updated_at = NOW()
WHERE id = 42 AND version = 3;

-- If 0 rows affected → another transaction modified the row → handle conflict (retry or error)

When to use:

• Low contention (concurrent writes to the same row are rare)
• Read-heavy workloads
• User-facing forms where edits happen seconds/minutes apart
• Distributed systems where holding database locks across requests is impractical

ORM support:

@VersionColumn()

version

@ORM\Version

laravel-optimistic-locking

[Timestamp]

IsRowVersion()

@Version

gorm:"column:version"

Pessimistic Locking

Pessimistic locking assumes conflicts are likely. It acquires locks at read time to prevent concurrent modification:

-- Lock the row immediately — other transactions block until this transaction commits
BEGIN;
    SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE;
    -- ... perform business logic ...
    UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT;

Variants:

-- FOR UPDATE SKIP LOCKED — skip already-locked rows (useful for job queues)
SELECT id, payload
FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- FOR UPDATE NOWAIT — fail immediately if the row is locked (instead of blocking)
SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE NOWAIT;
-- Throws an error immediately if the row is locked by another transaction

When to use:

• High contention (concurrent writes to the same row are likely)
• Critical sections that must not have conflicts (inventory decrements, booking seats)
• Short-lived transactions where holding a lock briefly is acceptable
• Job queues and task processing (

  SKIP LOCKED

  )

Optimistic vs Pessimistic — Decision Guide

Deadlock Prevention

1. Consistent lock ordering: Always acquire locks in the same order (e.g., by ascending primary key).
2. Short transactions: Minimise the time locks are held.
3. Lock timeout: Set

   lock_timeout

   to avoid indefinite blocking.
4. Detect and retry: Handle deadlock exceptions and retry the transaction.

-- PostgreSQL: set a lock timeout
SET lock_timeout = '5s';

-- MySQL: set innodb_lock_wait_timeout per session
SET innodb_lock_wait_timeout = 5;

8. Query Debugging with EXPLAIN

EXPLAIN Basics

-- Show the query plan (does not execute the query)
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Show the query plan AND execute the query (shows actual timings)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- Full diagnostic output (PostgreSQL)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'active'
ORDER BY o.created_at DESC
LIMIT 20;

Reading EXPLAIN Output

Key fields to examine:

Common Performance Problems and Fixes

Seq Scan

Seq Scan

ANALYZE table_name;

Sort

ORDER BY

Nested Loop

work_mem

Hash Join

work_mem

work_mem

Buffers: read

shared_buffers

Iterative Query Optimisation Process

Query optimisation process:
1. [ ] Identify the slow query (application logs, pg_stat_statements, slow query log)
2. [ ] Run EXPLAIN ANALYZE on the query
3. [ ] Identify the most expensive node in the plan
4. [ ] Determine root cause (missing index, bad statistics, inefficient join, sort)
5. [ ] Apply ONE fix at a time
6. [ ] Re-run EXPLAIN ANALYZE and compare
7. [ ] Repeat until acceptable performance is reached
8. [ ] Verify the fix doesn't degrade other queries

MySQL-Specific EXPLAIN Notes

-- MySQL uses a different EXPLAIN format
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Key MySQL EXPLAIN columns:
-- type: ALL (full scan) → index → range → ref → eq_ref → const → system (best to worst)
-- possible_keys: indexes the optimiser considered
-- key: index actually used
-- rows: estimated rows scanned
-- Extra: "Using filesort", "Using temporary" are red flags

-- MySQL 8.0+ supports EXPLAIN ANALYZE:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

9. SQL Writing Best Practices

Formatting Standards

-- GOOD: Readable, consistent formatting
SELECT
    u.id,
    u.email,
    u.first_name,
    u.last_name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS lifetime_value
FROM users u
LEFT JOIN orders o ON o.customer_id = u.id
    AND o.status = 'completed'
WHERE u.deleted_at IS NULL
  AND u.created_at >= '2025-01-01'
GROUP BY u.id, u.email, u.first_name, u.last_name
HAVING COUNT(o.id) > 0
ORDER BY lifetime_value DESC
LIMIT 50;

Formatting rules:

• Keywords in UPPERCASE:

  SELECT

  ,

  FROM

  ,

  WHERE

  ,

  JOIN

  ,

  ORDER BY

  ,

  GROUP BY

• One column per line in

  SELECT

  list for queries with more than 3 columns

• AND

  /

  OR

  at the beginning of the line, indented
• Table aliases: short but meaningful (

  u

  for

  users

  ,

  o

  for

  orders

  )
• Align

  JOIN ... ON

  conditions consistently
• Always terminate statements with

  ;

Security — Preventing SQL Injection

Never concatenate user input into SQL strings. Always use parameterised queries:

-- GOOD: Parameterised (prepared statement)
-- PostgreSQL / Node.js
SELECT * FROM users WHERE email = $1;

-- MySQL / PHP
SELECT * FROM users WHERE email = ?;

ORM equivalents (all safe by default):

// TypeORM — safe
const user = await userRepository.findOne({ where: { email: userInput } });

// Prisma — safe
const user = await prisma.user.findUnique({ where: { email: userInput } });

// Doctrine — safe
$user = $repository->findOneBy(['email' => $userInput]);

// Eloquent — safe
$user = User::where('email', $userInput)->first();

// Entity Framework — safe
var user = await context.Users.FirstOrDefaultAsync(u => u.Email == userInput);

// Hibernate — safe
User user = session.createQuery("FROM User u WHERE u.email = :email", User.class)
    .setParameter("email", userInput)
    .uniqueResult();

// GORM — safe
var user User
db.Where("email = ?", userInput).First(&user)

DANGER: Raw queries with string concatenation:

// TypeORM — DANGEROUS if not parameterised
const users = await dataSource.query(`SELECT * FROM users WHERE email = '${userInput}'`); // SQL INJECTION!

// TypeORM — SAFE raw query
const users = await dataSource.query('SELECT * FROM users WHERE email = $1', [userInput]);

NULL Handling

-- WRONG: = NULL never matches
SELECT * FROM users WHERE deleted_at = NULL;

-- CORRECT: Use IS NULL / IS NOT NULL
SELECT * FROM users WHERE deleted_at IS NULL;

-- Use COALESCE for default values
SELECT COALESCE(u.nickname, u.first_name, 'Anonymous') AS display_name
FROM users u;

-- NULL-safe comparison (when comparing two nullable columns)
-- PostgreSQL:
SELECT * FROM t1 JOIN t2 ON t1.val IS NOT DISTINCT FROM t2.val;
-- MySQL:
SELECT * FROM t1 JOIN t2 ON t1.val <=> t2.val;

UPSERT Patterns

-- PostgreSQL: INSERT ... ON CONFLICT
INSERT INTO products (sku, name, price, updated_at)
VALUES ('WIDGET-001', 'Widget', 19.99, NOW())
ON CONFLICT (sku)
DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    updated_at = NOW();

-- MySQL: INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO products (sku, name, price, updated_at)
VALUES ('WIDGET-001', 'Widget', 19.99, NOW())
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    price = VALUES(price),
    updated_at = NOW();

Avoiding Common Anti-Patterns

SELECT *

N+1 queries

JOIN

WHERE id IN (...)

WHERE column != value

!=

<>

LIKE '%value%'

tsvector

FULLTEXT

ORDER BY RAND()

WHERE id >= (SELECT FLOOR(RANDOM() * MAX(id)) FROM table) LIMIT 1

WHERE varchar_col = 123

WHERE varchar_col = '123'

JOIN

LIMIT

LIMIT

10. ORM Integration Guidelines

ORM Selection by Stack

ORM Best Practices

1. Always review generated SQL. Enable query logging in development to inspect what the ORM generates. Inefficient ORM usage can produce catastrophic queries.

// TypeORM: enable query logging
const dataSource = new DataSource({
    logging: ['query', 'error'],
    // ...
});

// Prisma: enable query logging
const prisma = new PrismaClient({
    log: ['query', 'warn', 'error'],
});

// Doctrine: enable SQL logger
$configuration->setSQLLogger(new \Doctrine\DBAL\Logging\EchoSQLLogger());

// Laravel: enable query log
DB::enableQueryLog();
// ... run queries ...
dd(DB::getQueryLog());

2. Solve N+1 queries with eager loading.

// TypeORM — eager loading with relations
const orders = await orderRepository.find({
    relations: ['customer', 'items', 'items.product'],
    where: { status: 'active' },
});

// Prisma — include related data
const orders = await prisma.order.findMany({
    where: { status: 'active' },
    include: {
        customer: true,
        items: { include: { product: true } },
    },
});

// Eloquent — eager loading
$orders = Order::with(['customer', 'items.product'])
    ->where('status', 'active')
    ->get();

// Doctrine — DQL with joins
$orders = $em->createQuery('
    SELECT o, c, i, p
    FROM App\Entity\Order o
    JOIN o.customer c
    JOIN o.items i
    JOIN i.product p
    WHERE o.status = :status
')->setParameter('status', 'active')
  ->getResult();

// Entity Framework — eager loading
var orders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .Where(o => o.Status == "active")
    .ToListAsync();

// Hibernate / Spring Data JPA — fetch join
@Query("SELECT o FROM Order o " +
       "JOIN FETCH o.customer " +
       "JOIN FETCH o.items i " +
       "JOIN FETCH i.product " +
       "WHERE o.status = :status")
List<Order> findActiveWithDetails(@Param("status") String status);

// GORM — preload
var orders []Order
db.Preload("Customer").Preload("Items.Product").
    Where("status = ?", "active").
    Find(&orders)

3. Use raw SQL for complex queries. When ORM abstractions produce inefficient SQL or cannot express the query you need, drop to raw SQL:

// TypeORM — raw query
const results = await dataSource.query(`
    SELECT u.id, u.email, COUNT(o.id) AS order_count
    FROM users u
    LEFT JOIN orders o ON o.customer_id = u.id AND o.status = 'completed'
    WHERE u.deleted_at IS NULL
    GROUP BY u.id, u.email
    HAVING COUNT(o.id) > 5
    ORDER BY order_count DESC
    LIMIT $1
`, [50]);

// Prisma — raw query
const results = await prisma.$queryRaw`
    SELECT u.id, u.email, COUNT(o.id) AS order_count
    FROM users u
    LEFT JOIN orders o ON o.customer_id = u.id AND o.status = 'completed'
    WHERE u.deleted_at IS NULL
    GROUP BY u.id, u.email
    HAVING COUNT(o.id) > 5
    ORDER BY order_count DESC
    LIMIT ${50}
`;

4. ORM Migration Workflow.

• Generate migrations from entity/model changes, never edit the database directly.
• Review generated migration SQL before applying — ORMs sometimes generate suboptimal DDL.
• Test migrations against a copy of production data to catch issues (data truncation, long-running locks).

5. Repository Pattern with ORMs. Keep all database queries in repository classes. Services should never directly use the ORM's query builder or entity manager:

Service → Repository Interface → Repository Implementation (uses ORM)

This keeps business logic decoupled from the data access mechanism and makes testing easier (mock the repository interface).

11. Database Maintenance & Monitoring

Statistics and Vacuuming (PostgreSQL)

-- Update table statistics for the planner
ANALYZE orders;

-- Update statistics for all tables
ANALYZE;

-- Check for table bloat (dead tuples)
SELECT relname, n_dead_tup, n_live_tup,
       ROUND(n_dead_tup::NUMERIC / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Slow Query Identification

-- PostgreSQL: enable pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries by total time
SELECT
    calls,
    ROUND(total_exec_time::NUMERIC, 2) AS total_ms,
    ROUND(mean_exec_time::NUMERIC, 2) AS mean_ms,
    ROUND(max_exec_time::NUMERIC, 2) AS max_ms,
    LEFT(query, 200) AS query_preview
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- MySQL: enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- queries taking > 1 second

Connection Pooling

• Always use connection pooling in production. Direct connections are expensive to create.
• PostgreSQL: use PgBouncer or built-in pooling in the ORM/driver.
• MySQL: use ProxySQL or driver-level pooling.
• Set pool size based on:

  pool_size = (core_count * 2) + effective_spindle_count

  (formula from PostgreSQL wiki).
• Monitor for connection exhaustion — set

  max_connections

  appropriately and alert when pool utilisation exceeds 80%.

Implementation Procedure

When writing SQL or designing a database schema, follow this workflow:

Implementation progress:
- [ ] Step 1: Understand the data requirements
- [ ] Step 2: Design the schema (entities, relationships, constraints)
- [ ] Step 3: Choose normalisation level and document any denormalisation decisions
- [ ] Step 4: Define indexes based on expected query patterns
- [ ] Step 5: Write the migration(s)
- [ ] Step 6: Write the queries (raw SQL or ORM)
- [ ] Step 7: Run EXPLAIN ANALYZE on critical queries
- [ ] Step 8: Implement appropriate locking and transaction strategy
- [ ] Step 9: Review ORM-generated SQL (if applicable)
- [ ] Step 10: Test with realistic data volumes

Step 1: Understand the data requirements Read the task description and acceptance criteria. Identify the entities, their attributes, and the relationships between them. Clarify cardinality (one-to-many, many-to-many).

Step 2: Design the schema Create tables following the naming conventions and standard columns defined in this skill. Define relationships with appropriate foreign keys and ON DELETE actions.

Step 3: Choose normalisation level Target 3NF by default. Document any intentional denormalisation with a clear justification tied to measured performance needs or snapshot requirements.

Step 4: Define indexes Identify the primary query patterns and create indexes to support them. Index all foreign keys. Create composite indexes for multi-column filters and sorts.

Step 5: Write the migration(s) Generate migration files with both

up

down

Step 6: Write the queries Write queries following the performance and formatting rules in this skill. Use parameterised queries. Avoid

SELECT *

Step 7: Run EXPLAIN ANALYZE Verify critical query plans. Look for sequential scans on large tables, missing indexes, and planner misestimates.

Step 8: Implement locking and transaction strategy Choose optimistic or pessimistic locking based on the contention profile. Define transaction boundaries and appropriate isolation levels.

Step 9: Review ORM-generated SQL Enable query logging and inspect generated queries for N+1 problems, unnecessary joins, or missing eager loading.

Step 10: Test with realistic data volumes Seed the development database with production-scale data. Verify that queries perform within acceptable time limits under load.

Connected Skills

• backend-api-development

  — for API layer patterns, repository pattern, and database handling guidance

• architecture-design

  — for designing data models as part of broader system architecture

• code-review

  — for validating SQL quality, index coverage, and query performance

• technical-context-discovery

  — for establishing database conventions and existing patterns before designing