v0.31.0

SQLPage is a tool that allows users to create dynamic, SQL-powered websites with minimal coding effort.

This release, v0.31.0, introduces enhanced table functionalities, deeper database integration, smarter search features, and improved debugging with detailed error logs. It also upgrades security and optimizes chart performance, making it easier to build secure data-driven websites.

🚀 New Features

Improved Components

• Columns Component

  • Markdown-supported descriptions (description_md) allow richer formatting.
  • Add simple text items without needing JSON handling.
  • Optionally skip displaying items (null as item).
  • 

• Table Component

  • New freeze headers and columns feature improves usability with large tables.
  • Enhanced search logic ensures more precise matches (e.g., "xy" no longer matches separate x and y cells in adjacent columns).
  • Search box visibility is retained during horizontal scrolling.
    Technical: Adds freeze_headers, freeze_columns, and improves the internal search algorithm.
  • 

• Form Component

  • Added an empty option (empty_option) to dropdowns, enabling placeholder-like behavior.
    • 
  • Improved handling of large form submissions with configurable size limits (max_uploaded_file_size, default 5MB).
    Technical: There used to be a hardcoded limit to 16kB for all forms.

---

Database Enhancements

• Support for New Data Types:

  • Microsoft SQL Server now supports BIT columns.
  • Improved handling of DATETIMEOFFSET in MSSQL and TIMESTAMPTZ in PostgreSQL, preserving their timezones instead of converting them to UTC.

• Better JSON Handling:

  • Accept nested JSON objects and arrays as function parameters.
    Useful for advanced usage like calling external APIs using sqlpage.fetch with complex data structures.

• SQL Parser Update:

  • Upgraded to v0.52.0 with new features:
    • Added support for:
      • advanced JSON_TABLE usage in MySQL for working with JSON arrays.
      • EXECUTE statements with parameters in MSSQL for running stored procedures.
      • MSSQL’s TRY_CONVERT function for type conversion.
      • ANY, ALL, and SOME subqueries (e.g., SELECT * FROM t WHERE a = ANY (SELECT b FROM t2)).
      • LIMIT max_rows, offset syntax in SQLite.
      • Assigning column names aliases using = in MSSQL (e.g., SELECT col_name = value).
  • Fixes a bug where the parser would fail parse a SET clause for a variable named role.

---

Security and Performance

• Encrypted Login Support for MSSQL:

  • Ensures secure connections with flexible encryption modes:
    • No encryption (?encrypt=not_supported): For legacy systems and environments where SSL is blocked
    • Partial encryption (?encrypt=off): Protects login credentials but not data packets.
    • Full encryption (?encrypt=on): Secures both login and data.
      Technical: Controlled using the encrypt parameter (not_supported, off, or strict) in mssql connection strings.

• Chart Library Optimization:

  • Updated ApexCharts to v4.0.0.
  • Fixed duplicate library loads, speeding up pages with multiple charts.
  • Fixed a bug where timeline chart tooltips displayed the wrong labels.

---

🛠 Bug Fixes

Database and Compatibility Fixes

• Microsoft SQL Server:

  • Fixed decoding issues for less common data types.
  • Resolved bugs in reading VARCHAR columns from non-European collations.
  • Correctly handles REAL values.

• SQLite:

  • Eliminated spurious warnings when using SQLPage functions with JSON arguments.
    Technical: Avoids warnings like The column _sqlpage_f0_a1 is missing.

Component Fixes

• Card Component:

  • Fixed layout issues with embedded content (e.g., removed double borders).
    • 
  • Corrected misaligned loading spinners.

• Form Dropdowns:

  • Resolved state retention after form resets, ensuring dropdowns reset correctly.

Usability Enhancements

• Removed unnecessary padding around tables for cleaner layouts.
• Increased spacing between items in the columns component for improved readability.
• Database errors are now consistently logged and displayed with more actionable details.
  • 
    Technical: Ensures warnings in the browser and console for faster debugging.

---

v0.30.1

v0.30.1: Bugfix release

• fix a bug where table sorting would break if table search was not also enabled.

Many thanks to @DSMejantel for quickly reporting the issue.

v0.30.0

With SQLPage 0.30, build APIs in 2 lines of SQL

