SQLPage v0.20.1 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.
- components are small user interface elements that you can use to display your data in a certain way.
- top-level parameters are the properties of these components, allowing you to customize their appearance and behavior.
- row-level parameters constitute the data that you want to display in the components.
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
class
id
method
reset
title
validate
validate_color
validate_outline
Row-level parameters
name
autocomplete
autofocus
checked
class
description
formaction
id
label
max
maxlength
min
minlength
multiple
options
pattern
placeholder
prefix
prefix_icon
required
step
suffix
type
value
width
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 tousers.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
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
json_each
function in SQLite and Postgres, - the
OPENJSON
function in Microsoft SQL Server. - in MySQL, json manipulation is less straightforward: see the SQLPage MySQL json example
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
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
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 simpleINSERT
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;