📝

Tera

Render Tera templates from SQL — Rust's Jinja2-style templating engine. Variable substitution, filters, loops, conditionals, autoescape, and template file inclusion driven by JSON context.

Browse Contents

Install

-- Install the extension
INSTALL tera FROM community;

-- Load it into your session
LOAD tera;

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

Technical Overview

Why Use Tera from DuckDB?

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 this extension is for

Anywhere you'd otherwise pull rows out of DuckDB just to feed them into a template loop in Python or Node, tera_render collapses that loop into a single SQL projection. The template stays declarative; the data stays in the query.

• Per-row HTML / email: Build the body of a transactional email per row: pass the row's columns as a JSON object via json_object and render against a template literal.
• Config-file generation: Materialize one nginx / systemd / Kubernetes manifest per row of a configuration table. Disable autoescape with autoescape := false for non-HTML output.
• Dynamic SQL: Use Tera as a meta-SQL preprocessor — render SELECT ... FROM {{ table }} WHERE {{ pred }} against context columns, then execute the result. Treat templates as trusted; Tera interpolation is not parameter-safe.
• Notifications and webhook payloads: Render Slack / PagerDuty / webhook JSON bodies from query results so a single SQL pipeline takes you from event to delivered message.

⚙️ How it works

A scalar function backed by the Tera Rust crate. Each call compiles (or reuses a cached) template, binds the JSON context, and returns the rendered text. There is no ATTACH, no secrets — the function surface is one name and a few optional named arguments.

• Inline and file-backed templates: The first argument is either an inline template string or a template name. Pass template_path := './templates/*.html' to load templates from disk by name; this enables {% extends %} / {% include %} across the matched files. See Tera's inheritance docs.
• JSON context: The second argument is a JSON value — a literal string, json_object(...), or a column. Nested objects, arrays, and primitives are all addressable as {{ user.name }}, {% for x in items %}, {{ items | length }}.
• Autoescape on by default: HTML special characters in context values are escaped — safe default for HTML / email output. Pass autoescape := false when generating raw text (configs, SQL, JSON payloads).
• Full Tera vocabulary: Filters (upper, lower, length, default, join, date, truncate, etc.), control flow (if / elif / else, for with loop.index / loop.last), inheritance, macros, and includes — see the Tera manual for the full surface.

🤝 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.

🎯 Common Use Cases

Render transactional emails per row

One row per recipient, one tera_render call producing the HTML body — fed straight into a downstream sender.

Generate per-host configuration

A virtual_hosts table plus a single Tera template equals one rendered nginx / Caddy / Envoy block per host, ready to write to disk or push to a config repo.

Build dynamic SQL from metadata

Drive SELECT / CREATE TABLE shape from a metadata table — Tera renders the SQL, your driver executes it. Useful for codegen, schema migrations, and templated reporting.

Format Slack / webhook payloads

Render alert JSON bodies directly from the query that detected the alert. Pair with http_client to deliver them in the same pipeline.

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 ' and &:

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.

API Reference

Function Documentation

Detailed documentation for each function including signatures, parameters, and examples.

tera_render

Scalar Function Render

Signature

tera_render(template: VARCHAR, varargs: ANY) → VARCHAR

Parameters (Positional)

Parameter	Type	Mode	Description
`template`	`VARCHAR`	Positional
`varargs`	`ANY`	Positional	Varargs

Returns

Description

Render a Tera template with JSON context. Supports the full Tera vocabulary: variables, filters, conditionals, for loops, autoescape, and inclusion. Optional named arguments tune behavior — autoescape := false for non-HTML output, template_path := './templates/*.html' to load templates from disk by name.

Examples

Inline variable

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

Nested context

SELECT tera_render('Hello {{ user.name }}', '{"user":{"name":"Alice"}}');

Disable autoescape

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

Render a named template file

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

Back to Extension Contents

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

Software License MIT

Pricing Free

Written In Rust

Source Available Yes

View on GitHub

Usage

34,366+

loads in last 30 days

Platforms

Linux

Linux (musl)

macOS

Windows

WASM

Supported platform architectures

Linux: aarch64, x86_64

Linux (musl): x86_64

macOS: Intel, Apple Silicon

Windows: x86_64

WASM: mvp, threads, eh

Compiled binary sizes

Platform	Architecture	Size
Linux	aarch64	4.86 MB
Linux	x86_64	5.34 MB
Linux (musl)	x86_64	5.18 MB
macOS	Apple Silicon	3.05 MB
macOS	Intel	3.46 MB
Windows	x86_64	8.69 MB
WASM	eh	606.3 KB
WASM	mvp	604.1 KB
WASM	threads	603.9 KB

Gzipped download size from the DuckDB community-extensions registry.

DuckDB Versions

Release calendar

Supported

v1.4.4 v1.5.2

Tera Templates From SQL

Install Tera to render templated text from JSON context — dynamic config, HTML, notifications, IaC. Sister of MiniJinja with template-file support.

Install Now View Other Extensions

Tera

Install

Why Use Tera from DuckDB?

📝 What this extension is for

⚙️ How it works

🤝 Tera vs MiniJinja

🎯 Common Use Cases

Render transactional emails per row

Generate per-host configuration

Build dynamic SQL from metadata

Format Slack / webhook payloads

Technical Details

What you can do with one query

Inline templates vs files on disk

Autoescape — on by default

Treat templates as trusted code

What’s in the box

Function Documentation

tera_render

Signature

Parameters (Positional)

Returns

Description

Examples

Cookbook

Variable substitution

Nested context

Loops

Autoescape

Render template files from disk

Per-row HTML email

Generate per-host config

Tera vs MiniJinja

Compatibility

Quick Facts

Platforms

DuckDB Versions

Related Extensions

MiniJinja

JSONata

Tera Templates From SQL