# SQLPage > SQLPage is a SQL-only web application framework. It lets you build entire websites and web applications using nothing but SQL queries. Write `.sql` files, and SQLPage executes them, maps results to UI components (handlebars templates), and streams HTML to the browser. SQLPage is designed for developers who are comfortable with SQL but want to avoid the complexity of traditional web frameworks. It works with SQLite, PostgreSQL, MySQL, and Microsoft SQL Server, and through ODBC with any other database that has an ODBC driver installed. Key features: - No backend code needed: Your SQL files are your backend - Component-based UI: Built-in components for forms, tables, charts, maps, and more - Database-first: Every HTTP request triggers a sequence of SQL queries from a .sql file, the results are rendered with built-in or custom components, defined as .handlebars files in the sqlpage/templates folder. - Simple deployment: Single binary with no runtime dependencies - Secure by default: Parameterized queries prevent SQL injection ## Getting Started - [Introduction to SQLPage: installation, guiding principles, and a first example](/your-first-sql-website/tutorial.md): Complete beginner tutorial covering setup, database connections, forms, and deployment ## Core Documentation - [Components reference](/documentation.sql): List of all 42 built-in UI components with parameters and examples - [Functions reference](/functions.sql): SQLPage built-in functions for handling requests, encoding data, and more - [Configuration guide](https://github.com/sqlpage/SQLPage/blob/main/configuration.md): Complete list of configuration options in sqlpage.json ## Components ### [list](/component.sql?component=list) A vertical list of items. Each item can be clickable and link to another page. #### Top-level parameters - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `compact` (BOOLEAN): Whether to display the list in a more compact format, allowing more items to be displayed on the screen. - `empty_description` (TEXT): Description to display if the list is empty. - `empty_description_md` (TEXT): Description to display if the list is empty, in Markdown format. - `empty_link` (URL): URL to which the user should be taken if they click on the empty list. - `empty_title` (TEXT): Title text to display if the list is empty. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `title` (TEXT): Text header at the top of the list. - `wrap` (BOOLEAN): Wrap list items onto multiple lines if they are too long #### Row-level parameters - `active` (BOOLEAN): Whether this item in the list is considered "active". Active items are displayed more prominently. - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `color` (COLOR): The name of a color, to be displayed as a dot near the list item contents. - `delete_link` (URL): A page that will be loaded when the user clicks on the delete button for this specific item. The link will be submitted as a POST request. - `description` (TEXT): A description of the list item, displayed as greyed-out text. - `description_md` (TEXT): A description of the list item, displayed as greyed-out text, in Markdown format, allowing you to use rich text formatting, including **bold** and *italic* text. - `edit_link` (URL): A URL to which the user should be taken when they click on the "edit" icon. Does not show the icon when omitted. - `icon` (ICON): Name of an icon to display on the left side of the item. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `image_url` (URL): The URL of a small image to display on the left side of the item. - `link` (URL): An URL to which the user should be taken when they click on the list item. - `title` (TEXT) **REQUIRED**: Name of the list item, displayed prominently. - `view_link` (URL): A URL to which the user should be taken when they click on the "view" icon. Does not show the icon when omitted. ### [datagrid](/component.sql?component=datagrid) Display small pieces of information in a clear and readable way. Each item has a name and is associated with a value. #### Top-level parameters - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `description` (TEXT): A short paragraph displayed below the title. - `description_md` (TEXT): A short paragraph displayed below the title - formatted using markdown. - `icon` (ICON): Name of an icon to display on the left side of the title. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `image_url` (URL): URL of an image to display on the left side of the title. - `title` (TEXT): Text header at the top of the data grid. #### Row-level parameters - `active` (BOOLEAN): Whether this item in the grid is considered "active". Active items are displayed more prominently. - `color` (COLOR): If set to a color name, the value will be displayed in a pill of that color. - `description` (TEXT): Value to display below the name. - `footer` (TEXT): Muted text to display below the value. - `icon` (ICON): An icon name (from tabler-icons.io) to display on the left side of the value. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `image_url` (URL): URL of a small image (such as an avatar) to display on the left side of the value. - `link` (URL): A target URL to which the user should be taken when they click on the value. - `title` (TEXT) **REQUIRED**: Name of the piece of information. - `tooltip` (TEXT): A tooltip to display when the user passes their mouse over the value. ### [steps](/component.sql?component=steps) Guide users through multi-stage processes, displaying a clear list of previous and future steps. #### Top-level parameters - `color` (COLOR): Color of the bars displayed between steps. - `counter` (TEXT): Display the number of the step on top of its name. - `description` (TEXT): Description of the section. - `id` (TEXT): id attribute injected as an anchor in HTML. It can be used for scrolling to this item through links (use "#id" in link url). Added in v0.18.0. - `title` (TEXT): Title of the section. #### Row-level parameters - `active` (BOOLEAN): Whether this item in the grid is considered "active". Active items are displayed more prominently. - `description` (TEXT): Tooltip to display when the user passes their mouse over the step's name. - `icon` (ICON): An icon name (from tabler-icons.io) to display on the left side of the step name. - `link` (URL): A target URL to which the user should be taken when they click on the step. - `title` (TEXT): Name of the step. ### [text](/component.sql?component=text) A paragraph of text. The entire component will render as a single paragraph, with each item being rendered as a span of text inside it, the styling of which can be customized using parameters. #### Top-level parameters - `article` (BOOLEAN): Makes long texts more readable by increasing the line height, adding margins, using a serif font, and decorating the initial letter. - `center` (BOOLEAN): Whether to center the title. - `contents` (TEXT): A top-level paragraph of text to display, without any formatting, without having to make additional queries. - `contents_md` (TEXT): Rich text in the markdown format. Among others, this allows you to write bold text using **bold**, italics using *italics*, and links using [text](https://example.com). - `html` (TEXT): Raw html code to include on the page. Don't use that if you are not sure what you are doing, it may have security implications. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `title` (TEXT): Text header before the paragraph. - `unsafe_contents_md` (TEXT): Markdown format with html blocks. Use this only with trusted content. See the html-blocks section of the Commonmark spec for additional info. - `width` (INTEGER): How wide the paragraph should be, in characters. #### Row-level parameters - `bold` (BOOLEAN): Whether the span of text should be displayed as bold. - `break` (BOOLEAN): Indicates that the current span of text starts a new paragraph. - `code` (BOOLEAN): Use a monospace font. Useful to display the text as code. - `color` (COLOR): The name of a color for this span of text. - `contents` (TEXT) **REQUIRED**: A span of text to display - `contents_md` (TEXT): Rich text in the markdown format. Among others, this allows you to write bold text using **bold**, italics using *italics*, and links using [text](https://example.com). - `italics` (BOOLEAN): Whether the span of text should be displayed as italics. - `link` (URL): An URL to which the user should be taken when they click on this span of text. - `size` (INTEGER): A number between 1 and 6 indicating the font size. - `underline` (BOOLEAN): Whether the span of text should be underlined. - `unsafe_contents_md` (TEXT): Markdown format with html blocks. Use this only with trusted content. See the html-blocks section of the Commonmark spec for additional info. ### [form](/component.sql?component=form) # Building forms in SQL The form component will display a series of input fields of various types, that can be filled in by the user. When the user submits the form, the data is posted to an SQL file specified in the `action` property. ## Handle Data with SQL The receiving SQL page will be able to handle the data, and insert it into the database, use it to perform a search, format it, update existing data, etc. A value in a field named "x" will be available as `:x` in the SQL query of the target page. ## Examples - [A multi-step form](https://github.com/sqlpage/SQLPage/tree/main/examples/forms-with-multiple-steps), guiding the user through a process without overwhelming them with a large form. - [File upload form](https://github.com/sqlpage/SQLPage/tree/main/examples/image%20gallery%20with%20user%20uploads), letting users upload images to a gallery. - [Rich text editor](https://github.com/sqlpage/SQLPage/tree/main/examples/rich-text-editor), letting users write text with bold, italics, links, images, etc. - [Master-detail form](https://github.com/sqlpage/SQLPage/tree/main/examples/master-detail-forms), to edit a list of structured items. - [Form with a variable number of fields](https://github.com/sqlpage/SQLPage/tree/main/examples/forms%20with%20a%20variable%20number%20of%20fields), when the fields are not known in advance. - [Demo of all input types](/examples/form), showing all the input types supported by SQLPage. #### Top-level parameters - `action` (TEXT): An optional link to a target page that will handle the results of the form. By default the target page is the current page with the id of the form (if passed) used as hash - this will bring us back to the location of the form after submission. Setting it to the name of a different sql file will load that file when the user submits the form. - `auto_submit` (BOOLEAN): Automatically submit the form when the user changes any of its fields, and remove the validation button. - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `id` (TEXT): A unique identifier for the form, which can then be used to validate the form from a button outside of the form. - `method` (TEXT): Set this to 'GET' to pass the form contents directly as URL parameters. If the user enters a value v in a field named x, submitting the form will load target.sql?x=v. If target.sql contains SELECT $x, it will display the value v. - `reset` (TEXT): The text to display in the button at the bottom of the form that resets the form to its original state. Omit this property not to show a reset button at all. - `reset_color` (COLOR): The color of the button at the bottom of the form that resets the form to its original state. Omit this property to use the default color. - `reset_icon` (ICON): Name of an icon to be displayed on the left side of the reset button. - `title` (TEXT): A name to display at the top of the form. It will be displayed in a larger font size at the top of the form. - `validate` (TEXT): The text to display in the button at the bottom of the form that submits the values. Omit this property to let the browser display the default form validation text, or set it to the empty string to remove the button completely. - `validate_color` (COLOR): The color of the button at the bottom of the form that submits the values. Omit this property to use the default color. - `validate_icon` (ICON): Name of an icon to be displayed on the left side of the submit button. - `validate_outline` (COLOR): A color to outline the validation button. #### Row-level parameters - `autocomplete` (BOOLEAN): Whether the browser should suggest previously entered values for this field. - `autofocus` (BOOLEAN): Automatically focus the field when the page is loaded - `checked` (BOOLEAN): Used only for checkboxes and radio buttons. Indicates whether the checkbox should appear as already checked. - `class` (TEXT): A CSS class to apply to the form element. - `create_new` (BOOLEAN): In a multiselect with a dropdown, this option allows the user to enter new values, that are not in the list of options. - `description` (TEXT): A helper text to display near the input field. - `description_md` (TEXT): A helper text to display near the input field - formatted using markdown. - `disabled` (BOOLEAN): Makes the field non-editable, non-focusable, and not submitted with the form. Use readonly instead for simple non-editable fields. - `dropdown` (BOOLEAN): An alias for "searchable". - `empty_option` (TEXT): Only for inputs of type `select`. Adds an empty option with the given label before the ones defined in `options`. Useful when generating other options from a database table. - `formaction` (TEXT): When type is "submit", this specifies the URL of the file that will handle the form submission. Useful when you need multiple submit buttons. - `id` (TEXT): A unique identifier for the input, which can then be used to select and manage the field with Javascript code. Usefull for advanced using as setting client side event listeners, interactive control of input field (disabled, visibility, read only, e.g.) and AJAX requests. - `label` (TEXT): A friendly name for the text field to show to the user. - `max` (REAL): The maximum value to accept for an input of type number - `maxlength` (INTEGER): Maximum length of text allowed in the field. - `min` (REAL): The minimum value to accept for an input of type number - `minlength` (INTEGER): Minimum length of text allowed in the field. - `multiple` (BOOLEAN): Used only for select elements. Indicates that multiple elements can be selected simultaneously. When using multiple, you should add square brackets after the variable name: 'my_variable[]' as name - `name` (TEXT) **REQUIRED**: The name of the input field, that you can use in the target page to get the value the user entered for the field. - `options` (JSON): A json array of objects containing the label and value of initial options of a select field. Used only when type=select. JSON objects in the array can contain the properties "label", "value" and "selected". - `options_source` (URL): Only for inputs of type `select`. URL of a SQL file that returns JSON search results for the dropdown. The SQL file receives the search text as `$search` and must return an array of objects with exactly `label` and `value`. Use `options` for options that should be available before the user searches. - `pattern` (TEXT): A regular expression that the value must match. For instance, [0-9]{3} will only accept 3 digits. - `placeholder` (TEXT): A placeholder text that will be shown in the field when is is empty. - `prefix` (TEXT): Text to display on the left side of the input field, on the same line. - `prefix_icon` (ICON): Icon to display on the left side of the input field, on the same line. - `readonly` (BOOLEAN): Set to true to prevent the user from modifying the value of the input field. - `required` (BOOLEAN): Set this to true to prevent the form contents from being sent if this field is left empty by the user. - `rows` (INTEGER): Number of rows to display for a textarea. Defaults to 3. - `searchable` (BOOLEAN): For select and multiple-select elements, displays them with a nice dropdown that allows searching for options. - `step` (REAL): The increment of values in an input of type number. Set to 1 to allow only integers. - `suffix` (TEXT): Short text to display after th input, on the same line. Useful to add units or a currency symbol to an input. - `type` (TEXT): Declares input control behavior and expected format. All HTML input types are supported (text, number, date, file, checkbox, radio, hidden, ...). SQLPage adds some custom types: textarea, switch, header. text by default. See https://developer.mozilla.org/en-US/docs/Web/HTML/Reference/Elements/input#input_types - `value` (TEXT): A default value that will already be present in the field when the user loads the page. - `width` (INTEGER): Width of the form field, between 1 and 12. ### [chart](/component.sql?component=chart) A component that plots data. Line, area, bar, and pie charts are all supported. Each item in the component is a data point in the graph. #### Top-level parameters - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `color` (COLOR): The name of a color in which to display the chart. If there are multiple series in the chart, this parameter can be repeated multiple times. - `height` (INTEGER): Height of the chart, in pixels. By default: 250 - `horizontal` (BOOLEAN): Displays a bar chart with horizontal bars instead of vertical ones. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `labels` (BOOLEAN): Whether to show the data labels on the chart or not. - `logarithmic` (BOOLEAN): Display the y-axis in logarithmic scale. - `marker` (REAL): Marker size - `show_legend` (BOOLEAN): Whether to display the legend listing all chart series. Defaults to true. - `stacked` (BOOLEAN): Whether to cumulate values from different series. Supported by the "line", "area" and "bar" chart types, and ignored by the others. Series are aligned on their x values, and a series that has no value for a given x counts as zero there. - `time` (BOOLEAN): Whether the x-axis represents time. If set to true, the x values will be parsed and formatted as dates for the user. - `title` (TEXT): The name of the chart. - `toolbar` (BOOLEAN): Whether to display a toolbar at the top right of the chart, that offers downloading the data as CSV. - `type` (TEXT) **REQUIRED**: The type of chart. One of: "line", "area", "bar", "column", "pie", "scatter", "bubble", "heatmap", "rangeBar". "column" is a synonym of "bar". - `xmax` (TEXT): The maximum value for the x-axis. When time is true, this can be a date or timestamp. - `xmin` (TEXT): The minimal value for the x-axis. When time is true, this can be a date or timestamp. - `xticks` (INTEGER): Number of ticks on the x axis. - `xtitle` (TEXT): Title of the x axis, displayed below it. - `ymax` (REAL): The maximum value for the y-axis. - `ymin` (REAL): The minimal value for the y-axis. - `ystep` (REAL): Step between ticks on the y axis. - `ytitle` (TEXT): Title of the y axis, displayed to its left. - `ztitle` (TEXT): Title of the z axis, displayed in tooltips. #### Row-level parameters - `label` (REAL): An alias for parameter "x" - `series` (TEXT): If multiple series are represented and share the same y-axis, this parameter can be used to distinguish between them. - `value` (REAL): An alias for parameter "y" - `x` (REAL) **REQUIRED**: The value of the point on the horizontal axis - `y` (REAL) **REQUIRED**: The value of the point on the vertical axis ### [table](/component.sql?component=table) A table with optional filtering and sorting. Unlike most others, this component does not have a fixed set of item properties, any property that is used will be rendered directly as a column in the table. Tables can contain rich text, including images, links, and icons. Table rows can be styled with a background color, and the table can be made striped, hoverable, and bordered. Advanced users can apply custom styles to table columns using a CSS class with the same name as the column, and to table rows using the `_sqlpage_css_class` property. #### Top-level parameters - `align_center` (TEXT): Name of a column the contents of which should be center-aligned. This argument can be repeated multiple times to align multiple columns to the center. - `align_right` (TEXT): Name of a column the contents of which should be right-aligned. This argument can be repeated multiple times to align multiple columns to the right. Introduced in v0.15.0. - `border` (BOOLEAN): Whether to draw borders on all sides of the table and cells. - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `currency` (TEXT): The ISO 4217 currency code (e.g., USD, EUR, GBP, etc.) to use when formatting monetary values. - `custom_actions` (JSON): If set, a column of custom action buttons will be added to each row. The value of this property should be a JSON array of objects, each object defining a button with the following properties: `name` (the text to display on the button), `icon` (the tabler icon name or image link to display on the button), `link` (the URL to navigate to when the button is clicked, possibly containing the `{id}` placeholder that will be replaced by the value of the `_sqlpage_id` property for that row), and `tooltip` (optional text to display when hovering over the button). Added in v0.39.0 - `delete_url` (TEXT): If set, a delete button will be added to each row. The value of this property should be a URL, possibly containing the `{id}` placeholder that will be replaced by the value of the `_sqlpage_id` property for that row. Clicking the delete button will take the user to that URL. Added in v0.39.0 - `description` (TEXT): Description of the table contents. Helps users with screen readers to find a table and understand what it’s about. - `edit_url` (TEXT): If set, an edit button will be added to each row. The value of this property should be a URL, possibly containing the `{id}` placeholder that will be replaced by the value of the `_sqlpage_id` property for that row. Clicking the edit button will take the user to that URL. Added in v0.39.0 - `empty_description` (TEXT): Text to display if the table does not contain any row. Defaults to "no data". - `freeze_columns` (BOOLEAN): Whether to freeze the leftmost column of the table. - `freeze_footers` (BOOLEAN): Whether to freeze the footer (bottom row) of the table, only works if that row has the `_sqlpage_footer` property applied to it. - `freeze_headers` (BOOLEAN): Whether to freeze the top row of the table. - `hover` (BOOLEAN): Whether to enable a hover state on table rows. - `icon` (TEXT): Set this to the name of a column whose content should be interpreted as a tabler icon name. Used to display icons in the table. This argument can be repeated multiple times to intepret multiple columns as icons. Introduced in v0.8.0. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `initial_search_value` (TEXT): Pre-fills the search bar used to filter the table. The user will still be able to edit the value to display table rows that will initially be filtered out. - `markdown` (TEXT): Set this to the name of a column whose content should be interpreted as markdown . Used to display rich text with links in the table. This argument can be repeated multiple times to intepret multiple columns as markdown. - `money` (TEXT): Name of a numeric column whose values should be displayed as currency amounts, in the currency defined by the `currency` property. This argument can be repeated multiple times. - `monospace` (TEXT): Name of a column the contents of which should be displayed in monospace. This argument can be repeated multiple times to display multiple columns in monospace. Introduced in v0.32.1. - `number_format_digits` (INTEGER): Maximum number of decimal digits to display for numeric values. - `overflow` (BOOLEAN): Whether to to let "wide" tables overflow across the right border and enable browser-based horizontal scrolling. - `raw_numbers` (TEXT): Name of a column whose values are numeric, but should be displayed as raw numbers without any formatting (no thousands separators, decimal separator is always a dot). This argument can be repeated multiple times. - `search` (BOOLEAN): Add a search bar at the top of the table, letting users easily filter table rows by value. - `search_placeholder` (TEXT): Customizes the placeholder text shown in the search input field. Replaces the default "Search..." with text that better describes what users should search for. - `small` (BOOLEAN): Whether to use compact table. - `sort` (BOOLEAN): Make the columns clickable to let the user sort by the value contained in the column. - `striped_columns` (BOOLEAN): Whether to add zebra-striping to any table column. - `striped_rows` (BOOLEAN): Whether to add zebra-striping to any table row. #### Row-level parameters - `_sqlpage_actions` (JSON): Sets custom action buttons for this specific row in addition to any defined at the table level, The value of this property should be a JSON array of objects, each object defining a button with the following properties: `name` (the text to display on the button), `icon` (the tabler icon name or image link to display on the button), `link` (the URL to navigate to when the button is clicked, possibly containing the `{id}` placeholder that will be replaced by the value of the `_sqlpage_id` property for that row), and `tooltip` (optional text to display when hovering over the button). Added in v0.39.0 - `_sqlpage_color` (COLOR): Sets the background color of the row. Added in v0.8.0. - `_sqlpage_css_class` (TEXT): For advanced users. Sets a css class on the table row. Added in v0.8.0. - `_sqlpage_footer` (BOOLEAN): Sets this row as the table footer. It is recommended that this parameter is applied to the last row. Added in v0.34.0. - `_sqlpage_id` (TEXT): Sets the id of the html tabler row element. Allows you to make links targeting a specific row in a table. ### [csv](/component.sql?component=csv) Lets the user download data as a CSV file. Each column from the items in the component will map to a column in the resulting CSV. When `csv` is used as a **header component** (without a [shell](?component=shell)), it will trigger a download of the CSV file directly on page load. If the csv file to download is large, we recommend using this approach. When used inside a page (after calling the shell component), this will add a button to the page that lets the user download the CSV file. The button will need to load the entire contents of the CSV file in memory, inside the browser, even if the user does not click on it. If the csv file to download is large, we recommend using this component without a shell component in order to efficiently stream the data to the browser. #### Top-level parameters - `bom` (BOOLEAN): Whether to include a Byte Order Mark (a special character indicating the character encoding) at the beginning of the file. This is useful for Excel compatibility. - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `color` (COLOR): Color of the button. Ignored when used as a header component. - `filename` (TEXT): The name of the file that should be downloaded (without the extension). - `icon` (ICON): Name of the icon (from tabler-icons.io) to display in the button. Ignored when used as a header component. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `separator` (TEXT): How individual values should be separated in the CSV. "," by default, set it to "\t" for tab-separated values. - `size` (TEXT): The size of the button (e.g., sm, lg). Ignored when used as a header component. - `title` (TEXT) **REQUIRED**: The text displayed on the download button. ### [dynamic](/component.sql?component=dynamic) Renders other components, given their properties as JSON. If you are looking for a way to run FOR loops, to share similar code between pages of your site, or to render multiple components for every line returned by your SQL query, then this is the component to use #### Top-level parameters - `properties` (JSON): A json object or array that contains the names and properties of other components. ### [shell](/component.sql?component=shell) Customize the overall layout, header and footer of the page. This is a special component that provides the page structure wrapping all other components on your page. It generates the complete HTML document including the `` section with metadata, title, and stylesheets, as well as the navigation bar, main content area, and footer. If you don't explicitly call the shell component at the top of your SQL file, SQLPage will automatically add a default shell component before your first try to display data on the page. Use the shell component to customize page-wide settings like the page title, navigation menu, theme, fonts, and to include custom visual styles (with CSS) or interactive behavior (with JavaScript) that should be loaded on the page. #### Top-level parameters - `css` (URL): The URL of a CSS file to load and apply to the page. - `description` (TEXT): A description of the page. It can be displayed by search engines when your page appears in their results. - `fixed_top_menu` (BOOLEAN): Fixes the top bar with menu at the top (the top bar remains visible when scrolling long pages). - `font` (TEXT): Specifies the font to be used for displaying text, which can be a valid font name from fonts.google.com or the path to a local WOFF2 font file starting with a slash (e.g., "/fonts/MyLocalFont.woff2"). - `font_size` (INTEGER): Font size on the page, in pixels. Set to 18 by default. - `footer` (TEXT): Muted text to display in the footer of the page. This can be used to display a link to the terms and conditions of your application, for instance. By default, shows "Built with SQLPage". Supports links with markdown. - `icon` (ICON): Name of an icon (from tabler-icons.io) to display next to the title in the navigation bar. - `image` (URL): The URL of an image to display next to the page title. - `javascript` (URL): The URL of a Javascript file to load and execute on the page. - `javascript_module` (URL): The URL of a javascript module in the ESM format (see javascript.info/modules) - `language` (TEXT): The language of the page. This can be used by search engines and screen readers to determine in which language the page is written. - `layout` (TEXT): The general page layout. Can be "boxed" (the default), "horizontal" (for a full-width menu), "vertical"(vertical menu), "fluid" (removes side margins). - `link` (URL): The target of the link in the top navigation bar. - `menu_item` (TEXT): Adds a menu item in the navigation bar at the top of the page. The menu item will have the specified name, and will link to as .sql file of the same name. A dropdown can be generated by passing a json object with a `title` and `submenu` properties. - `navbar_title` (TEXT): The title to display in the top navigation bar. Used to display a different title in the top menu than the one that appears in the tab of the browser. - `norobot` (BOOLEAN): Forbids robots to save this page in their database and follow the links on this page. This will prevent this page to appear in Google search results for any query, for instance. - `preview_image` (URL): The URL of an image to display as a link preview when the page is shared on social media - `refresh` (INTEGER): Number of seconds after which the page should refresh. This can be useful to display dynamic content that updates automatically. - `rss` (URL): The URL of an RSS feed to display in the top navigation bar. You can use the rss component to generate the field. - `rtl` (BOOLEAN): Whether the page should be displayed in right-to-left mode. Used to display Arabic, Hebrew, Persian, etc. - `search_button` (TEXT): Customizes the text displayed on the search button. Replaces the default "Search" label with custom text that may better match your applications terminology or language. - `search_placeholder` (TEXT): Customizes the placeholder text shown in the search input field. Replaces the default "Search" with text that better describes what users should search for. - `search_target` (TEXT): When this is set, a search field will appear in the top navigation bar, and load the specified sql file with an URL parameter named "search" when the user searches something. - `search_value` (TEXT): This value will be placed in the search field when "search_target" is set. Using the "$search" query parameter value will mirror the value that the user has searched for. - `sidebar` (BOOLEAN): Whether the menu defined by menu_item should be displayed on the left side of the page instead of the top. Introduced in v0.27. - `sidebar_theme` (TEXT): Used with sidebar property, It can be set to "dark" to exclusively set the sidebar into dark theme. - `social_image` (URL): The URL of the preview image that will appear in the Open Graph metadata when the page is shared on social media. - `target` (TEXT): "_blank" to open the link in a new tab, "_self" to open it in the same tab, "_parent" to open it in the parent frame, or "_top" to open it in the full body of the window - `theme` (TEXT): Set to "dark" to use a dark theme. - `title` (TEXT): The title of your page. Will be shown in a top bar above the page contents. Also usually displayed by web browsers as the name of the web page's tab. ### [hero](/component.sql?component=hero) Display a large title and description for your page, with an optional large illustrative image. Useful in your home page, for instance. #### Top-level parameters - `autoplay` (BOOLEAN): Automatically start playing the video - `description` (TEXT): A description of the page. Displayed below the title, in smaller characters and slightly greyed out. - `description_md` (TEXT): A description of the page. Displayed below the title, in smaller characters and slightly greyed out - formatted using markdown. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `image` (URL): The URL of an image to display next to the page title. - `link` (URL): Creates a large "call to action" button below the description, linking to the specified URL. - `link_text` (TEXT): The text to display in the call to action button. Defaults to "Go". - `loop` (BOOLEAN): Loop the video - `muted` (BOOLEAN): Mute the video - `nocontrols` (BOOLEAN): Hide the video controls (play, pause, volume, etc.), and autoplay the video. - `poster` (URL): URL of the image to be displayed before the video starts. Ignored if no video is present. - `reverse` (BOOLEAN): Reverse the order of the image and text: the image will be on the left, and the text on the right. - `title` (TEXT): The title of your page. Will be shown in very large characters at the top. - `video` (URL): The URL of a video to display next to the page title. #### Row-level parameters - `description` (TEXT): Description of the feature section. - `description_md` (TEXT): Description of the feature section - formatted using markdown. - `icon` (ICON): Icon of the feature section. - `link` (TEXT): An URL to which the user should be taken when they click on the section title. - `title` (TEXT): The name of a single feature section highlighted by this hero. ### [alert](/component.sql?component=alert) A visually distinctive message or notification. #### Top-level parameters - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `color` (COLOR): The color theme for the alert message. - `description` (TEXT): Detailed description or content of the alert message. - `description_md` (TEXT): Detailed description or content of the alert message, in Markdown format, allowing you to use rich text formatting, including **bold** and *italic* text. - `dismissible` (BOOLEAN): Whether the user can close the alert message. - `icon` (TEXT): Icon name (from tabler-icons.io) to display next to the alert message. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `important` (BOOLEAN): Set this to TRUE to make the alert message more prominent. - `link` (URL): A URL to link to from the alert message. - `link_text` (TEXT): Customize the text of the link in the alert message. The default is "Ok". - `title` (TEXT) **REQUIRED**: Title of the alert message. #### Row-level parameters - `color` (COLOR): Customize the color of the link. - `link` (URL): A URL to link to from the alert message. - `title` (TEXT): Customize the text of the link in the alert message. The default is "Ok". ### [http_header](/component.sql?component=http_header) An advanced component to set arbitrary HTTP headers: can be used to set a custom caching policy to your pages, or implement custom redirections, for example. If you are a beginner, you probably don't need this component. When used, this component has to be the first component in the page, because once the page is sent to the browser, it is too late to change the headers. HTTP headers are additional pieces of information sent with responses to web requests that provide instructions or metadata about the data being sent — for example, setting cache control directives to control caching behavior or specifying the content type of a response. Any valid HTTP header name can be used as a top-level parameter for this component. The examples shown here are just that, examples; and you can create any custom header if needed simply by declaring it. If your header's name contains a dash or any other special character, you will have to use your database's quoting mechanism to declare it. In standard SQL, you can use double quotes to quote identifiers (like "X-My-Header"), in Microsoft SQL Server, you can use square brackets (like [X-My-Header]). #### Top-level parameters - `Access-Control-Allow-Origin` (TEXT): Specifies which origins are allowed to access the resource in a cross-origin request, used for implementing Cross-Origin Resource Sharing (CORS). - `Cache-Control` (TEXT): Directives for how long the page should be cached by the browser. Set this to max-age=N to keep the page in cache for N seconds. - `Content-Disposition` (TEXT): Provides instructions on how the response content should be displayed or handled by the client, such as inline or as an attachment. - `Location` (TEXT): Specifies the URL to redirect the client to, usually used in 3xx redirection responses. - `Set-Cookie` (TEXT): Sets a cookie in the client browser, used for session management and storing user-related information. ### [cookie](/component.sql?component=cookie) Sets a cookie in the client browser, used for session management and storing user-related information. This component creates a single cookie. Since cookies need to be set before the response body is sent to the client, this component should be **placed at the top of the page**, before any other components that generate output. After being set, a cookie can be accessed anywhere in your SQL code using the `sqlpage.cookie('cookie_name')` pseudo-function. Note that if your site is accessed over HTTP (and not HTTPS), you have to set `false as secure` to force browsers to accept your cookies. #### Top-level parameters - `domain` (TEXT): The domain for which the cookie will be sent. If not specified, the cookie will be sent for all domains. - `expires` (TIMESTAMP): The date at which the cookie expires (either a timestamp or a date object). If not specified, the cookie will expire when the browser is closed. - `http_only` (BOOLEAN): Whether the cookie should only be accessible via HTTP and not via client-side scripts. If not specified, the cookie will be accessible via both HTTP and client-side scripts. - `max_age` (INTEGER): The maximum age of the cookie in seconds. number of seconds until the cookie expires. If both Expires and Max-Age are set, Max-Age has precedence. - `name` (TEXT) **REQUIRED**: The name of the cookie to set. - `path` (TEXT): The path for which the cookie will be sent. If not specified, the cookie will be sent for all paths. - `remove` (BOOLEAN): Set to TRUE to remove the cookie from the client browser. When specified, other parameters are ignored. - `same_site` (TEXT): Whether the cookie should only be sent for requests originating from the same site. See owasp.org/www-community/SameSite. `strict` is the recommended and default value, but you may want to set it to `lax` if you want your users to keep their session when they click on a link to your site from an external site. - `secure` (BOOLEAN): Whether the cookie should only be sent over a secure (HTTPS) connection. Defaults to TRUE. - `value` (TEXT): The value of the cookie to set. ### [debug](/component.sql?component=debug) Visualize any set of values as JSON. Can be used to display all the parameters passed to the component. Useful for debugging: just replace the name of the component you want to debug with 'debug', and see all the top-level and row-level parameters that are passed to it, and their types. ### [authentication](/component.sql?component=authentication) Create pages with password-restricted access. When you want to add user authentication to your SQLPage application, you have two main options: 1. The `authentication` component: - lets you manage usernames and passwords yourself - does not require any external service - gives you fine-grained control over - which pages and actions are protected - the look of the [login form](?component=login) - the duration of the session - the permissions of each user 2. [**Single sign-on**](/sso) - lets users log in with their existing accounts (like Google, Microsoft, or your organization's own identity provider) - requires setting up an external service (Google, Microsoft, etc.) - frees you from implementing a lot of features like password reset, account creation, user management, etc. This page describes the first option. When used, this component has to be at the top of your page, because once the page has begun being sent to the browser, it is too late to restrict access to it. The authentication component checks if the user has sent the correct password, and if not, redirects them to the URL specified in the link parameter. If you don't want to re-check the password on every page (which is an expensive operation), you can check the password only once and store a session token in your database (see the session example below). You can use the [cookie component](?component=cookie) to set the session token cookie in the client browser, and then check whether the token matches what you stored in subsequent pages. #### Top-level parameters - `link` (TEXT): The URL to redirect the user to if they are not logged in. If this parameter is not specified, the user will stay on the current page, but be asked to log in using a popup in their browser (HTTP basic authentication). - `password` (TEXT): The password that was sent by the user. You can set this to :password if you have a login form leading to your page. - `password_hash` (TEXT): The hash of the password that you stored for the user that is currently trying to log in. These hashes can be generated ahead of time using a tool like https://argon2.online/. ### [redirect](/component.sql?component=redirect) Redirects the user to another page. This component helps you: 1. Send users to a different page 1. Stop execution of the current page ### Conditional logic There is no `IF` statement in SQL. Even when you use a [`CASE` expression](https://modern-sql.com/caniuse/case_(simple)), all branches are always evaluated (and only one is returned). To conditionally execute a component or a [SQLPage function](/functions.sql), you can use the `redirect` component. A common use case is error handling. You may want to proceed with the rest of a page only when certain pre-conditions are met. ```sql SELECT 'redirect' AS component, 'error_page.sql' AS link WHERE NOT your_condition; -- The rest of the page is only executed if the condition is true ``` ### Technical limitation You must use this component **at the beginning of your SQL file**, before any other components that might send content to the browser. Since the component needs to tell the browser to go to a different page by sending an *HTTP header*, it will fail if the HTTP headers have already been sent by the time it is executed. > **Important difference from [http_header](?component=http_header)** > > This component completely stops the page from running after it's called. > This makes it a good choice for protecting sensitive information from unauthorized users. #### Top-level parameters - `link` (TEXT) **REQUIRED**: The URL to redirect the user to. ### [map](/component.sql?component=map) ## Visualize SQL data on a map. The map component displays a custom interactive map with markers on it. In its simplest form, the component displays points on a map from a table of latitudes and longitudes. But it can also be used by cartographers in combination with PostgreSQL's PostGIS or SQLite's spatialite, to create custom visualizations of geospatial data. Use the `geojson` property to generate rich maps from a GIS database. ### Example Use Cases 1. **Store Locator**: Build an interactive map to find the nearest store information using SQL-stored geospatial data. 2. **Delivery Route Optimization**: Visualize the results of delivery route optimization algorithms. 3. **Sales Heatmap**: Identify high-performing regions by mapping sales data stored in SQL. 4. **Real-Time Tracking**: Create dynamic dashboards that track vehicles, assets, or users live using PostGIS or MS SQL Server geospatial time series data. Use the [shell](?component=shell) component to auto-refresh the map. 5. **Demographic Insights**: Map customer demographics or trends geographically to uncover opportunities for growth or better decision-making. #### Top-level parameters - `attribution` (HTML): Text to display at the bottom right of the map. Defaults to "© OpenStreetMap". - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `height` (INTEGER): Height of the map, in pixels. Default to 350px - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `latitude` (REAL): Latitude of the center of the map. If omitted, the map will be centered on its markers. - `longitude` (REAL): Longitude of the center of the map. - `max_zoom` (INTEGER): How far the map can be zoomed in. Defaults to 18. Added in v0.15.2. - `tile_source` (URL): Custom map tile images to use, as a URL. Defaults to "https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png". Added in v0.15.2. - `zoom` (REAL): Zoom Level to apply to the map. Defaults to 5. #### Row-level parameters - `color` (COLOR): Background color of the marker on the map. Requires "icon" to be set. - `description` (TEXT): Plain text description of the marker, to be displayed in a tooltip when the marker is clicked. - `description_md` (TEXT): Description of the marker, in markdown, rendered in a tooltip when the marker is clicked. - `geojson` (JSON): A GeoJSON geometry (line, polygon, ...) to display on the map. Can be styled using geojson properties using the name of leaflet path options. Introduced in 0.15.1. Accepts raw strings in addition to JSON objects since 0.15.2. - `icon` (ICON): Name of the icon to use for the marker - `latitude` (REAL) **REQUIRED**: Latitude of the marker. Required only if geojson is not set. - `link` (TEXT): A link to associate to the marker's title. If set, the marker tooltip's title will be clickable and will open the link. - `longitude` (REAL) **REQUIRED**: Longitude of the marker. Required only if geojson is not set. - `size` (INTEGER): Size of the marker icon. Requires "icon" to be set. Introduced in 0.15.2. - `title` (TEXT): Title of the marker, displayed on hover and in the tooltip when the marker is clicked. ### [json](/component.sql?component=json) Converts SQL query results into the JSON machine-readable data format. Ideal to quickly build APIs for interfacing with external systems. **JSON** is a widely used data format for programmatic data exchange. For example, you can use it to integrate with web services written in different languages, with mobile or desktop apps, or with [custom client-side components](/custom_components.sql) inside your SQLPage app. Use it when your application needs to expose data to external systems. If you only need to render standard web pages, and do not need other software to access your data, you can ignore this component. This component **must appear at the top of your SQL file**, before any other data has been sent to the browser. An HTTP response can have only a single datatype, and it must be declared in the headers. So if you have already called the `shell` component, or another traditional HTML component, you cannot use this component in the same file. SQLPage can also return JSON or JSON Lines when the incoming request says it prefers them with an HTTP `Accept` header, so the same `/users.sql` page can show a table in a browser but return raw data to `curl -H "Accept: application/json" http://localhost:8080/users.sql`. Use this component when you want to control the payload or force JSON output even for requests that would normally get HTML. #### Top-level parameters - `contents` (TEXT): A single JSON payload to send. You can use your database's built-in json functions to build the value to enter here. If not provided, the contents will be taken from the next SQL statements and rendered as a JSON array. - `type` (TEXT): The type of the JSON payload to send: "array", "jsonlines", or "sse". In "array" mode, each query result is rendered as a JSON object in a single top-level array. In "jsonlines" mode, results are rendered as JSON objects in separate lines, without a top-level array. In "sse" mode, results are rendered as JSON objects in separate lines, prefixed by "data: ", which allows you to read the results as server-sent events in real-time from javascript. ### [tab](/component.sql?component=tab) Build a tabbed interface, with each tab being a link to a page. Each tab can be in two states: active or inactive. #### Top-level parameters - `center` (BOOLEAN): Whether the tabs should be centered or not. Defaults to false. #### Row-level parameters - `active` (BOOLEAN): Whether the tab is active or not. Defaults to false. - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `color` (TEXT): Color of the tab. See preview.tabler.io/colors.html for a list of available colors. - `description` (TEXT): Description of the tab. This is displayed when the user hovers over the tab. - `icon` (TEXT): Name of the icon to display on the tab. See tabler-icons.io for a list of available icons. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `link` (TEXT): Link to the page to display when the tab is clicked. By default, the link refers to the current page, with a 'tab' parameter set to the tab's title and hash set to the id (if passed) - this brings us back to the location of the tab after submission. - `title` (TEXT) **REQUIRED**: Text to display on the tab. If link is not set, the link will be the current page with a '$tab' parameter set to the tab's title. If 'id' is set, the page will be scrolled to the tab. ### [timeline](/component.sql?component=timeline) A list of events with a vertical line connecting them. #### Top-level parameters - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `simple` (BOOLEAN): If set to true, the timeline will be displayed in a condensed format without icons. #### Row-level parameters - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `color` (TEXT): Color of the icon. See preview.tabler.io/colors.html for a list of available colors. - `date` (TEXT) **REQUIRED**: Date of the event. - `description` (TEXT): Textual description of the event. - `description_md` (TEXT): Description of the event in Markdown. - `icon` (TEXT): Name of the icon to display next to the event. See tabler-icons.io for a list of available icons. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `link` (TEXT): Link to a page with more information about the event. - `title` (TEXT) **REQUIRED**: Name of the event. ### [button](/component.sql?component=button) A versatile button component do display one or multiple button links of different styles. #### Top-level parameters - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `justify` (TEXT): The horizontal alignment of the button list (e.g., start, end, center, between). - `shape` (TEXT): Shape of the buttons (e.g., pill, square) - `size` (TEXT): The size of the buttons (e.g., sm, lg). #### Row-level parameters - `color` (COLOR): The color of the button (e.g., red, green, blue, but also primary, warning, danger, etc.). Only base color names are supported, not variations like "blue-lt" or "gray-300". Use a custom CSS stylesheet to further customize the colors. - `disabled` (BOOLEAN): Whether the button is disabled or not. - `download` (TEXT): If defined, the link will download the target instead of navigating to it. Set the value to the desired name of the downloaded file. - `form` (TEXT): Identifier (id) of the form to which the button should submit. - `icon` (ICON): Name of an icon to be displayed on the left side of the button. - `icon_after` (ICON): Name of an icon to display after the text in the button - `id` (TEXT): HTML Identifier to add to the button element. - `image` (TEXT): Path to image file (relative. relative to web root or URL) to be displayed on the button. - `link` (URL): The URL to which the button should navigate when clicked. If the form attribute is specified, then this overrides the page to which the form is submitted. - `modal` (TEXT): Display the modal window corresponding to the specified ID. - `narrow` (BOOLEAN): Whether to trim horizontal padding. - `outline` (COLOR): Outline color of the button (e.g. red, purple, ...) - `rel` (TEXT): "nofollow" when the contents of the target link are not endorsed, "noopener" when the target is not trusted, and "noreferrer" to hide where the user came from when they open the link. - `space_after` (BOOLEAN): Whether there should be extra space to the right of the button. In a line of buttons, this will put the buttons before this one on the left, and the ones after on the right. - `target` (TEXT): "_blank" to open the link in a new tab, "_self" to open it in the same tab, "_parent" to open it in the parent frame, or "_top" to open it in the full body of the window. - `title` (TEXT): The text displayed on the button. - `tooltip` (TEXT): Text displayed when the user hovers over the button. ### [tracking](/component.sql?component=tracking) Component for visualising activity logs or other monitoring-related data. #### Top-level parameters - `center` (BOOLEAN): Whether to center the component. - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `description` (TEXT): A short paragraph. - `description_md` (TEXT): A short paragraph formatted using markdown. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `information` (TEXT): A short text displayed below the title. - `placement` (TEXT): Position of the tooltip (e.g. top, bottom, right, left) - `title` (TEXT) **REQUIRED**: Title of the tracking component. - `width` (INTEGER): Width of the component, between 1 and 12. #### Row-level parameters - `color` (TEXT): Color of the tracked item (e.g. success, warning, danger) - `title` (TEXT) **REQUIRED**: Description of the state. ### [divider](/component.sql?component=divider) Dividers help organize content and make the interface layout clear and uncluttered. #### Top-level parameters - `bold` (BOOLEAN): Whether the text is bold. - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `color` (COLOR): The name of a color for this span of text. - `contents` (TEXT): A text in the divider. - `italics` (BOOLEAN): Whether the text is italicized. - `link` (URL): URL of the link for the divider text. Available only when contents is present. - `position` (TEXT): Position of the text (e.g. left, right). - `size` (INTEGER): The size of the divider text, from 1 to 6. - `underline` (BOOLEAN): Whether the text is underlined. ### [breadcrumb](/component.sql?component=breadcrumb) A secondary navigation aid that helps users understand their location on a website or mobile application. #### Top-level parameters - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). #### Row-level parameters - `active` (TEXT): Whether the link is active or not. Defaults to false. - `description` (TEXT): Description of the link. This is displayed when the user hovers over the link. - `link` (TEXT): Link to the page to display when the link is clicked. By default, the link refers to the current page, with a 'link' parameter set to the link's title. - `title` (TEXT) **REQUIRED**: Hyperlink text to display. ### [card](/component.sql?component=card) A grid where each element is a small card that displays a piece of data. #### Top-level parameters - `columns` (INTEGER): The number of columns in the grid of cards. This is just a hint, the grid will adjust dynamically to the user's screen size, rendering fewer columns if needed to fit the contents. To control the size of cards individually, use the `width` row-level property instead. - `description` (TEXT): A short paragraph displayed below the title. - `description_md` (TEXT): A short paragraph displayed below the title - formatted using markdown. - `title` (TEXT): Text header at the top of the list of cards. #### Row-level parameters - `active` (BOOLEAN): Whether this item in the grid is considered "active". Active items are displayed more prominently. - `background_color` (COLOR): The background color of the card. - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `color` (COLOR): The name of a color, to be displayed on the left of the card to highlight it. If the embed parameter is enabled and you don't have a title or description, this parameter won't apply. - `description` (TEXT): The body of the card, where you put the main text contents of the card. This does not support rich text formatting, only plain text. If you want to use rich text formatting, use the `description_md` property instead. - `description_md` (TEXT): The body of the card, in Markdown format. This is useful if you want to display a lot of text in the card, with many options for formatting, such as line breaks, **bold**, *italics*, lists, #titles, [links](target.sql), ![images](photo.jpg), etc. - `footer` (TEXT): Muted text to display at the bottom of the card. - `footer_link` (URL): An URL to which the user should be taken when they click on the footer. - `footer_md` (TEXT): Muted text to display at the bottom of the card, with rich text formatting in Markdown format. - `icon` (ICON): Name of an icon to display on the right side of the card. - `link` (URL): An URL to which the user should be taken when they click on the card. - `style` (TEXT): Inline style property to your iframe embed code. For example "background-color: #FFFFFF" - `title` (TEXT) **REQUIRED**: Name of the card, displayed at the top. - `top_image` (URL): The URL (absolute or relative) of an image to display at the top of the card. - `top_image_height` (INTEGER): Specify the top image height, in pixels. Helps prevent layout shifts. - `top_image_lazy` (BOOLEAN): Whether the top image must be lazily loaded. Defaults to false, meaning eagerly loaded. - `top_image_width` (INTEGER): Specify the top image width, in pixels. Helps prevent layout shifts. - `width` (INTEGER): The width of the card, between 1 (smallest) and 12 (full-width). The default width is 3, resulting in 4 cards per line. ### [carousel](/component.sql?component=carousel) A carousel is used to display images. When used with multiple images, it will cycle through them automatically or with controls, creating a slideshow. #### Top-level parameters - `auto` (BOOLEAN): Whether to automatically cycle through the carousel items. Default is false. - `center` (BOOLEAN): Whether to center the carousel. - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `controls` (BOOLEAN): Whether to show the control links to go previous or next item. - `delay` (INTEGER): Specify the delay, in milliseconds, between two images. - `fade` (BOOLEAN): Whether to apply the fading effect. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `indicators` (TEXT): Style of image indicators (square or dot). - `title` (TEXT): A name to display at the top of the carousel. - `vertical` (BOOLEAN): Whether to use the vertical image indicators. - `width` (INTEGER): Width of the component, between 1 and 12. Default is 12. #### Row-level parameters - `description` (TEXT): A short paragraph. - `description_md` (TEXT): A short paragraph formatted using markdown. - `height` (INTEGER): The height of the image, in pixels. - `image` (URL) **REQUIRED**: The URL (absolute or relative) of an image to display in the carousel. - `title` (TEXT): Add caption to the slide. - `width` (INTEGER): The width of the image, in pixels. ### [title](/component.sql?component=title) Defines HTML headings. The level 1 is used for the maximal size and the level 6 is used for the minimal size. #### Top-level parameters - `center` (BOOLEAN): Whether to center the title. - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `contents` (TEXT) **REQUIRED**: A text to display. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `level` (INTEGER): Set the heading level (default level is 1) ### [code](/component.sql?component=code) Displays one or many blocks of code from a programming language or formated text as XML or JSON. #### Top-level parameters - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). #### Row-level parameters - `contents` (TEXT) **REQUIRED**: A block of code. - `description` (TEXT): Description of the snipet of code. - `description_md` (TEXT): Rich text in the markdown format. Among others, this allows you to write bold text using **bold**, italics using *italics*, and links using [text](https://example.com). - `language` (TEXT): Set the programming language name. - `title` (TEXT): Set the heading level (default level is 1) ### [rss](/component.sql?component=rss) Produces a data flow in the RSS format. Can be used to generate a podcast feed. To use this component, you must first return an HTTP header with the "application/rss+xml" content type (see http_header component). Next, you must use the shell-empty component to avoid that SQLPage generates HTML code. #### Top-level parameters - `author` (TEXT): Defines the group, person, or people responsible for creating the channel. - `category` (TEXT): Defines the category of the channel. The value should be a string representing the category (e.g., "News", "Technology", etc.). - `complete` (BOOLEAN): Specifies that a channel is complete and will not post any more items in the future. - `copyright` (TEXT): Provides the copyright details for the channel. - `description` (TEXT) **REQUIRED**: Describes the channel. - `explicit` (BOOLEAN): Indicates whether the channel contains explicit content. The value can be either TRUE or FALSE. - `funding_url` (URL): Specifies the donation/funding links for the channel. The content of the tag is the recommended string to be used with the link. - `guid` (TEXT): The globally unique identifier (GUID) for a channel. The value is a UUIDv5. - `image_url` (URL): Provides a URL linking to the artwork for the channel. - `language` (TEXT): Defines the language of the channel, specified in the ISO 639 format. For example, "en" for English, "fr" for French. - `link` (URL) **REQUIRED**: Defines the hyperlink to the channel. - `locked` (BOOLEAN): Tells podcast hosting platforms whether they are allowed to import this feed. - `self_link` (URL): URL of the RSS feed. - `title` (TEXT) **REQUIRED**: Defines the title of the channel. - `type` (TEXT): Specifies the channel as either episodic or serial. The value can be either "episodic" or "serial". #### Row-level parameters - `block` (BOOLEAN): Prevents a specific item from appearing in podcast listening applications. - `date` (TEXT): Indicates when the item was published (RFC-822 date-time). - `description` (TEXT) **REQUIRED**: Describes the item - `duration` (INTEGER): The duration of an item in seconds. - `enclosure_length` (INTEGER): The length in bytes of the audio/video episode content. - `enclosure_type` (TEXT): The MIME media type of the audio/video episode content (e.g., "audio/mpeg", "audio/m4a", "video/m4v", "video/mp4"). - `enclosure_url` (URL): For podcast episodes, provides a URL linking to the audio/video episode content, in mp3, m4a, m4v, or mp4 format. - `episode` (INTEGER): The chronological number that is associated with an item. - `episode_type` (TEXT): Defines the type of content for a specific item. The value can be either "full", "trailer", or "bonus". - `explicit` (BOOLEAN): Indicates whether the item contains explicit content. The value can be either TRUE or FALSE. - `guid` (TEXT): The globally unique identifier (GUID) for an item. - `image_url` (URL): Provides a URL linking to the artwork for the item. - `link` (URL) **REQUIRED**: Defines the hyperlink to the item (blog post URL, etc.). - `season` (INTEGER): The chronological number associated with an item's season. - `title` (TEXT) **REQUIRED**: Defines the title of the feed item (episode name, blog post title, etc.). - `transcript_type` (TEXT): The type of the transcript or closed captions file for the item (e.g., "text/plain", "text/html", "text/vtt", "application/json", "application/x-subrip"). - `transcript_url` (URL): A link to a transcript or closed captions file for the item. ### [html](/component.sql?component=html) Include raw HTML in the output. For advanced users only. Use this component to create complex layouts or to include external content. Be very careful when using this component with user-generated content, as it can lead to security vulnerabilities. Use this component only if you are familiar with the security implications of including raw HTML, and understand the risks of cross-site scripting (XSS) attacks. #### Top-level parameters - `html` (TEXT): Raw HTML content to include in the page. This will not be sanitized or escaped. If you include content from an untrusted source, your page will be vulnerable to cross-site scripting attacks. #### Row-level parameters - `html` (TEXT): Raw HTML content to include in the page. This will not be sanitized or escaped. If you include content from an untrusted source, your page will be vulnerable to cross-site scripting attacks. - `post_html` (TEXT): Raw HTML content to include after the text content. This will not be sanitized or escaped. If you include content from an untrusted source, your page will be vulnerable to cross-site scripting attacks. - `text` (TEXT): Text content to include in the page. This will be sanitized and escaped. Use this property to include user-generated content that should not contain HTML tags. ### [status_code](/component.sql?component=status_code) Sets the HTTP response code for the current page. This is an advanced technical component. You typically need it when building internet-facing APIs and websites, but you may not need it for simple internal applications. - Indicating operation results when using [SQLPage as an API](?component=json) - `200`: *OK*, for successful operations - `201`: *Created*, for successful record insertion - `404`: *Not Found*, for missing resources - `500`: *Internal Server Error*, for failed operations - Handling data validation errors - `400`: *Bad Request*, for invalid data - Enforcing access controls - `403`: *Forbidden*, for unauthorized access - `401`: *Unauthorized*, for unauthenticated access - Tracking system health - `500`: *Internal Server Error*, for failed operations For search engine optimization: - Use `404` for deleted content to remove outdated URLs from search engines - For redirection from one page to another, use - `301` (moved permanently), or - `302` (moved temporarily) - Use `503` during maintenance #### Top-level parameters - `status` (INTEGER) **REQUIRED**: HTTP status code (e.g., 200 OK, 401 Unauthorized, 409 Conflict) ### [big_number](/component.sql?component=big_number) A component to display key metrics or statistics with optional description, change indicator, and progress bar. Useful in dashboards. #### Top-level parameters - `class` (TEXT): An optional CSS class to be added to the component for custom styling - `columns` (INTEGER): The number of columns to display the big numbers in (default is one column per item). - `id` (TEXT): An optional ID to be used as an anchor for links. #### Row-level parameters - `change_percent` (INTEGER): The percentage change in value (e.g., 7 for 7% increase, -8 for 8% decrease). - `color` (COLOR): The color of the card - `description` (TEXT): A description or additional context for the big number. - `dropdown_item` (JSON): A list of JSON objects containing links. e.g. {"label":"This week", "link":"?days=7"} - `progress_color` (TEXT): The color of the progress bar (e.g., "primary", "success", "danger"). - `progress_percent` (INTEGER): The value of the progress (0-100). - `title` (TEXT): The title or label for the big number. - `title_link` (TEXT): A link for the Big Number title. If set, the entire title becomes clickable. - `title_link_new_tab` (BOOLEAN): If true, the title link will open in a new tab/window. - `unit` (TEXT): The unit of measurement for the value. - `value` (TEXT) **REQUIRED**: The main value to be displayed prominently. - `value_link` (TEXT): A link for the Big Number value. If set, the entire value becomes clickable. - `value_link_new_tab` (BOOLEAN): If true, the value link will open in a new tab/window. ### [columns](/component.sql?component=columns) A component to display various items in a card layout, allowing users to choose options. Useful for showcasing different features or services, or KPIs. See also the big_number component. #### Row-level parameters - `button_color` (TEXT): Optional color for the button. - `button_text` (TEXT): Text for the button. - `description` (TEXT): A brief description of the item. - `description_md` (TEXT): A brief description of the item, formatted using markdown. - `icon` (ICON): Optional icon to display in a ribbon. - `icon_color` (TEXT): Color for the icon in the ribbon. - `item` (JSON): A list of bullet points associated with the columns, represented either as text, or as a json object with "icon", "color", and "description" or "description_md" fields. - `link` (TEXT): A link associated with the item. - `size` (INTEGER): Size of the column, affecting layout. - `small_text` (TEXT): Optional small text to display after the value. - `target` (TEXT): Optional target for the button. Set to "_blank" to open links in a new tab. - `title` (TEXT): The title or label for the item. - `value` (TEXT): The value associated with the item. - `value_color` (TEXT): Color for the value text. ### [foldable](/component.sql?component=foldable) A foldable list of elements which can be expanded individually. #### Top-level parameters - `class` (TEXT): CSS class(es) to add to the foldable container. When set at the top level, applies to the entire foldable component. - `id` (TEXT): ID attribute added to the container in HTML. Used for targeting through CSS or for scrolling via links. When set at the top level, applies to the entire foldable component. #### Row-level parameters - `class` (TEXT): CSS class(es) to add to individual foldable items. - `description` (TEXT): Plain text description of the item, displayed when expanded. - `description_md` (TEXT): Markdown description of the item, displayed when expanded. - `expanded` (BOOLEAN): If set to TRUE, the foldable item starts in an expanded state. Defaults FALSE - `id` (TEXT): ID attribute added to individual foldable items. Used for targeting through CSS or for scrolling via links. - `title` (TEXT): Title of the foldable item, displayed on the button. ### [empty_state](/component.sql?component=empty_state) Displays a large placeholder message to communicate a single information to the user and invite them to take action. Typically includes a title, an optional icon/image, descriptive text (rich text formatting and images supported via Markdown), and a call-to-action button. Ideal for first-use screens, empty data sets, "no results" pages, or error messages. #### Top-level parameters - `class` (TEXT): Class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. - `description` (TEXT): A short text displayed below the title. - `header` (TEXT): Text displayed on the top of the empty state. - `icon` (ICON): Name of an icon to be displayed on the top of the empty state. - `id` (TEXT): ID attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `image` (URL): The URL (absolute or relative) of an image to display at the top of the empty state. - `link` (URL) **REQUIRED**: The URL to which the button should navigate when clicked. - `link_icon` (ICON) **REQUIRED**: Name of an icon to be displayed on the left side of the button. - `link_text` (TEXT) **REQUIRED**: The text displayed on the button. - `title` (TEXT) **REQUIRED**: Description of the empty state. ### [modal](/component.sql?component=modal) Defines the a temporary popup box displayed on top of a webpage’s content. Useful for displaying additional information, help, or collect data from users. Modals are closed by default, and can be opened by clicking on a button or link targeting their ID. #### Top-level parameters - `allow` (TEXT): For embedded content, this attribute specifies the features or permissions that can be used. - `class` (TEXT): Class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. - `close` (TEXT): The text to display in the Close button. - `embed` (TEXT): Embed remote content in an iframe. - `embed_mode` (TEXT): Use "iframe" to display embedded content within an iframe. - `height` (INTEGER): Height of the embedded content. - `id` (TEXT) **REQUIRED**: ID attribute added to the container in HTML. It can be used to target this item through css or for displaying this item. - `large` (BOOLEAN): Indicates that the modal box has an increased width. - `sandbox` (TEXT): For embedded content, this attribute specifies the security restrictions on the loaded content. - `scrollable` (BOOLEAN): Create a scrollable modal that allows scroll the modal body. - `small` (BOOLEAN): Indicates that the modal box has a reduced width. - `style` (TEXT): Applies CSS styles to the embedded content. - `title` (TEXT) **REQUIRED**: Description of the modal box. #### Row-level parameters - `contents` (TEXT): A paragraph of text to display, without any formatting, without having to make additional queries. - `contents_md` (TEXT): Rich text in the markdown format. Among others, this allows you to write bold text using **bold**, italics using *italics*, and links using [text](https://example.com). ### [download](/component.sql?component=download) The *download* component lets a page immediately return a file to the visitor. Instead of showing a web page, it sends the file's bytes as the whole response, so it should be used **at the very top of your SQL page** (before the shell or any other page contents). It is an error to use this component after another component that would display content. How it works in simple terms: - You provide the file content using a [data URL](https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/Data_URIs). A data URL is just a text string that contains both the file type and the actual data. - Optionally, you provide a "filename" so the browser shows a proper Save As name. If you do not provide a filename, many browsers will try to display the file inline (for example images or JSON), depending on the content type. - You link to the page that uses the download component from another page, using the [button](/components?component=button) component for example. What is a data URL? - It looks like this: `data:[content-type][;base64],DATA` - Examples: - Plain text (URL-encoded): `data:text/plain,Hello%20world` - JSON (URL-encoded): `data:application/json,%7B%22message%22%3A%22Hi%22%7D` - Binary data (Base64): `data:application/octet-stream;base64,SGVsbG8h` Tips: - Use URL encoding when you have textual data. You can use [`sqlpage.url_encode(source_text)`](/functions?function=url_encode) to encode the data. - Use Base64 when you have binary data (images, PDFs, or content that may include special characters). - Use [`sqlpage.read_file_as_data_url(file_path)`](/functions?function=read_file_as_data_url) to read a file from the server and return it as a data URL. > Keep in mind that large files are better served from disk or object storage. Data URLs are best for small to medium files. There is a big performance penalty for loading large files as data URLs, so it is not recommended. #### Top-level parameters - `data_url` (TEXT) **REQUIRED**: The file content to send, written as a data URL (for example: data:text/plain,Hello%20world or data:application/octet-stream;base64,SGVsbG8h). The part before the comma declares the content type and whether the data is base64-encoded. The part after the comma is the actual data. - `filename` (TEXT): The suggested name of the file to save (for example: report.csv). When set, the browser will download the file as an attachment with this name. When omitted, many browsers may try to display the file inline depending on its content type. ### [log](/component.sql?component=log) A component that writes messages to the server logs. When a page runs, it prints your message to the terminal/console (standard error). Use it to track what happens and troubleshoot issues. ### Where do the messages appear? - Running from a terminal (Linux, macOS, or Windows PowerShell/Command Prompt): they show up in the window. - Docker: run `docker logs `. - Linux service (systemd): run `journalctl -u sqlpage`. - This component's output is written to [standard error (stderr)](https://en.wikipedia.org/wiki/Standard_streams#Standard_error_(stderr)). SQLPage request access logs are separate and are written to standard output (stdout). #### Top-level parameters - `level` (TEXT): How important the message is. One of 'trace', 'debug', 'info' (default), 'warn', 'error'. Not case-sensitive. Controls the level shown in the logs. - `message` (TEXT) **REQUIRED**: The text to write to the server logs. It is printed when the page runs. ### [login](/component.sql?component=login) The login component is an authentication form with numerous customization options. It offers the main functionalities for this type of form. The user can enter their username and password. There are many optional attributes such as the use of icons on input fields, the insertion of a link to a page to reset the password, an option for the application to maintain the user's identity via a cookie. It is also possible to set the title of the form, display the company logo, or customize the appearance of the form submission button. This component should be used in conjunction with other components such as [authentication](component.sql?component=authentication) and [cookie](component.sql?component=cookie). It does not implement any logic and simply collects the username and password to pass them to the code responsible for authentication. A few things to know : - The form uses the POST method to transmit information to the destination page, - The user's username and password are entered into fields with the names `username` and `password`, - To obtain the values of username and password, you must use the variables `:username` and `:password`, - When you set the `remember_me_text` property, the variable `:remember` becomes available after form submission to check if the user checked the "remember me" checkbox. #### Top-level parameters - `action` (TEXT): An optional link to a target page that will handle the results of the form. - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `enctype` (TEXT): Form data encoding. - `error_message` (TEXT): An error message to display above the form, typically shown after a failed login attempt. - `error_message_md` (TEXT): A markdown error message to display above the form, typically shown after a failed login attempt. - `footer` (TEXT): A text placed at the bottom of the authentication form. If both footer and footer_md are specified, footer takes precedence. - `footer_md` (TEXT): A markdown text placed at the bottom of the authentication form. Useful for creating links to other pages (creating a new account, contacting technical support, etc.). - `forgot_password_link` (TEXT): The link to the page allowing the user to reset their password. - `forgot_password_text` (TEXT): A text for the link allowing the user to reset their password. If the text is empty, the link is not displayed. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `image` (URL): The URL of an centered image displayed before the title. - `password` (TEXT) **REQUIRED**: Label and placeholder for the password field. - `password_icon` (ICON): Icon to display on the left side of the input field, on the same line. - `remember_me_text` (TEXT): A text for the option allowing the user to request the preservation of their identity. If the text is empty, the option is not displayed. - `title` (TEXT): Title of the authentication form. - `username` (TEXT) **REQUIRED**: Label and placeholder for the user account identifier text field. - `username_icon` (ICON): Icon to display on the left side of the input field, on the same line. - `validate` (TEXT): The text to display in the button at the bottom of the form that submits the values. - `validate_color` (COLOR): The color of the button at the bottom of the form that submits the values. Omit this property to use the default color. - `validate_outline` (COLOR): A color to outline the validation button. - `validate_shape` (TEXT): The shape of the validation button. - `validate_size` (TEXT): The size of the validation button. ### [pagination](/component.sql?component=pagination) Navigation links to go to the first, previous, next, or last page of a dataset. Useful when data is divided into pages, each containing a fixed number of rows. This component only handles the display of pagination. **Your sql queries are responsible for filtering data** based on the page number passed as a URL parameter. This component is typically used in conjunction with a [table](?component=table), [list](?component=list), or [card](?component=card) component. The pagination component displays navigation buttons (first, previous, next, last) customizable with text or icons. For large numbers of pages, an offset can limit the visible page links. A minimal example of a SQL query that uses the pagination would be: ```sql select 'table' as component; select * from my_table limit 100 offset $offset; select 'pagination' as component; with recursive pages as ( select 0 as offset union all select offset + 100 from pages where offset + 100 < (select count(*) from my_table) ) select (offset/100+1) as contents, sqlpage.link(sqlpage.path(), json_object('offset', offset)) as link, offset = coalesce(cast($offset as integer), 0) as active from pages; ``` For more advanced usage, the [pagination guide](blog.sql?post=How+to+use+the+pagination+component) provides a complete tutorial. #### Top-level parameters - `circle` (BOOLEAN): Whether to use circle version of the pagination. - `class` (TEXT): class attribute added to the container in HTML. It can be used to apply custom styling to this item through css. Added in v0.18.0. - `first_disabled` (BOOLEAN): disables the button to go to the first page. - `first_link` (URL): A target URL to which the user should be directed to get to the first page. If none, the link is not displayed. - `first_title` (TEXT): The text displayed on the button to go to the first page. - `id` (TEXT): id attribute added to the container in HTML. It can be used to target this item through css or for scrolling to this item through links (use "#id" in link url). - `last_disabled` (BOOLEAN): disables the button to go to the last page. - `last_link` (URL): A target URL to which the user should be directed to get to the last page. If none, the link is not displayed. - `last_title` (TEXT): The text displayed on the button to go to the last page. - `next_disabled` (BOOLEAN): Disables the button to go to the next page. - `next_link` (URL): A target URL to which the user should be directed to get to the next page. If none, the link is not displayed. - `next_title` (TEXT): The text displayed on the button to go to the next page. - `outline` (BOOLEAN): Whether to use outline version of the pagination. - `previous_disabled` (BOOLEAN): disables the button to go to the previous page. - `previous_link` (URL): A target URL to which the user should be directed to get to the previous page. If none, the link is not displayed. - `previous_title` (TEXT): The text displayed on the button to go to the previous page. #### Row-level parameters - `active` (BOOLEAN): Whether the link is active or not. Defaults to false. - `contents` (INTEGER) **REQUIRED**: Page number. - `link` (URL): A target URL to which the user should be redirected to view the requested page of data. - `offset` (BOOLEAN): Whether to use offset to show only a few pages at a time. Usefull if the count of pages is too large. Defaults to false ### [toast](/component.sql?component=toast) Displays a brief notification above the page. Each top-level `toast` row creates one notification, and consecutive toasts at the same position are queued in a shared stack. Ordinary notifications use `role="status"` and `aria-live="polite"`. Colored variants retain a readable contrasting foreground. Automatic dismissal and the manual close control are configured independently. #### Top-level parameters - `class` (TEXT): Optional custom CSS class appended to the toast. - `color` (COLOR): Optional Tabler color. The default is the neutral toast appearance; colored variants use the matching contrasting foreground utility. - `description` (TEXT): Escaped plain-text body. This is used only when `description_md` is not supplied. - `description_md` (TEXT): Rich-text alternative to `description`, rendered as Markdown. When both properties are supplied, `description_md` takes precedence. - `dismissible` (BOOLEAN): Whether to render an accessible manual close button. Defaults to true and is independent of automatic dismissal. - `duration` (INTEGER): Automatic dismissal delay in milliseconds. Defaults to 5000. Set to 0 to keep the toast visible until manually dismissed (when `dismissible` is true) or the page is left. - `icon` (ICON): Optional [Tabler icon](https://tabler.io/icons) name. - `id` (TEXT): Optional stable HTML ID for the toast. - `position` (TEXT): Screen placement: `top-start`, `top-center`, `top-end`, `bottom-start`, `bottom-center`, or `bottom-end`. Defaults to `top-end`; invalid values safely fall back to that default. - `title` (TEXT): Optional notification heading. - `trigger` (TEXT): Optional URL fragment that opens the toast without reloading the page, with or without the leading `#`. When set, the toast does not open on page load and can be opened repeatedly by a link or button whose target is that fragment. Multiple toasts can share a trigger to open as a stack. ## Functions ### [sqlpage.cookie()](/functions.sql?function=cookie) Reads a [cookie](https://en.wikipedia.org/wiki/HTTP_cookie) with the given name from the request. Returns the value of the cookie as text, or NULL if the cookie is not present. Cookies can be set using the [cookie component](documentation.sql?component=cookie#component). ##### Example ###### Set a cookie Set a cookie called `username` to greet the user by name every time they visit the page: ```sql select 'cookie' as component, 'username' as name, :username as value; SELECT 'form' as component; SELECT 'username' as name, 'text' as type; ``` ###### Read a cookie Read a cookie called `username` and greet the user by name: ```sql SELECT 'text' as component, 'Hello, ' || sqlpage.cookie('username') || '!' as contents; ``` ### [sqlpage.header()](/functions.sql?function=header) Reads a [header](https://en.wikipedia.org/wiki/List_of_HTTP_header_fields) with the given name from the request. Returns the value of the header as text, or NULL if the header is not present. ##### Example Log the [`User-Agent`](https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/User-Agent) of the browser making the request in the database: ```sql INSERT INTO user_agent_log (user_agent) VALUES (sqlpage.header('user-agent')); ``` If you need access to all headers at once, use [`sqlpage.headers()`](?function=headers) instead. ### [sqlpage.basic_auth_username()](/functions.sql?function=basic_auth_username) Returns the username from the [Basic Authentication](https://en.wikipedia.org/wiki/Basic_access_authentication) header of the request. If the header is not present, this function raises an authorization error that will prompt the user to enter their credentials. ##### Example ```sql SELECT 'authentication' AS component, (SELECT password_hash from users where name = sqlpage.basic_auth_username()) AS password_hash, sqlpage.basic_auth_password() AS password; ``` ### [sqlpage.basic_auth_password()](/functions.sql?function=basic_auth_password) Returns the password from the [Basic Authentication](https://en.wikipedia.org/wiki/Basic_access_authentication) header of the request. If the header is not present, this function raises an authorization error that will prompt the user to enter their credentials. ##### Example ```sql SELECT 'authentication' AS component, (SELECT password_hash from users where name = sqlpage.basic_auth_username()) AS password_hash, sqlpage.basic_auth_password() AS password; ``` ### [sqlpage.hash_password()](/functions.sql?function=hash_password) Hashes a password with the Argon2id variant and outputs it in the [PHC string format](https://github.com/P-H-C/phc-string-format/blob/master/phc-sf-spec.md), ready to store in your users table. Every call generates a brand new cryptographic salt so that two people choosing the same password still end up with different hashes, which defeats rainbow-table attacks and lets you safely reveal only the hash. Use this function only when creating or resetting a password (for example while inserting a brand new user): it writes the stored value. Later, at login time, the [authentication component](documentation.sql?component=authentication#component) reads the stored hash, hashes the visitor's password with the embedded salt and parameters, and grants access only if they match. ##### Example ```sql SELECT 'form' AS component; SELECT 'username' AS name; SELECT 'password' AS name, 'password' AS type; INSERT INTO users (name, password_hash) VALUES (:username, sqlpage.hash_password(:password)); ``` ##### Try online You can try the password hashing function [on this page](/examples/hash_password.sql). ### [sqlpage.random_string()](/functions.sql?function=random_string) Returns a cryptographically secure random string of the given length. When used as a standalone selected column in a query that returns several rows, `random_string` runs once per returned row. Use `SET token = sqlpage.random_string(32)` first if you want one token reused later in the page. ##### Example Generate a random string of 32 characters and use it as a session ID stored in a cookie: ```sql INSERT INTO login_session (session_token, username) VALUES (sqlpage.random_string(32), :username) RETURNING 'cookie' AS component, 'session_id' AS name, session_token AS value; ``` ### [sqlpage.current_working_directory()](/functions.sql?function=current_working_directory) Returns the [current working directory](https://en.wikipedia.org/wiki/Working_directory) of the SQLPage server process. ##### Example ```sql SELECT 'text' AS component; SELECT 'Currently running from ' AS contents; SELECT sqlpage.current_working_directory() as contents, true as code; ``` ###### Result Currently running from `/home/user/my_sqlpage_website` ###### Notes The current working directory is the directory from which the SQLPage server process was started. By default, this is also the directory from which `.sql` files are loaded and served. However, this can be changed by setting the `web_root` [configuration option](https://github.com/sqlpage/SQLPage/blob/main/configuration.md). ### [sqlpage.web_root()](/functions.sql?function=web_root) Returns the web root directory where SQLPage serves `.sql` files from. ##### Example ```sql SELECT 'text' AS component; SELECT 'SQL files are served from ' AS contents; SELECT sqlpage.web_root() as contents, true as code; ``` ###### Result SQL files are served from `/home/user/my_sqlpage_website` ###### Notes The web root is the directory from which `.sql` files are loaded and served. By default, it is the current working directory, but it can be changed using: - the `--web-root` command line argument - the `web_root` [configuration option](https://github.com/sqlpage/SQLPage/blob/main/configuration.md) in `sqlpage.json` - the `WEB_ROOT` environment variable This is more reliable than `sqlpage.current_working_directory()` when you need to reference the location of your SQL files. ### [sqlpage.configuration_directory()](/functions.sql?function=configuration_directory) Returns the configuration directory where SQLPage looks for `sqlpage.json`, templates, and migrations. ##### Example ```sql SELECT 'text' AS component; SELECT 'Configuration files are in ' AS contents; SELECT sqlpage.configuration_directory() as contents, true as code; ``` ###### Result Configuration files are in `/home/user/my_sqlpage_website/sqlpage` ###### Notes The configuration directory is where SQLPage looks for: - `sqlpage.json` (the configuration file) - `templates/` (custom component templates) - `migrations/` (database migration files) By default, it is `./sqlpage` relative to the current working directory, but it can be changed using: - the `--config-dir` command line argument - the `SQLPAGE_CONFIGURATION_DIRECTORY` or `CONFIGURATION_DIRECTORY` environment variable This function is useful when you need to reference configuration-related files in your SQL code. ### [sqlpage.environment_variable()](/functions.sql?function=environment_variable) Returns the value of the given [environment variable](https://en.wikipedia.org/wiki/Environment_variable). ##### Example ```sql SELECT 'text' AS component; SELECT 'The value of the HOME environment variable is ' AS contents; SELECT sqlpage.environment_variable('HOME') as contents, true as code; ``` ### [sqlpage.version()](/functions.sql?function=version) Returns the current version of SQLPage as a string. ### [sqlpage.exec()](/functions.sql?function=exec) Executes a shell command and returns its output as text. ##### Example ###### Fetch data from a remote API using curl ```sql select 'card' as component; select value->>'name' as title, value->>'email' as description from json_each(sqlpage.exec('curl', 'https://jsonplaceholder.typicode.com/users')); ``` ###### Notes - This function is disabled by default for security reasons. You can enable it by setting `"allow_exec" : true` in `sqlpage/sqlpage.json`. Enable it only if you trust all the users that can access your SQLPage server files (both locally and on the database). - Be careful when using this function, as it can be used to execute arbitrary shell commands on your server. Do not use it with untrusted input. - The command is executed in the current working directory of the SQLPage server process. - The command is executed with the same user as the SQLPage server process. - The environment variables of the SQLPage server process are passed to the command, including potentially sensitive variables such as `DATABASE_URL`. - The command is executed asynchronously, but the SQLPage server has to wait for it to finish before sending the result to the client. This means that the SQLPage server will not be blocked while the command is running, it will be able to serve other requests, but it will not be able to serve the current request until the command has finished. You should generally avoid long running commands. - If the program name is NULL, the result will be NULL. - If any argument is NULL, it will be passed to the command as an empty string. - If the command exits with a non-zero exit code, the function will raise an error. - Arbitrary SQL operations are not allowed as sqlpage function arguments. Use `SET` to assign the result of a SQL query to a variable, and then use that variable as an argument to `sqlpage.exec`. - When `sqlpage.exec(...)` is a standalone selected column, the command runs once per returned row. Use `SET command_result = sqlpage.exec(...)` first if the command should run only once for the page. ### [sqlpage.url_encode()](/functions.sql?function=url_encode) Returns the given string, with all characters that are not allowed in a URL encoded. ##### Example ```sql select 'text' as component; select 'https://example.com/?q=' || sqlpage.url_encode($user_search) as contents; ``` ###### Result `https://example.com/?q=hello%20world` ### [sqlpage.variables()](/functions.sql?function=variables) Returns a JSON string containing variables from the HTTP request and user-defined variables. The [database's json handling functions](/blog?post=JSON+in+SQL%3A+A+Comprehensive+Guide) can then be used to process the data. #### Variable Types SQLPage distinguishes between three types of variables: - **GET variables**: URL parameters from the [query string](https://en.wikipedia.org/wiki/Query_string) (immutable) - **POST variables**: Values from form fields [submitted](https://en.wikipedia.org/wiki/POST_(HTTP)#Use_for_submitting_web_forms) by the user (immutable) - **SET variables**: User-defined variables created with the `SET` command (mutable) For more information about SQLPage variables, see the [*SQL in SQLPage* guide](/extensions-to-sql). #### Usage - `sqlpage.variables()` - returns all variables (GET, POST, and SET combined). When multiple variables of the same name are present, the order of precedence is: set > post > get. - `sqlpage.variables('get')` - returns only URL parameters - `sqlpage.variables('post')` - returns only POST form data - `sqlpage.variables('set')` - returns only user-defined variables created with `SET` When a SET variable has the same name as a GET or POST variable, the SET variable takes precedence in the combined result. #### Example: a form with a variable number of fields ##### Making a form based on questions in a database table We can create a form which has a field for each value in a given table like this: ```sql select 'form' as component, 'handle_survey_answer.sql' as action; select question_id as name, question_text as label from survey_questions; ``` ##### Handling form responses using `sqlpage.variables` In `handle_survey_answer.sql`, one can process the form results even if we don't know in advance how many fields it contains. The function to parse JSON data varies depending on the database engine you use. ###### In SQLite In SQLite, one can use [`json_each`](https://www.sqlite.org/json1.html#jeach) : ```sql insert into survey_answers(question_id, answer) select "key", "value" from json_each(sqlpage.variables('post')) ``` ###### In Postgres Postgres has [`json_each_text`](https://www.postgresql.org/docs/9.3/functions-json.html) : ```sql INSERT INTO survey_answers (question_id, answer) SELECT key AS question_id, value AS answer FROM json_each_text(sqlpage.variables('post')::json); ``` ###### In Microsoft SQL Server ```sql INSERT INTO survey_answers SELECT [key] AS question_id, [value] AS answer FROM OPENJSON(sqlpage.variables('post')); ``` ###### In MySQL MySQL has [`JSON_TABLE`](https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html), and [`JSON_KEYS`](https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-keys) which are a little bit less straightforward to use: ```sql INSERT INTO survey_answers (question_id, answer) SELECT question_id, json_unquote( json_extract( sqlpage.variables('post'), concat('$."', question_id, '"') ) ) FROM json_table( json_keys(sqlpage.variables('post')), '$[*]' columns (question_id int path '$') ) as question_ids ``` ### [sqlpage.path()](/functions.sql?function=path) Returns the request path of the current page. This is useful to generate links to the current page, and when you have a proxy in front of your SQLPage server that rewrites the URL. ##### Example If we have a page in a file named `my page.sql` at the root of your SQLPage installation then the following SQL query: ```sql select 'text' as component, sqlpage.path() as contents; ``` will return `/my%20page.sql`. > Note that the path is URL-encoded. ### [sqlpage.uploaded_file_path()](/functions.sql?function=uploaded_file_path) Returns the path to a temporary file containing the contents of an uploaded file. #### Example: handling a picture upload ##### Making a form ```sql select 'form' as component, 'handle_picture_upload.sql' as action; select 'myfile' as name, 'file' as type, 'Picture' as label; select 'title' as name, 'text' as type, 'Title' as label; ``` ##### Handling the form response ##### Inserting an image file as a [data URL](https://en.wikipedia.org/wiki/Data_URI_scheme) into the database In `handle_picture_upload.sql`, one can process the form results like this: ```sql insert into pictures (title, path) values (:title, sqlpage.read_file_as_data_url(sqlpage.uploaded_file_path('myfile'))); ``` > *Note*: Data URLs are larger than the original file, so it is not recommended to use them for large files. ##### Inserting file contents as text into the database When the uploaded file is a simple raw text file (e.g. a `.txt` file), one can use the [`sqlpage.read_file_as_text`](?function=read_file_as_text#function) function to insert the contents of the file into the database like this: ```sql insert into text_documents (title, path) values (:title, sqlpage.read_file_as_text(sqlpage.uploaded_file_path('my_text_file'))); ``` ##### Saving the uploaded file to a permanent location When the uploaded file is larger than a few megabytes, it is not recommended to store it in the database. Instead, one can save the file to a permanent location on the server, and store the path to the file in the database. You can move the file to a permanent location using the [`sqlpage.persist_uploaded_file`](?function=persist_uploaded_file#function) function. ##### Advanced file handling For more advanced file handling, such as uploading files to a cloud storage service, you can write a small script in your favorite programming language, and call it using the [`sqlpage.exec`](?function=exec#function) function. For instance, one could save the following small bash script to `/usr/local/bin/upload_to_s3`: ```bash ###!/bin/bash aws s3 cp "$1" s3://your-s3-bucket-name/ echo "https://your-s3-bucket-url/$(basename "$1")" ``` Then, you can call it from SQL like this: ```sql set url = sqlpage.exec('upload_to_s3', sqlpage.uploaded_file_path('myfile')); insert into uploaded_files (title, path) values (:title, $url); ``` ### [sqlpage.uploaded_file_mime_type()](/functions.sql?function=uploaded_file_mime_type) Returns the MIME type of an uploaded file. #### Example: handling a picture upload When letting the user upload a picture, you may want to check that the uploaded file is indeed an image. ```sql select 'redirect' as component, 'invalid_file.sql' as link where sqlpage.uploaded_file_mime_type('myfile') not like 'image/%'; ``` In `invalid_file.sql`, you can display an error message to the user: ```sql select 'alert' as component, 'Error' as title, 'Invalid file type' as description, 'alert-circle' as icon, 'red' as color; ``` #### Example: white-listing file types You could have a database table containing the allowed MIME types, and check that the uploaded file is of one of those types: ```sql select 'redirect' as component, 'invalid_file.sql' as link where sqlpage.uploaded_file_mime_type('myfile') not in (select mime_type from allowed_mime_types); ``` ### [sqlpage.uploaded_file_name()](/functions.sql?function=uploaded_file_name) Returns the `filename` value in the `content-disposition` header. #### Example: saving uploaded file metadata for later download ##### Making a form ```sql select 'form' as component, 'handle_file_upload.sql' as action; select 'myfile' as name, 'file' as type, 'File' as label; ``` ##### Handling the form response ##### Inserting an arbitrary file as a [data URL](https://en.wikipedia.org/wiki/Data_URI_scheme) into the database In `handle_file_upload.sql`, one can process the form results like this: ```sql insert into uploaded_files (fname, content, uploaded) values ( sqlpage.uploaded_file_name('myfile'), sqlpage.read_file_as_data_url(sqlpage.uploaded_file_path('myfile')), CURRENT_TIMESTAMP ); ``` > *Note*: Data URLs are larger than the original file, so it is not recommended to use them for large files. ##### Downloading the uploaded files The file can be downloaded by clicking a link like this: ```sql select 'button' as component; select name as title, content as link from uploaded_files where name = $file_name limit 1; ``` > *Note*: because the file is ecoded as a data uri, the file is transferred to the client whether or not the link is clicked ##### Large files See the [`sqlpage.uploaded_file_path`](?function=uploaded_file_path#function) function. See the [`sqlpage.persist_uploaded_file`](?function=persist_uploaded_file#function) function. ### [sqlpage.read_file_as_data_url()](/functions.sql?function=read_file_as_data_url) Returns a [data URL](https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/Data_URIs) containing the contents of the given file. The file path is relative to the `web root` directory, which is the directory from which your website is served. By default, this is the directory SQLPage is launched from, but you can change it with the `web_root` [configuration option](https://github.com/sqlpage/SQLPage/blob/main/configuration.md). If the given argument is null, the function will return null. As with other functions, if an error occurs during execution (because the file does not exist, for instance), the function will display an error message and the database query will not be executed. If you are using a `sqlpage_files` table to store files directly in the database (serverless mode), the function will attempt to read the file from the database filesystem if it is not found on the local disk, using the same logic as for serving files in response to HTTP requests. #### MIME type Data URLs contain the [MIME type](https://en.wikipedia.org/wiki/Media_type) of the file they represent. If the first argument to this function is the result of a call to the `sqlpage.uploaded_file_path` function, the declared MIME type of the uploaded file transmitted by the browser will be used. Otherwise, the MIME type will be guessed from the file extension, without looking at the file contents. #### Example: inlining a picture ```sql select 'card' as component; select 'Picture' as title, sqlpage.read_file_as_data_url('/path/to/picture.jpg') as top_image; ``` > **Note:** Data URLs are larger than the original file they represent, so they should only be used for small files > (under a few hundred kilobytes). > Otherwise, the page will take a long time to load. ### [sqlpage.read_file_as_text()](/functions.sql?function=read_file_as_text) Returns a string containing the contents of the given file. The file must be a raw text file using UTF-8 encoding. The file path is relative to the `web root` directory, which is the directory from which your website is served (not necessarily the directory SQLPage is launched from). If the given argument is null, the function will return null. As with other functions, if an error occurs during execution (because the file does not exist, for instance), the function will display an error message and the database query will not be executed. If you are using a `sqlpage_files` table to store files directly in the database (serverless mode), the function will attempt to read the file from the database filesystem if it is not found on the local disk, using the same logic as for serving files in response to HTTP requests. #### Example ##### Rendering a markdown file ```sql select 'text' as component, sqlpage.read_file_as_text('/path/to/file.md') as contents_md; ``` ### [sqlpage.protocol()](/functions.sql?function=protocol) Returns the protocol that was used to access the current page. This can be either `http` or `https`. This is useful to generate links to the current page. ##### Example ```sql select 'text' as component, sqlpage.protocol() || '://' || sqlpage.header('host') || sqlpage.path() as contents; ``` will return `https://example.com/example.sql`. > Note that the path is URL-encoded. The protocol is resolved in this order: > - `Forwarded` header > - `X-Forwarded-Proto` header > request target / URI ### [sqlpage.run_sql()](/functions.sql?function=run_sql) Executes another SQL file and returns its result as a JSON array. ##### Example ###### Include a common header in all your pages It is common to want to run the same SQL queries at the beginning of all your pages, to check if an user is logged in, render a header, etc. You can create a file called `common_header.sql`, and use the [`dynamic`](documentation.sql?component=dynamic#component) component with the `run_sql` function to include it in all your pages. ```sql select 'dynamic' as component, sqlpage.run_sql('common_header.sql') as properties; ``` ###### Factorize logic between pages Reuse a sqlpage query in multiple pages without duplicating code by storing the results of `run_sql` to variables: ####### `reusable.sql` ```sql select some_field from some_table; ``` ####### `index.sql` ```sql -- save the value of some_field from the first result row of reusable.sql into $my_var set my_var = sqlpage.run_sql('reusable.sql')->>0->>'some_field'; ``` See [json in SQL](/blog.sql?post=JSON%20in%20SQL%3A%20A%20Comprehensive%20Guide) for help with manipulating the json array returned by `run_sql`. ###### Notes - **recursion**: you can use `run_sql` to include a file that itself includes another file, and so on. However, be careful to avoid infinite loops. SQLPage will throw an error if the inclusion depth is superior to `max_recursion_depth` (10 by default). - **security**: be careful when using `run_sql` to include files. - Never use `run_sql` with a user-provided parameter. - Never run a file uploaded by a user, or a file that is not under your control. - Remember that users can also run the files you include with `sqlpage.run_sql(...)` directly just by loading the file in the browser. - Make sure this does not allow users to bypass security measures you put in place such as [access control](/component.sql?component=authentication). - If you need to include a file, but make it inaccessible to users, you can use hidden files and folders (starting with a `.`), or put files in the special `sqlpage/` folder that is not accessible to users. - **variables**: the included file will have access to the same variables (URL parameters, POST variables, etc.) as the calling file. If the included file changes the value of a variable or creates a new variable, the change will not be visible in the calling file. - **per-row execution**: when `sqlpage.run_sql(...)` is a standalone selected column in a query that returns several rows, the included file runs once per returned row. Use `SET included = sqlpage.run_sql(...)` first if the included file should run only once for the page. ##### Parameters You can pass parameters to the included file, as if it had been with a URL parameter. For instance, you can use: ```sql sqlpage.run_sql('included_file.sql', json_object('param1', 'value1', 'param2', 'value2')) ``` Which will make `$param1` and `$param2` available in the included file. [More information about building JSON objects in SQL](/blog.sql?post=JSON%20in%20SQL%3A%20A%20Comprehensive%20Guide). ### [sqlpage.persist_uploaded_file()](/functions.sql?function=persist_uploaded_file) Persists an uploaded file to the local filesystem, and returns its path. If the file input field is empty, the function returns NULL. ##### Example ###### User profile picture ####### `upload_form.sql` ```sql select 'form' as component, 'persist_uploaded_file.sql' as action; select 'file' as type, 'profile_picture' as name, 'Upload your profile picture' as label; ``` ####### `persist_uploaded_file.sql` ```sql update user set profile_picture = sqlpage.persist_uploaded_file('profile_picture', 'profile_pictures', 'jpg,jpeg,png,gif,webp') where id = ( select user_id from session where session_id = sqlpage.cookie('session_id') ); ``` ### [sqlpage.fetch()](/functions.sql?function=fetch) Sends an HTTP request and returns the results as a string. ##### Example ###### Simple GET query In this example, we use an API call to find the latitude and longitude of a place the user searched for, and we display it on a map. We use the simplest form of the fetch function, that takes the URL to fetch as a string. ```sql set url = 'https://nominatim.openstreetmap.org/search?format=json&q=' || sqlpage.url_encode($user_search) set api_results = sqlpage.fetch($url); select 'map' as component; select $user_search as title, CAST($api_results->>0->>'lat' AS FLOAT) as latitude, CAST($api_results->>0->>'lon' AS FLOAT) as longitude; ``` ###### POST query with a body In this example, we use the complex form of the function to make an authenticated POST request, with custom request headers and a custom request body. We use SQLite's json functions to build the request body. See [the list of SQL databases and their JSON functions](/blog.sql?post=JSON%20in%20SQL%3A%20A%20Comprehensive%20Guide) for more information on how to build JSON objects in your database. ```sql set request = json_object( 'method', 'POST', 'url', 'https://postman-echo.com/post', 'headers', json_object( 'Content-Type', 'application/json', 'Authorization', 'Bearer ' || sqlpage.environment_variable('MY_API_TOKEN') ), 'body', json_object( 'Hello', 'world' ) ); set api_results = sqlpage.fetch($request); select 'code' as component; select 'API call results' as title, 'json' as language, $api_results as contents; ``` ###### Authenticated request using Basic Auth Here's how to make a request to an API that requires [HTTP Basic Authentication](https://en.wikipedia.org/wiki/Basic_access_authentication): ```sql set request = json_object( 'url', 'https://api.example.com/data', 'username', 'my_username', 'password', 'my_password' ); set api_results = sqlpage.fetch($request); ``` > This will add the `Authorization: Basic bXlfdXNlcm5hbWU6bXlfcGFzc3dvcmQK` header to the request, > where `bXlfdXNlcm5hbWU6bXlfcGFzc3dvcmQK` is the base64 encoding of the string `my_username:my_password`. ### JSON parameter format The fetch function accepts either a URL string, or a JSON object with the following parameters: - `url`: The URL to fetch. Required. - `method`: The HTTP method to use. Defaults to `GET`. - `headers`: A JSON object with the headers to send. Defaults to sending a User-Agent header containing the SQLPage version. - `body`: The body of the request. If it is a JSON object, it will be sent as JSON. If it is a string, it will be sent as is. When omitted, no request body is sent. - `timeout_ms`: The maximum time to wait for the request, in milliseconds. Defaults to 5000. - `username`: Optional username for HTTP Basic Authentication. Introduced in version 0.33.0. - `password`: Optional password for HTTP Basic Authentication. Only used if username is provided. Introduced in version 0.33.0. - `response_encoding`: Optional charset to use for decoding the response body. Defaults to `utf8`, or `base64` if the response contains binary data. All [standard web encodings](https://encoding.spec.whatwg.org/#concept-encoding-get) are supported, plus `hex`, `base64`, and `base64url`. Introduced in version 0.37.0. ### Error handling and reading response headers If the request fails, this function throws an error, that will be displayed to the user. The response headers are not available for inspection. #### Conditional data fetching Since v0.40, `sqlpage.fetch(null)` returns null instead of throwing an error. This makes it easier to conditionnally query an API: ```sql set current_field_value = (select field from my_table where id = 1); set target_url = nullif('http://example.com/api/field/1', null); -- null if the field is currently null in the db set api_value = sqlpage.fetch($target_url); -- no http request made if the field is not null in the db update my_table set field = $api_value where id = 1 and $api_value is not null; -- update the field only if it was not present before ``` You can also make the HTTP request depend on whether a database query returns a row. When `fetch` is a standalone selected column, SQLPage runs it only for rows returned by the database: ```sql set api_value = ( select sqlpage.fetch(url) from cache_misses where key = $key ); ``` If `cache_misses` has no matching row, no HTTP request is made and `$api_value` is set to `NULL`. If the query returns more than one row or more than one column, SQLPage returns a clear scalar `SET` error. #### Advanced usage If you need to handle errors or inspect the response headers or the status code, use [`sqlpage.fetch_with_meta`](?function=fetch_with_meta). ### [sqlpage.request_method()](/functions.sql?function=request_method) Returns the HTTP request method (GET, POST, etc.) used to access the page. ### HTTP request methods HTTP request methods (also known as verbs) are used to indicate the desired action to be performed on the identified resource. The most common methods are: - **GET**: retrieve information from the server. This is the default method used by browsers when you click on a link. - **POST**: submit data to be processed by the server. This is the default method used by browsers when you submit a form. - **PUT**: replace the current representation of the target resource with the request payload. Most commonly used in REST APIs. - **DELETE**: remove the target resource. - **PATCH**, **HEAD**, **OPTIONS**, **CONNECT**, **TRACE**: less common methods that are used in specific situations. ### Example ```sql select 'redirect' as component, '/error?msg=expected+a+PUT+request' as link, where sqlpage.request_method() != 'PUT'; insert into my_table (column1, column2) values (:value1, :value2); ``` ### [sqlpage.link()](/functions.sql?function=link) Returns the URL of a SQLPage file with the given parameters. ##### Example Let's say you have a database of products, and you want the main page (`index.sql`) to link to the page of each product (`product.sql`) with the product name as a parameter. In `index.sql`, you can use the `link` function to generate the URL of the product page for each product. ```sql select 'list' as component; select name as title, sqlpage.link('product', json_object('product_name', name)) as link from products; ``` In `product.sql`, you can then use `$product_name` to get the name of the product from the URL parameter: ```sql select 'hero' as component, $product_name as title, product_info as description from products where name = $product_name; ``` > You could also have manually constructed the URL with `CONCAT('product?product_name=', name)`, > but using `sqlpage.link` is better because it ensures that the URL is properly encoded. > `sqlpage.link` will work even if the product name contains special characters like `&`, while `CONCAT(...)` would break the URL. ##### Parameters - `file` (TEXT): The name of the SQLPage file to link to. - `parameters` (JSON): The parameters to pass to the linked file. - `fragment` (TEXT): An optional fragment (hash) to append to the URL. This is useful for linking to a specific section of a page. For instance if `product.sql` contains `select 'text' as component, 'product_description' as id;`, you can link to the product description section with `sqlpage.link('product.sql', json_object('product_name', name), 'product_description')`. ### [sqlpage.request_body()](/functions.sql?function=request_body) Returns the raw request body as a string. A client (like a web browser, mobile app, or another server) can send information to your server in the request body. This function allows you to read that information in your SQL code, in order to create or update a resource in your database for instance. The request body is commonly used when building **REST APIs** (machines-to-machines interfaces) that receive data from the client. This is especially useful in: - `POST` and `PUT` requests for creating or updating resources in your database - Any API endpoint that needs to receive complex data ##### Example: Building a REST API Here's an example of building an API endpoint that receives a json object, and inserts it into a database. ###### `api/create_user.sql` ```sql -- Get the raw JSON body set user_data = sqlpage.request_body(); -- Insert the user into database with parsed_data as ( select json_extract($user_data, '$.name') as name, json_extract($user_data, '$.email') as email ) insert into users (name, email) select name, email from parsed_data; -- Return success response select 'json' as component, json_object( 'status', 'success', 'message', 'User created successfully' ) as contents; ``` ##### Testing the API You can test this API using curl: ```bash curl -X POST http://localhost:8080/api/create_user \ -H "Content-Type: application/json" \ -d '{"name": "John", "email": "john@example.com"}' ``` #### Special cases ##### NULL This function returns NULL if: - There is no request body - The request content type is `application/x-www-form-urlencoded` or `multipart/form-data` (in these cases, use [`sqlpage.variables('post')`](?function=variables) instead) ##### Binary data If the request body is not valid text encoded in UTF-8, invalid characters are replaced with the Unicode replacement character `�` (U+FFFD). If you need to handle binary data, use [`sqlpage.request_body_base64()`](?function=request_body_base64) instead. ### [sqlpage.request_body_base64()](/functions.sql?function=request_body_base64) Returns the raw request body encoded in base64. This is useful when receiving binary data or when you need to handle non-text content in your API endpoints. ##### What is Base64? Base64 is a way to encode binary data (like images or files) into text that can be safely stored and transmitted. This function automatically converts the incoming request body into this format. ##### Example: Handling Binary Data in an API This example shows how to receive and process an image uploaded directly in the request body: ```sql -- Assuming this is api/upload_image.sql -- Client would send a POST request with the raw image data -- Get the base64-encoded image data set image_data = sqlpage.request_body_base64(); -- Store the image data in the database insert into images (data, uploaded_at) values ($image_data, current_timestamp); -- Return success response select 'json' as component, json_object( 'status', 'success', 'message', 'Image uploaded successfully' ) as contents; ``` You can test this API using curl: ```bash curl -X POST http://localhost:8080/api/upload_image.sql \ -H "Content-Type: application/octet-stream" \ --data-binary "@/path/to/image.jpg" ``` This is particularly useful when: - Working with binary data (images, files, etc.) - The request body contains non-UTF8 characters - You need to pass the raw body to another system that expects base64 > Note: Like [`sqlpage.request_body()`](?function=request_body), this function returns NULL if: > - There is no request body > - The request content type is `application/x-www-form-urlencoded` or `multipart/form-data` > (in these cases, use [`sqlpage.variables('post')`](?function=variables) instead) ### [sqlpage.headers()](/functions.sql?function=headers) Returns all HTTP request headers as a JSON object. ##### Example The following displays all HTTP request headers in a list, using SQLite's `json_each()` function. ```sql select 'list' as component; select key as title, value as description from json_each(sqlpage.headers()); -- json_each() is SQLite only ``` If not on SQLite, use your [database's JSON function](/blog.sql?post=JSON%20in%20SQL%3A%20A%20Comprehensive%20Guide). ##### Details The function returns a JSON object where: - Keys are lowercase header names - Values are the corresponding header values - If no headers are present, returns an empty JSON object `{}` This is useful when you need to: - Debug HTTP requests - Access multiple headers at once If you only need access to a single known header, use [`sqlpage.header(name)`](?function=header) instead. ### [sqlpage.client_ip()](/functions.sql?function=client_ip) Returns the IP address of the client making the HTTP request. ##### Example ```sql insert into connection_log (client_ip) values (sqlpage.client_ip()); ``` ##### Details The function returns: - The IP address of the client as a string - `null` if the client IP cannot be determined (e.g., when serving through a Unix socket) ##### ⚠️ Important Notes for Production Use When [running behind a reverse proxy](/your-first-sql-website/nginx.sql) (e.g., Nginx, Apache, Cloudflare): - This function will return the IP address of the reverse proxy, not the actual client - To get the real client IP, use [`sqlpage.header`](?function=header): `sqlpage.header('x-forwarded-for')` or `sqlpage.header('x-real-ip')` - The exact header name depends on your reverse proxy configuration Example with reverse proxy: ```sql -- Choose the appropriate header based on your setup select coalesce( sqlpage.header('x-forwarded-for'), sqlpage.header('x-real-ip'), sqlpage.client_ip() ) as real_client_ip; ``` For security-critical applications, ensure your reverse proxy is properly configured to set and validate these headers. ### [sqlpage.fetch_with_meta()](/functions.sql?function=fetch_with_meta) Sends an HTTP request and returns detailed metadata about the response, including status code, headers, and body. This function is similar to [`fetch`](?function=fetch), but returns a JSON object containing detailed information about the response. The returned object has the following structure: ```json { "status": 200, "headers": { "content-type": "text/html", "content-length": "1234" }, "body": "a string, or a json object, depending on the content type", "error": "error message if any" } ``` If the request fails or encounters an error (e.g., network issues, invalid UTF-8 response), instead of throwing an error, the function returns a JSON object with an "error" field containing the error message. ##### Example: Basic Usage ```sql -- Make a request and get detailed response information set response = sqlpage.fetch_with_meta('https://pokeapi.co/api/v2/pokemon/ditto'); -- redirect the user to an error page if the request failed select 'redirect' as component, 'error.sql' as url where json_extract($response, '$.error') is not null or json_extract($response, '$.status') != 200; -- Extract data from the response json body select 'card' as component; select json_extract($response, '$.body.name') as title, json_extract($response, '$.body.abilities[0].ability.name') as description from $response; ``` ##### Example: Advanced Request with Authentication ```sql set request = json_object( 'method', 'POST', 'url', 'https://sqlpage.free.beeceptor.com', 'headers', json_object( 'Content-Type', 'application/json', 'Authorization', 'Bearer ' || sqlpage.environment_variable('API_TOKEN') ), 'body', json_object( 'key', 'value' ) ); set response = sqlpage.fetch_with_meta($request); -- Check response content type select 'debug' as component, $response as response; ``` The function accepts the same parameters as the [`fetch` function](?function=fetch). ### [sqlpage.user_info_token()](/functions.sql?function=user_info_token) # Accessing information about the current user, when logged in with SSO This function can be used only when you have [configured Single Sign-On with an OIDC provider](/sso). #### The ID Token When a user logs in through OIDC, your application receives an [identity token](https://openid.net/specs/openid-connect-core-1_0.html#IDToken) from the identity provider. This token contains information about the user, such as their name and email address. The `sqlpage.user_info_token()` function lets you access the entire contents of the ID token, as a JSON object. You can then use [your database's JSON functions](/blog.sql?post=JSON+in+SQL%3A+A+Comprehensive+Guide) to process that JSON. If you need to access a specific claim, it is easier and more performant to use the [`sqlpage.user_info()`](?function=user_info) function instead. ##### Example: Displaying User Information ```sql select 'list' as component; select key as title, value as description from json_each(sqlpage.user_info_token()); ``` This sqlite-specific example will show all the information available about the current user, such as: - `sub`: A unique identifier for the user - `name`: The user's full name - `email`: The user's email address - `picture`: A URL to the user's profile picture ##### Security Notes - The ID token is automatically verified by SQLPage to ensure it hasn't been tampered with. - The token is only available to authenticated users: if no user is logged in or sso is not configured, this function returns NULL - If some information is not available in the token, you have to configure it on your OIDC provider, SQLPage can't do anything about it. - The token is stored in a signed http-only cookie named `sqlpage_auth`. You can use [the cookie component](/component.sql?component=cookie) to delete it, and the user will be redirected to the login page on the next page load. ### [sqlpage.user_info()](/functions.sql?function=user_info) # Accessing Specific User Information The `sqlpage.user_info` function is a convenient way to access specific pieces of information about the currently logged-in user. When you [configure Single Sign-On](/sso), your OIDC provider will issue an [ID token](https://openid.net/specs/openid-connect-core-1_0.html#IDToken) for the user, which contains *claims*, with information about the user. Calling `sqlpage.user_info(claim_name)` lets you access these claims directly from SQL. #### How to Use The function takes one parameter: the name of the *claim* (the piece of information you want to retrieve). For example, to display a personalized welcome message, with the user's name, you can use: ```sql select 'text' as component; select 'Welcome, ' || sqlpage.user_info('name') || '!' as title; ``` #### Available Information The exact information available depends on your identity provider (the service you chose to authenticate with), its configuration, and the scopes you requested. Use [`sqlpage.user_info_token()`](?function=user_info_token) to see all the information available in the ID token of the current user. Here are some commonly available fields: ##### Basic Information - `name`: The user's full name (usually first and last name separated by a space) - `email`: The user's email address (*warning*: there is no guarantee that the user currently controls this email address. Use the `sub` claim for database references instead.) - `picture`: URL to the user's profile picture ##### User Identifiers - `sub`: A unique identifier for the user (use this to uniquely identify the user in your database) - `preferred_username`: The username the user prefers to use ##### Name Components - `given_name`: The user's first name - `family_name`: The user's last name #### Examples ##### Personalized Welcome Message ```sql select 'text' as component, 'Welcome back, **' || sqlpage.user_info('given_name') || '**!' as contents_md; ``` ##### User Profile Card ```sql select 'card' as component; select sqlpage.user_info('name') as title, sqlpage.user_info('email') as description, sqlpage.user_info('picture') as image; ``` ##### Conditional Content Based on custom claims Some identity providers let you add custom claims to the ID token. This lets you customize the behavior of your application based on arbitrary user attributes, such as the user's role. ```sql -- show everything to admins, only public items to others select 'list' as component; select title from my_items where is_public or sqlpage.user_info('role') = 'admin' ``` #### Security Best Practices > ⚠️ **Important**: Always use the `sub` claim to identify users in your database, not their email address. > The `sub` claim is guaranteed to be unique and stable for each user, while email addresses can change. > In most providers, receiving an id token with a given email does not guarantee that the user currently controls that email. ```sql -- Store the user's ID in your database insert into user_preferences (user_id, theme) values (sqlpage.user_info('sub'), 'dark'); ``` #### Troubleshooting If you're not getting the information you expect: 1. Check that OIDC is properly configured in your `sqlpage.json` 2. Verify that you requested the right scopes in your OIDC configuration 3. Try using `sqlpage.user_info_token()` to see all available information 4. Check your OIDC provider's documentation for the exact claim names they use Remember: If the user is not logged in or the requested information is not available, this function returns NULL. ### [sqlpage.oidc_logout_url()](/functions.sql?function=oidc_logout_url) # Secure OIDC Logout The `sqlpage.oidc_logout_url` function generates a secure logout URL for users authenticated via [OIDC Single Sign-On](/sso). When a user visits this URL, SQLPage will: 1. Remove the authentication cookie 2. Redirect the user to the OIDC provider's logout endpoint (if available) 3. Finally redirect back to the specified `redirect_uri` #### Security Features This function provides protection against **Cross-Site Request Forgery (CSRF)** attacks: - The generated URL contains a cryptographically signed token - The token includes a timestamp and expires after 10 minutes - The token is signed using your OIDC client secret - Only relative URLs (starting with `/`) are allowed as redirect targets This means that malicious websites cannot trick your users into logging out by simply including an image or link to your logout URL. #### How to Use ```sql select 'button' as component; select 'Logout' as title, sqlpage.oidc_logout_url('/') as link, 'logout' as icon, 'red' as outline; ``` This creates a logout button that, when clicked: 1. Logs the user out of your SQLPage application 2. Logs the user out of the OIDC provider (if the provider supports [RP-Initiated Logout](https://openid.net/specs/openid-connect-rpinitiated-1_0.html)) 3. Redirects the user back to your homepage (`/`) #### Examples ##### Logout Button in Navigation ```sql select 'shell' as component, 'My App' as title, json_array( json_object( 'title', 'Logout', 'link', sqlpage.oidc_logout_url('/'), 'icon', 'logout' ) ) as menu_item; ``` ##### Logout with Return to Current Page ```sql select 'button' as component; select 'Sign Out' as title, sqlpage.oidc_logout_url(sqlpage.path()) as link; ``` ##### Conditional Logout Link ```sql select 'button' as component where sqlpage.user_info('sub') is not null; select 'Logout ' || sqlpage.user_info('name') as title, sqlpage.oidc_logout_url('/') as link where sqlpage.user_info('sub') is not null; ``` #### Requirements - OIDC must be [configured](/sso) in your `sqlpage.json` - If OIDC is not configured, this function returns NULL - The `redirect_uri` must be a relative path starting with `/` #### Provider Support The logout behavior depends on your OIDC provider: | Provider | Full Logout Support | |----------|-------------------| | Keycloak | ✅ Yes | | Auth0 | ✅ Yes | | Google | ❌ No (local logout only) | | Azure AD | ✅ Yes | | Okta | ✅ Yes | When the provider doesn't support RP-Initiated Logout, SQLPage will still remove the local authentication cookie and redirect to your specified URI. ### [sqlpage.hmac()](/functions.sql?function=hmac) Creates a unique "signature" for some data using a secret key. This signature proves that the data hasn't been tampered with and comes from someone who knows the secret. ##### What is HMAC used for? [**HMAC**](https://en.wikipedia.org/wiki/HMAC) (Hash-based Message Authentication Code) is commonly used to: - **Verify webhooks**: Use HMAC to ensure only a given external service can call a given endpoint in your application. The service signs their request with a secret key, and you verify the signature before processing the data they sent you. Used for instance by [Stripe](https://docs.stripe.com/webhooks?verify=verify-manually), and [Shopify](https://shopify.dev/docs/apps/build/webhooks/subscribe/https#step-2-validate-the-origin-of-your-webhook-to-ensure-its-coming-from-shopify). - **Secure API requests**: Prove that an API request comes from an authorized source - **Generate secure tokens**: Create temporary access codes for downloads or password resets - **Protect data**: Ensure data hasn't been modified during transmission ##### How to use it The `sqlpage.hmac` function takes three inputs: 1. **Your data** - The text you want to sign (like a message or request body) 2. **Your secret key** - A password only you know (keep this safe!) 3. **Algorithm** (optional) - The hash algorithm and output format: - `sha256` (default) - SHA-256 with hexadecimal output - `sha256-base64` - SHA-256 with base64 output - `sha512` - SHA-512 with hexadecimal output - `sha512-base64` - SHA-512 with base64 output It returns a signature string. If someone changes even one letter in your data, the signature will be completely different. ##### Example: Verify a Webhooks signature When Shopify sends you a webhook (like when someone places an order), it includes a signature. Here's how to verify it's really from Shopify. This supposes you store the secret key in an [environment variable](https://en.wikipedia.org/wiki/Environment_variable) named `WEBHOOK_SECRET`. ```sql SET body = sqlpage.request_body(); SET secret = sqlpage.environment_variable('WEBHOOK_SECRET'); SET expected_signature = sqlpage.hmac($body, $secret, 'sha256'); SET actual_signature = sqlpage.header('X-Webhook-Signature'); -- redirect to an error page and stop execution if the signature does not match SELECT 'redirect' as component, '/error.sql?err=bad_webhook_signature' as link WHERE $actual_signature != $expected_signature OR $actual_signature IS NULL; -- If we reach here, the signature is valid - process the order INSERT INTO orders (order_data) VALUES ($body); SELECT 'json' as component, 'jsonlines' as type; SELECT 'success' as status; ``` ##### Example: Time-limited links You can create links that will be valid only for a limited time by including a signature in them. Let's say we have a `download.sql` page we want to link to, but we don't want it to be accessible to anyone who can find the link. Sign `file_id|expires_at` with a secret. Accept only if not expired and the signature matches. ###### Generate a signed link ```sql SET expires_at = datetime('now', '+1 hour'); SET token = sqlpage.hmac( $file_id || '|' || $expires_at, sqlpage.environment_variable('DOWNLOAD_SECRET'), 'sha256' ); SELECT '/download.sql?file_id=' || $file_id || '&expires_at=' || $expires_at || '&token=' || $token AS link; ``` ###### Verify the signed link ```sql SET expected = sqlpage.hmac( $file_id || '|' || $expires_at, sqlpage.environment_variable('DOWNLOAD_SECRET'), 'sha256' ); SELECT 'redirect' AS component, '/error.sql?err=expired' AS link WHERE $expected != $token OR $token IS NULL OR $expires_at < datetime('now'); -- serve the file ``` ##### Important Security Notes - **Keep your secret key safe**: If your secret leaks, anyone can forge signatures and access protected pages - **The signature is case-sensitive**: Even a single wrong letter means the signature won't match - **NULL handling**: Always use `IS DISTINCT FROM`, not `=` to check for hmac matches. - `SELECT 'redirect' as component WHERE sqlpage.hmac(...) != $signature` will not redirect if `$signature` is NULL (the signature is absent). - `SELECT 'redirect' as component WHERE sqlpage.hmac(...) IS DISTINCT FROM $signature` checks for both NULL and non-NULL values (but is not available in all SQL dialects). - `SELECT 'redirect' as component WHERE sqlpage.hmac(...) != $signature OR $signature IS NULL` is the most portable solution. ### [sqlpage.set_variable()](/functions.sql?function=set_variable) Returns a URL that is the same as the current page's URL, but with a variable set to a new value. This function is useful when you want to create a link that changes a parameter on the current page, while preserving other parameters. It is equivalent to `sqlpage.link(sqlpage.path(), json_patch(sqlpage.variables('get'), json_object(name, value)))`. ##### Example Let's say you have a list of products, and you want to filter them by category. You can use `sqlpage.set_variable` to create links that change the category filter, without losing other potential filters (like a search query or a sort order). ```sql select 'button' as component, 'sm' as size, 'center' as justify; select category as title, sqlpage.set_variable('category', category) as link, case when $category = category then 'primary' else 'secondary' end as color from categories; ``` When `sqlpage.set_variable(...)` is used as a standalone selected column in a query that returns several rows, it runs once per returned row. This is useful for generating one link per row, as in the example above. If you need a single link reused later in the page, store it with `SET` first. ##### Parameters - `name` (TEXT): The name of the variable to set. - `value` (TEXT): The value to set the variable to. If `NULL` is passed, the variable is removed from the URL. ### [sqlpage.regex_match()](/functions.sql?function=regex_match) Matches a text value against a regular expression and returns the capture groups as a JSON object. If the text matches the pattern, the result contains one entry for each capture group that matched: - key `0` contains the full match - named groups like `(?...)` use their name as the JSON key - unnamed groups like `( ... )` use their numeric index as a string If the text does not match, this function returns `NULL`. ##### Example: custom routing from `404.sql` This function is especially useful in a custom [`404.sql` page](/your-first-sql-website/custom_urls.sql), where you want to turn a dynamic URL into variables your SQL can use. For example, suppose you want `/categories/{category}/post/{id}` URLs such as `/categories/sql/post/42`, but there is no physical `categories/sql/post/42.sql` file on disk. You can put a `categories/404.sql` file in your project and extract the dynamic parts from the URL: ###### `categories/404.sql` ```sql set route = sqlpage.regex_match( '/categories/(?\w+)/post/(?\d+)', sqlpage.path() ); select 'redirect' as component, '/404' as link where $route is null; select 'text' as component; select 'Category: ' || ($route->>'category') || ' | Post id: ' || ($route->>'id') as contents; ``` If the current path is `/categories/sql/post/42`, `sqlpage.regex_match()` returns: ```json {"0":"/categories/sql/post/42","category":"sql","id":"42"} ``` You can then use those extracted values to query your database: ```sql select title, body from posts where category = $route->>'category' and id = cast($route->>'id' as integer); ``` ##### Details - Quick regex reminder: - `\w+` matches one or more "word" characters - `\d+` matches one or more digits - `(?...)` creates a named capture group - Some databases, such as MySQL and MariaDB, treat backslashes specially inside SQL strings. In those databases, you may need to write `\\w` and `\\d`, or use portable character classes such as `[A-Za-z0-9_]` and `[0-9]` instead. - In SQLite, PostgreSQL, and some other databases, you can read fields from the returned JSON with `->` and `->>` - On databases that do not support that syntax, use their JSON extraction function instead, such as `json_extract($route, '$.category')` - For the full regular expression syntax supported by SQLPage, see the Rust `regex` crate documentation: [regex syntax reference](https://docs.rs/regex/latest/regex/#syntax) - If the input text is `NULL`, the function returns `NULL` - If an optional capture group does not match, that key is omitted from the JSON object - If the regular expression is invalid, SQLPage returns an error The returned JSON can then be processed with your database's JSON functions. ### [sqlpage.send_mail()](/functions.sql?function=send_mail) Sends a plain-text email using the outgoing mail server configured in SQLPage. ##### Quick start You need an [SMTP server](https://en.wikipedia.org/wiki/Simple_Mail_Transfer_Protocol), which is the outgoing mail server provided by an email account or email delivery service. Add its connection details to `sqlpage/sqlpage.json`: ```json { "smtp_host": "smtp.example.com", "smtp_username": "your-smtp-user", "smtp_password": "your-smtp-password", "smtp_from": "My application " } ``` The default connection uses STARTTLS on port 587, which is the most common setup. Restart SQLPage after changing its configuration. **Important:** SQLPage can sign in with an SMTP username and password, but it does not support OAuth. If the provider instructions only offer OAuth or "Modern Auth", use a different SMTP relay. You can now send an email from any SQL file: ```sql set result = sqlpage.send_mail(json_object( 'to', 'alice@example.com', 'subject', 'Hello from SQLPage', 'body', 'Your first email is working!' )); ``` The sender comes from `smtp_from`. The result is a JSON object: ```json {"status":"accepted"} ``` If the message cannot be sent, the function returns the reason instead of stopping the request: ```json {"status":"error","error_code":"INVALID_EMAIL_TO","error":"'xxx' is not a valid to email address"} ``` For every non-`NULL` call, `status` is either `accepted` or `error`. Always check it before showing a success message or continuing work that depends on the email: ```sql select 'alert' as component, case when json_extract($result, '$.status') = 'accepted' then 'success' else 'danger' end as color, case when json_extract($result, '$.status') = 'accepted' then 'Email sent' else 'Email could not be sent' end as title, json_extract($result, '$.error') as description; ``` ##### Where to find the SMTP settings Search the help pages or administration panel of the service that sends email for you. Look for **SMTP**, **outgoing mail server**, **SMTP submission**, **SMTP relay**, or **send from an app or device**. Provider documentation may use different names for the same settings: | Provider documentation | SQLPage setting | | --- | --- | | SMTP server, outgoing server, relay, or smart host | `smtp_host` | | Port | `smtp_port` | | STARTTLS, SSL/TLS, or connection security | `smtp_tls_mode` | | SMTP username | `smtp_username` | | SMTP password, app password, token, or API key | `smtp_password` | | Sender or From address | `smtp_from` | The SMTP password is often a separate app password, token, or SMTP credential rather than the password used to open webmail. Use exactly what the provider instructions specify. For an existing mailbox, these official guides explain the available options: - [Personal Google Account app passwords](https://support.google.com/accounts/answer/185833), for eligible accounts - [Google Workspace: send email from a printer, scanner, or app](https://knowledge.workspace.google.com/admin/gmail/send-email-from-a-printer-scanner-or-app) - [Microsoft 365: send email from a device or application](https://learn.microsoft.com/en-us/exchange/mail-flow-best-practices/how-to-set-up-a-multifunction-device-or-application-to-send-email-using-microsoft-365-or-office-365) Personal Outlook.com SMTP requires OAuth and is therefore not currently compatible. Microsoft 365 administrators can use the relay options described in the linked organization guide. Dedicated email delivery services also provide SMTP settings. Here are examples in alphabetical order: - [Amazon SES SMTP credentials](https://docs.aws.amazon.com/ses/latest/dg/smtp-credentials.html) - [Mailgun SMTP](https://documentation.mailgun.com/docs/mailgun/user-manual/sending-messages/send-smtp) - [Postmark SMTP](https://postmarkapp.com/developer/user-guide/send-email-with-smtp) - [Resend SMTP](https://resend.com/docs/send-with-smtp) - [Twilio SendGrid SMTP](https://www.twilio.com/docs/sendgrid/for-developers/sending-email/integrating-with-the-smtp-api) These links are examples, not endorsements. SQLPage is not affiliated with any of these services. Compare their requirements, limits, and pricing for your own use case. For local development, [Mailpit](https://mailpit.axllent.org/) accepts messages and displays them in a browser without delivering them to real recipients. The [SQLPage email example](https://github.com/sqlpage/SQLPage/tree/main/examples/sending%20emails) includes a ready-to-run Mailpit setup. All SMTP options can also be set with uppercase environment variables such as `SMTP_HOST` and `SMTP_PASSWORD`. See the complete [SQLPage configuration reference](https://github.com/sqlpage/SQLPage/blob/main/configuration.md). Do not commit SMTP credentials to source control. ##### Message fields The function takes one JSON object. Every message requires `to`, `subject`, and message content. Message content can be supplied in one of these ways: - `body`: the plain-text email body. This is required unless `body_md` is provided. - `body_html`: an optional HTML alternative to `body`. It can be combined with `body`, but not with `body_md`. - `body_md`: a Markdown body rendered to HTML automatically. It can be used on its own or combined with `body`; when `body` is omitted, the raw Markdown is used as the plain-text alternative. It cannot be combined with `body_html`. The supported content combinations are: | Fields | Result | | --- | --- | | `body` | Plain-text email | | `body`, `body_html` | Plain-text and HTML alternatives | | `body_md` | Raw Markdown and rendered HTML alternatives | | `body`, `body_md` | `body` as the plain-text alternative and rendered Markdown as HTML | At least one of `body` or `body_md` is required. `body_html` by itself is not enough because every HTML email must also have a plain-text alternative. It also accepts: - `from`: overrides `smtp_from` for this message; - `reply_to`: the address that receives replies; - `cc`: a recipient who receives a visible copy; - `attachments`: files to include with the message. `to` and `cc` can each be either one address or an array of addresses. Addresses can include a display name, for example `"Jane Doe "`. Most SMTP servers only allow approved sender addresses. Prefer a fixed `smtp_from`. Override `from` only when the SMTP provider allows the address. ##### Multiple recipients and attachments Each attachment has a file name and a [data URL](https://developer.mozilla.org/en-US/docs/Web/URI/Schemes/data) containing its data. This example attaches a file from the SQLPage server: ```sql set result = sqlpage.send_mail(json_object( 'to', json_array('alice@example.com', 'bob@example.com'), 'cc', 'team@example.com', 'subject', 'Monthly report', 'body', 'The report is attached.', 'attachments', json_array(json_object( 'filename', 'report.pdf', 'data_url', sqlpage.read_file_as_data_url('report.pdf') )) )); ``` [`sqlpage.read_file_as_data_url`](/functions.sql?function=read_file_as_data_url) is one way to create attachment data. Data URLs can also come from an uploaded file, a database value, an HTTP response, or SQL. The combined decoded size of all attachments is limited by `max_email_attachment_size`, which defaults to 10 MiB. This is separate from `max_uploaded_file_size` because attachments do not have to come from form uploads. ##### HTML email Set `body_html` to send an HTML version of the message alongside the plain-text `body`. The message is sent as a `multipart/alternative`: mail clients that prefer HTML show the HTML body, and clients that prefer text show the plain-text body. ```sql set result = sqlpage.send_mail(json_object( 'to', 'alice@example.com', 'subject', 'Welcome', 'body', 'Welcome to our service.', 'body_html', '

