SQLPage v0.20.3 documentation

If you are completely new to SQLPage, you should start by reading the get started tutorial, which will guide you through the process of creating your first SQLPage application.

Building an application with SQLPage is quite simple. To create a new web page, just create a new SQL file. For each SELECT statement that you write, the data it returns will be analyzed and rendered to the user. The two most important concepts in SQLPage are components and parameters.

To select a component and set its top-level properties, you write the following SQL statement:

SELECT 'component_name' AS component, 'my value' AS top_level_parameter_1;

Then, you can set its row-level parameters by writing a second SELECT statement:

SELECT my_column_1 AS row_level_parameter_1, my_column_2 AS row_level_parameter_2 FROM my_table;

This page documents all the components provided by default in SQLPage and their parameters. Use this as a reference when building your SQL application. If at any point you need help, you can ask for it on the SQLPage forum.

If you know some HTML, you can also easily create your own components for your application.

components

The "shell" component

Personalize the "shell" surrounding your page contents. Used to set properties for the entire page.

Top-level parameters

css

The URL of a CSS file to load and apply to the page.

description

A description of the page. It can be displayed by search engines when your page appears in their results.

font

Name of a font to display the text in. This has to be a valid font name from fonts.google.com.

font_size

Font size on the page, in pixels. Set to 18 by default.

footer

Muted text to display in the footer of the page. This can be used to display a link to the terms and conditions of your application, for instance. By default, shows "Built with SQLPage". Supports links with markdown.

icon

Name of an icon (from tabler-icons.io) to display next to the title in the navigation bar.

image

The URL of an image to display next to the page title.

javascript

The URL of a Javascript file to load and execute on the page.

language

The language of the page. This can be used by search engines and screen readers to determine in which language the page is written.

layout

The general page layout. Can be "boxed" (the default), "horizontal" (for a full-width menu), "vertical"(vertical menu), "fluid" (removes side margins).

link

The target of the link in the top navigation bar.

menu_item

Adds a menu item in the navigation bar at the top of the page. The menu item will have the specified name, and will link to as .sql file of the same name. A dropdown can be generated by passing a json object with a `title` and `submenu` properties.

norobot

Forbids robots to save this page in their database and follow the links on this page. This will prevent this page to appear in Google search results for any query, for instance.

refresh

Number of seconds after which the page should refresh. This can be useful to display dynamic content that updates automatically.

rss

The URL of an RSS feed to display in the top navigation bar. You can use the rss component to generate the field.

search_target

When this is set, a search field will appear in the top navigation bar, and load the specified sql file with an URL parameter named "search" when the user searches something.

theme

Set to "dark" to use a dark theme.

title

The title of your page. Will be shown in a top bar above the page contents. Also usually displayed by web browsers as the name of the web page's tab.

Example 1

This example contains the values used for the shell of the page you are currently viewing.

The menu_item property is used both in its simple string form, to generate a link named "functions" that points to "functions.sql", and in its object form, to generate a dropdown menu named "Community" with links to the blog, the github repository, and the issues page.

The object form can be used directly only on database engines that have a native JSON type. On other engines (such as SQLite), you can use the dynamic component to generate the same result.

You see the page layouts demo for a live example of the different layouts.

select 
    'shell'                   as component,
    'SQLPage documentation'   as title,
    '/'                       as link,
    JSON('{"link":"index.sql","title":"Home"}') as menu_item,
    JSON('{"title":"Community","submenu":[{"link":"blog.sql","title":"Blog"},{"link":"//github.com/lovasoa/sqlpage/issues","title":"Issues"},{"link":"//github.com/lovasoa/sqlpage/discussions","title":"Discussions"},{"link":"//github.com/lovasoa/sqlpage","title":"Github"}]}') as menu_item,
    'functions'               as menu_item,
    'components'              as menu_item,
    'boxed'                   as layout,
    'en-US'                   as language,
    'Documentation for the SQLPage low-code web application framework.' as description,
    'Poppins'                 as font,
    'book'                    as icon,
    'https://cdn.jsdelivr.net/npm/prismjs@1/components/prism-core.min.js' as javascript,
    'https://cdn.jsdelivr.net/npm/prismjs@1/plugins/autoloader/prism-autoloader.min.js' as javascript,
    '/prism-tabler-theme.css' as css,
    'Official [SQLPage](https://sql.ophir.dev) documentation' as footer;

Example 2

A page without a shell

SQLPage provides the shell-empty component to create a page without a shell. In this case, the html and body tags are not generated, and the components are rendered directly in the page without any styling, navigation bar, footer, or dynamic content. This is useful when you want to generate a snippet of HTML that can be dynamically included in a larger page.

Any component whose name starts with shell will be considered as a shell component, so you can also create your own shell component.

If you generate your own HTML from a SQL query, you can also use the shell-empty component to include it in a page. Make sure you know what you are doing, and be careful to escape the HTML properly, as you are stepping out of the safe SQLPage framework and into the wild world of HTML.

select 
    'shell-empty' as component;
select 
    'text' as component,
    '<!DOCTYPE html>
<html>
<head>
  <title>My page</title>
</head>
<body>
  <h1>My page</h1>
</body>
</html>' as html;

Examples

Sharing the shell between multiple pages

It is common to want to share the same shell between multiple pages.

Static menu

If your menu is completely static (it does not depend on the database content), you can use the dynamic component together with the sqlpage.read_file_as_text function to load the shell from a json file.

SELECT 'dynamic' AS component, sqlpage.read_file_as_text('shell.json') AS properties;

and in shell.json:

{
    "component": "shell",
    "title": "SQL + JSON = <3",
    "link": "/",
    "menu_item": [
        {"link": "index.sql", "title": "Home"},
        {"title": "Community", "submenu": [
            {"link": "blog.sql", "title": "Blog"},
            {"link": "//github.com/lovasoa/sqlpage", "title": "Github"}
        ]}
    ]
}

Dynamic menu

If your menu depends on the database content, or on special sqlpage functions, you can use the dynamic component, but this time with the sqlpage.run_sql function to generate the menu from the database.

SELECT 'dynamic' AS component, sqlpage.run_sql('shell.sql') AS properties;

and in shell.sql:

SELECT 'shell' AS component, 'run_sql is cool' as title,
    json_group_array(json_object(
        'link', link,
        'title', title
    )) as menu_item
FROM my_menu_items

(check your database documentation for the exact syntax of the json_group_array function).

Official SQLPage documentation