SQLPage v0.18.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

alert
A visually distinctive message or notification.
authentication
An advanced component that can be used to create pages with password-restricted access. When used, this component has to be at the top of your page, because once the page has begun being sent to the browser, it is too late to restrict access to it. The authentication component checks if the user has sent the correct password, and if not, redirects them to the URL specified in the link parameter. If you don't want to re-check the password on every page (which is an expensive operation), you can check the password only once and store a session token in your database. You can use the cookie component to set the session token cookie in the client browser, and then check whether the token matches what you stored in subsequent pages.
breadcrumb
A secondary navigation aid that helps users understand their location on a website or mobile application.
button
A versatile button component do display one or multiple button links of different styles.
card
A grid where each element is a small card that displays a piece of data.
carousel
A carousel is used to display multiple pieces of visual content without taking up too much space.
chart
A component that plots data. Line, area, bar, and pie charts are all supported. Each item in the component is a data point in the graph.
code
Displays one or many blocks of code from a programming language or formated text as XML or JSON.
cookie
Sets a cookie in the client browser, used for session management and storing user-related information. This component creates a single cookie. Since cookies need to be set before the response body is sent to the client, this component should be placed at the top of the page, before any other components that generate output. After being set, a cookie can be accessed anywhere in your SQL code using the `sqlpage.cookie('cookie_name')` pseudo-function.
csv
A button that lets the user download data as a CSV file. Each column from the items in the component will map to a column in the resulting CSV.
datagrid
Display small pieces of information in a clear and readable way. Each item has a name and is associated with a value.
debug
Display all the parameters passed to the component. Useful for debugging: just replace the name of the component you want to debug with 'debug'.
divider
Dividers help organize content and make the interface layout clear and uncluttered.
dynamic
A special component that can be used to render other components, the number and properties of which are not known in advance.
form
A series of input fields that can be filled in by the user. The form contents can be posted and handled by another sql file in your site. The value entered by the user in a field named x will be accessible to the target SQL page as a variable named $x. For instance, you can create a SQL page named "create_user.sql" that would contain "INSERT INTO users(name) VALUES($name)" and a form with its action property set to "create_user.sql" that would contain a field named "name".
hero
Display a large title and description for your page, with an optional large illustrative image. Useful in your home page, for instance.
http_header
An advanced component to set arbitrary HTTP headers: can be used to set a custom caching policy to your pages, or implement custom redirections, for example. If you are a beginner, you probably don't need this component. When used, this component has to be the first component in the page, because once the page is sent to the browser, it is too late to change the headers. HTTP headers are additional pieces of information sent with responses to web requests that provide instructions or metadata about the data being sent — for example, setting cache control directives to control caching behavior or specifying the content type of a response. Any valid HTTP header name can be used as a top-level parameter for this component. The examples shown here are just that, examples; and you can create any custom header if needed simply by declaring it. If your header's name contains a dash or any other special character, you will have to use your database's quoting mechanism to declare it. In standard SQL, you can use double quotes to quote identifiers (like "X-My-Header"), in Microsoft SQL Server, you can use square brackets (like [X-My-Header]).
json
For advanced users, allows you to easily build an API over your database. The json component responds to the current HTTP request with a JSON object. This component must appear at the top of your SQL file, before any other data has been sent to the browser.
list
A vertical list of items. Each item can be clickable and link to another page.
map
Displays a map with markers on it. Useful in combination with PostgreSQL's PostGIS or SQLite's spatialite.
redirect
Redirects the user to another page. This component is useful for implementing redirects after a form submission, or to redirect users to a login page if they are not logged in. Contrary to the http_header component, this component completely stops the execution of the page after it is called, so it is suitable to use to hide sensitive information from users that are not logged in, for example. Since it uses an HTTP header to redirect the user, it is not possible to use this component after the page has started being sent to the browser.
shell
Personalize the "shell" surrounding your page contents. Used to set properties for the entire page.
steps
Guide users through multi-stage processes, displaying a clear list of previous and future steps.
tab
Build a tabbed interface, with each tab being a link to a page. Each tab can be in two states: active or inactive.
table
A table with optional filtering and sorting. Unlike most others, this component does not have a fixed set of item properties, any property that is used will be rendered directly as a column in the table.
text
A paragraph of text. The entire component will render as a single paragraph, with each item being rendered as a span of text inside it, the styling of which can be customized using parameters.
timeline
A list of events with a vertical line connecting them.
title
Defines HTML headings. The level 1 is used for the maximal size and the level 6 is used for the minimal size.
tracking
Component for visualising activity logs or other monitoring-related data.

