Templating SQL with Jinja — what actually got better

I started rendering SQL through Jinja because my queries were becoming unreadable string-building messes. Here's what the switch looked like in practice.

Somewhere around the fifth optional filter on the same query, I stopped pretending that building SQL with string concatenation was "fine for now." The function was 70 lines long, half of it was if blocks gluing fragments together, and I had to run the thing and print the output just to verify the query was syntactically valid.

So I moved the SQL into .sql files and rendered them with Jinja. That's the whole idea. The rest of this post is what that looked like in practice — what got better, what I had to be careful about, and where the approach has limits.

The kind of code I was replacing

This was a real function (names changed, logic preserved):

def get_orders(
    status: str | None = None,
    start_date: datetime | None = None,
    customer_ids: list[str] | None = None,
    limit: int = 100,
):
    query = "SELECT * FROM orders WHERE 1=1"
    params: list = []

    if status:
        query += " AND status = %s"
        params.append(status)

    if start_date:
        query += " AND created_at >= %s"
        params.append(start_date)

    if customer_ids:
        placeholders = ", ".join(["%s"] * len(customer_ids))
        query += f" AND customer_id IN ({placeholders})"
        params.extend(customer_ids)

    query += f" ORDER BY created_at DESC LIMIT {limit}"

    return cursor.execute(query, params).fetchall()

This pattern has a few things working against it:

  • The SQL is invisible. You can't read it as a standalone query, can't paste it into a database client, can't get syntax highlighting. It only exists after Python finishes assembling it at runtime.
  • WHERE 1=1 is a code smell and everyone knows it. It's there because nobody wants to track whether they're emitting the first predicate. The whole function is a string-builder wearing a business-logic costume.
  • IN lists are the worst part. ", ".join(["%s"] * len(customer_ids)) on one side, params.extend(customer_ids) on the other — and the two have to agree on length. When they don't, you get a not enough arguments for format string at 2am.
  • Table names can't be parameterized. SQL drivers bind values, not identifiers. If you need to point the same query at orders_staging vs orders, you end up with f"FROM {table}", which is exactly where security scanners start complaining.

The setup: a small SQL loader

The Jinja side is a thin wrapper. Point it at a directory of .sql files, render by relative path:

from collections.abc import Mapping
from pathlib import Path
from typing import Any

from jinja2 import Environment, FileSystemLoader, StrictUndefined


class SqlLoader:
    """Loads and renders SQL files using Jinja templates."""

    def __init__(self, base_dir: Path | str) -> None:
        self.base_dir = Path(base_dir)
        self.env = Environment(
            loader=FileSystemLoader(str(self.base_dir)),
            undefined=StrictUndefined,
            trim_blocks=True,
            lstrip_blocks=True,
            autoescape=False,  # SQL, not HTML — see the safety section below
        )

    def render(
        self, template_path: str, variables: Mapping[str, Any] | None = None
    ) -> str:
        template = self.env.get_template(template_path)
        return template.render(**(variables or {}))

A few constructor choices worth explaining:

StrictUndefined means a missing variable raises an exception instead of silently rendering as an empty string. Without it, a typo in a variable name gives you a query that quietly returns everything. I learned this one the fun way.

trim_blocks / lstrip_blocks clean up the whitespace around {% if %} and {% for %} blocks. Without them, the rendered SQL is full of blank lines and random indentation — fine for the database, annoying for anyone reading the logs.

autoescape=False is intentional. Jinja's default escaping targets HTML and would turn > into > inside a WHERE created_at > .... SQL injection prevention works differently here — more on that below.

What the query looks like now

The SQL moves into its own file with proper syntax highlighting:

-- queries/orders/list.sql
SELECT
  order_id,
  customer_id,
  status,
  created_at,
  total_amount
FROM {{ orders_table }}
WHERE 1 = 1
{% if status %}
  AND status = {{ status }}
{% endif %}
{% if start_date %}
  AND created_at >= {{ start_date }}
{% endif %}
{% if customer_ids %}
  AND customer_id IN (
    {% for cid in customer_ids %}{{ cid }}{% if not loop.last %}, {% endif %}{% endfor %}
  )
{% endif %}
ORDER BY created_at DESC
LIMIT {{ limit }}

And the Python shrinks to the boring part:

sql = loader.render("orders/list.sql", {
    "orders_table": settings.orders_table,
    "status": status,
    "start_date": start_date,
    "customer_ids": customer_ids,
    "limit": limit,
})
cursor.execute(sql, parameters)

I'll get to where the actual values should go in a moment — because that's where the safety story lives.

What improved

