HTTPS, Uploads, and more

[SQLPage](/) is a web application server that lets you build entire web applications with just SQL queries.
v0.17 was just released, and its worth a blog post to highlight some of the coolest new features.

Mostly, this release makes it a matter of minutes to build a data import pipeline for your website,
and a matter of seconds to deploy your SQLPage website securely with automatic HTTPS certificates.

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

```sql
select form as component;
select profile_picture 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`](/functions.sql?function=uploaded_file_path#function) function.

You can then persist the upload as a permanent file on the server with the
[`sqlpage.exec`](https://sql.ophir.dev/functions.sql?function=exec#function) function:

```sql
set file_path = sqlpage.uploaded_file_path(profile_picture);
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`](https://sql.ophir.dev/functions.sql?function=read_file_as_data_url#function) and
[`sqlpage.read_file_as_text`](https://sql.ophir.dev/functions.sql?function=read_file_as_text#function) functions:

```sql
insert into files (url) values (sqlpage.read_file_as_data_url(sqlpage.uploaded_file_path(profile_picture)))
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`](https://github.com/lovasoa/SQLpage/blob/main/configuration.md)
configuration parameter. By default, it is set to 5 MiB.

SQLPage can also parse uploaded [CSV](https://en.wikipedia.org/wiki/Comma-separated_values) files and insert them directly into a database table.
SQLPage re-uses PostgreSQLs [`COPY` syntax](https://www.postgresql.org/docs/current/sql-copy.html)
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.

```sql
select form as component, bulk_user_import.sql as action;
select user_csv_file as name, file as type, text/csv as accept;
```

```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 dont have any preprocessing to do, you can skip the temporary table and use the target table directly

copy csv_import(name, age) from user_csv_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 dont 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, birth_date)
select upper(name), date_part(year, CURRENT_DATE) - cast(age as int) from csv_import;
```

 - [`sqlpage.uploaded_file_path`](https://sql.ophir.dev/functions.sql?function=uploaded_file_path#function) 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 with `TMPDIR`). You can use [`sqlpage.exec`](https://sql.ophir.dev/functions.sql?function=exec#function) to operate on the file, for instance to move it to a permanent location.
 - [`sqlpage.uploaded_file_mime_type`](https://sql.ophir.dev/functions.sql?function=uploaded_file_mime_type#function) to get the type of file uploaded by the user. This is the MIME type of the file, such as `image/png` or `text/csv`. You can use this to easily check that the file is of the expected type before storing it.

 The new [*Image gallery* example](https://github.com/lovasoa/SQLpage/tree/main/examples/image%20gallery%20with%20user%20uploads)
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 contents of a file uploaded by the user,
but can also be used to read any file on the computer where SQLPage is running:

 - [`sqlpage.read_file_as_text`](https://sql.ophir.dev/functions.sql?function=read_file_as_text#function) reads the contents of a file on the server and returns a text string.
 - [`sqlpage.read_file_as_data_url`](https://sql.ophir.dev/functions.sql?function=read_file_as_data_url#function) reads the contents of a file on the server and returns a [data URL](https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/Data_URIs). 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 !
Until now, if you wanted to use HTTPS with SQLPage, you had to put it behind a
*reverse proxy*, which is what the official documentation website does.

This required a lot of manual configuration
that would compromise your security if you get it wrong.

With SQLPage v0.17, you just give your domain name,
and it takes care of everything.

And while were at it, SQLPage also supports HTTP/2, for even faster page loads.

To enable HTTPS, you need to buy a [domain name](https://en.wikipedia.org/wiki/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](./configuration.md).

```json
{
  "https_domain": "my-cool-website.com"
}
```

Thats it. No external tool to install, no certificate to generate, no configuration to tweak.
No need to restart SQLPage regularly either, or to worry about renewing your certificate when it expires.
SQLPage will automatically request a certificate from [Lets Encrypt](https://letsencrypt.org/) 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 use is very powerful and supports most SQL features,
but there are some edge cases where it fails to parse a query.
Thats 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](https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver16&tabs=json-path) 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.
 - The `VARCHAR(MAX)` type in MS SQL Server new works. We now use it for all variables bound as parameters to your SQL queries (we used to use `VARCHAR(8000)` before).
 - `INSERT INTO ... DEFAULT VALUES ...` is now parsed correctly.

 - 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 the `SameSite` attribute of cookies, and defaults to `SameSite=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` or `max` to `0` in a number field in the `form` component now works as expected.
 - Added support for `.env` files to set SQLPages [environment variables](./configuration.md#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](https://tabler-icons.io/changelog):
   - ![new icons in tabler 42](https://github.com/tabler/tabler-icons/assets/1282324/00856af9-841d-4aa9-995d-121c7ddcc005)

bugfix release

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

 - Add special handling of hidden inputs in [forms](https://sql.ophir…

….dev/documentation.sql?component=form#component). 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
   - https://github.com/tabler/tabler-icons/releases/tag/v2.40.0
   - https://github.com/tabler/tabler-icons/releases/tag/v2.41.0
 - Support multiple statements in [`on_connect.sql`](./configuration.md) 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 the [`max_connections`](./configuration.md) configuration parameter to `1` 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:
     ```sql
      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 using `SET`. Previously, storing `NULL` in a variable would store the string `'null'` instead of the `NULL` value. This is now fixed.
    ```sql
    SET myvar = NULL;
    SELECT 'card' as component;
    SELECT $myvar IS NULL as title; -- this used to display false, it now displays true
    ```

updated map component and embedded sqlite version

 - Many improvements in the [`form`](https://sql.ophir.dev/documentat…

…ion.sql?component=form#component) 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.
 - Support non-string values in markdown fields. `NULL` values are now displayed as empty strings, numeric values are displayed as strings, booleans as `true` or `false`, 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 SQLite `SELECT '1' = 1` returns `false` but `SELECT CAST('1' AS TEXT) = 1` returns `true`. This also fixes error messages like `could not determine data type of parameter $1` in PostgreSQL.
 - Fix a bug where [cookie](https://sql.ophir.dev/documentation.sql?component=cookie#component) removal set the cookie value to the empty string instead of removing the cookie completely.
 - Support form submission using the [button](https://sql.ophir.dev/documentation.sql?component=button#component) 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](https://sql.ophir.dev/documentation.sql?component=map#component) component.
 - Add support for GeoJSON in the [map](https://sql.ophir.dev/documentation.sql?component=map#component) 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.

0.15.0

 - New functions: sqlpage.path(), sqlpage.variables()
- bug fixes
- better sql support
- improved docs
- new `align_right` attribute in the table component

First beta-release for v0.15

button component and time series plots

 - Better support for time series in the [chart](https://sql.ophir.dev/documentation.sql?component=chart#component) component. You can now use the `time` top-attribute to display a time series chart
 with smart x-axis labels.
 - **New component**: [button](https://sql.ophir.dev/documentation.sql?component=button#component). 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](https://prismjs.com/) 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](https://sql.ophir.dev/documentation.sql?component=shell#component).
    - 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

v0.13.0: beatiful timelines and scatter plots

 - New [timeline](https://sql.ophir.dev/documentation.sql?component=timeline#component) component to display a timeline of events.
 - Add support for scatter and bubble plots in the chart component. See [the chart documentation](https://sql.ophir.dev/documentation.sql?component=chart#component).
 - 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](https://sql.ophir.dev/documentation.sql?component=card#component) 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.

variables, sqlpage.exec, better errors, on_connect.sql