DEV Community

Franck Pachot for YugabyteDB

Posted on

SQL for low code applications: SQLPage

I love SQL to process data. You can do a lot, especially with PostgreSQL features and extensions. To build a presentation layer on top of it, here is an interesting Web Server where you declare the UI component with SQL queries.

Here is an example of code for a page. I've put the following in an index.sql in my www current directory:

-- 'hero' component displays a large title and description

select 'hero' AS component,
 'SQLPage on Distributed SQL'                           as title,
 'this is all built in SQL 🤓'                          as description_md,
 '' as image,
 ''                   as link,
 'SQLpage github project'                               as link_text

-- 'text' displays a paragraph that can be built from SQL data

select 'text' as component,
  'You are connected to YugabyteDB version %s, here are the database nodes:'
  , current_setting('server_version')
 ) as contents

-- 'table' displays a grid from the following  querie(s)

select 'table' as component, 1 as sort, 1 as search;
select * from yb_servers();

Enter fullscreen mode Exit fullscreen mode

I started sqlpage (downloaded from or run from the Docker image) with the DATABASE_URL environement variable set to my PostgreSQL-compatible database (postgres://user:password@yb-node1:5433/yugabyte)

[2023-07-24T21:18:30Z INFO  sqlpage::webserver::database] Connecting to database: postgres://user:password@yb-node1:5433/yugabyte`
Enter fullscreen mode Exit fullscreen mode

The default port is 8080 and here is my http://localhost:8080:
Image description

Here I have displayed static text, and queried the version from current_setting('server_version'). The table reads yb_servers() which shows all nodes of the YugabyteDB cluster I'm connected to. YugabyteDB is an Open Source Distributed SQL using PostgreSQL as the query layer on top of distributed storage and transactions to provide cloud-native elasticity and high availability. You can use it as distributed Postgres: same features but no downtime for failures, upgrades or scaling.

You can build a full application with SQLPage, with user entry in 'form' component that sets variable which can be used with all the power of PostgreSQL: SQL DML or PL/pgSQL. All components are documented - the documentation is built with SQLPage of course. You can also return a CSV to download, or JSON, that you build with SQL.

Top comments (0)