Deployment

SQLPage applications are easy to deploy using Docker containers, systemd services, or cloud platforms. This guide covers production deployment strategies.

Docker Deployment

Docker is the recommended deployment method for most production environments.

Official Docker Images

SQLPage provides official Docker images:

lovasoa/sqlpage:latest - Latest stable release (minimal busybox-based image)
lovasoa/sqlpage:main - Latest development build
lovasoa/sqlpage:duckdb - With DuckDB ODBC driver (Debian-based)

Basic Docker Setup

# Pull the image
docker pull lovasoa/sqlpage:latest

# Run SQLPage
docker run -d \
  --name sqlpage \
  -p 8080:8080 \
  -v $(pwd)/website:/var/www \
  -v $(pwd)/sqlpage:/etc/sqlpage \
  lovasoa/sqlpage:latest

Dockerfile for Your Application

Create a custom image with your application:

FROM lovasoa/sqlpage:latest

# Copy your SQL files
COPY website/ /var/www/

# Copy configuration
COPY sqlpage/ /etc/sqlpage/

# Expose port
EXPOSE 8080

# SQLPage runs as CMD by default

Build and run:

docker build -t myapp .
docker run -d -p 8080:8080 myapp

Docker Compose

For multi-container setups with a database:

# docker-compose.yml
version: '3.8'

services:
  sqlpage:
    image: lovasoa/sqlpage:latest
    ports:
      - "8080:8080"
    volumes:
      - ./website:/var/www
      - ./sqlpage:/etc/sqlpage
    environment:
      - DATABASE_URL=postgres://user:password@postgres:5432/mydb
    depends_on:
      postgres:
        condition: service_healthy
    restart: unless-stopped

  postgres:
    image: postgres:16-alpine
    environment:
      - POSTGRES_USER=user
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=mydb
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U user"]
      interval: 5s
      timeout: 5s
      retries: 5
    restart: unless-stopped

volumes:
  postgres_data:

Deploy:

docker-compose up -d

Systemd Service

For Linux servers without Docker, deploy using systemd.

Installation

# Download SQLPage binary
wget https://github.com/sqlpage/SQLPage/releases/latest/download/sqlpage-linux.tgz
tar xzf sqlpage-linux.tgz
sudo mv sqlpage.bin /usr/local/bin/
sudo chmod +x /usr/local/bin/sqlpage.bin

# Create user and directories
sudo useradd -r -s /bin/false sqlpage
sudo mkdir -p /var/www/sqlpage
sudo chown -R sqlpage:sqlpage /var/www/sqlpage

