Creating your own SQLPage components

If you have some frontend development experience, you can create your own components, by placing .handlebars files in a folder called sqlpage/templates at the root of your server.

Web page structure

The shell component

Each page in SQLPage is composed of a shell component, which contains the page title and the navigation bar, and a series of normal components that display the data.

The shell component is always present unless explicitly skipped via the ?_sqlpage_embed query parameter. If you don't call it explicitly, it will be invoked with the default parameters automatically before your first component invocation that tries to render data on the page.

Custom shell components

You can override the default shell component by creating a file called shell.handlebars in the sqlpage/templates folder.

If you want to keep the default shell component on most of your pages, but want to create a custom shell component for a specific page, you can create a file called shell-custom.handlebars (or any other name starting with shell) in the sqlpage/templates folder. Here is an example for a minimal custom shell component:

<!DOCTYPE html>
<html>
<head>
    <title>{{default title "SQLPage"}}</title>
</head>

<body>
{{~#each_row~}}{{~/each_row~}}
</body>
</html>

Since you have full control over the shell component, you can use it to generate non-HTML content. For instance, you can write an XML shell to create a custom RSS feed.

Component template syntax

Components are written in handlebars, which is a simple templating language that allows you to insert data in your HTML.

Here is a simple example of a component that displays a list of items:

<h1>{{title}}</h1>

<ul>
{{#each_row}}
    <li>{{my_property}} {{other_property}}</li>
{{/each_row}}
</ul>

If you save this file as sqlpage/templates/my_list.handlebars, you can use it in your SQL queries by calling the my_list component:

SELECT 'my_list' AS component, 'My list' AS title;
SELECT first_name AS my_property, last_name AS other_property FROM clients;

Styling

SQLPage uses tabler for its default styling. You can include any of the tabler classes in your components to style them. Since tabler inherits from bootstrap, you can also use bootstrap classes.

For instance, you can easily create a multi-column layout with the following code:

<div class="row">
{{#each_row}}
    <div class="col">
        {{my_property}}
    </div>
{{/each_row}}
</div>

For custom styling, you can write your own CSS files and include them in your page header. You can use the css parameter of the default shell component, or create your own custom shell component with a <link> tag.

Helpers

Handlebars has a concept of helpers, which are functions that you can call from your templates to perform some operations.

Handlebars comes with a few built-in helpers, and SQLPage adds a few more:

  • eq, ne: compares two values for equality (equal, not equal)
  • gt, gte, lt, lte: compares two values (greater than, greater than or equal, less than, less than or equal)
  • or, and: combines two boolean values (logical operators)
  • not: negates a boolean value (logical operator)
  • len: returns the length of a list or string, or the number of keys in an object
  • stringify: converts a value to its json string representation, useful to pass parameters from the database to javascript functions
  • parse_json: parses a json string into a value, useful to accept complex parameters from databases that don't have a native json type
  • default: returns the first argument if it is not null, otherwise returns the second argument. For instance: {{default my_value 'default value'}}.
  • entries: returns the entries of an object as a list of {key, value} objects.
  • delay and flush_delayed: temporarily saves a value to memory, and outputs it later. For instance: 
    •   {{#if complex_condition}}
      <a href="{{link}}">
      {{#delay}}
      </a>
      {{/delay}}
  {{/if}}
  ...
  {{flush_delayed}}
  • sort: sorts a list of values
  • plus, minus, sum: mathematical operators
  • starts_with: returns true if a string starts with another string
  • to_array: useful to accept parameters that can optionally be repeated:
    • if the argument is a list, returns it unchanged,
    • if the argument is a string containing a valid json list, returns the parsed list,
    • otherwise returns a list containing only the argument
  • array_contains: returns true if a list contains a value
  • static_path: returns the path to one of the static files bundled with SQLPage. Accepts arguments like sqlpage.js, sqlpage.css, apexcharts.js, etc.
  • app_config: returns the value of a configuration parameter from sqlpage's configuration file, such as max_uploaded_file_size, site_prefix, etc.
  • icon_img: generate an svg icon from a tabler icon name
  • markdown: renders markdown text
  • each_row: iterates over the rows of a query result
  • typeof: returns the type of a value (string, number, boolean, object, array, null)
  • rfc2822_date: formats a date as a string in the RFC 2822 format, that is, Thu, 21 Dec 2000 16:01:07 +0200
  • url_encode: percent-encodes a string for use in a URL. For instance, {{url_encode "hello world"}} returns hello%20world.

Attributes

In addition to the parameters you pass to your components in your SQL queries, SQLPage adds the following attributes to the context of your components:

  • @component_index : the index of the current component in the page. Useful to generate unique ids or classes.
  • @row_index : the index of the current row in the current component. Useful to implement special behavior on the first row, for instance.
  • @csp_nonce : a random nonce that you must use as the nonce attribute of your <script> tags if you include external scripts.

External javascript

For security, by default SQLPage ships with a Content Security Policy that prevents the execution of inline javascript and the loading of external scripts. However, you can include external scripts in your page by adding them to the javascript parameter of the default shell component, or inside your own custom components using

<script nonce="{{@csp_nonce}}">
// your javascript code here
</script>

Overwriting the default components

You can overwrite the default components, including the shell component, by creating a file with the same name in the sqlpage/templates folder.

For example, if you want to change the appearance of the shell component, you can create a file called sqlpage/templates/shell.handlebars and write your own HTML in it. If you don't want to start from scratch, you can copy the default shell component from the SQLPage source code.

Examples

All the default components are written in handlebars, and you can read their source code to learn how to write your own. See the default components source code.

Some interesting examples are:

  • The shell component
  • The card component: simple yet complete example of a component that displays a list of items.
  • The table component: more complex example of a component that uses
    • the eq, or, and sort handlebars helpers,
    • the ../ syntax to access the parent context,
    • and the @key to work with objects whose keys are not known in advance.