1.4 — SQL

1. What is it?

SQL (Structured Query Language) is the declarative language for querying and manipulating relational data. In AI systems, SQL is where most of the "ground truth" enterprise data lives — customer records, transactions, tickets, orders — the data your RAG pipeline, your agent's tools, and your analytics all eventually touch.

2. Why does it exist?

Before relational databases and SQL, applications managed their own ad hoc file-based or hierarchical data structures, and every application had to reinvent query logic, consistency guarantees, and indexing. SQL and the relational model (Codd, 1970s) standardized this around a small set of powerful ideas: data as tables, relationships via foreign keys, and a declarative query language where you state what you want, not how to fetch it, leaving the query planner to work out an efficient execution.

3. What problem does it solve?

It solves reliable, consistent, queryable storage of structured data with strong guarantees (ACID transactions) — critical whenever an AI system needs to read enterprise ground truth or write results that must be trustworthy (e.g., an agent updating an order status must not leave the database in a half-updated state if it crashes mid-write).

4. How does it work internally?

Declarative query → execution plan

SELECT c.name, SUM(o.total) AS lifetime_value
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at > '2026-01-01'
GROUP BY c.name
ORDER BY lifetime_value DESC
LIMIT 10;

The database doesn't execute this top-to-bottom. The query planner reorders operations (e.g., applying the WHERE filter before the JOIN if that's cheaper), chooses join algorithms (nested loop, hash join, merge join) based on table sizes and available indexes, and produces an execution plan you can inspect with EXPLAIN ANALYZE. Understanding that SQL is declarative — you describe the result, not the algorithm — is the single biggest mental shift for engineers coming from procedural code, and it's why two logically-equivalent queries can have wildly different performance.

Indexes

An index is a separate, sorted data structure (typically a B-tree) that lets the database find matching rows without scanning the whole table. Without an index on orders.customer_id, the join above is a full table scan for every customer — indexes are what make this feasible at scale. The trade-off: every index speeds up reads on that column but slows down writes (the index must be updated on every insert/update) and takes storage.

Transactions and ACID

• Atomicity: a transaction's writes all happen or none do.
• Consistency: constraints (foreign keys, unique constraints) are never violated, even mid-failure.
• Isolation: concurrent transactions don't see each other's uncommitted changes (isolation levels — read committed, repeatable read, serializable — trade strictness for concurrency performance).
• Durability: once committed, a write survives a crash.

This matters directly for AI agents with tool access to a database: if an agent's tool call updates three related rows and the process crashes after the first, ACID transactions are what prevent that from leaving inconsistent state that then poisons every future LLM read of that data.

ORM vs. Core — a situational choice, not a rule

Every example so far uses SQLAlchemy Core (text(), hand-written SQL) — deliberately, because tuned reads (a similarity search, a report with several joins and aggregates) are exactly where you want full control over the query the planner sees. SQLAlchemy also ships an ORM (declarative models with Mapped[] type annotations), which is the better fit for app-owned CRUD objects — the entities your application creates, updates, and deletes as whole records, where mapping rows to typed Python objects reduces boilerplate more than it costs you query control:

from datetime import datetime

from sqlalchemy import ForeignKey, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    """Base class for all ORM-mapped tables in this application."""


class Ticket(Base):
    """A support ticket, owned and mutated by this application as a whole record."""

    __tablename__ = "tickets"

    id: Mapped[int] = mapped_column(primary_key=True)
    tenant_id: Mapped[str] = mapped_column(String(64), index=True)
    status: Mapped[str] = mapped_column(String(32), default="open")
    created_at: Mapped[datetime] = mapped_column(default=datetime.utcnow)
    comments: Mapped[list["TicketComment"]] = relationship(back_populates="ticket")


class TicketComment(Base):
    """A single comment attached to a support ticket."""

    __tablename__ = "ticket_comments"

    id: Mapped[int] = mapped_column(primary_key=True)
    ticket_id: Mapped[int] = mapped_column(ForeignKey("tickets.id"))
    body: Mapped[str]
    ticket: Mapped["Ticket"] = relationship(back_populates="comments")

