v0.16.0

SQLPage is a small web server that renders your SQL queries as beautiful interactive websites. This release brings important fixes, improves database compatibility, and adds new features allowing you to build things that were not possible or very cumbersome before.

SQLPage 0.16.0

🌟 Invisible Magic in Forms

Ever wished your hidden inputs were truly hidden? Your wish is our command! In this release, we've added special handling for hidden inputs in forms. Now, they're like ninjas—completely invisible to end users. This opens up possibilities for creating multi-step forms, adding CSRF protection, and tackling other complex form challenges with ease.

select 'form' as component; -- the page containing this followup form can be the target of a first form
select 'hidden' as type, :previous_form_input as value

🎨 Fresh Icons Galore!

What's a release without some eye candy? Get ready to spice up your interfaces with 36 new icons! Check out the goodies in the latest icon releases:

v2.40.0	v2.41.0

🚀 Temporary tables

In the past, when a SQLPage file was loaded, all its statements would be prepared eagerly. This meant that any attempt to reference a temporary table created earlier in the file from a later statement was met with confusion, because the CREATE TABLE statement had been prepared, but not yet executed. Like trying to reference a page number in a book that hasn't been written yet.

Now, with the introduction of lazy prepared statements, the SQLPage performance takes on a more dynamic choreography. Statements wait in the wings until their cue, preparing themselves just before their moment in the spotlight. If you have a CREATE TEMPORARY TABLE statement followed by a SELECT from that same table, the SELECT is not seen by the database until after the CREATE has been executed.

Prepared statements are still cached, which means that after the initial load of the file, the SQL is not sent to the database again, and SQLPage just references the existing prepared statement.

-- Create a temporary VIP lounge
CREATE TEMPORARY VIEW current_user AS
  SELECT * FROM users
  INNER JOIN sessions ON sessions.user_id = users.id
  WHERE sessions.session_id = sqlpage.cookie('session_id');

-- Roll out the red carpet
SELECT 'card' as component,
  'Welcome, ' || username as title
FROM current_user;

🔄 No More 'NULL' strings

SET can now be used to set a variable to NULL.

This fixes an oversight in previous versions. As an example, previously, SET username = (select username from users where id = $id) would set username to the string 'null' if no user had the given id. Annoying 😬 . Now it properly sets it to a real NULL value.

-- Resetting to null
SET myvar = NULL;

-- Check if null and proud
SELECT 'card' as component;
SELECT $myvar IS NULL as title; -- True, not false!

🤝 MySQL and MariaDB get good conversation starters

You can create a script in sqlpage/on_connect.sql that will be executed each time SQLPage opens a new connection to your database. When using a MySQL database, you used to be able to write only a single statement in this file. Now you can put as many as you want.

🔄 PGBouncer compatibility

When using a connection pooler such as PGBouncer in front of your postgres database, you previously could see errors such as prepared statement 'sqlx_s_1' already exists.
We've now randomized PostgreSQL prepared statement names to dodge those pesky name collisions. It's like a game of musical chairs, but for SQL prepared statements.

It is still not recommended to use SQLPage with an external connection pooler (such as pgbouncer), because SQLPage already implements its own connection pool. If you really want to use a connection pooler, you should set the max_connections configuration parameter to 1 to disable the connection pooling logic in SQLPage.

That's a wrap for SQLPage 0.16.0! We hope these improvements make your SQL journey smoother and more enjoyable. As always, happy querying! 🚀✨