The "form" component

A series of input fields that can be filled in by the user. The form contents can be posted and handled by another sql file in your site. The value entered by the user in a field named x will be accessible to the target SQL page as a variable named $x. For instance, you can create a SQL page named "create_user.sql" that would contain "INSERT INTO users(name) VALUES($name)" and a form with its action property set to "create_user.sql" that would contain a field named "name".

Top-level parameters

action

An optional link to a target page that will handle the results of the form. By default the target page is the current page with the id of the form (if passed) used as hash - this will bring us back to the location of the form after submission. Setting it to the name of a different sql file will load that file when the user submits the form.

class

class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0.

id

A unique identifier for the form, which can then be used to validate the form from a button outside of the form.

method

Set this to 'GET' to pass the form contents directly as URL parameters. If the user enters a value v in a field named x, submitting the form will load target.sql?x=v. If target.sql contains SELECT $x, it will display the value v.

reset

The text to display in the button at the bottom of the form that resets the form to its original state. Omit this property not to show a reset button at all.

title

A name to display at the top of the form. It will be displayed in a larger font size at the top of the form.

validate

The text to display in the button at the bottom of the form that submits the values. Omit this property to let the browser display the default form validation text, or set it to the empty string to remove the button completely.

validate_color

The color of the button at the bottom of the form that submits the values. Omit this property to use the default color.

validate_outline

A color to outline the validation button.

Row-level parameters

name

REQUIRED. The name of the input field, that you can use in the target page to get the value the user entered for the field.

autocomplete

Whether the browser should suggest previously entered values for this field.

autofocus

Automatically focus the field when the page is loaded

checked

Used only for checkboxes and radio buttons. Indicates whether the checkbox should appear as already checked.

class

A CSS class to apply to the form element.

description

A helper text to display near the input field.

formaction

When type is "submit", this specifies the URL of the file that will handle the form submission. Useful when you need multiple submit buttons.

label

A friendly name for the text field to show to the user.

max

The minimum value to accept for an input of type number

maxlength

Maximum length of text allowed in the field.

min

The minimum value to accept for an input of type number

minlength

Minimum length of text allowed in the field.

multiple

Used only for select elements. Indicates that multiple elements can be selected simultaneously. When using multiple, you should add square brackets after the variable name: 'my_variable[]' as name

options

A json array of objects containing the label and value of all possible options of a select field. Used only when type=select. JSON objects in the array can contain the properties "label", "value" and "selected".

pattern

A regular expression that the value must match. For instance, [0-9]{3} will only accept 3 digits.

placeholder

A placeholder text that will be shown in the field when is is empty.

prefix

Text to display on the left side of the input field, on the same line.

prefix_icon

Icon to display on the left side of the input field, on the same line.

required

Set this to true to prevent the form contents from being sent if this field is left empty by the user.

step

The increment of values in an input of type number. Set to 1 to allow only integers.

suffix

Short text to display after th input, on the same line. Useful to add units or a currency symbol to an input.

type

The type of input to use: text for a simple text field, textarea for a multi-line text input control, number for field that accepts only numbers, checkbox or radio for a button that is part of a group specified in the 'name' parameter. This is set to "text" by default.

value

A default value that will already be present in the field when the user loads the page.

width

Width of the form field, between 1 and 12.

Example 1

The best way to manage forms in SQLPage is to create at least two separate files:

  • one that will contain the form itself, and will be loaded when the user visits the page,
  • one that will handle the form submission, and will redirect to whatever page you want to display after the form has been submitted.

For instance, if you were creating a form to manage a list of users, you could create:

  • a file named users.sql that would contain a list of users and a form to create a new user,
  • a file named create_user.sql that would insert the new user in the database, and then redirect to users.sql.

create_user.sql could contain the following sql statement to safely insert the new user in the database:

INSERT INTO users(name) VALUES(:username)
RETURNING 'redirect' AS component, 'users.sql' AS link

When loading the page, the value for :username will be NULL if no value has been submitted.

select 
    'form'            as component,
    'create_user.sql' as action;
select 
    'username' as name;

Result

Example 2

A user registration form, illustrating the use of required fields, and different input types.

