How SQLPage Works

SQLPage is a web server that transforms SQL queries into interactive web pages. This page explains how SQLPage processes your SQL files and generates web responses.

Architecture Overview

SQLPage operates as a complete web application stack in a single executable:

HTTP Request

A user’s browser sends an HTTP request to SQLPage (e.g., GET /users.sql)

File Resolution

SQLPage’s routing system locates the corresponding .sql file

SQL Parsing

The SQL file is parsed and prepared with parameterized queries

Query Execution

Queries execute on your database, with request parameters bound to SQL placeholders

Component Rendering

Query results are mapped to UI components using Handlebars templates

HTTP Response

HTML is streamed back to the user’s browser

Detailed Architecture Diagram

Request-Response Flow

1. Request Reception

When SQLPage receives an HTTP request:

Method: GET, POST, PUT, DELETE, etc.
Path: /users.sql or /api/data.json
Query parameters: ?id=42&filter=active
POST data: Form submissions, JSON payloads
Headers: Cookies, authentication, content-type

2. Routing & File Discovery

The routing system determines which SQL file to execute based on the URL path. See Routing for details.

3. SQL Parsing & Parameter Extraction

Original SQL
After Parsing

SELECT 'card' as component;

SELECT 
    name as title,
    email as description
FROM users 
WHERE id = $id
  AND status = :status;

-- Stored in prepared statement
SELECT 
    name,
    email
FROM users 
WHERE id = $1
  AND status = $2;

-- Parameters tracked separately:
-- $1 = GET parameter 'id'
-- $2 = POST parameter 'status'

Parameter types extracted:

$param - GET/URL parameters (or SET variables)
:param - POST/form parameters (or SET variables)
?param - GET parameters only

SQLPage uses sqlparser-rs to parse SQL into an AST, then walks the tree to:

Extract parameters - Replace $id, :name with database-specific placeholders ($1, ?, @p1)
Identify SQLPage functions - Detect sqlpage.function() calls for special handling
Validate syntax - Ensure queries are well-formed before execution

SQLPage transforms your SQL parameters into database-specific placeholders:

PostgreSQL: $1, $2, $3
MySQL/ODBC: ?, ?, ?
SQL Server: @p1, @p2, @p3
SQLite: ?1, ?2, ?3

4. Query Execution

Each SQL statement in your file executes sequentially:

// Simplified execution flow from execute_queries.rs
for statement in sql_file.statements {
    match statement {
        StmtWithParams => {
            // 1. Bind parameters from request
            let query = bind_parameters(stmt, request)?;
            
            // 2. Execute on database connection
            let mut stream = connection.fetch_many(query);
            
            // 3. Stream results to renderer
            while let Some(row) = stream.next().await {
                yield DbItem::Row(row_to_json(&row));
            }
        },
        SetVariable { variable, value } => {
            // Store result in SET variables for later use
            set_variables.insert(variable, result);
        }
    }
}

Execution behavior:

Statements run in order, top to bottom
Results stream immediately (no buffering)
Errors stop execution by default
Transactions can span multiple statements

5. Component Rendering

Query results are mapped to UI components:

SELECT 'list' as component,
       'Users' as title;

SELECT 
    name as title,
    email as description,
    avatar_url as icon
FROM users;

Rendering process:

First result row defines the component (e.g., 'list' as component)
Top-level properties from first row configure the component
Subsequent rows become items within that component
New component selection closes previous component and starts new one

See Components for the complete rendering model.

6. Response Generation

HTML Response (Default)

HTTP/1.1 200 OK
Content-Type: text/html; charset=utf-8

<!DOCTYPE html>
<html>
  <head>
    <title>My App</title>
    <link rel="stylesheet" href="/sqlpage/sqlpage.css">
  </head>
  <body>
    <!-- Component HTML streamed here -->
    <div class="list">...</div>
  </body>
</html>

The default response format using Handlebars templates and Tabler CSS framework.

JSON Response

HTTP/1.1 200 OK
Content-Type: application/json

