PageQL - 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


{{#partial post add}}
  {{#insert into todos(text) values (:text)}}
{{/partial}}

{{#partial post :id/toggle}}
  {{#update todos set completed = 1 - completed WHERE id = :id}}
{{/partial}}

{{#partial patch :id}}
  {{#update todos set text = :text WHERE id = :id}}
{{/partial}}

{{#partial post toggle_all}}
  {{#let active_count = COUNT(*) from todos WHERE completed = 0}}
  {{#update todos set completed =  IIF(:active_count = 0, 0, 1)}}
{{/partial}}

{{#partial delete :id}}
  {{#delete from todos WHERE id = :id}}
{{/partial}}

{{#partial post clear_completed}}
  {{#delete from todos WHERE completed = 1}}
{{/partial}}

{{#param edit_id optional}}
{{#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>Todos</title>
</head>
<body>
  <h1>Todos</h1>
  <input name="text" placeholder="What needs to be done?" autofocus autocomplete="off"
    hx-post="/todos/add" hx-trigger="keyup[key=='Enter']" hx-on:htmx:after-on-load="this.value=''">
  <ul>
{{#from todos
  WHERE (:filter == 'all')
        OR (:filter == 'active'    AND completed = 0)
        OR (:filter == 'completed' AND completed = 1)
  ORDER BY id}}
    <li {{#if completed}}class="completed"{{/if}}>
        <input hx-post="/todos/{{id}}/toggle" class="toggle" type="checkbox" {{#if completed}}checked{{/if}}>
        <label
          contenteditable="false"
          onclick="this.contentEditable=true;this.focus();"
          onblur="this.contentEditable=false;"
          onkeydown="if(event.key==='Enter'){event.preventDefault();this.blur();}"
          hx-patch="/todos/{{id}}"
          hx-trigger="blur"
          hx-vals='js:{text: event.target.innerText}'
          hx-swap="none"
        >{{text}}</label>
        <button hx-delete="/todos/{{id}}" class="destroy" style="cursor:pointer; background:none; border:none; color:#ac4a1a;">✕</button>
    </li>
{{/from}}
  </ul>

  <input id="toggle-all" class="toggle-all" type="checkbox" {{#if all_complete}}checked{{/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{{/if}} left
</span>


{{#if :completed_count > 0}}
  <button class="clear-completed" hx-post="/todos/clear_completed">Clear completed</button>
{{/if}}
<div class="filters">
  <a {{#if :filter == 'all'}}class="selected"{{/if}} href="/todos?filter=all">All</a> |
  <a {{#if :filter == 'active'}}class="selected"{{/if}} href="/todos?filter=active">Active</a> |
  <a {{#if :filter == 'completed'}}class="selected"{{/if}} href="/todos?filter=completed">Completed</a>
</div>
</body>
</html>

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.