# Copy your application
sudo cp -r website/* /var/www/sqlpage/

Systemd Unit File

Create /etc/systemd/system/sqlpage.service:

[Unit]
Description=SQLPage website
Documentation=https://sql-page.com
After=network.target

[Service]
# User and group
User=sqlpage
Group=sqlpage

# Working directory and executable
WorkingDirectory=/var/www/sqlpage
ExecStart=/usr/local/bin/sqlpage.bin

# Environment variables
Environment="RUST_LOG=info"
Environment="LISTEN_ON=0.0.0.0:80"
Environment="DATABASE_URL=sqlite://sqlpage.db?mode=rwc"

# Allow binding to port 80
AmbientCapabilities=CAP_NET_BIND_SERVICE

# Restart options
Restart=always
RestartSec=10

# Logging
SyslogIdentifier=sqlpage

# Security options
NoNewPrivileges=true
ProtectSystem=full
PrivateTmp=true
ProtectControlGroups=true
ProtectKernelModules=true
ProtectKernelTunables=true
ProtectClock=true
ProtectHostname=true
ProtectProc=invisible

# Resource limits
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target

Manage the Service

# Enable and start
sudo systemctl daemon-reload
sudo systemctl enable sqlpage
sudo systemctl start sqlpage

# Check status
sudo systemctl status sqlpage

# View logs
sudo journalctl -u sqlpage -f

# Restart after updates
sudo systemctl restart sqlpage

Reverse Proxy Setup

NGINX

NGINX is recommended as a reverse proxy for SQLPage:

# /etc/nginx/sites-available/sqlpage
server {
    listen 80;
    server_name myapp.example.com;

    # Redirect HTTP to HTTPS
    return 301 https://$server_name$request_uri;
}

server {
    listen 443 ssl http2;
    server_name myapp.example.com;

    # SSL certificates
    ssl_certificate /etc/letsencrypt/live/myapp.example.com/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/myapp.example.com/privkey.pem;

    # SSL configuration
    ssl_protocols TLSv1.2 TLSv1.3;
    ssl_ciphers HIGH:!aNULL:!MD5;
    ssl_prefer_server_ciphers on;

    # Rate limiting
    limit_req_zone $binary_remote_addr zone=sqlpage:10m rate=10r/s;
    limit_req zone=sqlpage burst=20 nodelay;

    location / {
        # Proxy to SQLPage
        proxy_pass http://127.0.0.1:8080;
        
        # Preserve original request information
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;

        # Streaming configuration
        proxy_buffering on;
        proxy_buffer_size 16k;
        proxy_buffers 4 16k;
        
        # Compression (SQLPage should have compress_responses=false)
        gzip on;
        gzip_types text/html text/plain text/css application/javascript application/json;
        gzip_min_length 1000;
        
        chunked_transfer_encoding on;
    }

    # Static file caching
    location ~* \.(jpg|jpeg|png|gif|ico|css|js|woff|woff2|ttf)$ {
        proxy_pass http://127.0.0.1:8080;
        proxy_cache_valid 200 1h;
        expires 1h;
        add_header Cache-Control "public, immutable";
    }
}

Enable the site:

sudo ln -s /etc/nginx/sites-available/sqlpage /etc/nginx/sites-enabled/
sudo nginx -t
sudo systemctl reload nginx

Using Unix Sockets

For better performance when NGINX and SQLPage run on the same host: SQLPage configuration:

{
  "unix_socket": "/tmp/sqlpage/sqlpage.sock"
}

NGINX configuration:

location / {
    proxy_pass http://unix:/tmp/sqlpage/sqlpage.sock;
    proxy_set_header Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
}

Apache

Alternatively, use Apache as a reverse proxy:

<VirtualHost *:80>
    ServerName myapp.example.com
    
    ProxyPreserveHost On
    ProxyPass / http://127.0.0.1:8080/
    ProxyPassReverse / http://127.0.0.1:8080/
    
    <Proxy *>
        Order deny,allow
        Allow from all
    </Proxy>
</VirtualHost>

SSL/TLS Certificates

Option 1: Built-in HTTPS

SQLPage can handle HTTPS automatically with Let’s Encrypt:

{
  "https_domain": "myapp.example.com",
  "https_certificate_email": "admin@example.com"
}

SQLPage will:

Listen on port 443
Request and renew certificates automatically
Cache certificates in sqlpage/https/

Option 2: Reverse Proxy SSL

Use Certbot with NGINX:

sudo apt install certbot python3-certbot-nginx
sudo certbot --nginx -d myapp.example.com

Certbot automatically configures NGINX and sets up renewal.

Production Configuration

sqlpage/sqlpage.json

{
  "environment": "production",
  "database_url": "postgres://user:password@db.example.com:5432/myapp",
  "max_database_pool_connections": 50,
  "database_connection_idle_timeout_seconds": 300,
  "compress_responses": false,
  "max_uploaded_file_size": 10485760,
  "https_domain": "myapp.example.com",
  "https_certificate_email": "admin@example.com",
  "content_security_policy": "default-src 'self'; script-src 'self' 'nonce-{NONCE}'; style-src 'self' 'unsafe-inline';"
}

Environment Variables

For sensitive data, use environment variables:

export DATABASE_URL="postgres://user:password@db:5432/myapp"
export DATABASE_PASSWORD="secure_password"
export OIDC_CLIENT_SECRET="oauth_secret"

Or use a .env file (not committed to version control):

DATABASE_URL=postgres://user:password@db:5432/myapp
OIDC_CLIENT_SECRET=oauth_secret

Database Migrations

SQLPage runs migrations automatically from sqlpage/migrations/ on startup:

sqlpage/migrations/
├── 0001_initial_schema.sql
├── 0002_add_users_table.sql
└── 0003_add_indexes.sql

Migrations run in alphabetical order and are tracked to prevent re-execution.

Monitoring and Logging

Application Logs

Docker:

docker logs -f sqlpage

Systemd:

sudo journalctl -u sqlpage -f

Log Levels

Set via RUST_LOG environment variable:

export RUST_LOG=sqlpage=info
# Or for debugging:
export RUST_LOG=sqlpage=debug

Health Check Endpoint

Create a health check page:

-- health.sql
SELECT 'json' AS component;
SELECT 'ok' AS status, CURRENT_TIMESTAMP AS timestamp;

Configure monitoring tools to check /health.sql.

Performance Tuning

Connection Pooling

Adjust based on your database and traffic:

{
  "max_database_pool_connections": 50,
  "database_connection_idle_timeout_seconds": 600,
  "database_connection_acquire_timeout_seconds": 10
}

Caching

Use proxy caching for static content:

proxy_cache_path /var/cache/nginx levels=1:2 keys_zone=sqlpage_cache:10m max_size=1g;

location / {
    proxy_pass http://127.0.0.1:8080;
    proxy_cache sqlpage_cache;
    proxy_cache_valid 200 5m;
    proxy_cache_valid 404 1m;
    add_header X-Cache-Status $upstream_cache_status;
}

Response Compression

Disable in SQLPage, enable in NGINX:

{
  "compress_responses": false
}

Backup Strategies

SQLite Backups

# Backup script
#!/bin/bash
BACKUP_DIR="/backups/sqlpage"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR
sqlite3 /var/www/sqlpage/sqlpage.db ".backup $BACKUP_DIR/sqlpage_$DATE.db"

# Keep only last 30 days
find $BACKUP_DIR -name "sqlpage_*.db" -mtime +30 -delete

Schedule with cron:

0 2 * * * /usr/local/bin/backup-sqlpage.sh

PostgreSQL Backups

pg_dump -h db.example.com -U user myapp > backup_$(date +%Y%m%d).sql

Security Checklist

Zero-Downtime Deployments

Blue-Green Deployment with Docker

# Start new version (green)
docker run -d --name sqlpage-green -p 8081:8080 myapp:new

# Test the new version
curl http://localhost:8081/health.sql

# Switch NGINX to new version
# Update upstream in nginx.conf
sudo systemctl reload nginx

# Stop old version (blue)
docker stop sqlpage-blue
docker rm sqlpage-blue

Rolling Updates with Docker Swarm

docker service update --image myapp:new sqlpage

Cloud Platform Deployment

AWS ECS

Use the official Docker image with ECS task definitions.

Google Cloud Run

Deploy as a container:

gcloud run deploy sqlpage \
  --image lovasoa/sqlpage:latest \
  --platform managed \
  --region us-central1 \
  --allow-unauthenticated

Fly.io

Create fly.toml:

app = "myapp"

[build]
  image = "lovasoa/sqlpage:latest"

[[services]]
  internal_port = 8080
  protocol = "tcp"

  [[services.ports]]
    handlers = ["http"]
    port = 80

  [[services.ports]]
    handlers = ["tls", "http"]
    port = 443

Deploy:

fly deploy

Examples

See deployment examples:

NGINX reverse proxy: examples/nginx/
Apache proxy: examples/web servers - apache/
Docker Compose: Various examples include docker-compose.yml

Get Started

Core Concepts

Configuration

Guides

​Deployment

​Docker Deployment

​Official Docker Images

​Basic Docker Setup

​Dockerfile for Your Application

​Docker Compose

​Systemd Service

​Installation

​Systemd Unit File

​Manage the Service

​Reverse Proxy Setup

​NGINX

​Using Unix Sockets

​Apache

​SSL/TLS Certificates

​Option 1: Built-in HTTPS

​Option 2: Reverse Proxy SSL

​Production Configuration

​sqlpage/sqlpage.json

​Environment Variables

​Database Migrations

​Monitoring and Logging

​Application Logs

​Log Levels

​Health Check Endpoint

​Performance Tuning

​Connection Pooling

​Caching

​Response Compression

​Backup Strategies

​SQLite Backups

​PostgreSQL Backups

​Security Checklist

​Zero-Downtime Deployments

​Blue-Green Deployment with Docker

​Rolling Updates with Docker Swarm

​Cloud Platform Deployment

​AWS ECS

​Google Cloud Run

​Fly.io

​Examples

​Resources

Build docs developers (and LLMs) love