select 
    'form'            as component,
    'User'            as title,
    'Create new user' as validate;
select 
    'First name' as name,
    'John'       as placeholder;
select 
    'Last name' as name,
    TRUE        as required,
    'We need your last name for legal purposes.' as description;
select 
    'Resume'   as name,
    'textarea' as type;
select 
    'Birth date' as name,
    'date'       as type,
    '2010-01-01' as max,
    '1994-04-16' as value;
select 
    'Password' as name,
    'password' as type,
    '^(?=.*[A-Za-z])(?=.*\d)[A-Za-z\d]{8,}$' as pattern,
    TRUE       as required,
    'Minimum eight characters, at least one letter and one number.' as description;
select 
    'I accept the terms and conditions' as label,
    'terms'    as name,
    'checkbox' as type,
    TRUE       as required;

Result

User

Example 3

Create prepended and appended inputs to make your forms easier to use.

select 
    'form' as component;
select 
    'Your account'  as name,
    'mail'          as prefix_icon,
    'Email:'        as prefix,
    '@mydomain.com' as suffix;

Result

Example 4

This example illustrates the use of the select type. In this select input, the various options are hardcoded, but they could also be loaded from a database table, using a function to convert the rows into a json array like

  • json_group_array() in SQLite,

  • json_agg() in Postgres,

  • JSON_ARRAYAGG() in MySQL, or

  • FOR JSON PATH in Microsoft SQL Server.

In SQLite, the query would look like

SELECT 
    'select' as type,
    json_group_array(json_object(
        "label", name,
        "value", id
    )) as options
FROM fruits
select 
    'form' as component;
select 
    'Fruit'  as name,
    'select' as type,
    1        as value,
    '[{"label": "Orange", "value": 0}, {"label": "Apple", "value": 1}, {"label": "Banana", "value": 3}]' as options;

Result

Example 5

Multi-select

You can authorize the user to select multiple options by setting the multiple property to true. This creates a more compact (but arguably less user-friendly) alternative to a series of checkboxes. In this case, you should add square brackets to the name of the field. The target page will then receive the value as a JSON array of strings, which you can iterate over using

The target page could then look like this:

insert into best_fruits(id) -- INSERT INTO ... SELECT ... runs the SELECT query and inserts the results into the table
select CAST(value AS integer) as id -- all values are transmitted by the browser as strings
from json_each($preferred_fruits); -- json_each returns a table with a "value" column for each element in the JSON array

Example multiselect generated from a database table

As an example, if you have a table of all possible options (my_options(id int, label text)), and another table that contains the selected options per user (my_user_options(user_id int, option_id int)), you can use a query like this to generate the multi-select field:

select 'select' as type, true as multiple, json_group_array(json_object(
    'label', my_options.label,
    'value', my_options.id,
    'selected', my_user_options.option_id is not null
)) as options
from my_options
left join my_user_options
    on  my_options.id = my_user_options.option_id
    and my_user_options.user_id = $user_id
select 
    'form' as component;
select 
    'Fruit'  as name,
    'select' as type,
    TRUE     as multiple,
    'press ctrl to select multiple values' as description,
    '[{"label": "Orange", "value": 0, "selected": true}, {"label": "Apple", "value": 1}, {"label": "Banana", "value": 3, "selected": true}]' as options;

Result

Example 6

This example illustrates the use of the radio type. The name parameter is used to group the radio buttons together. The value parameter is used to set the value that will be submitted when the user selects the radio button. The label parameter is used to display a friendly name for the radio button. The description parameter is used to display a helper text near the radio button.

We could also save all the options in a database table, and then run a simple query like

SELECT 'form' AS component;
SELECT * FROM fruit_option;

In this example, depending on what the user clicks, the target index.sql page will be loaded with a the variable $fruit set to the string "1", "2", or "3".

select 
    'form'      as component,
    'GET'       as method,
    'index.sql' as action;
select 
    'fruit' as name,
    'radio' as type,
    1       as value,
    'An apple a day keeps the doctor away' as description,
    'Apple' as label;
select 
    'fruit'  as name,
    'radio'  as type,
    2        as value,
    'Oranges are a good source of vitamin C' as description,
    'Orange' as label,
    TRUE     as checked;
select 
    'fruit'  as name,
    'radio'  as type,
    3        as value,
    'Bananas are a good source of potassium' as description,
    'Banana' as label;

