SQLPage v0.20.5 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 "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.

create_new

In a multiselect with a dropdown, this option allows the user to enter new values, that are not in the list of options.

description

A helper text to display near the input field.

disabled

Set to true to prevent the user from interacting with the input field.

dropdown

An alias for "searchable".

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.

id

A unique identifier for the input, which can then be used to select and manage the field with Javascript code. Usefull for advanced using as setting client side event listeners, interactive control of input field (disabled, visibility, read only, e.g.) and AJAX requests.

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.

readonly

Set to true to prevent the user from modifying the value of the input field.

required

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

searchable

For select and multiple-select elements, displays them with a nice dropdown that allows searching for options.

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,
    'examples/show_variables.sql' as action;
select 
    'Fruit'  as name,
    'select' as type,
    TRUE     as searchable,
    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,
    'examples/show_variables.sql' as action;
select 
    'Fruits'         as label,
    'fruits[]'       as name,
    'select'         as type,
    TRUE             as multiple,
    TRUE             as create_new,
    'Good fruits...' as placeholder,
    TRUE             as searchable,
    '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 small 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