Custom Components

SQLPage’s component system is extensible - you can create custom components using Handlebars templates to add any UI element not covered by the built-in components.

Understanding SQLPage Components

Every SQLPage component is a Handlebars template that transforms SQL query results into HTML. When you write:

SELECT 'text' AS component, 'Hello World' AS contents;

SQLPage:

Looks for sqlpage/templates/text.handlebars
Passes your query results to the template
Renders HTML from the template
Streams it to the browser

Creating Your First Custom Component

Step 1: Create the Template File

Create a file in sqlpage/templates/ with your component name:

sqlpage/templates/my_component.handlebars

Step 2: Write the Handlebars Template

Here’s a simple example:

{{!-- sqlpage/templates/greeting.handlebars --}}
<div class="greeting-card">
    <h2>{{title}}</h2>
    <p>{{message}}</p>
    {{#if link}}
        <a href="{{link}}">{{link_text}}</a>
    {{/if}}
</div>

Step 3: Use Your Component

SELECT 'greeting' AS component,
    'Welcome!' AS title,
    'Thanks for visiting our site.' AS message,
    '/about' AS link,
    'Learn more' AS link_text;

That’s it! SQLPage will automatically find and render your template.

Handlebars Basics

Variables

Access query result columns as variables:

<h1>{{title}}</h1>
<p>{{description}}</p>

Conditionals

{{#if show_button}}
    <button>{{button_text}}</button>
{{/if}}

{{#if user}}
    <p>Welcome, {{user}}!</p>
{{else}}
    <p>Please log in.</p>
{{/if}}

Loops

For components that display multiple rows:

<ul>
{{#each_row}}
    <li>
        <strong>{{name}}</strong>: {{description}}
    </li>
{{/each_row}}
</ul>

Component Structure

Top-Level Properties

The first row of your query provides top-level properties:

SELECT 'my_list' AS component,
    'My List Title' AS title,  -- Top-level property
    'primary' AS color;         -- Top-level property

SELECT 'Item 1' AS name, 'First item' AS description;  -- Row-level
SELECT 'Item 2' AS name, 'Second item' AS description; -- Row-level

{{!-- Access top-level properties directly --}}
<div class="list list-{{color}}">
    <h2>{{title}}</h2>
    <ul>
    {{#each_row}}
        {{!-- Access row-level properties inside each_row --}}
        <li><strong>{{name}}</strong>: {{description}}</li>
    {{/each_row}}
    </ul>
</div>

The each_row Helper

{{#each_row}} iterates over all rows except the first (which contains top-level properties):

{{#each_row}}
    <div class="item">
        <h3>{{title}}</h3>
        <p>{{description}}</p>
    </div>
{{/each_row}}

Real-World Example: Dual-List Component

Here’s a complete example of a custom component for selecting items from two lists:

{{!-- sqlpage/templates/dual-list.handlebars --}}
<form method="POST" action="{{action}}">
  <div class="row justify-content-center align-items-center g-4">
    {{!-- Left List: Available Items --}}
    <div class="col-5">
      <div class="card border-0 shadow-sm">
        <div class="card-header bg-white border-bottom fw-semibold text-secondary py-3">
          Available Items
        </div>
        <div class="card-body p-3">
          <select class="form-select" id="leftList" multiple style="height: 300px">
            {{#each_row}}
            <option value="{{id}}" {{#if selected}}selected{{/if}}>{{label}}</option>
            {{/each_row}}
          </select>
        </div>
      </div>
    </div>

    {{!-- Middle: Transfer Buttons --}}
    <div class="col-auto d-flex flex-column gap-2">
      <button type="button" class="btn btn-outline-primary rounded-circle"
              id="moveRight" style="width: 40px; height: 40px">
        {{icon_img 'arrow-narrow-right' 20}}
      </button>
      <button type="button" class="btn btn-outline-primary rounded-circle"
              id="moveLeft" style="width: 40px; height: 40px">
        {{icon_img 'arrow-narrow-left' 20}}
      </button>
    </div>

    {{!-- Right List: Selected Items --}}
    <div class="col-5">
      <div class="card border-0 shadow-sm">
        <div class="card-header bg-white border-bottom fw-semibold text-secondary py-3">
          Selected Items
        </div>
        <div class="card-body p-3">
          <select class="form-select" id="rightList" name="selected_items[]" multiple style="height: 300px"></select>
        </div>
      </div>
    </div>

    {{!-- Submit Button --}}
    <div class="col-12 text-center mt-4">
      <input type="submit" class="btn btn-primary px-4 py-2 fw-semibold shadow-sm"
             id="submitBtn" disabled value="Submit Selection">
    </div>
  </div>
</form>

<script nonce="{{@csp_nonce}}">
  const rightList = document.getElementById('rightList');
  const leftList = document.getElementById('leftList');

  function transferItems(fromList, toList) {
    for (const option of toList.options) option.selected = false;
    const newOptions = [...toList.options, ...fromList.selectedOptions];
    newOptions.sort((a, b) => a.text.localeCompare(b.text));
    toList.append(...newOptions);
    toList.focus();
    updateSubmitButton();
  }

  function updateSubmitButton() {
    submitBtn.disabled = rightList.options.length === 0;
  }

  function handleSubmit() {
    for (const option of rightList.options) option.selected = true;
  }

  updateSubmitButton();
  transferItems(leftList, rightList);

  document.getElementById('moveRight').addEventListener('click', transferItems.bind(null, leftList, rightList));
  document.getElementById('moveLeft').addEventListener('click', transferItems.bind(null, rightList, leftList));
  document.querySelector('form').addEventListener('submit', handleSubmit);
</script>

Use it like this:

SELECT 'dual-list' AS component,
    'assign.sql' AS action;

SELECT
    id,
    name AS label,
    is_assigned AS selected
FROM users
ORDER BY name;

Built-in Handlebars Helpers

SQLPage provides several custom helpers:

icon_img

Render icons from Tabler Icons:

<button>
    {{icon_img 'check' 20}}
    Save
</button>

markdown

Render Markdown content:

<div class="content">
    {{{markdown description_md}}}
</div>

Note: Use triple braces {{{ to avoid escaping HTML.

default

Provide fallback values:

<h1>{{default title 'Untitled'}}</h1>
<button class="btn btn-{{default color 'primary'}}">
    {{default button_text 'Submit'}}
</button>

eq, ne, gt, lt, gte, lte

Comparison operators:

{{#if (eq type 'admin')}}
    <span class="badge badge-danger">Admin</span>
{{/if}}

{{#if (gt count 10)}}
    <span class="text-danger">Too many items!</span>
{{/if}}

static_path

Get the correct path for static assets:

<script src="{{static_path 'custom.js'}}"></script>
<link rel="stylesheet" href="{{static_path 'styles.css'}}">

app_config

Access configuration values:

<input type="file" data-max-size="{{app_config 'max_uploaded_file_size'}}">

@csp_nonce

For inline scripts to comply with Content Security Policy:

<script nonce="{{@csp_nonce}}">
    console.log('This script is CSP-compliant');
</script>

Styling Custom Components

Using Bootstrap Classes

SQLPage includes Bootstrap 5, so you can use its utility classes:

<div class="card mb-3">
    <div class="card-header bg-primary text-white">
        {{title}}
    </div>
    <div class="card-body">
        <p class="card-text">{{description}}</p>
    </div>
</div>

Custom CSS

Add custom styles in sqlpage/sqlpage.css:

.greeting-card {
    border: 2px solid #4a90e2;
    border-radius: 8px;
    padding: 1.5rem;
    background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
    color: white;
}

Reference it in your template:

<div class="greeting-card">
    <h2>{{title}}</h2>
    <p>{{message}}</p>
</div>

Advanced Techniques

Processing JSON Data

{{#each (parse_json options)}}
    <option value="{{value}}">{{label}}</option>
{{/each}}

Delayed Template Execution

The {{#delay}} helper defers content rendering:

{{#each_row}}
    {{#if (eq type "file")}}
        {{#delay}}enctype="multipart/form-data"{{/delay}}
    {{/if}}
{{/each_row}}

<form {{flush_delayed}}>
    <!-- Form content -->
</form>

This allows row-level data to affect top-level attributes.

Component State

Use @component_index to generate unique IDs for multiple instances:

<div id="component-{{@component_index}}">
    {{!-- Component content --}}
</div>

<script nonce="{{@csp_nonce}}">
    const element = document.getElementById('component-{{@component_index}}');
    // Component-specific JavaScript
</script>

Overriding Built-in Components

You can override any built-in component by creating a file with the same name:

# Override the built-in form component
cp sqlpage/templates/form.handlebars sqlpage/templates/form.handlebars.backup
# Edit sqlpage/templates/form.handlebars with your customizations

Best Practices

1. Keep Templates Simple

Complex logic belongs in SQL, not templates:

-- Good: Calculate in SQL
SELECT
    'my_component' AS component,
    name,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        ELSE 'C'
    END AS grade
FROM students;

<!-- Simple template -->
{{#each_row}}
    <p>{{name}}: {{grade}}</p>
{{/each_row}}

2. Use Semantic HTML

<!-- Good -->
<article class="blog-post">
    <header>
        <h2>{{title}}</h2>
        <time datetime="{{date}}">{{formatted_date}}</time>
    </header>
    <div class="content">
        {{{markdown content}}}
    </div>
</article>

3. Make Components Accessible

<button aria-label="{{aria_label}}" title="{{title}}">
    {{icon_img icon 20}}
</button>

<img src="{{image}}" alt="{{alt_text}}">

4. Handle Missing Data

<h1>{{default title 'Untitled'}}</h1>

{{#if description}}
    <p>{{description}}</p>
{{else}}
    <p class="text-muted">No description available.</p>
{{/if}}

5. Security: Escape User Input

Use double braces {{}} to escape HTML by default:

<!-- Safe: HTML is escaped -->
<p>{{user_input}}</p>

<!-- Dangerous: HTML is not escaped (only use for trusted Markdown) -->
<div>{{{markdown trusted_content}}}</div>

Testing Custom Components

Preview During Development

Set environment to development for live reloading:

{
  "environment": "development"
}

Templates will reload on every request without restarting SQLPage.

Test with Sample Data

Create a test page:

-- test_my_component.sql
SELECT 'my_component' AS component,
    'Test Title' AS title,
    'primary' AS color;

SELECT 'Item ' || num AS name, 'Description ' || num AS description
FROM (SELECT 1 AS num UNION SELECT 2 UNION SELECT 3);

Inspecting Built-in Components

Learn by example - all built-in component templates are in the SQLPage source:

cd sqlpage/templates/
ls -la
# alert.handlebars, card.handlebars, form.handlebars, etc.

Study components similar to what you want to build:

Simple components: code.handlebars, text.handlebars
List-based: list.handlebars, table.handlebars
Complex forms: form.handlebars
Interactive: chart.handlebars, map.handlebars

Examples

Explore custom component examples:

Dual-list selector: examples/custom form component/
Custom charts: examples/charts, computations and custom components/

Get Started

Core Concepts

Configuration

Guides

​Custom Components

​Understanding SQLPage Components

​Creating Your First Custom Component

​Step 1: Create the Template File

​Step 2: Write the Handlebars Template

​Step 3: Use Your Component

​Handlebars Basics

​Variables

​Conditionals

​Loops

​Component Structure

​Top-Level Properties

​The each_row Helper

​Real-World Example: Dual-List Component

​Built-in Handlebars Helpers

​icon_img

​markdown

​default

​eq, ne, gt, lt, gte, lte

​static_path

​app_config

​@csp_nonce

​Styling Custom Components

​Using Bootstrap Classes

​Custom CSS

​Advanced Techniques

​Processing JSON Data

​Delayed Template Execution

​Component State

​Overriding Built-in Components

​Best Practices

​1. Keep Templates Simple

​2. Use Semantic HTML

​3. Make Components Accessible

​4. Handle Missing Data

​5. Security: Escape User Input

​Testing Custom Components

​Preview During Development

​Test with Sample Data

​Inspecting Built-in Components

​Examples

​Resources

Build docs developers (and LLMs) love