async def close_ticket(session: AsyncSession, ticket_id: int) -> None:
    """Loads a ticket as a typed object, mutates it, and lets the ORM generate the UPDATE."""
    ticket = await session.get(Ticket, ticket_id)
    ticket.status = "closed"
    await session.commit()

The practical framing for an FDE: reach for the ORM when the code's job is "create/read/update/delete this application entity" (a ticket, a user, a document record) and the convenience of typed objects and relationships outweighs hand-tuning the SQL. Reach for Core/raw parameterized SQL (as in the rest of this chapter and Part 1.5's pgvector queries) when the query itself — its exact joins, its index usage, its ORDER BY embedding <=> ... — is the point, and an ORM's generated SQL would fight you or hide what's actually running. Don't present this as "always use the ORM" or "always use Core" — most production codebases use both, side by side, chosen per query.

Versioned schema migrations with Alembic

Section 8 already states migrations must be versioned and reversible — Alembic is SQLAlchemy's standard migration tool, and it works directly off the declarative models above:

alembic init migrations                 # scaffolds migrations/ and alembic.ini once, per project
alembic revision --autogenerate -m "add tickets and ticket_comments tables"
alembic upgrade head                    # applies every unapplied migration, in order

--autogenerate diffs your Base.metadata (the ORM models) against the database's current schema and writes a migration file for you to review — never apply an autogenerated migration unread, since it can miss things (renamed columns look like a drop + an add) or generate an operation that's unsafe on a large, live table:

"""add tickets and ticket_comments tables

Revision ID: 8f1a2b3c4d5e
"""
from alembic import op
import sqlalchemy as sa

revision = "8f1a2b3c4d5e"
down_revision = None


def upgrade() -> None:
    """Applies this migration: creates the tickets and ticket_comments tables."""
    op.create_table(
        "tickets",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("tenant_id", sa.String(64), nullable=False, index=True),
        sa.Column("status", sa.String(32), nullable=False, server_default="open"),
        sa.Column("created_at", sa.DateTime, nullable=False),
    )
    op.create_table(
        "ticket_comments",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("ticket_id", sa.Integer, sa.ForeignKey("tickets.id"), nullable=False),
        sa.Column("body", sa.Text, nullable=False),
    )


def downgrade() -> None:
    """Reverses this migration: drops both tables, in dependency order."""
    op.drop_table("ticket_comments")
    op.drop_table("tickets")

Every migration having a working downgrade() is what makes "versioned and reversible" (section 8) an actual guarantee rather than an aspiration — it's what lets you roll back a bad schema change in production without a manual, error-prone hand-written ALTER TABLE.

5. Simple mental model

SQL is like ordering from a very literal, very fast librarian: you describe exactly what book you want ("all books by author X published after year Y, sorted by title") and the librarian (query planner) decides the fastest way to find them, using the card catalog (index) if one exists for that criterion, or walking every shelf (table scan) if not.

6. Real-world example

A support-ticket AI assistant needs to answer "what's the status of ticket #4521" by querying the tickets table directly — not by RAG over unstructured documents. This is a common FDE judgment call: structured, precise, frequently-changing data (ticket status, order status, account balance) belongs in SQL queries executed as agent tool calls, not embedded into a vector store where it would go stale and be retrieved fuzzily instead of exactly (see Part 3.5 on when RAG is the wrong tool).

7. Architecture diagram

AI AgentLangGraph

Query Toolparameterized query only

PostgreSQLor other RDBMS

The agent never sees raw SQL — the tool boundary enforces parameterization.

8. Production considerations

