A quick guide to Jinja syntax for SQL

Suggest Edits

While there are certainly more comprehensive guides to Jinja (we love the direct-from-source Template Designer Documentation from the creators of Jinja2), below is a quick intro to Jinja alongside some cheat sheet of Jinja commands to get started quickly.

Delimiters

The first concept to learn about are delimiters, which tell the Jinja engine that there's some Jinja within them to be parsed.

DelimiterDescription
{% .. %}Statements, like {% for .. %} or {% if .. %} - structures that control the flow of the Jinja execution flow.
{{ .. }}Expressions which control what's actually printed out with Jinja. I.e. {{ a }} will find the value of a, then string replace the reference with the value in the rendered template.

The third delimiter {# comment #} enables commenting in Jinja, but these are largely unnecessary in SQL work.

Control statements

While there is a ton you can do in Jinja, here's a quick cheatsheet of the most useful commands for rendering SQL, along with some sample outputs.

CommandDescriptionSample commandRendered output
{% for .. %}For loop{% for x in ['a', 'b', 'c'] %}
{{ x }},
{% endfor %}		a,

b,

c,
{% if %}If statement{% if True is True %}
Hi
{% endif %}		Hi
{% set .. = .. %Set variables{% set x = 1 %}
{{ x }}		1

Loop variables

Within for loops, you can access a number of loop attributes. When used with SQL, these tend to be useful in referencing previous items in the loop, specifying custom behaviors within the first/last loop, etc. You can find a full list of attributes here, but below are a few key properties we've found useful in writing SQL.

VariableDescriptionSample commandRendered output
loop.firstTrue if first iteration.{%- for col in ['col1, col2'] -%}
{%- if loop.first -%}
select {{ col }},
{%- else -%}
{{ col }},
{%- endif -%}
{%- endfor -%}		select col1, col2,
loop.lastTrue if last iteration.{%- for col in ['col1, col2'] -%}
{%- if loop.last -%}
{{ col }}
{%- else -%}
{{ col }},
{%- endif -%}
{%- endfor -%}		col1, col2

Whitespace formatting

You'll likely find when writing statements that a lot of extra newlines are introduced. Using - in front of the statement delimiters (i.e. {%- or -%}) will remove the newline before or after the statement. Read more on this here.

Jinja tests

You may have noticed in previous statements we used the keyword in (e.g. {% if x in ['a', 'b'] %}. This is an example of a Jinja test. Tests are basically built-in keywords that allow more customized conditional statements. in is probably the most common one, but be aware that there are others. For example:

{% if x is defined %}
...

Here, defined is another example of a built-in Jinja test. This checks if the variable {{ x }} is defined anywhere. Here's a short list of particularly useful ones:

  • defined
  • divisibleby()
  • even and odd
  • integer, number, string to check types
  • upper and lower to check string case

A full list can be found here.

Updated almost 2 years ago