Result

Example 7

When you want to include some information in the form data, but not display it to the user, you can use a hidden field.

This can be used to track simple data such as the current user's id, or to implement more complex flows, such as a multi-step form, where the user is redirected to a different page after each step.

This can also be used to implement CSRF protection, if your website has authenticated users that can perform sensitive actions through simple links. But note that SQLPage cookies already have the SameSite=strict attribute by default, which protects you against CSRF attacks by default in most cases.

select 
    'form'   as component,
    'Delete' as validate,
    'red'    as validate_color;
select 
    'hidden'      as type,
    'resource_id' as name,
    '1234'        as value;
select 
    'confirm' as name,
    'Please type "sensitive resource" here to confirm the deletion' as label,
    TRUE      as required;

Result

Example 8

This example illustrates the use of custom validation buttons and half-width fields.

select 
    'form'            as component,
    'User'            as title,
    'Create new user' as validate,
    'green'           as validate_color,
    'Clear'           as reset;
select 
    'first_name' as name,
    'First name' as label,
    'John'       as placeholder,
    4            as width;
select 
    'middle_name' as name,
    'Middle name' as label,
    'Fitzgerald'  as placeholder,
    4             as width;
select 
    'last_name' as name,
    'Last name' as label,
    'Doe'       as placeholder,
    4           as width;
select 
    'email'              as name,
    'Email'              as label,
    '[email protected]' as placeholder,
    12                   as width;
select 
    'password' as name,
    'Password' as label,
    'password' as type,
    6          as width;
select 
    'password_confirmation' as name,
    'Password confirmation' as label,
    'password'              as type,
    6                       as width;
select 
    'terms'    as name,
    'I accept the terms and conditions' as label,
    'checkbox' as type,
    TRUE       as required;

Result

User

Example 9

File upload

You can use the file type to allow the user to upload a file. The file will be uploaded to the server, and you will be able to access it using the sqlpage.uploaded_file_path function.

Here is how you could save the uploaded file to a table in the database:

INSERT INTO uploaded_file(name, data) VALUES(:filename, sqlpage.uploaded_file_data_url(:filename))
select 
    'form'             as component,
    'Upload a picture' as title,
    'Upload'           as validate,
    'examples/handle_picture_upload.sql' as action;
select 
    'my_file'               as name,
    'file'                  as type,
    'image/png, image/jpeg' as accept,
    'Picture'               as label,
    'Upload a nice picture' as description,
    TRUE                    as required;

Result

Upload a picture

Example 10

Bulk data insertion

You can use the file type to allow the user to upload a CSV file containing data to insert in a table.

SQLPage can load data from a CSV file and insert it into a database table. SQLPage re-uses PostgreSQL's COPY syntax to specify the format of the CSV file, but makes it work with all supported databases.

When connected to a PostgreSQL database, SQLPage will use the native COPY statement, for super fast and efficient on-database CSV parsing. But it will also work transparently with other databases, by parsing the CSV locally and emulating the same behavior with simple INSERT statements.

Here is how you could easily copy data from a CSV to a table in the database:

copy product(name, description) from 'product_data_input'
with (header true, delimiter ',', quote '"');

If you want to pre-process the data before inserting it into the final table, you can use a temporary table to store the data, and then insert it into the final table:

-- temporarily store the data in a table with text columns
create temporary table if not exists product_tmp(name text, description text, price text);
delete from product_tmp;

-- copy the data from the CSV file into the temporary table
copy product_tmp(name, description, price) from 'product_data_input';

-- insert the data into the final table, converting the price column to an integer
insert into product(name, description, price)
select name, description, CAST(price AS integer) from product_tmp
where price is not null and description is not null and length(description) > 10;

This will load the processed CSV into the product table, provided it has the following structure:

name,description,price
"SQLPage","A tool to create websites using SQL",0
"PostgreSQL","A powerful open-source relational database",0
"SQLite","A lightweight relational database",0
"MySQL","A popular open-source relational database",0
select 
    'form'       as component,
    'CSV import' as title,
    'Load data'  as validate,
    'examples/handle_csv_upload.sql' as action;
select 
    'product_data_input' as name,
    'file'               as type,
    'text/csv'           as accept,
    'Products'           as label,
    'Upload a CSV with a name, description, and price columns' as description,
    TRUE                 as required;

Result

CSV import

Official SQLPage documentation