#web #sql #framework

app sqlpage

A SQL-only web application framework. Takes .sql files and formats the query result using pre-made configurable professional-looking components.

6 releases

new 0.4.4 Jan 29, 2023
0.4.1 Jan 23, 2023
0.3.2 Jan 10, 2023

#20 in HTTP server

Download history 31/week @ 2022-12-28 31/week @ 2023-01-04 37/week @ 2023-01-11 26/week @ 2023-01-18 32/week @ 2023-01-25

137 downloads per month

MIT license

7MB
2.5K SLoC

Rust 2K SLoC // 0.0% comments Handlebars 418 SLoC JavaScript 130 SLoC // 0.1% comments SQL 60 SLoC // 0.2% comments

SQLpage

A short video explaining the concept of sqlpage

SQLPage is an SQL-only webapp builder. It is meant for data scientists, analysts, and business intelligence teams to build powerful data-centric applications quickly, without worrying about any of the traditional web programming languages and concepts.

With SQLPage, you write simple .sql files containing queries to your database to select, group, update, insert, and delete your data, and you get good-looking clean webpages displaying your data as text, lists, grids, plots, and forms.

Examples

CodeResult
SELECT 
    'list' as component,
    'Popular websites' as title;
SELECT 
    name as title,
    url as link,
    CASE type
      WHEN 1 THEN 'green'
      ELSE 'red'
    END as color,
    description, icon, active
FROM website;

SQLPage list component

SELECT
  'chart' as component,
  'Syracuse' as title, 'area' as type;
SELECT month AS x, SUM(revenue) AS y
FROM income GROUP BY month;  

SQLPage list component

SELECT 
    'form' as component,
    'User' as title,
    'Create new user' as validate;
SELECT 
    name, type, placeholder, required,
    description, min, max
FROM user_form;

SQLPage list component

Supported databases

  • sqlite
  • PostgreSQL, and other compatible databases such as CockroachDB and Aurora.
  • MySQL, and other compatible databases such as MariaDB and TiDB.

How it works

SQLPage is a web server written in rust and distributed as a single executable file. When it receives a request to a URL ending in .sql, it finds the corresponding SQL file, runs it on the database, passing it information from the web request as SQL statement parameters. When the database starts returning rows for the query, SQLPage maps each piece of information in the row to a parameter in one of its pre-defined components' templates, and streams the result back to the user's browser.

Get started

  • Install docker
  • In a terminal, run the following command:
    • docker run -it --name sqlpage -p 8080:8080 --volume "$(pwd):/var/www" --rm lovasoa/sqlpage
    • ("$(pwd):/var/www" allows sqlpage to run sql files from your current working directory)
  • Create a file called index.sql with the contents from this example
  • Open https://localhost:8080 in your browser

Environment variables

variable default description
LISTEN_ON 0.0.0.0:8080 Interface and port on which the web server should listen
DATABASE_URL sqlite://sqlpage.db?mode=rwc Database connection URL

Serverless

You can run SQLpage serverless by compiling it to an AWS Lambda function. An easy way to do so is using the provided docker image:

 docker build -t sqlpage-lambda-builder . -f lambda.Dockerfile --target builder
 docker run sqlpage-lambda-builder cat deploy.zip > deploy.zip

You can then use deploy.zip as the source for an AWS Lambda, selecting Custom runtime on Amazon Linux 2 as a runtime.

Hosting sql files directly inside the database

When running serverless, you can include the SQL files directly in the image that you are deploying. But if you want to be able to update your sql files on the fly without creating a new image, you can store the files directly inside the database, in a table that has the following structure:

CREATE TABLE sqlpage_files(
  path VARCHAR(255) NOT NULL PRIMARY KEY,
  contents BLOB,
  last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Make sure to update last_modified every time you update the contents of a file (or do it inside a TRIGGER). SQLPage will re-parse a file from the database only when it has been modified.

Technologies and libraries used

  • actix web handles HTTP requests at an incredible speed,
  • tabler handles the styling for professional-looking clean components,
  • tabler icons is a large set of icons you can select directly from your SQL,
  • handlebars render HTML pages from readable templates for each component.

Dependencies

~60MB
~1.5M SLoC