Skip to content
DuckDB Extensions All extensions Tera

Tera

Render Tera templates from SQL — Rust's Jinja2-style templating engine.

96,431
extension loads · last 90 days
On this page

Technical Overview

One rendered string per row, without leaving SQL

Render Tera templates inside SQL with JSON context. Tera is a Jinja2-inspired template engine written in Rust; this extension makes it a scalar function so each query row can produce its own rendered string — emails, config blocks, dynamic SQL, Slack payloads, IaC fragments — without leaving DuckDB.

What it is

A single scalar function, tera_render, that turns the full Tera template language into something you can call mid-query. The pattern it replaces is familiar: you SELECT rows out of DuckDB only to feed them into a template loop in Python or Node, then ship the rendered strings somewhere. Here the loop collapses into one SQL projection — the template stays declarative, the data stays in the query, and the rendered text comes back as a regular VARCHAR column you can write, insert, or pass to another extension.

How it works

Each call compiles (or reuses a cached) template, binds a JSON value as the rendering context, and returns the rendered text. It is backed by the Tera Rust crate and is purely function-based — no ATTACH, no secrets, no virtual catalog. The non-obvious parts are worth knowing up front.

  • • Templates are cached: A template body is compiled once and reused across the rows of a query rather than re-parsed per row, which is why a single tera_render over a large table stays cheap. Inline template literals and named templates loaded from a template_path glob both go through the same compile-and-cache path.
  • • Autoescape defaults to HTML-safe: HTML special characters in context values are escaped by default — correct for HTML and email, but it turns ampersands and apostrophes into & / ' when you're emitting configs, SQL, or JSON. Disable it for non-HTML output. See Tera's autoescape behavior.
  • • Interpolation is not parameter-safe: Tera substitution is not an injection-safe parameterization mechanism. A template body assembled from untrusted input can render arbitrary SQL or shell strings. Keep the template surface fixed and authored by trusted code; only the JSON context should carry query data or user input, and bind any externally-sourced value as a real query parameter.

Tera vs MiniJinja

This catalog ships two Jinja-style template engines as DuckDB extensions: Tera and minijinja. They overlap heavily — both render {{ var }} / {% for %} / {% if %} against JSON context. The differences are real but small.

  • • Pick Tera for the bigger feature surface: Tera ships a wider built-in filter and tester library, template inheritance via template_path, and macros — choose Tera when your templates lean on Jinja2's richer authoring features.
  • • Pick MiniJinja for speed and Jinja2 fidelity: MiniJinja targets strict Python-Jinja2 semantics with a tiny, fast core. Choose minijinja when you want behavior that matches an existing Python Jinja codebase, or when render-rate per row matters most.
  • • Either is fine for everyday substitution: For {{ name }} / {{ x | upper }} / {% for x in xs %}, both produce identical output. Lock in whichever syntax matches your team's existing templates.

Deep Dive

Technical Details

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.

Tera vs MiniJinja — pick once, stay consistent

This catalog ships two Jinja-style template engines. They overlap heavily on everyday syntax ({{ var }} / {% for %} / {% if %} / common filters); the differences only matter at the edges.

  • Pick Tera for the larger feature surface — more built-in filters and testers, template inheritance via template_path, and macros. Best when your templates lean on Jinja2’s richer authoring features.
  • Pick minijinja for strict Python-Jinja2 fidelity and faster per-row rendering. Best when you’re matching an existing Python Jinja codebase or render rate per row matters most.

For brand-new projects with simple substitution-and-loops templates, either is a defensible choice — lock in whichever your team already knows.

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;

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&amp;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 &#39; and &amp;:

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

Function Purpose
tera_render Render an inline template, or a named template from a template_path glob, with JSON context. The full extension surface.

This is a deliberately small extension — one function, two 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.

Install

INSTALL tera FROM community;
LOAD tera;

Quick Start

Variable substitution

SELECT tera_render('Hello {{ name }}!', '{"name":"World"}');

For loop

SELECT tera_render(
  '{% for x in xs %}{{ x }}{% if not loop.last %}, {% endif %}{% endfor %}',
  '{"xs":["A","B","C"]}'
);

Render a template file from disk

SELECT tera_render('email.html', '{"name":"Alice"}', template_path := './templates/*.html');

Reference

Extension Contents

Quick reference to all available functions and settings organized by category.

Name Description
Render

Render Tera templates with JSON context. Single-function surface; the optional template_path argument lets you load templates from disk by name rather than embedding them inline.
tera_render() Render a Tera template with JSON context.

API Reference

Function Documentation

Practical Examples

Cookbook

Real-world recipes and patterns for common use cases.

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&amp;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'
);

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.

Platform Support

Compatibility

Extension availability may vary by platform and DuckDB version. Check below to ensure this extension supports your environment before installation.

Quick Facts

Release status Stable
Software License MIT
Pricing Free
Written In Rust
Source Available Yes
View on GitHub
Usage
96,431
loads · last 90 days

Platforms

  • Linux x86_64 aarch64
  • Linux (musl) Not available
  • macOS Intel Apple Silicon
  • Windows x86_64
  • WASM eh mvp threads
Compiled binary sizes
Platform Architecture Size
Linux x86_64 5.43 MB
Linux aarch64 4.97 MB
macOS Intel 3.32 MB
macOS Apple Silicon 2.93 MB
Windows x86_64 8.54 MB
WASM eh 577.9 KB
WASM mvp 964.0 KB
WASM threads 828.8 KB

Compressed download size from the Haybarn extension repository.

DuckDB & Haybarn

Release calendar