Database

The application server connects to a single database at startup and maintains that connection throughout its lifetime. The application server controls all page interactions with the database and is responsible for pooling connections. Multiple direct database connections are not currently supported, but can be achieved through database engine features such as Microsoft SQL Server's Linked Servers and PostgreSQL's Foreign Data Wrappers.

The database engine and connection details are configured in the database section of the configuration file.

SQLite

SQLite stores the entire database in a single file on the server. It requires no separate database server process and is a good fit for small to medium-sized applications or for getting started quickly.

To use SQLite, set database.type=sqlite and specify the path to the database file in database.name. The path is relative to the paths.root folder. If the database file does not exist, it will be created automatically.

Microsoft SQL Server

All editions of SQL Server are supported, including SQL Express.

To use SQL Server, set database.type=mssql. At minimum, specify the server hostname in database.host and the database name in database.name. The database must exist prior to connection; it will not be created automatically.

To connect to a named instance, append the instance name to the hostname using a backslash: "host": "servername\\instancename".

If the server is on a port other than 1433, specify it in database.port.

To connect using SQL credentials, set database.user and database.password. To connect using Windows authentication, leave both values empty.

If the server requires an encrypted connection, set database.encryption=true. By default the server certificate is validated. If the server uses a self-signed or otherwise unverifiable certificate, set database.verifyEncryption=false.

PostgreSQL

To use PostgreSQL, set database.type=postgres. At minimum, specify the server hostname in database.host and the database name in database.name. The database must exist prior to connection; it will not be created automatically.

If the server is on a port other than 5432, specify it in database.port.

If credentials are required, set database.user and database.password.

Connection Pool and Query Limits

The server maintains a single pooled connection to the database and runs all page queries through it. Three settings bound how much load a single request - or an accidentally-unbounded query - can place on the database engine and on the server's own memory. All three are enabled with safe defaults; size them to your hardware rather than turning them off.

database.maxOpenConns (default 25) caps the number of simultaneous connections, and therefore concurrent queries. When every connection is busy, additional requests wait for one to free up instead of opening an unbounded number and overwhelming the database. Raise it for a powerful database server with many concurrent users; lower it to protect a constrained engine. 0 removes the cap (not recommended in production).
database.queryTimeoutSeconds (default 30) gives every page query a deadline covering both execution and reading the result rows. A query that exceeds it is cancelled at the database and the page returns an error, so one slow or runaway query cannot tie up a connection indefinitely. 0 disables the timeout.
database.maxResultMB (default 256) aborts a query whose result set would build up more than approximately this much in memory, guarding against a SELECT with no WHERE/LIMIT against a large table. The page returns an error suggesting a narrower query. The figure estimates retained data; actual heap use is somewhat higher, so keep it comfortably below available RAM. 0 disables the guard.

Together these keep one expensive page from degrading the whole application: concurrency is bounded, individual queries are time-boxed, and result sets are size-boxed. Pair them with the rate limiters to also bound how often expensive requests can arrive.

Database Migrations

Migrations allow the application server to manage the database schema by running SQL scripts automatically on startup. This is useful for deploying schema changes across multiple environments (development, test, production) or for redistributable applications.

Migrations must be explicitly enabled by setting migrations.enabled=true in the configuration file. Migrations are supported with all database engines.

Migration File Format

Migration files are stored in the migrations folder and must follow this naming convention:

<number>_<description>.sql

The numeric prefix determines the order in which migrations run - lower numbers run first. The number can be any numeric format that sorts correctly as a filename, for example:

Sequential: 001_create_invoices.sql, 002_add_due_date.sql, 003_add_clients.sql
Date-based: 20260101_initial_schema.sql, 20260228_add_due_date.sql, 20260516_add_clients.sql

Date-based prefixes are useful when multiple developers may be adding migrations concurrently, as they are less likely to conflict than sequential numbers.

Each migration runs exactly once. The server records applied migrations in a tracking table and skips any file already recorded there.

Migration Transactions

By default each migration - its SQL and the bookkeeping that records it as applied - runs inside a single transaction, so a migration that fails partway is rolled back completely (nothing committed, nothing recorded) and a re-run starts from a clean state. This is controlled by migrations.useTransactions (default true) and applies on engines with transactional DDL (SQLite, PostgreSQL, SQL Server).

A few statements cannot run inside a transaction - for example CREATE DATABASE/ALTER DATABASE on SQL Server, or CREATE INDEX CONCURRENTLY/VACUUM on PostgreSQL. For a migration that contains one of these, opt that file out of the transaction by adding a directive line anywhere in the file:

-- htmql:no-transaction
CREATE INDEX CONCURRENTLY idx_invoice_owner ON invoice (owner);

The directive is an ordinary SQL comment, so it is harmless if the database also sees it.

When a migration runs without a transaction - because migrations.useTransactions is false, the file opts out with the directive, or the engine does not support transactional DDL - write it to be idempotent so a partial-failure re-run is safe: guard each statement with CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS, or an existence check before ALTER, and prefer one schema change per file. (Idempotency is good practice even for wrapped migrations.)

Migration Settings

migrations.table - database table used to track applied migrations (default: htmql_migrations). Created automatically if it does not exist.
migrations.useTransactions - when true (default), each migration runs inside a transaction (see Migration Transactions). Set to false to apply every migration without a transaction (then migrations must be idempotent).
migrations.validateChecksums - when true, verifies on startup that previously applied migration files have not been modified. Guards against accidental edits to applied migrations (default: true).
migrations.allowMissingAppliedFiles - when false, startup fails if a migration recorded in the tracking table is no longer present on disk. Set to true to allow startup to continue in that case (default: false).