0.19.1 (2024年02月28日)

• SECURITY: fixes users being able to re-run migrations by visiting /sqlpage/migrations/NNNN_name.sql pages. If you are using sqlpage migrations, your migrations are not idempotent, and you use the default SQLPAGE_WEB_ROOT (./) and SQLPAGE_CONFIGURATION_DIRECTORY (./sqlpage/), you should upgrade to this version as soon as possible. If you are using a custom SQLPAGE_WEB_ROOT or SQLPAGE_CONFIGURATION_DIRECTORY or your migrations are idempotent, you can upgrade at your convenience.
• Better error messages on invalid database connection strings. SQLPage now displays a more precise and useful message when an error occurs instead of a "panic" message.

SQLPage v0.19.0 Released!

We're excited to announce the release of SQLPage v0.19.0!
If you are new here: SQLPage is a small web server that renders your SQL queries as beautiful interactive websites.
This update is focused on improving built-in components and making deployment easier.

Improved User Interface

• Styled tables: Customize the appearance of your tables with new attributes like striped_rows, striped_columns, hover, border, and small. (Refer to documentation: https://sql.ophir.dev/documentation.sql?component=table#component)
  • screenshot
• Input groups: Create visually clear and informative input fields using the new prefix, prefix_icon, and suffix attributes in the form component. This is valuable for adding units, currency symbols, or other visual cues beside your form inputs.
  • screenshot
• Multiple page layouts: Choose your preferred layout with the new configuration options in the shell component. Three options are available: boxed (default), fluid (full width), and horizontal (boxed content with full-width header).
  • https://github.com/lovasoa/SQLpage/assets/552629/3c0fde36-7bf6-414e-b96f-c8880a2fc786
• Row-level actions: Enhance your tables with the new edit_link, delete_link, and view_link attributes in the list component. These attributes allow you to add icons and links to each row for easy interaction.
  • https://github.com/lovasoa/SQLpage/assets/552629/df085592-8359-4fed-9aeb-27a2416ab6b8
• Enhanced charting: The chart component now leverages the latest versions of the ApexCharts library, providing updated features and bug fixes. (See release notes for details: https://github.com/apexcharts/apexcharts.js/releases)
• New icons: Enjoy a wider selection of icons with the update to Tabler Icon library v2.47. (See changelog: https://tabler.io/icons/changelog)

Developer Experience Enhancements:

• Dynamic components at top level: Previously restricted, dynamic components can now be used freely at the top level of your SQL files for improved flexibility.
• Custom shells: Take customization to a new level by creating multiple shell-*.handlebars files in the sqlpage directory. This allows defining different shell layouts for various pages within your application. (See documentation: https://sql.ophir.dev/custom_components.sql)
• Environment variable for configuration: Manage configuration more efficiently with the new SQLPAGE_CONFIGURATION_DIRECTORY environment variable. This enables you to define a custom location for your configuration files, independent of the current working directory.

Additional improvements:

• Cookie component behavior now aligns with the documentation, setting cookies for the entire website by default.

We encourage you to explore the new features in v0.19.0 and experience the enhanced capabilities of SQLPage! The full release notes with detailed descriptions and examples are available on GitHub: https://github.com/lovasoa/SQLpage/releases/tag/v0.19.0

• Updated dependencies
  • Updated sql parser, to add support for new syntax, including:
    • MySQL's JSON_TABLE table-valued function, that allows easily iterating over json structures
    • MySQL's CALL statements, to call stored procedures.
    • PostgreSQL ^@ starts-with operator
• New carousel component to display a carousel of images. Contributed by @olivierauverlot !
  • image
• For those who write custom components, a new @component_index variable is available in templates to get the index of the current component in the page. This makes it easy to generate unique ids for components.

Contributors

• @olivierauverlot

SQLPage is a small web server that renders your SQL queries as beautiful interactive websites. This release has seen significant new features and fixes from new contributors, which is great and show the health of the project ! If you feel something is missing or isn't working quite right, all your contributions are always welcome.

On a side note, I gave a talk about SQLPage last December at PGConf.eu

1. New tracking component for beautiful and compact status reports: This feature adds a new way to display status reports, making them more visually appealing and concise.

   1. screenshot

2. New divider component to add a horizontal line between other components: This simple yet useful addition allows for better separation of elements on your pages.

   1. image

3. New breadcrumb component to display a breadcrumb navigation bar: This component helps users navigate through your website's hierarchical structure, providing a clear path back to the homepage.

   1. image

4. Multi-column layouts with embed attribute in card component: This feature enables you to create more complex and dynamic layouts within cards.

   1. image

5. Customizable y-axis step size in chart component with ystep attribute: This feature gives you more control over the chart's appearance, especially for situations with multiple series.

6. Updated default graph colors for better distinction: This enhancement ensures clarity and easy identification of different data series.

7. ID and class attributes for all components for easier styling and referencing: This improvement simplifies custom CSS customization and inter-page element linking.

8. Implementation of uploaded_file_mime_type function: This function allows you to determine the MIME type of a uploaded file.

9. Upgraded built-in SQLite database to version 3.45.0: This ensures compatibility with recent SQLite features and bug fixes. See sqlite release notes

10. Unicode support for built-in SQLite database: This enables case-insensitive string comparisons and lower/upper case transformations.

11. Improved card component with smaller margin below footer text: This fix ensures consistent and visually balanced card layouts.

Fixes

• v0.18.1 fixes a bug introduced in v0.18 with the size of graphs.
• v0.18.2 fixes a bug with missing chart titles.

SQLPage is a small web server that renders your SQL queries as beautiful interactive websites. This release has seen significant new features and fixes from new contributors, which is great and show the health of the project ! If you feel something is missing or isn't working quite right, all your contributions are always welcome.

On a side note, I gave a talk about SQLPage last December at PGConf.eu

1. New tracking component for beautiful and compact status reports: This feature adds a new way to display status reports, making them more visually appealing and concise.

   1. screenshot

2. New divider component to add a horizontal line between other components: This simple yet useful addition allows for better separation of elements on your pages.

   1. image

3. New breadcrumb component to display a breadcrumb navigation bar: This component helps users navigate through your website's hierarchical structure, providing a clear path back to the homepage.

   1. image

4. Multi-column layouts with embed attribute in card component: This feature enables you to create more complex and dynamic layouts within cards.

   1. image

5. Customizable y-axis step size in chart component with ystep attribute: This feature gives you more control over the chart's appearance, especially for situations with multiple series.

6. Updated default graph colors for better distinction: This enhancement ensures clarity and easy identification of different data series.

7. ID and class attributes for all components for easier styling and referencing: This improvement simplifies custom CSS customization and inter-page element linking.

8. Implementation of uploaded_file_mime_type function: This function allows you to determine the MIME type of a uploaded file.

9. Upgraded built-in SQLite database to version 3.45.0: This ensures compatibility with recent SQLite features and bug fixes. See sqlite release notes

10. Unicode support for built-in SQLite database: This enables case-insensitive string comparisons and lower/upper case transformations.

11. Improved card component with smaller margin below footer text: This fix ensures consistent and visually balanced card layouts.

v0.18.1 fixes a bug introduced in v0.18 with the size of graphs.

SQLPage is a small web server that renders your SQL queries as beautiful interactive websites. This release has seen significant new features and fixes from new contributors, which is great and show the health of the project ! If you feel something is missing or isn't working quite right, all your contributions are always welcome.

On a side note, I gave a talk about SQLPage last December at PGConf.eu

1. New tracking component for beautiful and compact status reports: This feature adds a new way to display status reports, making them more visually appealing and concise.

   1. screenshot

2. New divider component to add a horizontal line between other components: This simple yet useful addition allows for better separation of elements on your pages.

   1. image

3. New breadcrumb component to display a breadcrumb navigation bar: This component helps users navigate through your website's hierarchical structure, providing a clear path back to the homepage.

   1. image

4. Multi-column layouts with embed attribute in card component: This feature enables you to create more complex and dynamic layouts within cards.

   1. image

5. Customizable y-axis step size in chart component with ystep attribute: This feature gives you more control over the chart's appearance, especially for situations with multiple series.

6. Updated default graph colors for better distinction: This enhancement ensures clarity and easy identification of different data series.

7. ID and class attributes for all components for easier styling and referencing: This improvement simplifies custom CSS customization and inter-page element linking.

8. Implementation of uploaded_file_mime_type function: This function allows you to determine the MIME type of a uploaded file.

9. Upgraded built-in SQLite database to version 3.45.0: This ensures compatibility with recent SQLite features and bug fixes. See sqlite release notes

10. Unicode support for built-in SQLite database: This enables case-insensitive string comparisons and lower/upper case transformations.

11. Improved card component with smaller margin below footer text: This fix ensures consistent and visually balanced card layouts.

• The previous version reduced log verbosity, but also removed the ability to see the HTTP requests in the logs.
  This is now fixed, and you can see the HTTP requests again. Logging is still less verbose than before, but you can enable debug logs by setting the RUST_LOG environment variable to debug, or to sqlpage=debug to only see SQLPage debug logs.
• Better error message when failing to bind to a low port (<1024) on Linux. SQLPage now displays a message explaining how to allow SQLPage to bind to a low port.
• When https_domain is set, but a port number different from 443 is set, SQLPage now starts both an HTTP and an HTTPS server.
• Better error message when component order is invalid. SQLPage has "header" components, such as redirect and cookie, that must be executed before the rest of the page. SQLPage now displays a clear error message when you try to use them after other components.
• Fix 404 error not displaying. 404 responses were missing a content-type header, which made them invisible in the browser.
• Add an image_url row-level attribute to the datagrid component to display tiny avatar images in data grids.
• change breakpoints in the hero component to make it more responsive on middle-sized screens such as tablets or small laptops. This avoids the hero image taking up the whole screen on these devices.
• add an image_url row-level attribute to the list component to display small images in lists.
• Fix bad contrast in links in custom page footers.
• Add a new configuration option: environment. This allows you to set the environment in which SQLPage is running. It can be either development or production. In production mode, SQLPage will hide error messages and stack traces from the user, and will cache sql files in memory to avoid reloading them from disk when under heavy load.
• Add support for selected in multi-select inputs in the form component. This allows you to pre-select some options in a multi-select input.
• New function: sqlpage.protocol to get the protocol used to access the current page. This is useful to build links that point to your own site, and work both in http and https.
• Add an example to the documentation showing how to create heatmaps with the chart component.
• 18 new icons available: https://tabler.io/icons/changelog#2.43
• New top-level attributes for the datagrid component: description, description_md , icon , image_url.

Read the release announcement on the official website

bugfix release

fixes a bug where setting a variable to a non-string value would always set it to null

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:

🚀 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! 🚀✨