Embed Reactive SQL in HTML Templates

What is PageQL?

PageQL is an experimental template language and micro Python web framework that allows embedding SQL inside HTML directly. It was inspired by ColdFusion language that allows embedding SQL and Handlebars / Mustache logic-less templates and also HTMX that simplifies web development.

Installation
pip install pageql
pageql data.db templates --create
Start Tutorial Todos Demo

Core Philosophy

PageQL aims to simplify web development involving relational data by allowing direct embedding of SQL-like queries within HTML templates.

✦

Minimal

The language fully embraces SQL and HTML and adds as few features as needed to implement the common 80 % of web site features—lists, forms, CRUD—while leaving power-user cases to extensions

✦

Declarative Focus

While control structures exist, extensive scripting is discouraged to maintain clarity.

✦

Leverages SQL

PageQL relies on the underlying SQL engine for expression evaluation, rather than implementing its own complex expression language.

Key Tags

  • from - Query and loop over database data
  • insert / update / delete - SQL data manipulation commands
  • let - Set variables
  • param - Validate and bind request parameters
  • import - Import modules
  • partial - Create reusable template blocks
  • render - Render imported modules or partials with parameters
  • if / else / ifdef / ifndef - Conditional rendering
  • redirect - HTTP redirects

Example Code

PageQL Example

{%
  param filter default='all' pattern="^(all|active|completed)$";
  -- Ensure the table exists (harmless if already created)
  create table if not exists todos (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      text TEXT NOT NULL,
      completed INTEGER DEFAULT 0 CHECK(completed IN (0,1))
  );
  let active_count    = COUNT(*) from todos WHERE completed = 0;
  let completed_count = COUNT(*) from todos WHERE completed = 1;
  let total_count     = COUNT(*) from todos;
  let all_complete    = (:active_count == 0 AND :total_count > 0)
%}

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>TODOMVC</title>
  <style>
    body { font-family: Arial, sans-serif; margin: 2rem; }
    ul { list-style: none; padding: 0; }
    li { margin-bottom: 0.5rem; }
    li.completed label { text-decoration: line-through; color: #777; }
    .filters { margin-top: 1rem; }
    .filters a { text-decoration: none; margin: 0 0.5rem; }
    .filters a.selected { font-weight: bold; }
    .back-link {
      margin-bottom: 2rem;
      padding-bottom: 1rem;
      border-bottom: 1px solid #ccc;
      font-size: 1.8rem;
      font-weight: bold;
    }
    .back-link a {
      color: #be5028;
      text-decoration: none;
    }
    .code-column {
      overflow-x: auto;
    }
    
    /* Mobile styles */
    @media (max-width: 768px) {
      body { 
        margin: 0; 
        padding: 0;
      }
      .back-link {
        margin-bottom: 1rem;
      }
    }
  </style>
</head>
<body>
  <div class="back-link"><a href="/">&larr; PageQL</a> <span style="font-size: 1.8rem; font-weight: bold; color:rgb(40, 170, 190);">TODOMVC</span></div>
  <div>
      {%if :total_count < 20%}
      <input
        name="text"
        placeholder="What needs to be done?"
        maxlength="100"
        autofocus
        autocomplete="off"
        hx-post="/todos/add"
        hx-trigger="keyup[key=='Enter']"
        hx-on:htmx:after-on-load="this.value=''">
      {%end if%}
      <ul>
        {%from todos
          WHERE (:filter == 'all')
                OR (:filter == 'active'    AND completed = 0)
                OR (:filter == 'completed' AND completed = 1)
          %}
            <li {%if completed%}class="completed"{%end if%}>
                <input
                  hx-post="/todos/{{id}}/toggle"
                  class="toggle"
                  type="checkbox"
                  {%if completed%}checked{%end if%}>
                <label
                  contenteditable="false"
                  onclick="this.contentEditable=true;this.focus();"
                  onblur="this.contentEditable=false;"
                  onkeydown="if(event.key==='Enter'){event.preventDefault();this.blur();}"
                  oninput="if(this.innerText.length>100){this.innerText=this.innerText.slice(0,100);}"
                  hx-patch="/todos/{{id}}"
                  hx-trigger="blur"
                  hx-vals='js:{text: event.target.innerText.slice(0, 100)}'
                  hx-swap="none"
                >{{text}}</label>

                <button
                  hx-delete="/todos/{{id}}"
                  class="destroy"
                  style="cursor:pointer; background:none; border:none; color:#ac4a1a;">✕</button>
            </li>
        {%end from%}
      </ul>

  <input
    id="toggle-all"
    class="toggle-all"
    type="checkbox"
    {%if all_complete%}checked{%end if%}
    hx-post="/todos/toggle_all">
  <label for="toggle-all">Mark all as complete</label>

<span class="todo-count">
  <strong>{{active_count}}</strong>
  item{%if :active_count != 1%}s{%end if%} left
</span>


{%if :completed_count > 0%}
  <button
    class="clear-completed"
    hx-post="/todos/clear_completed">Clear completed</button>
{%end if%}
<div class="filters">
  <a {%if :filter == 'all'%}class="selected"{%end if%} href="/todos?filter=all">All</a> |
  <a {%if :filter == 'active'%}class="selected"{%end if%} href="/todos?filter=active">Active</a> |
  <a {%if :filter == 'completed'%}class="selected"{%end if%} href="/todos?filter=completed">Completed</a>
</div>
</div>
  <h2 style="color:rgb(50, 56, 86); margin-top: 2rem; margin-bottom: 0.5rem;">Source code</h2>
{%showsource%}
</body>
</html>
{%
partial post add;
   param text maxlength=100;
   let current_total = COUNT(*) from todos;
   if :current_total < 20;
     insert into todos(text) values (:text);
   end if;
end partial;

partial post :id/toggle;
  update todos set completed = 1 - completed WHERE id = :id;
end partial;

partial patch :id;
  param text maxlength=100;
  -- Update todo text
  update todos set text = :text WHERE id = :id;
end partial;

partial post toggle_all;
  let active_count = COUNT(*) from todos WHERE completed = 0;
    -- Set all todos completed state based on active count
    update todos set completed =  IIF(:active_count = 0, 0, 1);
end partial;

partial delete :id;
  delete from todos WHERE id = :id;
end partial;

partial post clear_completed;
  delete from todos WHERE completed = 1;
end partial
%}

Future plans

PageQL was built with a simple core language but lots of extensibility in mind

✦

Embeddable, simple, performant

While the current implementation is in Python, the language is designed to be simple enough to be reimplemented in any language (C / C++ / Rust / JavaScript...)

✦

Reactivity

PageQL now includes a relational reactive mode. Because both HTML and SQL are declarative, it's much easier to reason about than if an imperative language was used as glue.

✦

Client based, offline mode with SQL database automatic syncing.

Lots of web frameworks get very complex when client and server code is mixed. PageQL solves it by creating a simple SQL change synchronization protocol as an orthogonal project. With reactive SQL updates now built in, and an SQL database as the main data store, the cognitive overhead remains low with this approach.

✦

Mustache / Handlebars / Jinja like templating API

As PageQL combines a micro web framework with a templating engine, templating can be used directly as well from other frameworks, it's just not documented yet.

✦

Advanced routing, importing modules, authentication, authorization, production deployment, optimization

As PageQL is a micro framework / experiment, right now it's for hobbyist projects and feedback.