🤖 Easy APIs

• Enhanced CSV Support: The CSV component can now create URLs that trigger a CSV download directly on page load.
  • This finally makes it possible to allow the download of large datasets as CSV
  • This makes it possible to create an API that returns data as CSV and can be easily exposed to other software for interoperabily.
• Easy json APIs
  • The json component now accepts a second sql query, and will return the results as a json array in a very resource-efficient manner. This makes it easier and faster than ever to build REST APIs entirely in SQL.

    • select 'json' as component;
      select * from users;

    • [ { "id": 0, "name": "Jon Snow" }, { "id": 1, "name": "Tyrion Lannister" } ]

  • Ease of use : the component can now be used to automatically format any query result as a json array, without manually using your database''s json functions.
  • server-sent events : the component can now be used to stream query results to the client in real-time using server-sent events.

🔒 Database Connectivity

• Encrypted Microsoft SQL Server Connections: SQLPage now supports encrypted connections to SQL Server databases, enabling connections to secure databases (e.g., those hosted on Azure).
• Separate Database Password Setting: Added database_password configuration option to store passwords securely outside the connection string. This is useful for security purposes, to avoid accidentally leaking the password in logs. This also allows setting the database password as an environment variable directly, without having to URL-encode it inside the connection string.

😎 Developer experience improvements

• Improved JSON Handling: SQLPage now automatically converts JSON strings to JSON objects in databases like SQLite and MariaDB, making it easier to use JSON-based components.

  • -- Now works out of the box in SQLite
    select 'big_number' as component;
    select 'Daily performance' as title, perf as value;
        json_object(
          'label', 'Monthly',
          'link', 'monthly.sql'
        ) as dropdown_item
    from performance;

📈 Table & Search Improvements

• Initial Search Value: Pre-fill the search bar with a default value in tables with initial_search_value, making it easier to set starting filters.
• Faster Sorting and Searching: Table filtering and sorting has been entirely rewritten.
  • filtering is much faster for large datasets
  • sorting columns that contain images and links now works as expected
  • Since the new code is smaller, initial page loads should be slightly faster, even on pages that do not use tables

🖼️ UI & UX Improvements

• Carousel Updates:
  • Autoplay works as expected when embedded in a card.
  • Set image width and height to prevent layout shifts due to varying image sizes.
• Improved Site SEO: The site title in the shell component is no longer in <h1> tags, which should aid search engines in understanding content better, and avoid confusion between the site name and the page's title.

🛠️ Fixes and improvements

• Shell Component Search: Fixed search feature when no menu item is defined.
• Updated Icons: The Tabler icon set has been refreshed from 3.10 to 3.21, making many new icons available: https://tabler.io/changelog

v0.29.0

SQLPage v0.29 : better SQL websites 🎨

• ✨ New Components: columns for comparisons, foldable for expandable lists.
• ⚙️ Improvements: CLI arguments parsing, dynamic database URL, config validation.
• 🐛 Fixes: UI tweaks, table markdown rendering, shell font, and mobile menu display.

Detailed release notes

• New columns component: columns. Useful to display a comparison between items, or large key figures to an user.
  • 
• New foldable component: foldable. Useful to display a list of items that can be expanded individually.
  • 
• CLI arguments parsing: SQLPage now processes command-line arguments to set the web root and configuration directory. It also allows getting the currently installed version of SQLPage with sqlpage --version without starting the server.

  • $ sqlpage --help
    Build data user interfaces entirely in SQL. A web server that takes .sql files and formats the query result using pre-made configurable professional-looking components.
    
    Usage: sqlpage [OPTIONS]
    
    Options:
      -w, --web-root <WEB_ROOT>        The directory where the .sql files are located
      -d, --config-dir <CONFIG_DIR>    The directory where the sqlpage.json configuration, the templates, and the migrations are located
      -c, --config-file <CONFIG_FILE>  The path to the configuration file
      -h, --help                       Print help
      -V, --version                    Print version
    

• Configuration checks: SQLPage now checks if the configuration file is valid when starting the server. This allows to display a helpful error message when the configuration is invalid, instead of crashing or behaving unexpectedly. Notable, we now ensure critical configuration values like directories, timeouts, and connection pool settings are valid.

  • ./sqlpage --web-root /xyz
    [ERROR sqlpage] The provided configuration is invalid
    Caused by:
       Web root is not a valid directory: "/xyz"
    