[
  {"component": "json"},
  {"name": "Alice", "email": "[email protected]"},
  {"name": "Bob", "email": "[email protected]"}
]

Access via ?_sqlpage_format=json or use the json component.

CSV Response

HTTP/1.1 200 OK
Content-Type: text/csv
Content-Disposition: attachment; filename=data.csv

name,email
Alice,[email protected]
Bob,[email protected]

Triggered by the csv component for data exports.

Streaming Architecture

SQLPage uses streaming throughout its architecture for optimal performance:

Because responses stream immediately, you cannot change HTTP headers after sending the first component. Components like redirect, http_header, and cookie must come first in your SQL file.

Component Lifecycle

Each component follows a three-phase lifecycle:

Component Initialization

First row with component column triggers creation:

SELECT 'card' as component, 'My Cards' as title;

Renders the before_list template with top-level properties.

Row Iteration

Subsequent rows without component column are items:

SELECT 
    name as title,
    description
FROM items;

Each row renders the list_content template.

Component Closure

New component or end of file closes current component:

SELECT 'text' as component; -- Closes 'card' component

Renders the after_list template.

Database Connection Pooling

SQLPage maintains a connection pool using sqlx:

Pool size: Configurable via max_database_pool_size
Lazy acquisition: Connections acquired only when needed
Connection reuse: Single connection per request by default
Transaction support: BEGIN starts transaction, COMMIT/ROLLBACK ends it

You can control when connections are acquired:

First SQL statement triggers connection acquisition
Connection held for entire request duration
Errors trigger automatic ROLLBACK

Error Handling

When errors occur during execution:

Development Mode
Production Mode

-- SQL syntax error or database error
SELECT * FROM non_existent_table;

Shows detailed error with:

Error message and stack trace
SQL query with syntax highlighting
Line number and position
Suggestions for fixing

<div class="alert alert-error">
  An error occurred. Please contact the administrator.
</div>

Errors are:

Logged to server logs
Generic message shown to users
No sensitive information exposed

Set via environment = "production" in configuration.

Performance Optimizations

SQLPage employs several optimizations:

Optimization	Description
Prepared Statements	Queries cached and reused with different parameters
Streaming	Results sent as they arrive, no buffering
File Caching	Parsed SQL files cached in memory
Template Compilation	Handlebars templates pre-compiled
Connection Pooling	Database connections reused across requests

Next Steps

Routing

Learn how URLs map to SQL files

Components

Understand the component rendering system

SQL Parameters

Master parameter handling and types

Configuration

Configure SQLPage for your needs

Get Started

Core Concepts

Configuration

Guides

How SQLPage Works

Architecture Overview

Detailed Architecture Diagram

Request-Response Flow

1. Request Reception

2. Routing & File Discovery

3. SQL Parsing & Parameter Extraction

4. Query Execution

5. Component Rendering

6. Response Generation

Streaming Architecture

Component Lifecycle

Database Connection Pooling

Error Handling

Performance Optimizations

Next Steps

Routing

Components

SQL Parameters

Configuration

Build docs developers (and LLMs) love

Get Started

Core Concepts

Configuration

Guides

​Architecture Overview

​Detailed Architecture Diagram

​Request-Response Flow

​1. Request Reception

​2. Routing & File Discovery

​3. SQL Parsing & Parameter Extraction

​4. Query Execution

​5. Component Rendering

​6. Response Generation

​Streaming Architecture

​Component Lifecycle

​Database Connection Pooling

​Error Handling

​Performance Optimizations

​Next Steps

Routing

Components

SQL Parameters

Configuration

Build docs developers (and LLMs) love

Architecture Overview

Detailed Architecture Diagram

Request-Response Flow

1. Request Reception

2. Routing & File Discovery

3. SQL Parsing & Parameter Extraction

4. Query Execution

5. Component Rendering

6. Response Generation

Streaming Architecture

Component Lifecycle

Database Connection Pooling

Error Handling

Performance Optimizations

Next Steps