Tera

What you can do with one query

The single most useful pattern: render one transactional email body per row, directly inside the query that selected the recipients.

SELECT email,
       tera_render(
         '<h1>Hi {{ name }}!</h1>
          <p>You have {{ unread }} new messages.</p>',
         json_object('name', name, 'unread', unread_count)
       ) AS html
FROM users
WHERE unread_count > 0;

tera_render compiles the template once, binds json_object(...) as context for each row, and returns the rendered string as a regular VARCHAR column. Anywhere SQL gives you a string column, this extension can fill it with templated text — emails, config blocks, dynamic SQL, Slack JSON, IaC fragments.

Inline templates vs files on disk

By default, the first argument to tera_render is the template body itself — handy for short templates that live inside the SQL.

For non-trivial templates, pass template_path := '<glob>' and use a template name:

SELECT tera_render(
  'email.html',
  json_object('name', name, 'unread', unread_count),
  template_path := './templates/*.html'
)
FROM users;

The files are read and parsed once per query (per worker thread), not once per row, and inline templates are likewise parsed once and reused for every row that uses the same template text.

Loading from disk is what unlocks Tera’s {% extends %} / {% include %} / {% import %} constructs — you can’t extends "base.html" against a template that has no name. Authoring large templates in a .html file alongside the rest of your project is also nicer than embedding them as SQL string literals.

Autoescape — on by default

Tera escapes HTML special characters in interpolated context values by default, which is the right default for HTML output:

SELECT tera_render('{{ v }}', '{"v":"B&O"}');
-- 'B&O'

Turn it off when you’re rendering anything that isn’t HTML — config files, SQL, Slack JSON, plain text — otherwise apostrophes and ampersands come out as ' and &:

SELECT tera_render('{{ v }}', '{"v":"B&O"}', autoescape := false);
-- 'B&O'

The same default applies to templates loaded with template_path, whatever their file extension. To escape only some files — say, .html templates but not .txt ones in the same glob — list the suffixes in autoescape_extensions:

SELECT tera_render('welcome.txt', json_object('name', name),
                   template_path := './templates/*',
                   autoescape_extensions := ['.html'])
FROM users;
-- welcome.txt renders unescaped; any .html template in the glob is escaped

Options

On DuckDB 2.0 the options are native named parameters, declared keyword-only with typed defaults. Both overloads share them:

tera_render(template VARCHAR, *,
            autoescape BOOLEAN := true,
            template_path VARCHAR := '',
            autoescape_extensions VARCHAR[] := [])

tera_render(template VARCHAR, context JSON, *, ...same options...)

• Keyword-only. Options are always passed as name := value; tera_render('Hello', '{}', false) does not match any signature. The positional arguments can be named too, in any order: tera_render(context := '{"a": 1}', template := '{{ a }}').
• Typed. DuckDB casts the value to the declared type when it can do so implicitly — string literals included, so autoescape := 'false' works — while autoescape := 1 or a misspelled name such as autoescap := false fails with DuckDB’s standard No function matches the given name and argument types error.
• Constant. Options are read when the query is bound, so they must be constant expressions; per-row values belong in the context.
• NULL in, NULL out. A NULL template, context, or option (autoescape := NULL) returns NULL. A NULL element inside autoescape_extensions is an error.

SELECT tera_render('{{ v }}', '{"v": "B&O"}', autoescape := 'false');
-- 'B&O'

Treat templates as trusted code

Tera interpolation is not an injection-safe parameterization mechanism. A context value that contains {{ ... }} or {% ... %} syntax won’t be re-evaluated, but a template body assembled from untrusted input absolutely can render arbitrary SQL or shell strings. Keep the template surface fixed and authored by trusted code; only the context (the second argument) should come from query data or user input.

For SQL specifically: use Tera to template the shape of a query (column lists, table names from a metadata table, predicates from a config), and use ordinary parameter binding for any value that originated outside your control.

What’s in the box

This is a deliberately small extension — one function, three optional named arguments, the full Tera language available inside the templates. See the Tera manual for the template language itself; recipes for common SQL-side patterns live in the Cookbook.

Variable substitution

SELECT tera_render('{{ foo }}', '{"foo":"bar"}');           -- 'bar'

SELECT tera_render('Hello, World!');                        -- 'Hello, World!'

Nested context

SELECT tera_render(
  'Hello {{ user.name }}, you have {{ user.messages }} new messages',
  '{"user":{"name":"Alice","messages":5}}'
);

Loops

SELECT tera_render(
  '{% for x in items %}{{ x.name }}{% if not loop.last %}, {% endif %}{% endfor %}',
  '{"items":[{"name":"Apple"},{"name":"Banana"},{"name":"Cherry"}]}'
);
-- 'Apple, Banana, Cherry'

Autoescape

-- Default: autoescape on, HTML special chars are escaped
SELECT tera_render('{{ v }}', '{"v":"B&O"}');
-- 'B&O'

-- Off when generating non-HTML
SELECT tera_render('{{ v }}', '{"v":"B&O"}', autoescape := false);
-- 'B&O'

Render template files from disk

Pass a glob with template_path and use template names instead of inline strings:

SELECT tera_render(
  'index.html',
  '{"v":"B&O"}',
  autoescape := false,
  template_path := './templates/*.html'
);
-- 'B&O' (file templates are HTML-escaped by default, like inline ones)

This is the killer feature over inline-only engines — separate authoring of templates from queries that render them.

Per-row HTML email

SELECT email,
       tera_render(
         '<h1>Hi {{ name }}!</h1><p>{{ unread }} new messages.</p>',
         json_object('name', name, 'unread', unread_count)
       ) AS html
FROM users
WHERE unread_count > 0;

Generate per-host config

SELECT tera_render(
  'server { listen 80; server_name {{ host }}; proxy_pass http://{{ upstream }}; }',
  json_object('host', hostname, 'upstream', upstream)
) AS nginx_block
FROM virtual_hosts;

Tera vs MiniJinja

minijinja is the other Jinja-style engine in this catalog. Both speak the same dialect for everyday use. Pick Tera when you want template-file inclusion (template_path); pick MiniJinja when you want maximum Python-Jinja compatibility on the filter set. For brand-new projects, either is a defensible choice.