• Never let an LLM construct raw SQL that executes directly against production without a validation/allowlist layer — even "read-only" natural-language-to-SQL tools need row-level security or a restricted read replica (Part 9 covers the injection risk in depth).
• Use parameterized queries always — string-formatting values into SQL is the classic injection vector, made worse when the "user input" is LLM-generated.
• Migrations (Alembic, etc.) — schema changes must be versioned and reversible; an AI feature that adds a column without a migration plan breaks reproducibility.
• Use read replicas for analytics/RAG-adjacent read-heavy queries so they don't compete with transactional write load.

9. Common mistakes

• Missing an index on a frequently-filtered/joined column, only discovered when the table grows past a demo-sized dataset.
• N+1 query patterns (looping in application code and issuing one query per item instead of one batched query) — extremely common when an agent tool calls the DB once per item in a loop.
• Using SELECT * in production code, breaking when the schema changes.
• Forgetting that NULL doesn't equal NULL in SQL comparisons (WHERE x = NULL never matches; use IS NULL).

10. Security considerations

• SQL injection: even with an LLM in the loop, if any user-provided or LLM-generated string is concatenated into a query, it's exploitable. Parameterized queries are non-negotiable.
• Natural-language-to-SQL agents are a distinct, serious risk surface: an LLM asked to "answer this question using SQL" can, if unconstrained, generate DROP TABLE or exfiltrate rows outside the intended scope. Restrict such agents to read-only DB roles, specific schemas, and ideally an allowlist of query patterns (Part 9.2 covers this as an "excessive agency" case study).
• Row-level security (RLS) is often the correct enforcement point for multi-tenant data, rather than trusting application code to always filter by tenant_id correctly.

11. Performance considerations

• EXPLAIN ANALYZE before assuming a query is slow "because SQL is slow" — it's almost always a missing index or an unnecessarily large scan.
• Batch operations (INSERT ... VALUES (...), (...), (...)) instead of one query per row from application code.
• Watch for query plans that degrade non-linearly as data grows — a demo with 100 rows tells you nothing about performance at 10 million.

12. Cost considerations

• Inefficient queries scale cost with both compute (managed DB pricing is often usage-based) and downstream LLM cost if inefficient queries are inside a retrieval loop that's called per-agent-step.
• Analytics-heavy ad hoc querying against a production transactional database can force a costly instance upsize that a read replica would have avoided.

13. When to use it

Any time data has a clear relational structure, needs transactional guarantees, or must be queried precisely (exact match, aggregation, joins) rather than semantically.

14. When NOT to use it

• Semantic/fuzzy search over unstructured text (documents, support transcripts) — that's what vector databases and embeddings are for (Part 3.4).
• Extremely high-throughput, simple key-value lookups where a dedicated cache (Redis) or NoSQL store fits the access pattern better.
• Rapidly-changing schema, document-shaped data where enforcing a rigid relational schema fights the data instead of helping.

15. Alternatives and trade-offs

Store	Good for	Weak point
SQL (Postgres, MySQL)	Structured, relational, transactional data	Rigid schema; not built for semantic search
NoSQL (MongoDB, DynamoDB)	Flexible schema, horizontal scale for simple access patterns	Weaker consistency/join support
Vector DB	Semantic similarity search over embeddings	No transactional guarantees, poor for exact/structured queries
Redis	Fast key-value/cache access	Not durable-by-default, not for complex queries

16. Practical Python/code example

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession


async def get_ticket_status(session: AsyncSession, ticket_id: int) -> dict | None:
    """
    Fetches a support ticket's current status by id, using a parameterized query.

    Args:
        session (AsyncSession): An active async SQLAlchemy session.
        ticket_id (int): The ticket's numeric identifier.

    Returns:
        dict | None: The ticket's status fields, or None if not found.
    """
    result = await session.execute(
        text("SELECT id, status, updated_at FROM tickets WHERE id = :ticket_id"),
        {"ticket_id": ticket_id},
    )
    row = result.mappings().first()
    return dict(row) if row is not None else None

Note :ticket_id as a bound parameter, never an f-string — this is true whether the value comes from a human or an LLM tool call.

17. Production-quality example

from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.ext.asyncio import AsyncSession
import logging

logger = logging.getLogger("db_tool")