Welcome to our service.

' )); ``` Include a meaningful plain-text alternative for deliverability and accessibility. SQLPage does not sanitize `body_html`: the SQL author is responsible for the HTML content. Email clients ignore scripts, and styles are often stripped or sandboxed. ##### Markdown email Set `body_md` to send a [Markdown](https://daringfireball.net/projects/markdown/) version of the body. SQLPage renders the Markdown to HTML and sends the message as a `multipart/alternative` with two parts: the raw Markdown as the plain-text body, and the rendered HTML as the HTML body. When `body_md` is provided, `body` becomes optional. ```sql set result = sqlpage.send_mail(json_object( 'to', 'alice@example.com', 'subject', 'Welcome', 'body_md', '# Welcome\n\nWelcome to **our service**.' )); ``` When both `body` and `body_md` are provided, `body` is used as the plain-text alternative and the rendered `body_md` is used as the HTML alternative. `body_md` cannot be combined with `body_html`; use one or the other. SQLPage renders Markdown using the same [GFM](https://github.github.com/gfm/) options as the `markdown` template helper, honoring the `markdown_allow_dangerous_html` and `markdown_allow_dangerous_protocol` configuration options. ##### Contact form ```sql select 'form' as component, 'post' as method; select 'email' as name, 'email' as type, true as required; select 'message' as name, 'textarea' as type, true as required; set mail = json_object( 'to', 'admin@example.com', 'reply_to', :email, 'subject', 'Website contact form', 'body', :message ); set result = ( select sqlpage.send_mail($mail) where :message is not null ); select 'alert' as component, case when json_extract($result, '$.status') = 'accepted' then 'success' else 'danger' end as color, case when json_extract($result, '$.status') = 'accepted' then 'Message sent' else 'Message could not be sent' end as title, json_extract($result, '$.error') as description where :message is not null; ``` On the initial page load, `:message` is `NULL`, so the query returns no row and no email is sent. After submission, `:email` and `:message` contain the form fields. For a public form, keep `to` fixed in SQL so visitors cannot use your server to email arbitrary recipients. Validate inputs and add suitable rate limiting and anti-abuse controls. ##### Before using this in production - A `status` of `accepted` is not proof of delivery. A message can still bounce or be filtered later. Check the provider logs or delivery webhooks when delivery status matters. - The provider may require sender or domain verification and DNS records such as SPF, DKIM, or DMARC. Configure these with the provider and DNS host. - The function waits for the SMTP server during the web request. It opens a new connection for each call and does not retry automatically or save failed messages in a queue. - SMTP commands use a fixed 60-second timeout. SQLPage does not currently provide a setting to change it. - A selected call runs once for every row returned by its query. If the query returns no rows, it sends no email. Avoid using it over many rows; use a background queue or provider bulk API for bulk sending. ##### Connection options `smtp_host` must contain only a host name or IP address. Do not include `smtp://`, `https://`, a path, or a port. Choose `smtp_tls_mode` according to the provider instructions: - `starttls` (default) requires a STARTTLS upgrade before authentication or message submission. Its default port is 587. SQLPage fails rather than continuing without encryption when STARTTLS is unavailable. - `tls` encrypts the connection from the beginning. Its default port is 465. Providers may call this implicit TLS, SSL/TLS, or SMTPS. - `none` sends the message without encryption. Its default port is 25. It cannot be used with a username and password and is intended only for a trusted local server such as Mailpit. Set `smtp_port` when the provider specifies another port, such as 2525. `smtp_username` and `smtp_password` must either both be configured or both be omitted. SQLPage normally validates TLS certificates using public web PKI roots. Enable `system_root_ca_certificates`, or set `SSL_CERT_FILE` or `SSL_CERT_DIR`, to use roots installed by the system administrator, including private roots. ##### Supported and unsupported features SQLPage supports unauthenticated SMTP servers and username/password authentication using the SMTP `PLAIN` and `LOGIN` mechanisms. Credentials are allowed only over an encrypted connection. SQLPage does not currently support: - OAuth or XOAUTH2 authentication. If a provider only allows OAuth, it is not compatible with this function; - CRAM-MD5, DIGEST-MD5, client-certificate authentication, or a per-server custom CA file; - opportunistic STARTTLS, direct delivery to recipient mail servers, or receiving email; - BCC, multiple reply-to addresses, or custom email headers; - provider-specific headers for templates, tags, tracking, scheduling, idempotency, or metadata; - DKIM signing inside SQLPage, S/MIME, or end-to-end encryption. The SMTP provider may add DKIM signatures; - connection pooling, automatic retries, a persistent queue, scheduled sending, or a bulk-send API; - a configurable SMTP command timeout or EHLO client identity; - delivery receipts, bounce processing, suppression lists, open or click tracking, or webhooks. ##### `NULL`, empty values, and invalid input - SQL `NULL` is passed through: `sqlpage.send_mail(NULL)` returns SQL `NULL`, sends nothing, and does not log a warning. - A JSON value other than an object and unknown or invalid message fields produce a JSON result with `status`, `error_code`, and `error` fields. - `to` and `subject` are required and cannot be JSON `null`. At least one of `body` or `body_md` must be provided. - `from`, `reply_to`, `cc`, `body`, `body_html`, and `body_md` treat JSON `null` like an omitted field. If `from` is omitted, `smtp_from` must be configured. If `body` and `body_md` are both omitted, the message is rejected for missing content. When `body_html` and `body_md` are both omitted, the message is plain text only. - `body_md` cannot be combined with `body_html`. - `attachments` can be omitted or an empty array. JSON `null` is not accepted for `attachments`. - Empty recipient arrays, JSON `null` inside recipient arrays, invalid addresses, an empty attachment file name, invalid data URLs, and unknown attachment fields produce an error result with `status`, `error_code`, and `error`. - Empty strings are allowed for `subject` and `body`, although an SMTP server may reject them. ##### Error codes `error_code` is a stable, machine-readable value. `error` is the corresponding human-readable detail. | `error_code` | Meaning | | --- | --- | | `INVALID_MESSAGE` | The argument is not a valid message object, a required field is missing, or the message cannot be constructed. | | `SMTP_NOT_CONFIGURED` | `smtp_host` is not configured. | | `MISSING_EMAIL_FROM` | Neither the message nor `smtp_from` provides a sender. | | `INVALID_EMAIL_FROM` | `from` is not a valid email address. | | `INVALID_EMAIL_TO` | `to` is empty or contains an invalid email address. | | `INVALID_EMAIL_CC` | `cc` is empty or contains an invalid email address. | | `INVALID_EMAIL_REPLY_TO` | `reply_to` is not a valid email address. | | `INVALID_ATTACHMENT` | An attachment has an invalid name, data URL, media type, or exceeds the configured size limit. | | `SMTP_TLS_FAILED` | TLS certificates could not be configured or the encrypted connection failed. | | `SMTP_TIMEOUT` | The SMTP operation timed out. | | `SMTP_REJECTED` | The SMTP server returned a temporary or permanent rejection, including authentication failures. | | `SMTP_CONNECTION_FAILED` | SQLPage could not connect to or communicate with the SMTP server. | ## Examples - [Authentication example](https://github.com/sqlpage/SQLPage/tree/main/examples/user-authentication): Complete user registration and login system - [CRUD application](https://github.com/sqlpage/SQLPage/tree/main/examples/CRUD%20-%20Authentication): Create, read, update, delete with authentication - [Image gallery](https://github.com/sqlpage/SQLPage/tree/main/examples/image%20gallery%20with%20user%20uploads): File upload and image display - [Todo application](https://github.com/sqlpage/SQLPage/tree/main/examples/todo%20application): Simple CRUD app - [Master-detail forms](https://github.com/sqlpage/SQLPage/tree/main/examples/master-detail-forms): Working with related data - [Charts example](https://github.com/sqlpage/SQLPage/tree/main/examples/plots%20tables%20and%20forms): Data visualization ## Optional - [Custom components guide](/custom_components.sql): Create your own handlebars components - [Safety and security](/safety.sql): Understanding SQL injection prevention - [Docker deployment](https://github.com/sqlpage/SQLPage#with-docker): Running SQLPage in containers - [Systemd service](https://github.com/sqlpage/SQLPage/blob/main/sqlpage.service): Production deployment setup - [Repository structure](https://github.com/sqlpage/SQLPage/blob/main/CONTRIBUTING.md): Project organization and contribution guide