Databases & ORMs

The Big Picture

Every backend application needs to store and retrieve data. The database landscape has two major camps — SQL and NoSQL — with ORMs sitting on top as a translation layer between your code and the database engine.

SQL vs NoSQL

This is the first decision you'll make for every project: do you need strict structure or flexible schemas? Here's the honest breakdown.

Database Types at a Glance

CRUD Operations

CRUD stands for Create, Read, Update, Delete — the four fundamental operations every database supports. Let's compare how they look in SQL versus MongoDB so you can see the mental model shift between relational and document databases.

Create

-- SQL: Insert a row into a table
INSERT INTO users (name, email, age)
VALUES ('Alice', '[email protected]', 30);

// MongoDB: Insert a document into a collection
db.users.insertOne({
  name: "Alice",
  email: "[email protected]",
  age: 30
});

Read

-- SQL: Query with a filter
SELECT name, email FROM users
WHERE age > 25
ORDER BY name;

// MongoDB: Find with a filter
db.users.find(
  { age: { $gt: 25 } },
  { name: 1, email: 1 }
).sort({ name: 1 });

Update

-- SQL: Update matching rows
UPDATE users
SET email = '[email protected]'
WHERE name = 'Alice';

// MongoDB: Update matching documents
db.users.updateOne(
  { name: "Alice" },
  { $set: { email: "[email protected]" } }
);

Delete

-- SQL: Delete matching rows
DELETE FROM users
WHERE age < 18;

// MongoDB: Delete matching documents
db.users.deleteMany({ age: { $lt: 18 } });

ORMs: The Database Translator

An ORM (Object-Relational Mapper) lets you interact with your database using your programming language's objects and methods instead of writing raw SQL strings. You define a model class, and the ORM generates the SQL behind the scenes.

Pros and Cons

Popular ORMs

SQLAlchemy Example

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, Session

Base = declarative_base()

# Define a model (maps to a database table)
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    email = Column(String(200), unique=True)

# Connect and create tables
engine = create_engine("postgresql://user:pass@localhost/mydb")
Base.metadata.create_all(engine)

# Create a user (INSERT)
with Session(engine) as session:
    user = User(name="Alice", email="[email protected]")
    session.add(user)
    session.commit()

# Query users (SELECT)
with Session(engine) as session:
    users = session.query(User).filter(User.name == "Alice").all()
    for u in users:
        print(u.name, u.email)

The N+1 Problem

The N+1 problem is the single most common performance trap when using ORMs. It happens when your code fetches a list of records, then makes a separate database query for each record's related data. One query to get the list (1), plus one query per item (N) = N+1 queries.

The Problem: N+1 Queries

Imagine you want to display all users and their posts:

# BAD: N+1 queries
# Query 1: Get all users
users = session.query(User).all()

# Queries 2 to N+1: Get posts for EACH user
for user in users:
    # This fires a SEPARATE query for each user!
    print(f"{user.name}: {len(user.posts)} posts")

If you have 100 users, this fires 101 database queries. With 10,000 users, it fires 10,001. Your page load time goes from milliseconds to seconds.

The Fix: Eager Loading

# GOOD: 1 query with eager loading (JOIN)
from sqlalchemy.orm import joinedload

users = session.query(User).options(
    joinedload(User.posts)
).all()

# Now posts are already loaded — no extra queries
for user in users:
    print(f"{user.name}: {len(user.posts)} posts")

The Fix in Raw SQL

-- Instead of querying users, then posts separately:
-- Query 1: SELECT * FROM users;
-- Query 2-N: SELECT * FROM posts WHERE user_id = ?;

-- Use a single JOIN:
SELECT users.name, COUNT(posts.id) AS post_count
FROM users
LEFT JOIN posts ON posts.user_id = users.id
GROUP BY users.name;

Indexing Strategies

An index is a data structure that makes queries faster — like the index at the back of a textbook. Without it, the database scans every single row (a "full table scan"). With it, the database jumps straight to the matching rows.

Index Types