class TicketLookupTool:
    """An agent-callable tool that looks up ticket status, scoped to one tenant, read-only."""

    def __init__(self, session_factory, tenant_id: str):
        """
        Args:
            session_factory: An async session factory bound to a read-only DB role.
            tenant_id (str): The tenant this tool instance is scoped to, enforced on every query.
        """
        self._session_factory = session_factory
        self._tenant_id = tenant_id

    async def lookup(self, ticket_id: int) -> dict:
        """
        Looks up a ticket's status, enforcing tenant scoping at the query level.

        Args:
            ticket_id (int): The ticket's numeric identifier.

        Returns:
            dict: A structured result the calling agent can reason over, including
                an explicit "found" flag rather than raising on missing data.
        """
        async with self._session_factory() as session:
            try:
                result = await session.execute(
                    text(
                        "SELECT id, status, updated_at FROM tickets "
                        "WHERE id = :ticket_id AND tenant_id = :tenant_id"
                    ),
                    {"ticket_id": ticket_id, "tenant_id": self._tenant_id},
                )
                row = result.mappings().first()
            except SQLAlchemyError:
                logger.exception("ticket lookup failed for ticket_id=%s", ticket_id)
                return {"found": False, "error": "lookup_failed"}

        if row is None:
            return {"found": False}
        return {"found": True, "status": row["status"], "updated_at": str(row["updated_at"])}

Note the tenant_id is enforced in the query itself (defense in depth alongside RLS), the DB role should be read-only, and failures return a structured result rather than raising into the agent loop unhandled.

18. Short exercise

Given a tickets table with columns (id, tenant_id, status, assignee_id, created_at), write the EXPLAIN ANALYZE you'd run to check whether a query filtering by tenant_id and sorting by created_at would benefit from a composite index, and state what index you'd create.

19. Interview questions

• Why is SQL injection still a live risk when an LLM, not a human, is generating the query string?
• Explain the trade-off an index makes, and how you'd decide whether a given column needs one.
• Why would you give a natural-language-to-SQL agent a read-only database role instead of relying on prompt instructions to prevent destructive queries?
• When would you choose the SQLAlchemy ORM over Core (raw parameterized SQL), and vice versa?
• Why must every Alembic migration have a correct downgrade(), not just an upgrade()?

20. FDE/customer scenario

Customer: "We want our AI assistant to be able to answer any question about our sales data by just asking in plain English."

Before reaching for a text-to-SQL agent, ask: what's the actual query surface (a fixed set of report types, or truly open-ended)? Who can see what (row-level security requirements)? What happens if the LLM generates a plausible-looking but wrong query? For most real cases, a constrained set of parameterized query templates the LLM selects between (function/tool calling with a fixed schema) is safer and more reliable than fully open text-to-SQL — this is a recurring FDE pattern: constrain the LLM's degrees of freedom to match the actual risk tolerance.

---

Key takeaways

• SQL's declarative model means performance depends on indexes and the query planner, not the order you write clauses in.
• ACID transactions matter directly for agent tool calls that mutate enterprise data.
• Text-to-SQL agents are a real, distinct security risk surface.
• ORM vs. Core is a situational choice: app-owned CRUD entities fit the ORM; tuned reads and vector queries fit raw parameterized SQL.

Things you should be able to explain

• What an index actually does and its write-side cost.
• Why parameterized queries matter even more with LLM-generated inputs.
• When to reach for the ORM versus Core, and why Alembic migrations need a working downgrade().

Things you should be able to build

• A tenant-scoped, read-only, parameterized SQL tool usable safely by an agent.
• A declarative ORM model with Mapped[] annotations and a reviewed Alembic migration for it.

Common mistakes

• String-concatenated SQL from LLM output.
• Missing indexes discovered only at production scale.
• Trusting prompt instructions instead of DB-level permissions to prevent destructive queries.
• Applying an autogenerated Alembic migration without reading it first.

Recommended next chapter

05-postgresql.md