• The configuration directory is now created if it does not exist. This allows to start the server without having to manually create the directory.
• The default database URL is now computed from the configuration directory, instead of being hardcoded to sqlite://./sqlpage/sqlpage.db. So when using a custom configuration directory, the default SQLite database will be created inside it. When using the default ./sqlpage configuration directory, or when using a custom database URL, the default behavior is unchanged.
• New navbar_title property in the shell component to set the title of the top navigation bar. This allows to display a different title in the top menu than the one that appears in the tab of the browser. This can also be set to the empty string to hide the title in the top menu, in case you want to display only a logo for instance.
• Fixed: The font property in the shell component was mistakingly not applied since v0.28.0. It works again.
• Updated SQL parser to v0.51.0. Improved INTERVAL parsing.
• Important note: this version removes support for the SET $variable = ... syntax in SQLite. This worked only with some databases. You should replace all occurrences of this syntax with SET variable = ... (without the $ prefix).
• slightly reduce the margin at the top of pages to make the content appear higher on the screen.
• fix the display of the page title when it is long and the sidebar display is enabled.
• Fix an issue where the color name blue could not be used in the chart component.
• divider component: Add new properties to the divider component: link, bold, italics, underline, size.
  • 
• form component: fix slight misalignment and sizing issues of checkboxes and radio buttons.
  • 
• table component: fixed a bug where markdown contents of table cells would not be rendered as markdown if the column name contained uppercase letters on Postgres. Column name matching is now case-insensitive, so 'title' as markdown will work the same as 'Title' as markdown. In postgres, non-double-quoted identifiers are always folded to lowercase.
• shell component: fixed a bug where the mobile menu would display even when no menu items were provided.

spreadsheet component

There is a new spreadsheet component that displays your data as an editable excel-like spreadsheet that supports custom formatting.

For licensing reasons, it is not included in the default SQLPage distribution, but you can download it from https://github.com/lovasoa/sqlpage-spreadsheet.

v0.28.0

🎉 SQLPage v0.28 🎉

• Chart component: fix the labels of pie charts displaying too many decimal places.
  • 
• You can now create a 404.sql file anywhere in your SQLPage project to handle requests to non-existing pages. This allows you to create custom 404 pages, or create nice URLs that don't end with .sql.
  • Create if /folder/404.sql exists, then it will be called for all URLs that start with folder and do not match an existing file.
• Updated SQL parser to v0.50.0
  • Support postgres String Constants with Unicode Escapes, like U&'\2713'. Fixes https://github.com/lovasoa/SQLpage/discussions/511
• New big_number component to display key statistics and indicators in a large, easy-to-read format. Useful for displaying KPIs, metrics, and other important numbers in dashboards and reports.
  • 