Creating Indexes

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (column order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Partial index (only index active users)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- Check if your query uses the index
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Performance Impact

When NOT to Index

• Low-cardinality columns — A boolean is_active column with only 2 values won't benefit much from an index.
• Tiny tables — If the table has fewer than 1,000 rows, a full scan is already fast.
• Write-heavy tables — If you're inserting millions of rows per second, every index slows writes.
• Columns you never query on — Indexes only help the columns you actually filter or sort by.

Connection Pooling

Opening a database connection is expensive. It involves DNS resolution, TCP handshake, TLS negotiation, and authentication — all before you send a single query. A typical connection takes 50–200ms to establish. If every API request opens a new connection, your app crumbles under load.

A connection pool solves this by maintaining a set of pre-opened connections. When your code needs to talk to the database, it borrows a connection from the pool, uses it, and returns it. No setup cost, no teardown waste.

How It Works

• Pool size — The number of connections kept open (e.g., 10–20).
• Borrow — Your code requests a connection; the pool hands one over instantly.
• Return — After the query, the connection goes back to the pool (not closed).
• Overflow — If all connections are in use, requests wait in a queue or create temporary overflow connections.

Pool Configuration Example

# SQLAlchemy connection pool
from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://user:pass@localhost/mydb",
    pool_size=10,        # Keep 10 connections open
    max_overflow=20,     # Allow 20 extra under heavy load
    pool_timeout=30,     # Wait max 30s for a connection
    pool_recycle=1800,   # Recycle connections every 30 min
    pool_pre_ping=True,  # Test connections before use
)

Migrations

A migration is a versioned script that changes your database schema — adding a table, renaming a column, creating an index. Migrations let you evolve your database the same way you evolve your code: incrementally, reversibly, and tracked in version control.

Why Migrations Matter

• Reproducibility — Any developer can run migrations to get an identical database schema.
• Version control — Schema changes are tracked in git alongside code changes.
• Rollbacks — Each migration has an "up" (apply) and "down" (revert) operation.
• Team collaboration — Multiple developers can evolve the schema without conflicts.

Migration Tools

Migration Example (Alembic)

"""Add email column to users table

Revision ID: a1b2c3d4
Create Date: 2026-04-01
"""
from alembic import op
import sqlalchemy as sa

def upgrade():
    # Apply the change
    op.add_column('users', sa.Column('email', sa.String(200)))
    op.create_unique_constraint('uq_users_email', 'users', ['email'])

def downgrade():
    # Revert the change
    op.drop_constraint('uq_users_email', 'users')
    op.drop_column('users', 'email')

# Generate a migration from model changes
alembic revision --autogenerate -m "Add email column"

# Apply all pending migrations
alembic upgrade head

# Rollback the last migration
alembic downgrade -1

Transactions & ACID

A transaction groups multiple database operations into a single unit. Either all operations succeed, or none of them do. This is critical for data integrity — you never want money to vanish mid-transfer.

ACID Properties

Transaction Example: Money Transfer

-- Transfer $100 from Alice to Bob
BEGIN TRANSACTION;

-- Debit Alice
UPDATE accounts SET balance = balance - 100
WHERE name = 'Alice' AND balance >= 100;

-- Credit Bob
UPDATE accounts SET balance = balance + 100
WHERE name = 'Bob';

-- If both succeeded, make it permanent
COMMIT;

-- If anything failed, undo everything
-- ROLLBACK;

# Same transaction in SQLAlchemy
from sqlalchemy.orm import Session

with Session(engine) as session:
    try:
        alice = session.query(Account).filter_by(name="Alice").one()
        bob = session.query(Account).filter_by(name="Bob").one()

        if alice.balance < 100:
            raise ValueError("Insufficient funds")

        alice.balance -= 100
        bob.balance += 100

        session.commit()  # All or nothing
    except Exception:
        session.rollback()  # Undo everything
        raise

Choosing the Right Database

Don't overthink this. Most applications start with PostgreSQL and add specialized databases as needed. Here's a practical decision guide:

• Structured data with relationships? → PostgreSQL. Users, orders, products, anything with foreign keys.
• Document-like, flexible data? → MongoDB. CMS content, product catalogs with varying attributes, user-generated data.
• Caching or sessions? → Redis. In-memory, blazing fast, perfect for short-lived data.
• Complex relationships matter most? → PostgreSQL (or Neo4j for extreme cases like social graphs).
• Massive scale, simple queries? → DynamoDB or Cassandra. When you need to handle millions of writes per second with simple key-value lookups.
• Full-text search? → PostgreSQL (built-in) or Elasticsearch (dedicated search engine).
• Time-series data? → TimescaleDB (PostgreSQL extension) or InfluxDB.