Download SQLPage: the SQL website framework

SQLPage is a small executable file that will take requests to your website, execute the SQL files you write, and render the database responses as nice web pages.

Download the latest SQLPage for your operating system. In the release assets section, you will find files named sqlpage-windows.zip, sqlpage-linux.tgz, and sqlpage-macos.tgz. Download the one that corresponds to your operating system, and extract the executable file from the archive.

Note: Advanced users can alternatively install SQLPage using docker, brew, nix, scoop, or cargo.

Building your website locally

Create a folder on your computer where you will store all contents related to your sql website. In the rest of this tutorial, we will call this folder the root folder of your website.

Open the file you downloaded above, and place sqlpage.bin (if you are on linux or Mac OS) or sqlpage.exe at the root of the folder.

Then launch the sqlpage.bin executable file you just downloaded in a terminal from this folder.

You should see a message in your terminal that includes the sentence SQLPage is now running on http://127.0.0.1:8080/

You can open your website locally by visiting http://127.0.0.1:8080

Your website’s first SQL file

In the root folder of your SQLPage website, create a new SQL file called index.sql. Open it in a text editor that supports SQL syntax highlighting (I recommend VSCode).

The index.sql file will be executed every time a visitor opens your website's home page. You can use it to retrieve data from your database and define how it should be displayed to your visitors.

As an example, let's start with a simple index.sql that displays a list of popular websites:

SELECT 'list' AS component, 'Popular websites' AS title;

SELECT 'Hello' AS title, 'world' AS description, 'https://wikipedia.org' AS link;

The first line of the file defines the component that will be used to display the data, and properties of that component. In this case, we use the list component to display a list of items. The second line defines the data that will populate the component. All the components you can use and their properties are documented in SQLPage's online documentation.

Your database schema

If you already have a database populated with data, or if you intend to use other tools to manage your database structure, you can skip this section.

The database schema for your SQLPage website can be defined using SQL scripts located in the sqlpage/migrations subdirectory of your website's root folder. Each script represents a migration that sets up or modifies the database structure.

The scripts are executed in order. You must prefix them with a number to control the order in which they are executed. For instance, my_website/sqlpage/migrations/0001_create_users_table.sql will be executed before my_website/sqlpage/migrations/0002_add_email_to_users_table.sql. SQLPage keeps track of which migrations have already run (by storing the executed migrations in a table named _sqlx_migrations), and will only execute new migrations.

For our first website, let's create a file located in sqlpage/migrations/0001_create_users_table.sql with the following contents:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

Note: The migration system is not supported on Microsoft SQL Server databases. If you are using a SQL Server database, you should create your tables using a different tool, such as SQL Server Management Studio.

Connect to a custom database

By default, SQLPage uses a SQLite database stored in a file named sqlpage.db in the sqlpage configuration folder. You can change this by creating a file named sqlpage.json in a folder called sqlpage. So, if your website's root folder is /my_website, you should create a file at /my_website/sqlpage/sqlpage.json.

Here is an example sqlpage.json file:

{ "database_url": "sqlite://:memory:" }

This will tell SQLPage to use an in-memory SQLite database instead of the default file-based database. All your data will be lost when you stop the SQLPage server, but it is useful for quickly testing and iterating on your database schema.

Later, when you want to deploy your website online, you can switch back to a persisted database like

• a SQLite file with sqlite://your-database-file.db (see options),
• a PostgreSQL-compatible server with postgres://user:password@host/database (see options),
• a MySQL-compatible server with mysql://user:password@host/database (see options),
• a Microsoft SQL Server with mssql://user:password@host/database (see options, note about named instances),

If user or password contains special characters, you should percent-encode them. For instance, a SQL Server database named db running on localhost port 1433 with the username funny:user and the password p@ssw0rd would be represented as mssql://funny%3Auser:p%40ssw0rd@localhost:1433/db. For more information about the properties that can be set in sqlpage.json, see SQLPage's configuration documentation

Use dynamic SQL queries to let users interact with your database

Displaying a form

Let’s create a form to let our users insert data into our database. Add the following code to your index.sql file:

SELECT 'form' AS component, 'Add a user' AS title;
SELECT 'Username' as name, TRUE as required;

The snippet above uses the form component to display a form on your website.

Handling form submission

Nothing happens when you submit the form at the moment. Let’s fix that. Add the following below the previous code:

INSERT INTO users (name)
SELECT :Username
WHERE :Username IS NOT NULL;

The snippet above uses an INSERT INTO SELECT SQL statement to safely insert a new row into the users table when the form is submitted. It uses a WHERE clause to make sure that the INSERT statement is only executed when the :Username parameter is present. The :Username parameter is set to NULL when you initially load the page, and then SQLPage automatically sets it to the value from the text field when the user submits the form.

There are three types of parameters you can use in your SQL queries:

• :ParameterName is a POST parameter. It is set to the value of the field with the corresponding name in a form. If no form was submitted, it is set to NULL.
• $ParameterName works the same as :ParameterName, but it can also be set through a query parameter in the URL. If you add ?x=1&y=2 to the end of the URL of your page, $x will be set to the string '1' and $y will be set to the string '2'. If a query parameter was not provided, it is set to NULL.

Displaying contents from the database

Now, users are present in our database, but we can’t see them. Let’s fix that by adding the following code to our index.sql file:

SELECT 'list' AS component, 'Users' AS title;
SELECT name AS title,  name || ' is a user on this website.' as description FROM users;

Your first SQLPage website is ready!

You can view the full source code for this example on Github

Here is a screenshot of the final result:

To go further, have a look at the examples section of our Github repository.

Deploy your SQLPage website online

If you want to make your SQLPage website accessible online for everyone to browse, you can deploy it to a VPS (Virtual Private Server). To get started, sign up for a VPS provider of your choice. Some popular options include: AWS EC2, DigitalOcean, Linode, Hetzner.

Once you have signed up with a VPS provider, create a new VPS instance. The steps may vary depending on the provider, but generally, you will need to:

1. Choose the appropriate server type and specifications. SQLPage uses very few resources, so you should be fine with the cheaper options.
2. Set up SSH access.

Once your VPS instance is up and running, you can connect to it using SSH. The provider should provide you with the necessary instructions on how to connect via SSH.

For example, if you are using a Linux or macOS terminal, you can use the following command:

ssh username@your-vps-ip-address

Transfer your SQLPage website files to the VPS

For example, if you are using SCP, you can run the following command from your local computer, replacing the placeholders with your own information:

scp -r /path/to/your/sqlpage/folder username@your-vps-ip-address:/path/to/destination

Run SQLPage on the server

Once your SQLPage website files are on the server, you can run SQLPage on the server, just like you did on your local computer. Download the SQLPage for linux binary and upload it to your server.

Then, run the following command on your server:

./sqlpage

To access your website, enter the address of your VPS in your address bar, followed by the port on which SQLPage runs. For instance: http://123.123.123.123:8080.