• Fixed small display inconsistencies in the shell component with the new sidebar feature (#556).
• Cleanly close all open database connections when shutting down sqlpage. Previously, when shutting down SQLPage, database connections that were opened during the session were not explicitly closed. These connections would remain open until the database itself closes them. Now, SQLPage ensures that all opened database connections are cleanly closed during shutdown. This guarantees that resources are freed immediately, ensuring more reliable operation, particularly in environments with limited database connections.

v0.27.0

• chart component
  • TreeMap charts in the chart component allow you to visualize hierarchical data structures.
    • 
  • Timeline charts allow you to visualize time intervals.
    • 
  • Fixed multiple small display issues in the chart component.
  • When no series name nor top-level title is provided, display the series anyway (with no name) instead of throwing an error in the javascript console.
  • updated Apex Charts to v3.52.0
    • see https://github.com/apexcharts/apexcharts.js/releases
• New sidebar attribute in the shell component to display the menu on the side instead of in the header
  • 
• Better error handling: SQLPage now stops processing the SQL file after the first error is encountered.
  • The previous behavior was to try parsing a new statement after a syntax error, leading to a cascade of irrelevant error messages after a syntax error.
  • Much better error messages when a call to sqlpage.fetch fails.
• Fixed a bug where in very specific conditions, sqlpage functions could mess up the order of the arguments passed to a sql query. This would happen when a sqlpage function was called with both a column from the database and a sqlpage variable in its arguments, and the query also contained references to other sqlpage variables after the sqlpage function call. An example would be select sqlpage.exec('xxx', some_column = $a) as a, $b as b from t. A test was added for this case.
• added a new url_encode helper for custom components to encode a string for use in a URL.
• CSV
  • fixed a bug where the CSV component would break when the data contained a # character.
  • properly escape fields in the CSV component to avoid generating invalid CSV files.
• Nicer inline code style in markdown.
• Fixed width attribute in the card component not being respected when the specified width was < 6.
• Fixed small inaccuracies in decimal numbers leading to unexpectedly long numbers in the output, such as 0.47000000000000003 instead of 0.47.
• Allow giving an id to HTML rows in the table component. This allows making links to specific rows in the table using anchor links. (my-table.sql#myid)
• Fixed a bug where long menu items in the shell component's menu would wrap on multiple lines.

v0.26.0

SQLPage v0.26 🚀

SQLPage makes it easy to build data applications entirely in SQL.
This release enhances customization and usability with new features like custom layouts, improved Datagrid displays, and better handling of empty Tables. Bug fixes in Form and Shell components and new HTML attributes improve overall performance. Security and compatibility updates were made too.

Come and build a rich data-driven applications effortlessly with sqlpage!

Also in the news: SQLPage just passed 1000 stars on github. Star us too 🌟 !

Components

Card

New width attribute in the card component to set the width of the card. This finally allows you to create custom layouts, by combining the embed and width attributes of the card component! This also updates the default layout of the card component: when columns is not set, there is now a default of 4 columns instead of 5.

Datagrid

fix datagrid color pills display when they contain long text.

Table

Fixed a bug that could cause issues with other components when a table was empty.
Improved handling of empty tables. Added a new empty_description attribute, which defaults to No data. This allows you to display a custom message when a table is empty.

Form

• Fixed a bug where a form input with a value of 0 would diplay as empty instead of showing the 0.
• Reduced the margin at the botton of forms to fix the appearance of forms that are validated by a button component declared separately from the form.

Shell

Fixed ugly wrapping of items in the header when the page title is long. We now have a nice text ellipsis (...) when the title is too long.

Fixed the link to the website title in the shell component.

Allow loading javascript ESM modules in the shell component with the new javascript_module property.

html

Added text and post_html properties to the html component. This allows to include sanitized user-generated content in the middle of custom HTML.

select 
    'html' as component;
select 
    '<b>Username</b>: <mark>' as html,
    'username that will be safely escaped: <"& ' as text,
    '</mark>' as post_html;

Other

• allow customizing the Content-Security-Policy in the configuration.
• the new default content security policy is both more secure and easier to use. You can now include inline javascript in your custom components with <script nonce="{{@csp_nonce}}">...</script>.
• update to sqlparser v0.49.0
  • support WITH ORDINALITY in postgres FROM clauses
• update to handlebars-rs v6
• fix the "started successfully" message being displayed before the error message when the server failed to start.
• add support for using the system's native SSL Certificate Authority (CA) store in sqlpage.fetch. See the new system_root_ca_certificates configuration option.

v0.25.0

This update introduces several enhancements and fixes.

• The hero component now supports reversing the order of text and images, allowing more flexibility in design.
• The datagrid component has been optimized for mobile displays by reducing the maximum item width.
• Additionally, a new html component has been added for displaying raw HTML content, aimed at advanced users with a caution on potential security risks.
• Error messages in the dynamic component and syntax errors have been improved for better clarity and troubleshooting.
• The update also includes the addition of 54 new icons and the latest version of apexcharts.js for enhanced visual elements.

Several bug fixes have been implemented, such as

• correct display of points with a latitude of 0 on the map component and
• consistent behavior of the lower() function in SQLite.
• along with better truncation of long page titles

The update enhances SQL capabilities

• adding the ability to use arbitrary SQL expressions as arguments to SQLPage functions in most cases
• supporting custom operators in postgres
• The new sqlpage.link function simplifies creating links with parameters between pages, ensuring proper encoding of special characters.
• Lastly, the update includes a new parameter in the run_sql function to pass variables to SQL files, promoting modular and reusable SQL code.

-- Example using sqlpage.link to create links with parameters
SELECT 'list' AS component;
SELECT
  product_name AS title,
  sqlpage.link('product.sql', json_object('product', product_name)) AS link
FROM products;

-- Before, you would manually build links like this:
-- CONCAT('/product.sql?product=', product_name)
-- but that would fail if product_name contained special characters like '&' or '%'

-- Example using run_sql with variables
-- The SQL file display_product.sql can be modular and reusable
-- It can accept parameters passed through json_object
-- This allows for dynamic and flexible SQL execution

SELECT 'dynamic' AS component, 
       sqlpage.run_sql('display_product.sql', json_object('product_id', product_id)) AS properties 
FROM products;

detailed notes: https://github.com/lovasoa/SQLpage/blob/main/CHANGELOG.md#0250-2024-07-13

v0.24.0

🚀 SQLPage just got a major upgrade!

Forms, maps, menus, performance, bug fixes

🗺️ The form and map components are way more powerful now. A limitation over the number of options in multi-select fields was removed, and maps are smarter about centering and allow removing the base map completely. The shell component got a nice boost too, making it a breeze to create complex menus and use custom fonts, adding icons, or fixing the top menu when scrolling in the page.
💻 Performance-wise, things are looking up. Pages load faster, and SQLite's been bumped to the latest version. Oh, and there's a bunch of new tricks for buttons, lists, and cards - like compact modes and background colors. Plus, we squashed some pesky bugs and threw in a default favicon to keep those logs clean. 🐞🔨

Check out the full list of changes and how to use them in your applications

v0.23.0

SQLPage v0.23: Improved Performance, Enhanced Components, and Bug Fixes

• 🔁 Connection Reuse: The sqlpage.run_sql function now reuses existing database connections for the current query. This enhancement allows for creating temporary tables, spanning SQL transactions over multiple run_sql calls, and generally makes run_sql more performant.

• 🗃️ Empty Uploaded Files Fix: Optional file upload fields that are left empty will no longer be accessible to SQLPage file-related functions like sqlpage.uploaded_file_path and sqlpage.uploaded_file_mime_type. These functions will now return NULL if no file is uploaded. Similarly, sqlpage.persist_uploaded_file will not create an empty file but will return NULL if no file is uploaded.

• 🖱️ Button Component Updates:

  • A new tooltip property has been added to the button component.
    • 
      Sure, here’s the revised point with a bit more detail for the target and rel properties:
  • The button component now supports:
    • download: makes the button download a file when clicked,
    • target: specifies where to open the linked document (e.g., in a new tab or window),
    • rel: defines the relationship between the current document and the linked document, and can also prevent search engines from following the link.
    • 

• 📋 CSV Component Fix: The separator parameter in the CSV component now functions correctly, enabling the creation of Excel-compatible CSVs in regions where ; is the expected separator.

  • 

• 🔍 Shell Component Update: A new search_value property has been added.

  • 

• 📏 Hero Component Text Fix: Long button text in the hero component now renders on multiple lines when the viewport is narrow.

  • 

• 🍪 Cookie Component Bug Fix: Fixed an issue where removing a cookie from a subdirectory was not working. See #361.

• 🛠️ SQL Parser Update: The SQL parser has been updated to fix support for AT TIME ZONE in PostgreSQL and GROUP_CONCAT() in MySQL.

• ⚠️ Form Field Warning: A new warning message is logged when attempting to use SET $x = if there is already a form field named x.

• 🗺️ Map Component Centering: The map component now centers on its markers if latitude and longitude properties are omitted. This makes it easier to create zoomed maps with a single marker. See issue.

  • 

• ⏲️ Fetch Timeout Option: The sqlpage.fetch function now includes a timeout option for requests, useful for handling slow or unreliable APIs and large payloads.

• 🎥 Hero Component Video Properties: The hero component now supports poster, loop, muted, and nocontrols properties for videos.

• 🌐 Icon Bug Fix: Fixed an issue where icons would disappear when serving a SQLPage website from a subdirectory using the site_prefix configuration option.