The form component

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;

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;

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;

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;

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

More information on how to handle JSON in SQL.

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;

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;

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;

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;

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.read_file_as_data_url(sqlpage.uploaded_file_path('my_file'))
)

select 
    'form'                as component,
    'multipart/form-data' as enctype,
    '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;

Example 10

Form Encoding

You can specify the way form data should be encoded by setting the enctype top-level property on the form.

You may also specify formenctype on submit and image type inputs. This will take precedence over the enctype specified on the form and is useful in the case there are multiple submit buttons on the form. For example, an external site may have specific requirements on encoding type.

As a rule of thumb, multipart/form-data is best when fields may contain copious non-ascii characters or for binary data such as an image or a file. However, application/x-www-form-urlencoded creates less overhead when many short ascii text values are submitted.

select 
    'form'                        as component,
    'post'                        as method,
    'multipart/form-data'         as enctype,
    'Submit with different encoding types' as title,
    'Submit with form encoding type' as validate,
    'examples/handle_enctype.sql' as action;
select 
    'data' as name,
    'text' as type,
    'Data' as label,
    TRUE   as required;
select 
    'percent_encoded'             as name,
    'submit'                      as type,
    'Submit as'                   as label,
    4                             as width,
    'examples/handle_enctype.sql' as formaction,
    'application/x-www-form-urlencoded' as formenctype,
    'application/x-www-form-urlencoded' as value;
select 
    'multipart_form_data'         as name,
    'submit'                      as type,
    'Submit as'                   as label,
    4                             as width,
    'examples/handle_enctype.sql' as formaction,
    'multipart/form-data'         as formenctype,
    'multipart/form-data'         as value;

Example 11

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;