DOCSEARCHbyAlgolia
Submit Docs

SQLPage Documentation

repository·main·Indexed 25 days ago

https://github.com/sqlpage/sqlpage

SQLPage is an SQL-only webapp builder and web server that transforms database queries into interactive, data-centric websites. It allows developers to build user interfaces—including lists, charts, forms, and dashboards—entirely in SQL files using pre-made configurable components. Version 0.45.0 supports features such as lazy-loading remote pages into cards, reusable code modules via sqlpage.run_sql(), and comprehensive patterns for implementing authentication and CRUD applications.

Tokens
30.3K
Snippets
73
Records
180
Agent score
82%

What's inside SQLPage

  1. What is SQLPage?

    main
    SQLPage is an open-source web server distributed as a single binary that allows you to build full web applications directly on top of a SQLite database using only standard SQL queries. It executes .sql files and automatically renders the results using built-in web components such as tables, lists, forms, and plots. This eliminates the need to write HTML, CSS, or JavaScript for many application types.

  2. Overview of Docker Build Scripts

    main
    The scripts/ directory contains specialized scripts used during the Docker build process to enable cross-compilation for SQLPage. These scripts manage the complexities of building for different architectures (such as amd64, arm64, and arm) by handling system dependencies, cross-compiler installation, and library extraction required for the runtime environment.

  4. Use Static Simple Selects for performance

    main

    A static simple select is a highly restricted SELECT statement that SQLPage executes internally without contacting your database. This is faster for trivial data needs.

    To be considered a static simple select, the statement must:

    • Contain no clauses like FROM, WHERE, GROUP BY, ORDER BY, LIMIT, WITH, etc.
    • Only select items in the format value AS alias.
    • Use only literals (strings, numbers, booleans, NULL) or variables ($name, :message) as values.

    Examples that ARE static (executed by SQLPage)

    SELECT 'text' AS component, 'Hello' AS contents;
SELECT 'text' AS component, $name AS contents;

    Examples that are NOT static (sent to the database)

    -- String concatenation requires the database
select 'from' as component, 'handle_form.sql?id=' || $id as action;

-- WHERE clause requires the database
select 'text' as component, $alert_message as contents where $should_alert;

-- Database functions require the database
SELECT CURRENT_TIMESTAMP AS now;

  5. Include common UI elements using shell.sql

    main
    To maintain a consistent layout (like a navigation menu) across different pages, you can use a shell.sql file. This file acts as a wrapper or template that includes your page-specific content. You can combine this with the sqlpage.run_sql function to dynamically inject components or content into your common layout.

  6. Use `run_sql()` to create reusable code modules

    main

    To avoid duplicating authentication or header logic across multiple pages, place common code in a separate .sql file and execute it using the sqlpage.run_sql() function within a dynamic component.

    Variables set in the calling page (like $_session_required) are accessible within the loaded module. This allows you to control the behavior of the module based on the context of the page that calls it.

    set _curpath = sqlpage.path();
set _session_required = 1;

SELECT
    'dynamic' AS component,
    sqlpage.run_sql('header_shell_session.sql') AS properties;

  7. Structure a CRUD application using modules

    main

    A data-centric application in SQLPage can be organized into specialized modules to separate concerns. A common pattern for a single entity (e.g., "currencies") involves three types of modules:

    1. Table View Module: Displays the entire dataset using the table component. It often includes an "actions" column (rendered as markdown) with shortcuts to edit or delete records.
    2. Detail View Module: A GUI module that shows all fields for a single record. It can function as an editable form or a read-only view (using the datagrid component). It handles logic for both existing records (via an id parameter) and new records.
    3. DML Processor Module: A "no-GUI" module that processes database modification operations (INSERT, UPDATE, DELETE) based on submitted form data. It typically suppresses UI elements like top menus using the $_shell_enabled flag.

  8. Understand SQLPage data types and mapping

    main

    SQLPage acts as a bridge between untyped HTTP requests and typed databases.

    From User to SQLPage

    • Strings: All URL and POST parameters are treated as UTF-8 strings.
    • Arrays: If a parameter name ends with [] (e.g., user[]=Tim&user[]=Tom), SQLPage converts it into a JSON string: '["Tim", "Tom"]'. This can be parsed using your database's JSON functions.

    From SQLPage to Database

    • SQLPage sends parameters to your database as either strings (TEXT/VARCHAR) or NULL.

    From Database to SQLPage (Component Data)

    Each row returned by the database is converted into a JSON object for components:

    • Columns: Become keys in the JSON object. Duplicate column names are automatically converted into arrays.
    • Numbers/Booleans/Text/NULL: Map directly.
    • Dates/Times: Converted to ISO 8601 strings.
    • Binary (BLOB): Converted to a Data URL with auto-detected MIME type.

    Example Mapping

    SELECT
  1 AS one,
  'x' AS my_array, 'y' AS my_array,
  now() AS today,
  '<svg></svg>'::bytea AS my_image;

    Resulting JSON:

    {
  "one": 1,
  "my_array": ["x", "y"],
  "today": "2025-08-30T06:40:13.894918+00:00",
  "my_image": "data:image/svg+xml;base64,PHN2Zz48L3N2Zz48L3N2Zy4+"
}

  9. Core features of SQLPage

    main

    SQLPage provides several key capabilities for rapid application development:

    • SQL-only development: Create web interfaces without manual HTML/CSS/JS.
    • Auto-generated Web UI: Write raw SQL queries to generate UI components.
    • SQLite Integration: Works with any existing SQLite database and supports SQLite extensions.
    • Web Standards: Supports HTTP cookies, user authentication, form submissions, and URL parameters.
    • Security and Performance: Built in Rust to prevent memory corruption, SQL injection, and XSS vulnerabilities.

  10. Store multi-step form state in cookies

    main

    You can store each user answer in a cookie using the cookie component and retrieve it on the next step using the sqlpage.cookie function.

    Pros:

    • Simple to implement.
    • State is persisted even if the user leaves and returns later.
    • Works even if intermediate steps do not use the form component.

    Cons:

    • The entire state is re-sent to the server on each step.
    • Requires cookies to be enabled by the user.
    • Form state is sent to every page the user visits until the form is submitted.

  11. Use the dynamic component for complex page layouts

    main
    The dynamic component can be used to create single-page applications or complex layouts where multiple forms or elements exist on one page. In this example, admin.sql uses the dynamic component to generate a single page that renders one form for every Multiple Choice Question (MCQ) option available in the database.

  12. How SQLPage executes SQL statements

    main

    SQLPage processes your .sql files by reading and executing one statement at a time. For each statement, SQLPage makes a decision:

    1. Handled locally by SQLPage: Trivial queries that don't require the database. This includes "Static Simple Selects" and simple variable assignments using only literals or variables.
    2. Sent to your database: Most queries, including those with JOIN, WHERE, GROUP BY, ORDER BY, subqueries, or database-specific functions (e.g., CURRENT_TIMESTAMP).

    sqlpage.* functions are always executed by SQLPage, never by your database. They can be used as input values (evaluated before the query) or as top-level selected columns (applied per row after the query).