SQLPage is a fun and innovative open source project that lets you do the presentation layer in plain SQL.

With SQLPage your SQL skills reach beyond manipulating the Data layer of your application; you can now do HTML too without needing to know any of the traditional web programming languages and concepts. The difference is that your SQL includes and refers to UI components as columns.

A simple example will make the goal clear. The following SQL code uses the 'list' component

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

which gets rendered as :

That was a simplistic example;you can also do more elaborate designs like nested, dynamic components and composite Forms:

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

INSERT INTO user (first_name, last_name, birth_date)
SELECT ($first_name, $last_name, $birth_date)
WHERE $first_name IS NOT NULL;

There's a range of ready made UI components. The main ones are:

card
A grid where each element is a small card that displays a piece of data.
chart

datagrid
Display small pieces of information in a clear and readable way. Each item has a name and is associated with a value.

form

dynamic
A special component that can be used to render other components, the number and properties of which are not known in advance.

list
A vertical list of items. Each item can be clickable and link to another page.

tab
Build a tabbed interface, with each tab being a link to a page. Each tab can be in two states: active or inactive.

table
A table with optional filtering and sorting. Unlike most others, this component does not have a fixed set of item properties, any property that is used will be rendered directly as a column in the table.

text
A paragraph of text. The entire component will render as a single paragraph, with each item being rendered as a span of text inside it, the styling of which can be custo

and more. .

I hear you say, what about aligning those components on the page? How do you do that?

Each component is full width, and they are stacked up from top to bottom. Inside of a single component, there can be multiple columns, and then the layout depends on the particular component. For instance the cards component has a "columns" attribute which is the number of columns in the grid of cards. This is just a hint, the grid will adjust dynamically to the user's screen size, rendering fewer columns if needed to fit the contents.

That said you can also write your own custom components, and then you are free to do whatever you want in CSS. SQLPage uses the great "tabler" CSS library which has a ton of useful utilities.

SQLPage draws on an interesting idea, however I thought that mixing the data and the presentation layers goes against the best practices. But SQLPage offers an alternative that is useful in some scenarios, targeting professional groups like data scientists, analysts, and business intelligence teams who need to build powerful data-centric applications quickly. Those people will use SQLPage in scenarios like:

• Internal Dashboards: Build data-driven dashboards and reporting tools that empower teams to make informed decisions.
• Business Intelligence Apps: Develop powerful business intelligence applications with intuitive interfaces for data exploration and analysis.
• Rapid Prototyping: Validate and iterate on your ideas quickly by rapidly creating functional minimum viable products.
• Admin Interfaces: Construct efficient and user-friendly administrative interfaces for managing and interacting with your PostgreSQL data.

And while not a framework, do not underestimate its capabilities;you can also read and write HTTP cookies, manage user authentication, handle form submissions and URL parameters. Unsurprisingly that functionality comes in the form of components as well:

authentication
An advanced component that can be used to create pages with password-restricted access.

cookie
Sets a cookie in the client browser, used for session management and storing user-related information.

redirect
Redirects the user to another page.

http_header
An advanced component that can be used to create redirections, set a custom caching policy to your pages, or set any HTTP header.

json
For advanced users, allows you to easily build an API over your database. The json component responds to the current HTTP request with a JSON object. This component must appear at the top of your SQL file, before any other data has been sent to the browser.

Infrastructure wise the underlying magic that makes all that happen, is nicely laied out on the project's Github README page :

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.

Of course there's also a Docker distribution if you don't want to go binary executable.

Behind the scene and as far data storage is concerened, SQLPage works with the following dbms:

• sqlite
• PostgreSQL, and other compatible databases such as YugabyteDB, CockroachDB and Aurora.
• MySQL, and other compatible databases such as MariaDB and TiDB.
• Microsoft SQL Server, and all compatible databases and providers such as Azure SQL and Amazon RDS.

In the end I really enjoyed both the concept behind SQLPage as well as its materialization. SQLPage in certain scenarios can prove a very useful tool, as well as enabling you to leverage your sql skills for other purposes than data analysis. Or for simply just having fun!

More Information

SQLPage

Github

Related Articles

Turn Your SQLite Database Into A Server

Lost at SQL - The Game

Entity Relationship Diagraming with ERDLab

Real World Schema Exploring With Azimutt

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info