Optional clauses read like SQL, not like Python

The if-blocks-gluing-strings pattern is gone. The template is the conditional structure:

WHERE 1 = 1
{% if status %}
  AND status = {{ status }}
{% endif %}
{% if start_date %}
  AND created_at >= {{ start_date }}
{% endif %}

Someone reviewing this sees which predicates are optional at a glance. And the rendered SQL is copy-pasteable — you can grab it from the logs, drop it into your database client, and debug. That round-trip alone justified the switch for me.

IN lists stopped being a puzzle

The Jinja version of an IN list is small enough that I stopped dreading it:

WHERE customer_id IN (
  {% for cid in customer_ids %}
    {{ cid }}{% if not loop.last %}, {% endif %}
  {% endfor %}
)

No ", ".join(["%s"] * len(...)). No keeping the params list in sync. loop.last handles separators, which is built into Jinja's for-loop. If the list might be empty, you wrap the whole block in {% if customer_ids %} and the entire predicate vanishes.

Table and schema names became configurable

Since SQL drivers won't let you bind identifiers as parameters (and shouldn't), swapping table names used to mean f-strings or environment-variable branching. Now it's:

FROM {{ orders_table }}

Where orders_table comes from your app config or settings object — not from user input. The boundary is clear: identifiers come from code you control, values come from parameterized queries.

Shared fragments via macros

Once queries live in files, you can import common patterns the way you'd import functions in Python:

-- _macros/in_list.sql
{% macro in_list(values) %}
  {% for v in values %}{{ v }}{% if not loop.last %}, {% endif %}{% endfor %}
{% endmacro %}

{% from "_macros/in_list.sql" import in_list %}
WHERE customer_id IN ({{ in_list(customer_ids) }})

Same idea works for soft-delete filters, tenant-scoping predicates, audit-column projections — the things that used to get copy-pasted between query functions.

The safety model

Any post about SQL templating that doesn't address injection would be irresponsible. Here's the discipline.

Template the shape. Parameterize the values. Jinja controls structure — which tables, which clauses, how many items in an IN list. The database driver binds the values. In practice, the template renders placeholders rather than literals:

WHERE status = %(status)s
  AND created_at >= %(start_date)s
  AND customer_id IN (
    {% for i in range(customer_ids|length) %}
      %(customer_id_{{ i }})s{% if not loop.last %}, {% endif %}
    {% endfor %}
  )

And Python builds the matching parameter dict:

params = {"status": status, "start_date": start_date}
params.update({f"customer_id_{i}": cid for i, cid in enumerate(customer_ids)})

sql = loader.render("orders/list.sql", {
    "orders_table": settings.orders_table,
    "customer_ids": customer_ids,  # only used for the for-loop length
})
cursor.execute(sql, params)

More bookkeeping than inlining {{ status }} directly? Yes. But now the value's path is: Python variable → driver parameter → bound by the database engine. No string concatenation. No way for a malicious value to escape its placeholder.

Identifiers come from config, not from requests. {{ orders_table }} is safe when orders_table comes from a YAML config that ships with your deployment. It's a vulnerability when it comes from a request header. Treat identifier templating as a privileged operation.

On the S608 lint warning. Bandit and Ruff's security rules will flag cursor.execute(rendered_sql) with S608 — potential SQL injection via string-based query. The linter can't tell statically that your template only emitted placeholders. I wrap the loader in a thin function whose docstring explains the safety invariant and suppress the warning there, once. Better than scattering # noqa: S608 across every call site.

When this is more trouble than it's worth

For a one-file script with two queries that never change, this is ceremony for ceremony's sake. The approach starts paying off when you hit a few of these:

  • Queries with more than one optional clause
  • IN lists or other variadic constructs showing up in multiple queries
  • Multiple environments where identifiers differ but query logic doesn't
  • Teammates or analysts who'd benefit from reading the SQL outside of Python

Below that threshold, an ORM or carefully parameterized f-strings are fine.

Two honest downsides: you lose the static analysis that a typed query builder gives you — the template renders a string, and only the database can tell you if you misspelled a column. And StrictUndefined plus optional variables means you'll write {% if foo is defined %} more than you'd expect. Neither is a dealbreaker, but they're worth knowing about upfront.

Wrapping up

Moving SQL into Jinja templates didn't make my queries faster or smarter. It moved the complexity from being tangled inside Python string operations into standalone files that read top-to-bottom as SQL. The queries became reviewable, diffable, and copy-pasteable into a database client without reconstruction.

The first time I added a new filter by editing only the .sql file — no Python changes — I knew the setup had earned its keep.