pg_render

Render engine for PostgreSQL — render HTML in SQL

pg_render is a PostgreSQL extension that lets you render Liquid templates with data from your queries. Generate HTML pages, emails, or any templated content directly in SQL. 

select render(
  '<html>
    <head>
      <title>{{ title }}</title>
    </head>
    <body>
      <h1>{{ title }}</h1>
      <p>{{ text }}</p>
      <strong>{{ author }}</strong>
    </body>
  </html>',
  (select to_json(props) from (
    select title, text, author
    from posts where id = 1
  ) props)
);
→ 
<html>
  <head>
    <title>My First Post</title>
  </head>
  <body>
    <h1>My First Post</h1>
    <p>Hello world!</p>
    <strong>Jane Doe</strong>
  </body>
</html>

Installation

Download a pre-built PostgreSQL extension package from Releases for your PostgreSQL version (13–18). 

# Ubuntu / Debian with PostgreSQL 18
wget https://github.com/mkaski/pg_render/releases/download/v0.1.3/pg_render-v0.1.3-pg18-amd64-linux-gnu.deb
dpkg -i pg_render-v0.1.3-pg18-amd64-linux-gnu.deb

Build from source

git clone https://github.com/mkaski/pg_render
cargo install --locked cargo-pgrx
cargo pgrx init
cargo pgrx run

Usage

-- PostgreSQL extension
create extension pg_render;

-- Serve /index using PostgREST
create function api.index() returns "text/html" as $$
select render(
  '<html>
    <head><title>{{ title }}</title></head>
    <body>
      <h1>{{ title }}</h1>
      <p>{{ text }}</p>
      <strong>{{ author }}</strong>
    </body>
  </html>',
  (select to_json(props) from (select title, text, author from posts where id = 1) props)
)
$$ language sql;

Docs

render(template, input)

Render a template with query results as context. Input can be a scalar value, a JSON object, or an array.

-- scalar value
select render('Total posts: {{ value }}', (select count(*) from posts));

->

Total posts: 42
-- row data
select render(
    '{{ title }} by {{ author }}',
    (select to_json(props) from (select title, author from posts where id = 1) props)
);

->

My First Post by Jane Doe
-- array
select render(
    '{% for value in values %} {{ value }} {% endfor %}',
    (select array(select title from posts))
);
-- saved template
select render(
    (select template from templates where id = 'post'),
    (select to_json(props) from (select title, author from posts where id = 1) props)
);

render_agg(template, input)

An aggregate function that renders a template per row and concatenates the results.

select render_agg('<li>{{ value }}</li>', title) from posts;
select render_agg(
    '<article><h2>{{ title }}</h2><p>{{ text }}</p></article>',
    props
) from (select title, text from posts) as props;

Template syntax

pg_render uses the Liquid templating language. Templates support variables, control flow, loops, and filters.

{{ title }}
{{ title | upcase }}
{{ content | truncate: 100 }}
{% if image_url != blank %}
    <img src="{{ image_url }}">
{% endif %}
{% for row in rows %}
    <li>{{ row.title }}</li>
{% endfor %}

See the full Liquid documentation for all available tags and filters.

Usage with PostgREST

pg_render works well with PostgREST to serve HTML pages directly from PostgreSQL.

Create a domain so PostgREST sets the correct Content-Type header:

create domain "text/html" as text;

Create a function that renders your page:

create function index() returns "text/html" as $$
    select render(
        (select template from templates where id = 'layout'),
        json_build_object(
            'title',   'My Site',
            'content', (select render_agg(
                (select template from templates where id = 'post'),
                props
            ) from (select title, text from posts) as props)
        )
    );
$$ language sql;
# nginx.conf
location = / {
    proxy_pass http://postgrest/rpc/index;
}

Examples

This is the pattern used to build this page and other pages