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, 'https://docs.yugabyte.com/images/yugabytedb-logo.svg' as image, 'https://github.com/lovasoa/SQLpage' as link, 'SQLpage github project' as link_text ; -- 'text' displays a paragraph that can be built from SQL data select 'text' as component, format( '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();
sqlpage (downloaded from https://github.com/lovasoa/SQLpage/releases or run from the Docker image) with the
DATABASE_URL environement variable set to my PostgreSQL-compatible database (
0a7ba07905f27af3f3efbca96b6865a73c40114d35ab3e9b7db243045ff8b907 [2023-07-24T21:18:30Z INFO sqlpage::webserver::database] Connecting to database: postgres://user:password@yb-node1:5433/yugabyte`
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.