SQLPage v0.37.0 documentation

A visually distinctive message or notification.

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 - 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.

A component to display key metrics or statistics with optional description, change indicator, and progress bar. Useful in dashboards.

A secondary navigation aid that helps users understand their location on a website or mobile application.

A versatile button component do display one or multiple button links of different styles.

A grid where each element is a small card that displays a piece of data.

A carousel is used to display images. When used with multiple images, it will cycle through them automatically or with controls, creating a slideshow.

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.

Displays one or many blocks of code from a programming language or formated text as XML or JSON.

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.

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.

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.

Display small pieces of information in a clear and readable way. Each item has a name and is associated with a value.

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.

Dividers help organize content and make the interface layout clear and uncluttered.

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.

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

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.

A foldable list of elements which can be expanded individually.

# Building forms in SQL So, you have an SQL database, and would like to let users input data into it? The `form` component is what you are looking for. ## Collecting data from users to your database 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 User-entered data is posted to an SQL file, that will handle the data, and will be able to insert it into the database, search for it, format it, etc. For example, a value in a field named "x" can be referenced as `:x` in the SQL query of the target page. ## Examples - **Data Entry Automation**: Forms for tasks like inventory management. - **Custom Report Builder**: Generate reports based on user-specified criteria. - **Database Management**: Update records or query data. - **Admin Panel**: Manage user roles and permissions. - **Data Analytics with SQL**: Collect data for analytics. - **SQL Query Parametrization**: Build and execute complex SQL queries that depend on user input. - **SQL CRUD Operations**: Perform Create, Read, Update, and Delete operations. - **Web SQL**: Integrate forms into web applications.

Display a large title and description for your page, with an optional large illustrative image. Useful in your home page, for instance.

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.

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]).

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.

A vertical list of items. Each item can be clickable and link to another page.

## 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.

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.

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.

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.

Personalize the "shell" surrounding your page contents. Used to set properties for the entire page.

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

Guide users through multi-stage processes, displaying a clear list of previous and future steps.

Build a tabbed interface, with each tab being a link to a page. Each tab can be in two states: active or inactive.

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.

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.

A list of events with a vertical line connecting them.

Defines HTML headings. The level 1 is used for the maximal size and the level 6 is used for the minimal size.

Component for visualising activity logs or other monitoring-related data.