- 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. - 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.
- 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 withfolder
and do not match an existing file.
- Create if
- Updated SQL parser to v0.50.0
- Support postgres String Constants with Unicode Escapes, like
U&'\2713'
. Fixes #511
- Support postgres String Constants with Unicode Escapes, like
- 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 could remain open until the database closes it. 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.
- updated Apex Charts to v3.52.0
- 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. - 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 of0.47
. - 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. - Better error handling: Stop processing the SQL file after the first error is encountered.
- The previous behavior was to try paresing a new statement after a syntax error, leading to a cascade of irrelevant error messages after a syntax error.
- 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.
- Much better error messages when a call to sqlpage.fetch fails.
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.
fix datagrid color pills display when they contain long text.
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.
- Fixed a bug where a form input with a value of
0
would diplay as empty instead of showing the0
. - 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.
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.
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;
- 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 postgresFROM
clauses
- support
- 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 newsystem_root_ca_certificates
configuration option.
- hero component: allow reversing the order of text and images. Allows hero components with the text on the right and the image on the left.
- Reduce the max item width in the datagrid component for a better and more compact display on small screens. This makes the datagrid component more mobile-friendly. If you have a datagrid with long text items, this may impact the layout of your page. You can override this behavior by manually changing the
--tblr-datagrid-item-width
CSS variable in your custom CSS. - Apply migrations before initializing the on-database file system. This allows migrations to create files in the database file system.
- Added a new example to the documentation
- Bug fix: points with a latitude of 0 are now displayed correctly on the map component.
- Bug fix: in sqlite, lower(NULL) now returns NULL instead of an empty string. This is consistent with the standard behavior of lower() in other databases. SQLPage has its own implementation of lower() that supports unicode characters, and our implementation now matches the standard behavior of lower() in mainstream SQLite.
- Allow passing data from the database to sqlpage functions.
- SQLPage functions are special, because they are not executed inside your database, but by SQLPage itself before sending the query to your database. Thus, they used to require all the parameters to be known at the time the query is sent to your database.
- This limitation is now relaxed, and you can pass data from your database to SQLPage functions, at one condition: the function must be called at the top level of a
SELECT
statement. In this case, SQLPage will get the value of the function arguments from the database, and then execute the function after the query has been executed. - This fixes most errors like:
Arbitrary SQL expressions as function arguments are not supported.
.
- Better error messages in the dynamic component when properties are missing.
- Bug fix: the top bar was shown only when page title was defined. Now icon, image, and menu_item are also considered.
- 54 new icons (tabler icons updated from 3.4 to 3.7)
- updated the SQL parser to v0.48
- upport UPDATE statements that contain tuple assignments , like
UPDATE table SET (a, b) = (SELECT 1, 2)
- support custom operators in postgres. Usefull when using extensions like PostGIS, PGroonga, pgtrgm, or pg_similarity, which define custom operators like
&&&
,@>
,<->
,~>
,~>=
,~<=
,<@
...
- upport UPDATE statements that contain tuple assignments , like
- New
html
component to display raw HTML content. This component is meant to be used by advanced users who want to display HTML content that cannot be expressed with the other components. Make sure you understand the security implications before using this component, as using untrusted HTML content can expose your users to cross-site scripting (XSS) attacks. - New parameter in the
run_sql
function to pass variables to the included SQL file, instead of using the global variables. Together with the new ability to pass data from the database to SQLPage functions, this allows you to create more modular and reusable SQL files. For instance, the following is finally possible:select 'dynamic' as component, sqlpage.run_sql('display_product.sql', json_object('product_id', product_id)) as properties from products;
- New icons (see tabler icons 3.10)
- Updated apexcharts.js to v3.50.0
- Improve truncation of long page titles
- new function:
sqlpage.link
to easily create links with parameters between pages. For instance, you can now useselect 'list' as component; select product_name as title, sqlpage.link('product.sql', json_object('product', product_name)) as link from products;
- Before, you would usually build the link manually with
CONCAT('/product.sql?product=', product_name)
, which would fail if the product name contained special characters like '&'. The newsqlpage.link
function takes care of encoding the parameters correctly.
- Before, you would usually build the link manually with
- Calls to
json_object
are now accepted as arguments to SQLPage functions. This allows you to pass complex data structures to functions such assqlpage.fetch
,sqlpage.run_sql
, andsqlpage.link
. - Better syntax error messages, with a short quotation of the part of the SQL file that caused the error:
- in the form component, searchable
select
fields now support more than 50 options. They used to display only the first 50 options. - map component
- automatically center the map on the contents when no top-level latitude and longitude properties are provided even when the map contains geojson data.
- allow using
FALSE as tile_source
to completely remove the base map. This makes the map component useful to display even non-geographical geometric data.
- Fix a bug that occured when no
database_url
was provided in the configuration file. SQLPage would generate an incorrect default SQLite database URL. - Add a new
background_color
attribute to the card component to set the background color of the card. - new handlebars helper for custom components:
{{app_config 'property'}}
to access the configuration object from the handlebars template. - Prevent form validation and give a helpful error message when an user tries to submit a form with a file upload field that is above the maximum file size.
- Fix a bug in
sqlpage.read_file_as_data_url
where it would truncate the mime subtype of the file. This would cause the browser to refuse to display SVG files, for instance. - Avoid vertical scrolling caused by the footer even when the page content is short.
- Add a new
compact
attribute to the list, allowing to display more items in a list without taking up too much space. Great for displaying long lists of items. - Add property
narrow
to the button component to make the button narrower. Ideal for buttons with icons. - new
tooltip
property in the datagrid component. - datagrids are now slightly more compact, with less padding and less space taken by each item.
- fix a bug in the card component where the icon would sometimes overflow the card's text content.
- new
image
property in the button component to display a small image inside a button. - In the
shell
component- allow easily creating complex menus even in SQLite:
select 'shell' as component, 'My Website' as title, '{"title":"About","submenu":[{"link":"/x.sql","title":"X"},{"link":"/y.sql","title":"Y"}]}' as menu_item;
- allow easily creating optional menu items that are only displayed in some conditions:
select 'shell' as component, 'My Website' as title, CASE WHEN $role = 'admin' THEN 'Admin' END as menu_item;
- Add the ability to use local Woff2 fonts in the shell component. This is useful to use custom fonts in your website, without depending on google fonts (and disclosing your users' IP addresses to google).
- Add a
fixed_top_menu
attribute to make the top menu sticky. This is useful to keep the menu visible even when the user scrolls down the page.
- allow easily creating complex menus even in SQLite:
- Add a
wrap
attribute to thelist
component to wrap items on multiple lines when they are too long. - New
max_pending_rows
configuration option to limit the number of messages that can be sent to the client before they are read. Usefule when sending large amounts of data to slow clients. - New
compress_responses
configuration option. Compression is still on by default, but can now be disabled to allow starting sending the page sooner. It's sometimes better to start displaying the shell immediateley and render components as soon as they are ready, even if that means transmitting more data over the wire. - Update sqlite to v3.46: https://www.sqlite.org/releaselog/3_46_0.html
- major upgrades to PRAGMA optimize, making it smarter and more efficient on large databases
- enhancements to date and time functions, including easy week-of-year calculations
- support for underscores in numeric literals. Write
1_234_567
instead of1234567
- new
json_pretty()
function
- Faster initial page load. SQLPage used to wait for the first component to be rendered before sending the shell to the client. We now send the shell immediately, and the first component as soon as it is ready. This can make the initial page load faster, especially when the first component requires a long computation on the database side.
- Include a default favicon when none is specified in the shell component. This fixes the
Unable to read file "favicon.ico"
error message that would appear in the logs by default.
- fix a bug in the csv component. The
separator
parameter now works as expected. This facilitates creating excel-compatible CSVs in european countries where excel expects the separator to be;
instead of,
. - new
tooltip
property in the button component. - New
search_value
property in the shell component. - Fixed a display issue in the hero component when the button text is long and the viewport is narrow.
- reuse the existing opened database connection for the current query in
sqlpage.run_sql
instead of opening a new one. This makes it possible to create a temporary table in a file, and reuse it in an included script, create a SQL transaction that spans over multiple run_sql calls, and should generally make run_sql more performant. - Fixed a bug in the cookie component where removing a cookie from a subdirectory would not work.
- Updated SQL parser. Fixes support for
AT TIME ZONE
in postgres. FixesGROUP_CONCAT()
in MySQL. - Add a new warning message in the logs when trying to use
SET $x =
when there is already a form field namedx
. - Empty Uploaded files: when a form contains an optional file upload field, and the user does not upload a file, the field used to still be accessible to SQLPage file-related functions such as
sqlpage.uploaded_file_path
andsqlpage.uploaded_file_mime_type
. This is now fixed, and these functions will returnNULL
when the user does not upload a file.sqlpage.persist_uploaded_file
will not create an empty file in the target directory when the user does not upload a file, instead it will do nothing and returnNULL
. - In the map component, when top-level latitude and longitude properties are omitted, the map will now center on its markers. This makes it easier to create zoomed maps with a single marker.
- In the button component, add a
download
property to make the button download a file when clicked, atarget
property to open the link in a new tab, and arel
property to prevent search engines from following the link. - New
timeout
option in the sqlpage.fetch function to set a timeout for the request. This is useful when working with slow or unreliable APIs, large payloads, or when you want to avoid waiting too long for a response. - In the hero component, add a
poster
property to display a video poster image, aloop
property to loop the video (useful for short animations), amuted
property to mute the video, and anocontrols
property to hide video controls. - Fix a bug where icons would disappear when serving a SQLPage website from a subdirectory and not the root of the (sub)domain using the
site_prefix
configuration option.
- Important Security Fix: The behavior of
SET $x
has been modified to matchSELECT $x
.- Security Risk: Previously,
SET $x
could be overwritten by a POST parameter namedx
. - Solution: Upgrade to SQLPage v0.22. If not possible, then update your application to use
SET :x
instead ofSET $x
. - For more information, see GitHub Issue #342.
- Security Risk: Previously,
- Deprecation Notice: Reading POST variables using
$x
.- New Standard: Use
:x
for POST variables and$x
for GET variables. - Current Release Warning: Using
$x
for POST variables will display a console warning:Deprecation warning! $x was used to reference a form field value (a POST variable) instead of a URL parameter. This will stop working soon. Please use :x instead.
- Future Change:
$x
will evaluate toNULL
if no GET variable namedx
is present, regardless of any POST variables. - Detection and Update: Use provided warnings to find and update deprecated usages in your code.
- Reminder about GET and POST Variables:
- GET Variables: Parameters included in the URL of an HTTP GET request, used to retrieve data. Example:
https://example.com/page?x=value
, wherex
is a GET variable. - POST Variables: Parameters included in the body of an HTTP POST request, used for form submissions. Example: the value entered by the user in a form field named
x
.
- GET Variables: Parameters included in the URL of an HTTP GET request, used to retrieve data. Example:
- New Standard: Use
- Two backward-incompatible changes in the chart component's timeseries plotting feature (actioned with
TRUE as time
):- when providing a number for the x value (time), it is now interpreted as a unix timestamp, in seconds (number of seconds since 1970-01-01 00:00:00 UTC). It used to be interpreted as milliseconds. If you were using the
TRUE as time
syntax with integer values, you will need to divide your time values by 1000 to get the same result as before.- This change makes it easier to work with time series plots, as most databases return timestamps in seconds. For instance, in SQLite, you can store timestamps as integers with the
unixepoch()
function, and plot them directly in SQLPage.
- This change makes it easier to work with time series plots, as most databases return timestamps in seconds. For instance, in SQLite, you can store timestamps as integers with the
- when providing an ISO datetime string for the x value (time), without an explicit timezone, it is now interpreted and displayed in the local timezone of the user. It used to be interpreted as a local time, but displayed in UTC, which was confusing. If you were using the
TRUE as time
syntax with naive datetime strings (without timezone information), you will need to convert your datetime strings to UTC on the database side if you want to keep the same behavior as before. As a side note, it is always recommended to store and query datetime strings with timezone information in the database, to avoid ambiguity.- This change is particularly useful in SQLite, which generates naive datetime strings by default. You should still store and query datetimes as unix timestamps when possible, to avoid ambiguity and reduce storage size.
- when providing a number for the x value (time), it is now interpreted as a unix timestamp, in seconds (number of seconds since 1970-01-01 00:00:00 UTC). It used to be interpreted as milliseconds. If you were using the
- When calling a file with
sqlpage.run_sql
, the target file now has access to uploaded files. - New article by Matthew Larkin about migrations.
- Add a row-level
id
attribute to the button component. - Static assets (js, css, svg) needed to build SQLPage are now cached individually, and can be downloaded separately from the build process. This makes it easier to build SQLPage without internet access. If you use pre-built SQLPage binaries, this change does not affect you.
- New
icon_after
row-level property in the button component to display an icon on the right of a button (after the text). Contributed by @amrutadotorg. - New demo example: dark theme. Contributed by @lyderic.
- Add the ability to bind to a unix socket instead of a TCP port for better performance on linux. Contributed by @vlasky.
sqlpage.hash_password(NULL)
now returnsNULL
instead of throwing an error. This behavior was changed unintentionally in 0.20.5 and could have broken existing SQLPage websites.- The dynamic component now supports multiple
properties
attributes. The following is now possible:select 'dynamic' as component, '{ "component": "card", "title": "Hello" }' as properties, '{ "title": "World" }' as properties;
- Casting values from one type to another using the
::
operator is only supported by PostgreSQL. SQLPage versions before 0.20.5 would silently convert all casts to theCAST(... AS ...)
syntax, which is supported by all databases. Since 0.20.5, SQLPage started to respect the original::
syntax, and pass it as-is to the database. This broke existing SQLPage websites that used the::
syntax with databases other than PostgreSQL. For backward compatibility, this version of SQLPage re-establishes the previous behavior, converts::
casts on non-PostgreSQL databases to theCAST(... AS ...)
syntax, but will display a warning in the logs.- In short, if you saw an error like
Error: unrecognized token ":"
after upgrading to 0.20.5, this version should fix it.
- In short, if you saw an error like
- The
dynamic
component now properly displays error messages when its properties are invalid. There used to be a bug where errors would be silently ignored, making it hard to debug invalid dynamic components. - New
sqlpage.request_method
function to get the HTTP method used to access the current page. This is useful to create pages that behave differently depending on whether they are accessed with a GET request (to display a form, for instance) or a POST request (to process the form). - include the trailing semicolon as a part of the SQL statement sent to the database. This doesn't change anything in most databases, but Microsoft SQL Server requires a trailing semicolon after certain statements, such as
MERGE
. Fixes issue #318 - New
readonly
anddisabled
attributes in the form component to make form fields read-only or disabled. This is useful to prevent the user from changing some fields. - 36 new icons (tabler icons 3.4)
- Bug fixes in charts (apexcharts.js v3.49.1)
- Searchable multi-valued selects in the form component
- Added support for SSL client certificates in MySQL and Postgres
- SSL client certificates are commonly used to secure connections to databases in cloud environments. To connect to a database that requires a client certificate, you can now use the ssl_cert and ssl_key connection options in the connection string. For example: postgres://user@host/db?ssl_cert=/path/to/client-cert.pem&ssl_key=/path/to/client-key.pem
- The SQLPage function system was greatly improved
- All the functions can now be freely combined and nested, without any limitation. No more
Expected a literal single quoted string.
errors when trying to nest functions. - The error messages when a function call is invalid were rewritten, to include more context, and provide suggestions on how to fix the error. This should make it easier get started with SQLPage functions. Error messages should always be clear and actionnable. If you encounter an error message you don't understand, please open an issue on the SQLPage repository.
- Adding new functions is now easier, and the code is more maintainable. This should make it easier to contribute new functions to SQLPage. If you have an idea for a new function, feel free to open an issue or a pull request on the SQLPage repository. All sqlpage functions are defined in
functions.rs
.
- All the functions can now be freely combined and nested, without any limitation. No more
- The
shell-empty
component (used to create pages without a shell) now supports thehtml
attribute, to directly set the raw contents of the page. This is useful to advanced users who want to generate the page content directly in SQL, without using the SQLPage components. - Updated sqlparser to v0.46
- The changes include support for DECLARE parsing and CONVERT styles in MSSQL, improved JSON access parsing and ?-based jsonb operators in Postgres, and
ALTER TABLE ... MODIFY
support for MySQL.
- The changes include support for DECLARE parsing and CONVERT styles in MSSQL, improved JSON access parsing and ?-based jsonb operators in Postgres, and
- Improvements to the fetch function
- Set a default user-agent header when none is specified (
User-Agent: sqlpage
). - bundle root certificates with sqlpage so that we can always access HTTPS URLs even on outdated or stripped-down systems.
- update our https library to the latest version everywhere, to avoid having to bundle two distinct versions of it.
- Set a default user-agent header when none is specified (
- New
dropdown
row-level property in theform
component - Adds a new
sqlpage.fetch
function that allows sending http requests from SQLPage. This is useful to query external APIs. This avoids having to resort tosqlpage.exec
. - Fixed a bug that occured when using both HTTP and HTTPS in the same SQLPage instance. SQLPage tried to bind to the same (HTTP)
port twice instead of binding to the HTTPS port. This is now fixed, and SQLPage can now be used with both a non-443
port
and anhttps_domain
set in the configuration file. - Updated sqlparser
- adds support for named windows in window functions
- New icons with tabler icons 3.2: https://tabler.io/icons/changelog
- Optimize queries like
select 'xxx' as component, sqlpage.some_function(...) as parameter
to avoid making an unneeded database query. This is especially important for the performance ofsqlpage.run_sql
and thedynamic
component.
- the default component, used when no
select '...' as component
is present, is now table. It used to be thedebug
component instead.table
makes it extremely easy to display the results of any SQL query in a readable manner. Just write any query in a.sql
file open it in your browser, and you will see the results displayed in a table, without having to use any SQLPage-specific column names or attributes. - Better error messages when a custom component contains a syntax error. Fix contributed upstream
- Lift a limitation on sqlpage function nesting. In previous versions, some sqlpage functions could not be used inside other sqlpage functions. For instance,
sqlpage.url_encode(sqlpage.exec('my_program'))
used to throw an error sayingNested exec() function not allowed
. This limitation is now lifted, and you can nest any sqlpage function inside any other sqlpage function. - Allow string concatenation in inside sqlpage function parameters. For instance,
sqlpage.exec('echo', 'Hello ' || 'world')
is now supported, whereas it used to throw an error sayingexec('echo', 'Hello ' || 'world') is not a valid call. Only variables (such as $my_variable) and sqlpage function calls (such as sqlpage.header('my_header')) are supported as arguments to sqlpage functions.
. - Bump the minimal supported rust version to 1.77 (this is what allows us to easily handle nested sqlpage functions)
- More than 200 new icons, with tabler icons v3
- New
sqlpage.persist_uploaded_file
function to save uploaded files to a permanent location on the local filesystem (where SQLPage is running). This is useful to store files uploaded by users in a safe location, and to serve them back to users later. - Correct error handling for file uploads. SQLPage used to silently ignore file uploads that failed (because they exceeded max_uploaded_file_size, for instance), but now it displays a clear error message to the user.
- file inclusion. This is a long awaited feature that allows you to include the contents of one file in another. This is useful to factorize common parts of your website, such as the header, or the authentication logic. There is a new
sqlpage.run_sql
function that runs a given SQL file and returns its result as a JSON array. Combined with the existingdynamic
component, this allows you to include the content of a file in another, like this:
select 'dynamic' as component, sqlpage.run_sql('header.sql') as properties;
- more powerful dynamic component: the
dynamic
component can now be used to generate the special header components too, such as theredirect
,cookie
,authentication
,http_header
andjson
components. The shell component used to be allowed in dynamic components, but only if they were not nested (a dynamic component inside another one). This limitation is now lifted. This is particularly useful in combination with the new file inclusion feature, to factorize common parts of your website. There used to be a limited to how deeply nested dynamic components could be, but this limitation is now lifted too. - Add an
id
attribute to form fields in the form component. This allows you to easily reference form fields in custom javascript code. - New
rss
component to create RSS feeds, including podcast feeds. You can now create and manage your podcast feed entirely in SQL, and distribute it to all podcast directories such as Apple Podcasts, Spotify, and Google Podcasts. - Better error handling in template rendering. Many template helpers now display a more precise error message when they fail to execute. This makes it easier to debug errors when you develop your own custom components.
- better error messages when an error occurs when defining a variable with
SET
. SQLPage now displays the query that caused the error, and the name of the variable that was being defined. - Updated SQL parser to v0.44
- support EXECUTE ... USING in PostgreSQL
- support
INSERT INTO ... SELECT ... RETURNING
, which allows you to insert data into a table, and easily pass values from the inserted row to a SQLPage component. postgres docs, mysql docs, sqlite docs - support
UPDATE ... FROM
in SQLite
- Bug fixes in charts. See apexcharts.js v3.47.0
- 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 (./
) andSQLPAGE_CONFIGURATION_DIRECTORY
(./sqlpage/
), you should upgrade to this version as soon as possible. If you are using a customSQLPAGE_WEB_ROOT
orSQLPAGE_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.
- New
SQLPAGE_CONFIGURATION_DIRECTORY
environment variable to set the configuration directory from the environment. The configuration directory is where SQLPage looks for thesqlpage.json
configuration file, for themigrations
andtemplates
directories, and theon_connect.sql
file. It used to be hardcoded to./sqlpage/
, which made each SQLPage invokation dependent on the current working directory. Now you can, for instance, setSQLPAGE_CONFIGURATION_DIRECTORY=/etc/sqlpage/
in your environment, and SQLPage will look for its configuration files in/etc/sqlpage
, which is a more standard location for configuration files in a Unix environment.- The official docker image now sets
SQLPAGE_CONFIGURATION_DIRECTORY=/etc/sqlpage/
by default, and changes the working directory to/var/www/
by default.⚠️ WARNING: This change can break your docker image if you relied on setting the working directory to/var/www
and putting the configuration in/var/www/sqlpage
. In this case, the recommended setup is to store your sqlpage configuration directory and sql files in different directory. For more information see this issue.
- The official docker image now sets
- Updated the chart component to use the latest version of the charting library
- Updated Tabler Icon library to v2.47 with new icons
- Added
prefix
,prefix_icon
andsuffix
attributes to theform
component to create input groups. Useful to add a currency symbol or a unit to a form input, or to visually illustrate the type of input expected. - Added
striped_rows
,striped_columns
,hover
,border
, andsmall
attributes to the table component. - In the cookie component, set cookies for the entire website by default. The old behavior was to set the cookie only for files inside the current folder by default, which did not match the documentation, that says "If not specified, the cookie will be sent for all paths".
- Dynamic components at the top of sql files.
- If you have seen Dynamic components at the top level are not supported, except for setting the shell component properties in the past, you can now forget about it. You can now use dynamic components at the top level of your sql files, and they will be interpreted as expected.
- Custom shells:
- It has always been possible to change the default shell of a SQLPage website by writing a
sqlpage/shell.handlebars
file. But that forced you to have a single shell for the whole website. It is now possible to have multiple shells, just by creating multipleshell-*.handlebars
files in thesqlpage
directory. Ashell-empty
file is also provided by default, to create pages without a shell (useful for returning non-html content, such as an RSS feed).
- It has always been possible to change the default shell of a SQLPage website by writing a
- New
edit_link
,delete_link
, andview_link
row-level attributes in the list component to add icons and links to each row. - Multiple page layouts : The page layout is now configurable from the shell component. 3 layouts are available:
boxed
(the default),fluid
(full width), andhorizontal
(with boxed contents but a full-width header).
- 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
- MySQL's
- Updated sql parser, to add support for new syntax, including:
- New carousel component to display a carousel of images.
- 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.
- Completes the 0.18.1 fix for the
chart
component: fix missing chart title.
- Fixes a bug introduced in 0.18.0 where the
chart
component would not respect itsheight
attribute.
- Fix small display issue on cards without a title.
- New component:
tracking
for beautiful and compact status reports. - New component:
divider
to add a horizontal line between other components. - New component:
breadcrumb
to display a breadcrumb navigation bar. - fixed a small visual bug in the
card
component, where the margin below footer text was too large. - new
ystep
top-level attribute in thechart
component to customize the y-axis step size. - Updated default graph colors so that all series are easily distinguishable even when a large number of series are displayed.
- New
embed
attribute in thecard
component that lets you build multi-column layouts of various components with cards. - Added
id
andclass
attributes to all components, to make it easier to style them with custom CSS and to reference them in intra-page links and custom javascript code. - Implemented uploaded_file_mime_type
- Update the built-in SQLite database to version 3.45.0: https://www.sqlite.org/releaselog/3_45_0.html
- Add support for unicode in the built-in SQLite database. This includes the
lower
andupper
functions, and theNOCASE
collation.
- 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 todebug
, or tosqlpage=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 eitherdevelopment
orproduction
. Inproduction
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
.
This release is all about a long awaited feature: file uploads. Your SQLPage website can now accept file uploads from users, store them either in a directory or directly in a database table.
You can add a file upload button to a form with a simple
select 'form' as component;
select 'user_file' as name, 'file' as type;
when received by the server, the file will be saved in a temporary directory (customizable with TMPDIR
on linux). You can access the temporary file path with the new sqlpage.uploaded_file_path
function.
You can then persist the upload as a permanent file on the server with the sqlpage.exec
function:
set file_path = sqlpage.uploaded_file_path('user_file');
select sqlpage.exec('mv', $file_path, '/path/to/my/file');
or you can store it directly in a database table with the new sqlpage.read_file_as_data_url
and sqlpage.read_file_as_text
functions:
insert into files (content) values (sqlpage.read_file_as_data_url(sqlpage.uploaded_file_path('user_file')))
returning 'text' as component, 'Uploaded new file with id: ' || id as contents;
The maximum size of uploaded files is configurable with the max_uploaded_file_size
configuration parameter. By default, it is set to 5 MiB.
SQLPage can also parse uploaded CSV files and insert them directly into a database table.
SQLPage re-uses PostgreSQL's COPY
syntax
to import the CSV file into the database.
When connected to a PostgreSQL database, SQLPage will use the native COPY
statement,
for super fast and efficient on-database CSV parsing.
But it will also work with any other database as well, by
parsing the CSV locally and emulating the same behavior with simple INSERT
statements.
user_file_upload.sql
:
select 'form' as component, 'bulk_user_import.sql' as action;
select 'user_file' as name, 'file' as type, 'text/csv' as accept;
bulk_user_import.sql
:
-- create a temporary table to preprocess the data
create temporary table if not exists csv_import(name text, age text);
delete from csv_import; -- empty the table
-- If you don't have any preprocessing to do, you can skip the temporary table and use the target table directly
copy csv_import(name, age) from 'user_file'
with (header true, delimiter ',', quote '"', null 'NaN'); -- all the options are optional
-- since header is true, the first line of the file will be used to find the "name" and "age" columns
-- if you don't have a header line, the first column in the CSV will be interpreted as the first column of the table, etc
-- run any preprocessing you want on the data here
-- insert the data into the users table
insert into users (name, email)
select upper(name), cast(email as int) from csv_import;
sqlpage.uploaded_file_path
to get the temprary local path of a file uploaded by the user. This path will be valid until the end of the current request, and will be located in a temporary directory (customizable withTMPDIR
). You can usesqlpage.exec
to operate on the file, for instance to move it to a permanent location.sqlpage.uploaded_file_mime_type
to get the type of file uploaded by the user. This is the MIME type of the file, such asimage/png
ortext/csv
. You can use this to easily check that the file is of the expected type before storing it.
The new Image gallery example in the official repository shows how to use these functions to create a simple image gallery with user uploads.
These new functions are useful to read the content of a file uploaded by the user, but can also be used to read any file on the server.
sqlpage.read_file_as_text
reads the contents of a file on the server and returns a text string.sqlpage.read_file_as_data_url
reads the contents of a file on the server and returns a data URL. This is useful to embed images directly in web pages, or make link
This is the other big feature of this release: SQLPage now supports HTTPS !
And it does not require you to do a lot of manual configuration that will compromise your security if you get it wrong, like most other web servers do. You just give SQLPage your domain name, and it will take care of the rest.
And while we're at it, SQLPage also supports HTTP/2, for even faster page loads.
To enable HTTPS, you need to buy a domain name
and make it point to the server where SQLPage is running.
Then set the https_domain
configuration parameter to yourdomain.com
in your sqlpage.json
configuration file.
{
"https_domain": "my-cool-website.com"
}
That's it. No external tool to install, no certificate to generate, no configuration to tweak. No need to restart SQLPage either, or to worry about renewing your certificate when it expires. SQLPage will automatically request a certificate from Let's Encrypt by default, and does not even need to listen on port 80 to do so.
SQLPage needs to parse SQL queries to be able to bind the right parameters to them, and to inject the results of built-in sqlpage functions in them. The parser we user is very powerful and supports most SQL features, but there are some edge cases where it fails to parse a query. That's why we contribute to it a lot, and bring the latest version of the parser to SQLPage as soon as it is released.
SQLPage now supports the FOR JSON
syntax in MS SQL Server.
This unlocks a lot of new possibilities, that were previously only available in other databases.
This is particularly interesting to build complex menus with the shell
component,
to build multiple-answer select inputs with the form
component,
and to create JSON APIs.
- SQLPage now supports the custom
CONVERT
expression syntax for MS SQL Server, and the one for MySQL. - SQLPage now supports the
VARCHAR(MAX)
type in MS SQL Server and uses it for all variables bound as parameters to your SQL queries (we used to useVARCHAR(8000)
before). INSERT INTO ... DEFAULT VALUES ...
is now supported
- Dates and timestamps returned from the database are now always formatted in ISO 8601 format, which is the standard format for dates in JSON. This makes it easier to use dates in SQLPage.
- The
cookie
component now supports setting an explicit expiration date for cookies. - The
cookie
component now supports setting theSameSite
attribute of cookies, and defaults toSameSite=Strict
for all cookies. What this means in practice is that cookies set by SQLPage will not be sent to your website if the user is coming from another website. This prevents someone from tricking your users into executing SQLPage queries on your website by sending them a malicious link. - Bugfix: setting
min
ormax
to0
in a number field in theform
component now works as expected. - Added support for
.env
files to set SQLPage's environment variables. - Better responsive design in the card component. Up to 5 cards per line on large screens. The number of cards per line is still customizable with the
columns
attribute. - New icons:
- fix a bug where setting a variable to a non-string value would always set it to null
- clearer debug logs (wooorm/markdown-rs#92)
- update compiler to rust 1.74
- use user id and group id 1000 in docker image (this is the default user id in most linux distributions)
-
Add special handling of hidden inputs in forms. Hidden inputs are now completely invisible to the end user, facilitating the implementation of multi-step forms, csrf protaction, and other complex forms.
-
36 new icons available
-
Support multiple statements in
on_connect.sql
in MySQL. -
Randomize postgres prepared statement names to avoid name collisions. This should fix a bug where SQLPage would report errors like
prepared statement "sqlx_s_1" already exists
when using a connection pooler in front of a PostgreSQL database. 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 themax_connections
configuration parameter to1
to disable the connection pooling logic in SQLPage. -
SQL statements are now prepared lazily right before their first execution, instead of all at once when a file is first loaded, which allows referencing a temporary table created at the start of a file in a later statement in the same file. This works by delegating statement preparation to the database interface library we use (sqlx). The logic of preparing statements and caching them for later reuse is now entirely delegated to sqlx. This also nicely simplifies the code and logic inside sqlpage itself, and should slightly improve performance and memory usage.
-
Creating temporary tables at the start of a file is a nice way to keep state between multiple statements in a single file, without having to use variables, which can contain only a single string value:
DROP VIEW IF EXISTS current_user; 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'); SELECT 'card' as component, 'Welcome, ' || username as title FROM current_user;
-
-
Add support for resetting variables to a
NULL
value usingSET
. Previously, storingNULL
in a variable would store the string'null'
instead of theNULL
value. This is now fixed.SET myvar = NULL; SELECT 'card' as component; SELECT $myvar IS NULL as title; -- this used to display false, it now displays true
- Several improvements were made to the map component
- Fix a bug where the new geojson support in the map component would not work when the geojson was passed as a string. This impacted databases that do not support native json objects, such as SQLite.
- Improve support for geojson points (in addition to polygons and lines) in the map component.
- Add a new
size
parameter to the map component to set the size of markers. - Document the
height
parameter to customize the size of the map. tile_source
parameter to customize the map tiles, giving completely free control over the map appearance.attribution
parameter to customize or remove the small copyright information text box at the bottom of the map.- Add the ability to customize top navigation links and to create submenus in the
shell
component.- Postgres example:
select 'shell' as component, 'SQLPage' as title, JSON('{ "link":"/", "title":"Home" }') as menu_item, JSON('{ "title":"Options", "submenu":[ {"link":"1.sql","title":"Page 1"}, {"link":"2.sql","title":"Page 2"} ]}') as menu_item;
- note: this requires a database that supports json objects natively. If you are using SQLite, you can work around this limitation by using the
dynamic
component.
- Updated the embedded database to SQLite 3.44, which improves performance, compatibility with other databases, and brings new date formatting functions. The new
ORDER BY
clause in aggregate functions is not supported yet in SQLPage.
- Many improvements in the
form
component- Multiple form fields can now be aligned on the same line using the
width
attribute. - A reset button can now be added to the form using the
reset
top-level attribute. - The submit button can now be customized, and can be removed completely, which is useful to create multiple submit buttons that submit the form to different targets.
- Multiple form fields can now be aligned on the same line using the
- Support non-string values in markdown fields.
NULL
values are now displayed as empty strings, numeric values are displayed as strings, booleans astrue
orfalse
, and arrays as lines of text. This avoids the need to cast values to strings in SQL queries. - Revert a change introduced in v0.15.0:
- Re-add the systematic
CAST(? AS TEXT)
around variables, which helps the database know which type it is dealing with in advance. This fixes a regression in 0.15 where some SQLite websites were broken because of missing affinity information. In SQLiteSELECT '1' = 1
returnsfalse
butSELECT CAST('1' AS TEXT) = 1
returnstrue
. This also fixes error messages likecould not determine data type of parameter $1
in PostgreSQL.
- Re-add the systematic
- Fix a bug where cookie removal set the cookie value to the empty string instead of removing the cookie completely.
- Support form submission using the button component using its new
form
property. This allows you to create a form with multiple submit buttons that submit the form to different targets. - Custom icons and colors for markers in the map component.
- Add support for GeoJSON in the map component. This makes it much more generic and allows you to display any kind of geographic data, including areas, on a map very easily. This plays nicely with PostGIS and Spatialite which can return GeoJSON directly from SQL queries.
- New function:
sqlpage.path
to get the path of the current page. - Add a new
align_right
attribute to the table component to align a column to the right. - Fix display of long titles in the shell component.
- New
sqlpage.variables
function for easy handling of complex formssqlpage.variables('get')
returns a json object containing all url parameters. Inside/my_page.sql?x=1&y=2
, it returns the string'{"x":"1","y":"2"}'
sqlpage.variables('post')
returns a json object containg all variables passed through a form. This makes it much easier to handle a form with a variable number of fields.
- Remove systematic casting in SQL of all parameters to
TEXT
. The supported databases understand the type of the parameters natively. - Some advanced or database-specific SQL syntax that previously failed to parse inside SQLPage is now supported. See updates in SQLParser
- Better support for time series in the chart component. You can now use the
time
top-attribute to display a time series chart with smart x-axis labels. - New component: button. This allows you to create rows of buttons that allow navigation between pages.
- Better error messages for Microsoft SQL Server. SQLPage now displays the line number of the error, which is especially useful for debugging long migration scripts.
- Many improvements in the official website and the documentation.
- Most notably, the documentation now has syntax highlighting on code blocks (using prism with a custom theme made for tabler). This also illustrates the usage of external javascript and css libraries in SQLPage. See the shell component documentation.
- Better display of example queries in the documentation, with smart indentation that makes it easier to read.
- Clarify some ambiguous error messages:
- make it clearer whether the error comes from SQLPage or from the database
- specific tokenization errors are now displayed as such
- New timeline component to display a timeline of events.
- Add support for scatter and bubble plots in the chart component. See the chart documentation.
- further improve debuggability with more precise error messages. In particular, it usd to be hard to debug errors in long migration scripts, because the line number and position was not displayed. This is now fixed.
- Better logs on 404 errors. SQLPage used to log a message without the path of the file that was not found. This made it hard to debug 404 errors. This is now fixed.
- Add a new
top_image
attribute to the card component to display an image at the top of the card. This makes it possible to create beautiful image galleries with SQLPage. - Updated dependencies, for bug fixes and performance improvements.
- New icons (see https://tabler-icons.io/changelog)
- When
NULL
is passed as an icon name, display no icon instead of raising an error. - Official docker image folder structure changed. The docker image now expects
- the SQLPage website (
.sql
files) to be in/var/www/
, and - the SQLPage configuration folder to be in
/etc/sqlpage/
- the configuration file should be in
/etc/sqlpage/sqlpage.json
- the database file should be in
/etc/sqlpage/sqlpage.db
- custom templates should be in
/etc/sqlpage/templates/
- This configuration change concerns only the docker image. If you are using the sqlpage binary directly, nothing changes.
- the SQLPage website (
- variables . SQLPage now support setting and reusing variables between statements. This allows you to write more complex SQL queries, and to reuse the result of a query in multiple places.
-- Set a variable SET person = (select username from users where id = $id); -- Use it in a query SELECT 'text' AS component, 'Hello ' || $person AS contents;
- asynchronous password hashing . SQLPage used to block a request processing thread while hashing passwords. This could cause a denial of service if an attacker sent many requests to a page that used
sqlpage.hash_password()
(typically, the account creation page of your website). SQLPage now launches password hashing operations on a separate thread pool, and can continue processing other requests while waiting for passwords to be hashed. - Easier configuration for multiple menu items. Syntax like
SELECT 'shell' as component, '["page 1", "page 2"]' as menu_item'
now works as expected. See the newsqlpage_shell
definition in the small sql game example and this discussion. - New
sqlpage.exec
function to execute a command on the server. This allows you to run arbitrary code on the server, and use the result in your SQL queries. This can be used to make external API calls, send emails, or run any other code on the server.
select 'card' as component;
select value->>'name' as title, value->>'email' as description
from json_each(sqlpage.exec('curl', 'https://jsonplaceholder.typicode.com/users'));
This function is disabled by default for security reasons. To enable it, set the allow_exec
configuration parameter to true
in the configuration. Enabling it gives full access to the server to anyone who can write SQL queries on your website (this includes users with access to the local filesystem and users with write access to the sqlpage_files
table on your database), so be careful !
- New
sqlpage.url_encode
function to percent-encode URL parameters.select 'card' as component; select 'More...' as title, 'advanced_search.sql?query=' || sqlpage.url_encode($query)
- Add the ability to run a sql script on each database connection before it is used,
by simply creating
sqlpage/on_connect.sql
file. This has many interesting use cases:- allows you to set up your database connection with custom settings, such as
PRAGMA
in SQLite - set a custom
search_path
,application_name
or other variables in PostgreSQL - create temporary tables that will be available to all SQLPage queries but will not be persisted in the database
ATTACH
a database in SQLite to query multiple database files at once
- allows you to set up your database connection with custom settings, such as
- Better error messages. SQLPage displays a more precise and useful message when an error occurs, and displays the position in the SQL statement where the error occured. Incorrect error messages on invalid migrations are also fixed.
- We now distribute docker images from ARM too. Say hello to SQLPage on your Raspberry Pi and your Mac M1 !
- Create the default SQLite database file in the "sqlpage" config directory instead of at the root of the web server by default. This makes it inaccessible from the web, which is a more secure default. If you want to keep the old behavior, set the
database_url
configuration parameter tosqlite://sqlpage.db
in your configuration. - New
empty_title
,empty_description
, andempty_link
top-level attributes on thelist
component to customize the text displayed when the list is empty.
- Support for environment variables ! You can now read environment variables from sql code using
sqlpage.environment_variable('VAR_NAME')
. - Better support for connection options in mssql.
- New icons (see https://tabler-icons.io/changelog)
- New version of the CSS library (see https://preview.tabler.io/changelog.html)
- configurable web root (see configuration.md)
- new welcome message
-
SQLPage is now running on http://127.0.0.1:8080/ You can write your code in .sql files in /path/to/your/website/directory.
-
- New
sqlpage.current_working_directory
function to get the current working directory of the SQLPage process. - New
sqlpage.version
function to get the version of SQLPage.
- Update database drivers to the latest version.
- Adds new connection string options for mssql, including
app_name
andinstance
. Set them withDATABASE_URL=mssql://user:password@host:port/database?app_name=My%20App&instance=My%20Instance
- Adds new connection string options for mssql, including
- Fix a bug where the
map
component followed by another component would break the page layout.
- Update the SQL parser, with multiple fixes. See https://github.com/sqlparser-rs/sqlparser-rs/blob/main/CHANGELOG.md#0370-2023-08-22
- Display all parameters in the debug component (instead of only row-level parameters).
- Update dashmap for better file lookup performance.
- Fix table sorting.
- Fix a bug with Basic Authentication. See #72. Thanks to @edgrip for the contribution !
.sql
files are now parsed in the dialect of the database they are executed against, instead of always being parsed as a "Generic" dialect. This allows using more database-specific features in SQLPage and avoids confusion. This should not change anything in most cases, but could break your web application if you were relying on an SQL dialect syntax that is not directly supported by your database, hence the major version change.- Added the ability to download chart data as SVG, PNG, and CSV using the new
toolbar
attribute of thechart
component. This makes it easy to provide a large data set and allow users to download it as a CSV file from a nice UI.SELECT 'chart' as component, 1 as toolbar; SELECT quarter as label, sum(sales) as value FROM sales GROUP BY quarter;
- Added a dark theme ! You can now choose between a light and a dark theme for your SQLPage website.
Select the dark theme using the
theme
parameter of theshell
component:See #50SELECT 'shell' AS component, 'dark' AS theme;
- Fixed a bug where the default index page would be displayed when
index.sql
could not be loaded, instead of displaying an error page explaining the issue. - Improved the appearance of scrollbars. (Workaround for tabler/tabler#1648). See #17
- Create a single database connection by default when using
sqlite://:memory:
as the database URL. This makes it easier to use temporary tables and other connection-specific features. - When no component is selected, display data with the
debug
component by default. This makes any simpleSELECT
statement a valid SQLPage file. Before, data returned outside of a component would be ignored. - Improved error handling. SQLPage now displays a nice error page when an error occurs, even if it's at the top of the page. This makes it easier to debug SQLPage websites. Before, errors that occured before SQLPage had started to render the page would be displayed as a raw text error message without any styling.
- Added the ability to retry database connections when they fail on startup.
This makes it easier to start SQLPage concurrently with the database, and have it wait for the database to become available.
See the
database_connection_retries
anddatabase_connection_acquire_timeout_seconds
configuration parameter.
- New
tab
component to create tabbed interfaces. See the documentation. - Many improvements in database drivers.
- performance and numeric precision improvements,
- multiple fixes around passing NUMERIC, DECIMAL, and JSON values to SQLPage.
Small bugfix release
- Fix a bug with simple queries (ones with only static values) that contained multiple repeated columns
(such as
SELECT 'hello' AS menu_item, 'world' AS menu_item
). Only the last column would be taken into account. This could manifest as a bug where- only the last menu item in the shell component would be displayed,
- only the last markdown column in a table would be interpreted as markdown,
- only the last icon column in a table would be displayed as an icon.
- Icons are now loaded directly from the sqlpage binary instead of loading them from a CDN.
This allows pages to load faster, and to get a better score on google's performance audits, potentially improving your position in search results.
- This also makes it possible to host a SQLPage website on an intranet without access to the internet.
- Fixes #37
- store compressed frontend assets in the SQLPage binary:
- smaller SQLPage binary
- Faster page loads, less work on the server
- Fix a bug where table search would fail to find a row if the search term contained some special characters.
- Fixes #46
- Split the charts javascript code from the rest of the frontend code, and load it only when necessary. This greatly diminishes the amount of js loaded by default, and achieves very good performance scores by default. SQLPage websites now load even faster, een on slow mobile connections.
- Added support for more SQL data types. This notably fixes an issue with the display of datetime columns in tables.
- See: #41
- Updated dependencies, better SQL drivers
- Fix issues with the new experimental mssql driver.
- Added a new
json
component, which allows building a JSON API entirely in SQL with SQLPage ! Now creating an api over your database is as simple asSELECT 'json' AS component, JSON_OBJECT('hello', 'world') AS contents
. SELECT
statements that contain only static values are now interpreted directly by SQLPage, and do not result in a database query. This greatly improves the performance of pages that contain many static elements.- Redirect index pages without a trailing slash to the same page with the trailing slash. This ensures that relative links work correctly, and gives each page a unique canonical URL. (For instance, if you have a file in
myfolder/index.sql
, then it will be accessible atmysite.com/myfolder/
andmysite.com/myfolder
will redirect tomysite.com/myfolder/
). - Update the database drivers to the latest version, and switch to a fork of
sqlx
. This also updates the embedded version of SQLite to 3.41.2, with many cool new features such as:- better json support
- better performance
- Add experimental support for Microsoft SQL Server. If you have a SQL Server database lying around, please test it and report any issue you might encounter !
- Added a new
sqlite_extensions
configuration parameter to load SQLite extensions. This allows many interesting use cases, such as- using spatialite to build a geographic data application,
- querying CSV data from SQLPage with vsv,
- or building a search engine for your data with FTS5.
- Breaking: change the order of priority for loading configuration parameters: the environment variables have priority over the configuration file. This makes it easier to tweak the configuration of a SQLPage website when deploying it.
- Fix the default index page in MySQL. Fixes #23.
- Add a new map component to display a map with markers on it. Useful to display geographic data from PostGIS or Spatialite.
- Add a new
icon
attribute to the table component to display icons in the table. - Fix
textarea
fields in the form component to display the providedvalue
attribute. Thanks Frank for the contribution ! - SQLPage now guarantees that a single web request will be handled by a single database connection. Previously, connections were repeatedly taken and put back to the connection pool between each statement, preventing the use of temporary tables, transactions, and other connection-specific features such as
last_insert_rowid
. This makes it much easier to keep state between SQL statements in a single.sql
file. Please report any performance regression you might encounter. See the many-to-many relationship example. - The table component now supports setting a custom background color, and a custom CSS class on a given table line.
- New
checked
attribute for checkboxes and radio buttons.
- New authentication component to handle user authentication, and password checking
- New redirect component to stop rendering the current page and redirect the user to another page.
- The debug component is now documented
- Added properties to the shell component:
css
to add custom CSS to the pagejavascript
to add custom Javascript to the page. An example of how to use it to integrate a react component is available.footer
to set a message in the footer of the page
- New
sqlpage.basic_auth_username
function to get the name of the user logged in with HTTP basic authentication - New
sqlpage.basic_auth_password
function to get the password of the user logged in with HTTP basic authentication. - New
sqlpage.hash_password
function to hash a password with the same algorithm as the authentication component uses. - New
sqlpage.header
function to read an HTTP header from the request. - New
sqlpage.random_string
function to generate a random string. Useful to generate session ids.
- Fix a bug where the page style would not load in pages that were not in the root directory: #19
- Fix resources being served with the wrong content type
- Fix compilation of SQLPage as an AWS lambda function
- Fixed logging and display of errors, to make them more useful