Skip to main content
SQLPage needs a database connection to store and retrieve data. This page explains how to configure connections to various database engines and optimize connection pooling.

​
Basic Configuration

The database connection is configured using the database_url parameter, which follows this general format: 
dbengine://user:password@host:port/dbname?param=value

​
Setting the Database URL

Via environment variable: 
export DATABASE_URL="postgres://myuser:mypass@localhost:5432/mydb"
Via configuration file (sqlpage/sqlpage.json): 
{
  "database_url": "postgres://myuser:mypass@localhost:5432/mydb"
}
​
database_url
string
default:"sqlite://sqlpage.db?mode=rwc"
Database connection URL. Can be a standard connection string (dbengine://user:password@host:port/dbname) or an ODBC connection string.Special characters should be percent-encoded:
  • @ → %40
  • : → %3A
  • / → %2F
  • ? → %3F
  • # → %23
​
database_password
string
Database password. If set, this will override any password specified in the database_url. This allows you to keep the password separate from the connection string for better security.
This password does not need to be percent-encoded.

​
Database-Specific Connection Strings

​
SQLite

SQLite is a serverless database that stores data in a single file. It’s perfect for small to medium applications. Basic connection: 
DATABASE_URL="sqlite://sqlpage.db?mode=rwc"
In-memory database (for testing): 
DATABASE_URL="sqlite://:memory:?cache=shared"
Parameters:
  • mode=rwc - Open in read-write mode, create if doesn’t exist
  • mode=ro - Read-only mode
  • cache=shared - Share cache between connections (required for in-memory databases)
Loading SQLite extensions: 
{
  "database_url": "sqlite://sqlpage.db?mode=rwc",
  "sqlite_extensions": ["mod_spatialite", "crypto", "regexp"]
}
​
sqlite_extensions
array
An array of SQLite extensions to load, such as mod_spatialite for GIS functionality.

​
PostgreSQL

PostgreSQL is a powerful open-source relational database. Basic connection: 
DATABASE_URL="postgres://myuser:mypass@localhost:5432/mydb"
With SSL required: 
DATABASE_URL="postgres://myuser:mypass@localhost:5432/mydb?sslmode=require"
Full example with all security options: 
DATABASE_URL="postgres://my_user:p%40ss@localhost:5432/my_database?sslmode=verify-ca&sslrootcert=/path/to/ca.pem&sslcert=/path/to/cert.pem&sslkey=/path/to/key.pem&application_name=my_application"
Common parameters:
  • sslmode - SSL mode: disable, allow, prefer, require, verify-ca, verify-full
  • sslrootcert - Path to CA certificate file
  • sslcert - Path to client certificate file
  • sslkey - Path to client key file
  • application_name - Application name (useful for monitoring and logging)
Separate password for better security: 
DATABASE_URL="postgres://myuser@localhost:5432/mydb"
DATABASE_PASSWORD="my_secure_password"

​
MySQL / MariaDB

MySQL and MariaDB are popular open-source relational databases. Basic connection: 
DATABASE_URL="mysql://myuser:mypass@localhost:3306/mydb"
With SSL: 
DATABASE_URL="mysql://myuser:mypass@localhost:3306/mydb?ssl-mode=REQUIRED"
With custom collation: 
DATABASE_URL="mysql://myuser:mypass@localhost:3306/mydb?collation=utf8mb4_unicode_ci"
With client certificates: 
DATABASE_URL="mysql://myuser:mypass@localhost:3306/mydb?sslcert=/path/to/cert.pem&sslkey=/path/to/key.pem"

​
Microsoft SQL Server (MSSQL)

Microsoft SQL Server is an enterprise relational database. Basic connection: 
DATABASE_URL="mssql://myuser:mypass@localhost:1433/mydb"
With Windows authentication: 
DATABASE_URL="mssql://localhost:1433/mydb?trusted_connection=true"
With encryption: 
DATABASE_URL="mssql://myuser:mypass@localhost:1433/mydb?encrypt=true&trustServerCertificate=false"

​
ODBC (Oracle, Snowflake, BigQuery, DB2, DuckDB, etc.)

For ODBC-compatible databases, you can use ODBC connection strings directly.
ODBC drivers must be installed and configured on your system. On Linux, the unixODBC driver manager is statically linked into the SQLPage binary.
Using a Data Source Name (DSN): 
DATABASE_URL="DSN=MyDatabase"
Using inline connection parameters: 
DATABASE_URL="Driver={PostgreSQL};Server=localhost;Port=5432;Database=mydb;UID=myuser;PWD=mypassword"
Oracle example: 
DATABASE_URL="Driver={Oracle ODBC Driver};Server=localhost:1521/XE;UID=hr;PWD=password"
Snowflake example: 
DATABASE_URL="Driver={SnowflakeDSIIDriver};Server=account.snowflakecomputing.com;Database=mydb;UID=user;PWD=password"
DuckDB example: 
DATABASE_URL="DSN=DuckDB"
BigQuery example: 
DATABASE_URL="Driver={Simba ODBC Driver for Google BigQuery};Catalog=my-project;SQLDialect=1;OAuthMechanism=0"

​
Connection Pooling

SQLPage uses connection pooling to efficiently manage database connections. Multiple requests can share a pool of connections, improving performance and resource usage.

​
Pool Size

​
max_database_pool_connections
integer
How many simultaneous database connections to open at most.Default values:
  • PostgreSQL: 50
  • MySQL: 75
  • SQLite: 16
  • MSSQL: 100
Adjust based on your database server’s capacity and expected concurrent users.
Example: 
{
  "max_database_pool_connections": 50
}

​
Connection Timeouts

​
database_connection_idle_timeout_seconds
number
Automatically close database connections after this period of inactivity. Set to 0 to disable.Default values:
  • SQLite: No timeout
  • All other databases: 1800 seconds (30 minutes)
​
database_connection_max_lifetime_seconds
number
Always close database connections after this amount of time. Set to 0 to disable.Default values:
  • SQLite: No limit
  • All other databases: 3600 seconds (60 minutes)
​
database_connection_acquire_timeout_seconds
number
default:"10"
How long to wait when acquiring a database connection from the pool before giving up and returning an error.
Example: 
{
  "database_connection_idle_timeout_seconds": 600,
  "database_connection_max_lifetime_seconds": 1800,
  "database_connection_acquire_timeout_seconds": 5
}

​
Connection Retries

​
database_connection_retries
integer
default:"6"
Database connection attempts before giving up when the server starts. Retries will happen every 5 seconds. The default of 6 retries means SQLPage will wait up to 30 seconds for the database to become available.This is useful when starting SQLPage and the database server at the same time (e.g., with Docker Compose).
Example: 
DATABASE_CONNECTION_RETRIES=12  # Wait up to 60 seconds

​
Connection Management Scripts

SQLPage allows you to run SQL scripts when connections are opened or returned to the pool.

​
on_connect.sql

Run SQL when a new database connection is opened. Place this file at sqlpage/on_connect.sql. PostgreSQL example - Set connection parameters: 
SET TIME ZONE 'UTC';
SET search_path = my_schema;
SET statement_timeout = '30s';
SQLite example - Attach additional databases: 
ATTACH DATABASE '/path/to/other.db' AS other_db;
Create temporary tables: 
CREATE TEMPORARY TABLE session_data (
    key TEXT PRIMARY KEY,
    value TEXT
);

​
on_reset.sql

Run SQL after a request has been processed, before returning the connection to the pool. Place this file at sqlpage/on_reset.sql. Rollback any open transactions: 
ROLLBACK;
PostgreSQL - Clean up all connection state: 
DISCARD ALL;
SQL Server - Reset connection: 
EXEC sp_reset_connection;
Mark connection as unhealthy: To close a connection that’s in an undesirable state, return a row with a boolean column named is_healthy set to false: 
SELECT false as is_healthy;

​
Security Best Practices

Instead of embedding passwords in the connection string:
DATABASE_URL="postgres://myuser@localhost:5432/mydb"
DATABASE_PASSWORD="my_secure_password"
This makes it easier to manage secrets and avoid accidentally committing passwords to version control.
Always use SSL/TLS when connecting to a remote database:
DATABASE_URL="postgres://user:[email protected]:5432/db?sslmode=verify-full&sslrootcert=/path/to/ca.pem"
Set max_database_pool_connections based on your database server’s capacity to prevent overwhelming it.
For pages that only read data, consider creating a separate database user with read-only permissions.
For SQLite databases, store the .db file in the configuration directory (default: ./sqlpage/), not in the web root where it could be downloaded.
Don’t commit database credentials to version control. Use environment variables or a .env file that’s excluded from git:
# .gitignore
.env

​
Troubleshooting

​
Connection Failed

If SQLPage can’t connect to your database:
  1. Check the connection string format - Make sure special characters are percent-encoded
  2. Verify database server is running - Try connecting with another tool (psql, mysql, etc.)
  3. Check firewall settings - Ensure the database port is accessible
  4. Review SSL/TLS settings - Try with sslmode=disable first, then add SSL
  5. Enable debug logging - Set RUST_LOG=sqlpage=debug to see detailed connection attempts

​
Pool Exhausted

If you see “timeout waiting for connection” errors:
  1. Increase pool size - Raise max_database_pool_connections
  2. Decrease timeout values - Lower database_connection_idle_timeout_seconds
  3. Check for connection leaks - Review your SQL code for unclosed transactions
  4. Add connection reset logic - Use on_reset.sql to rollback transactions

​
Slow Connections

If connections take a long time to establish:
  1. Check network latency - Use a database closer to your SQLPage server
  2. Review on_connect.sql - Simplify or remove expensive initialization queries
  3. Disable SSL temporarily - Test if SSL negotiation is the bottleneck
  4. Check database server load - Monitor database server performance

Configuration Overview

Learn about all configuration options

Environment Variables

Complete reference of all environment variables

Migrations

Set up database migrations

Functions Reference

Database functions available in SQLPage

Build docs developers (and LLMs) love

Get started for freeTalk to us