aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorDennis Fink2026-11-17 21:06:05 +0100
committerDennis Fink2026-11-17 21:06:05 +0100
commitc4c17e3e046a627a7d2353c734bc69f05d4fc3b1 (patch)
treeebc343ce994c0c32243e1f8b7e654ee902d714bc
parent5e24e8307f650e72881f747661a2b6e52db15f2d (diff)
downloadwebmentions-ssg-c4c17e3e046a627a7d2353c734bc69f05d4fc3b1.tar.gz
webmentions-ssg-c4c17e3e046a627a7d2353c734bc69f05d4fc3b1.zip
feat(core): add configurable SQLite pragmas
Apply validated SQLite settings through SQLAlchemy's connection event so every SQLite connection and bind receives consistent foreign key, WAL, timeout, and security defaults. Allow deployments to override supported PRAGMAs safely while skipping file-only settings for in-memory databases and leaving other database backends untouched.
-rw-r--r--contrib/webmentions-ssg.example.cfg29
-rw-r--r--tests/conftest.py6
-rw-r--r--tests/test_config.py61
-rw-r--r--tests/test_init.py145
-rw-r--r--webmentions_ssg/__init__.py5
-rw-r--r--webmentions_ssg/config.py75
-rw-r--r--webmentions_ssg/extensions.py70
7 files changed, 388 insertions, 3 deletions
diff --git a/contrib/webmentions-ssg.example.cfg b/contrib/webmentions-ssg.example.cfg
index 9be2cf0..6393b3f 100644
--- a/contrib/webmentions-ssg.example.cfg
+++ b/contrib/webmentions-ssg.example.cfg
@@ -40,6 +40,35 @@ PROXY_FIX_TRUSTED_HOPS = 0
SQLALCHEMY_DATABASE_URI = "sqlite:////var/lib/webmentions-ssg/webmentions-ssg.db"
SQLALCHEMY_TRACK_MODIFICATIONS = False
+# Optional SQLite-only tuning. Unspecified keys use the built-in defaults;
+# setting a value to None leaves that SQLite PRAGMA unchanged. These settings
+# apply to SQLAlchemy connections (including binds), not the separate Huey queue.
+# They do not affect PostgreSQL or other SQLAlchemy database backends.
+#
+# Built-in defaults: foreign_keys=True, journal_mode="WAL", busy_timeout=5000
+# (milliseconds), trusted_schema=False. The remaining settings are None.
+#
+# WAL requires a file-backed database on a local filesystem. SQLite also needs
+# directory write access for the -wal and -shm sidecar files. In-memory SQLite
+# ignores journal_mode, wal_autocheckpoint, and journal_size_limit here.
+#
+# SQLITE_PRAGMAS = {
+# "busy_timeout": 10000, # milliseconds waiting for a write lock
+# "synchronous": "NORMAL", # faster WAL commits; reduced crash durability
+# "cache_size": -8192, # approx. 8 MiB per connection (negative = KiB)
+# "wal_autocheckpoint": 1000, # pages; SQLite's built-in default
+# "journal_size_limit": 67108864, # bytes of retained journal/WAL storage
+# "journal_mode": "WAL", # or "DELETE", "TRUNCATE", "PERSIST"
+# "foreign_keys": True, # enforce foreign key constraints
+# "trusted_schema": False, # disable untrusted schema functions
+# }
+# You may also set one key through the environment, for example:
+# WEBMENTIONS_SSG_SQLITE_PRAGMAS__CACHE_SIZE=-8192
+# (environment values are parsed as JSON; lower/upper-case keys are accepted).
+# Only known PRAGMAs and correctly typed values are accepted at startup.
+# "synchronous" also accepts "FULL", "EXTRA" or "OFF" (unsafe); None leaves
+# SQLite's choice alone. Optimizer PRAGMA optimize is NOT run at connect time.
+
# ---------------------------------------------------------------------------
# Logging
# ---------------------------------------------------------------------------
diff --git a/tests/conftest.py b/tests/conftest.py
index d4336b0..4b1da24 100644
--- a/tests/conftest.py
+++ b/tests/conftest.py
@@ -17,13 +17,15 @@ def app() -> Iterator[Flask]:
application = create_app(TestingConfig)
with application.app_context():
- db.create_all()
+ # Only create the default database. Separate tests register transient
+ # bind metadata on the shared SQLAlchemy extension.
+ db.create_all(bind_key=None)
yield application
with application.app_context():
db.session.remove()
- db.drop_all()
+ db.drop_all(bind_key=None)
db.engine.dispose()
diff --git a/tests/test_config.py b/tests/test_config.py
index 81ff498..ca87403 100644
--- a/tests/test_config.py
+++ b/tests/test_config.py
@@ -497,3 +497,64 @@ def test_proxy_fix_can_be_configured_via_environment(
app = create_app(EnvProxyConfig)
assert app.config["PROXY_FIX_TRUSTED_HOPS"] == 1
assert app.wsgi_app.__class__.__name__ == "ProxyFix"
+
+
+def test_sqlite_pragma_overrides_keep_default_settings(tmp_path: Path) -> None:
+ class PartialSQLiteConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "partial-sqlite-test.log")
+ SQLITE_PRAGMAS = {"busy_timeout": 10000, "synchronous": "normal"}
+
+ app = create_app(PartialSQLiteConfig)
+ assert app.config["SQLITE_PRAGMAS"] == {
+ "foreign_keys": True,
+ "journal_mode": "WAL",
+ "busy_timeout": 10000,
+ "trusted_schema": False,
+ "synchronous": "NORMAL",
+ "cache_size": None,
+ "wal_autocheckpoint": None,
+ "journal_size_limit": None,
+ }
+ # The default mapping must not be modified by individual app overrides.
+ assert TestingConfig.SQLITE_PRAGMAS["busy_timeout"] == 5000
+
+
+@pytest.mark.parametrize(
+ "invalid",
+ [
+ "PRAGMA foreign_keys=OFF",
+ None,
+ {"foreign_keys": "OFF"},
+ {"journal_mode": "WAL; DROP TABLE users"},
+ {"journal_mode": "MEMORY"},
+ {"trusted_schema": 0},
+ {"busy_timeout": -1},
+ {"busy_timeout": True},
+ {"cache_size": 2**32},
+ {"wal_autocheckpoint": -1},
+ {"journal_size_limit": -2},
+ {"synchronous": "INVALID"},
+ {"unsupported_pragma": 42},
+ ],
+)
+def test_invalid_sqlite_pragmas_fail_at_startup(
+ tmp_path: Path, invalid: object
+) -> None:
+ class BadSQLiteConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "bad-sqlite-test.log")
+ SQLITE_PRAGMAS = invalid
+
+ with pytest.raises((TypeError, ValueError), match="SQLITE_PRAGMAS"):
+ create_app(BadSQLiteConfig)
+
+
+def test_uppercase_sqlite_pragma_keys_support_environment_convention(
+ tmp_path: Path,
+) -> None:
+ class EnvStyleSQLiteConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "env-style-sqlite-test.log")
+ SQLITE_PRAGMAS = {"CACHE_SIZE": -8192, "SYNCHRONOUS": "normal"}
+
+ app = create_app(EnvStyleSQLiteConfig)
+ assert app.config["SQLITE_PRAGMAS"]["cache_size"] == -8192
+ assert app.config["SQLITE_PRAGMAS"]["synchronous"] == "NORMAL"
diff --git a/tests/test_init.py b/tests/test_init.py
index f334c88..cff42f9 100644
--- a/tests/test_init.py
+++ b/tests/test_init.py
@@ -6,10 +6,13 @@
import subprocess
import sys
+from pathlib import Path
from flask import Flask
+from sqlalchemy import create_engine
-from webmentions_ssg import VERSION, huey
+from webmentions_ssg import VERSION, create_app, db, huey
+from webmentions_ssg.config import TestingConfig
from webmentions_ssg.net import is_http_url
@@ -47,3 +50,143 @@ assert webmentions_bp.name == 'webmentions'
[sys.executable, "-c", script], capture_output=True, text=True, check=False
)
assert result.returncode == 0, result.stderr
+
+
+def test_in_memory_sqlite_enforces_foreign_keys_without_wal(app: Flask) -> None:
+ with app.app_context():
+ with db.engine.connect() as connection:
+ assert connection.exec_driver_sql("PRAGMA foreign_keys").scalar() == 1
+ assert connection.exec_driver_sql("PRAGMA journal_mode").scalar() == "memory"
+
+
+def test_file_sqlite_enables_wal_and_foreign_keys(tmp_path: Path) -> None:
+ class FileSQLiteConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "sqlite-test.log")
+ SQLALCHEMY_DATABASE_URI = f"sqlite:///{tmp_path / 'test.db'}"
+
+ app = create_app(FileSQLiteConfig)
+ with app.app_context():
+ try:
+ with db.engine.connect() as connection:
+ assert connection.exec_driver_sql("PRAGMA foreign_keys").scalar() == 1
+ assert connection.exec_driver_sql("PRAGMA journal_mode").scalar() == "wal"
+ # Foreign-key enforcement must also apply to fresh pooled connections.
+ db.engine.dispose()
+ with db.engine.connect() as connection:
+ assert connection.exec_driver_sql("PRAGMA foreign_keys").scalar() == 1
+ assert connection.exec_driver_sql("PRAGMA journal_mode").scalar() == "wal"
+ finally:
+ db.engine.dispose()
+
+
+def test_sqlite_settings_apply_to_sqlite_binds(tmp_path: Path) -> None:
+ class SQLiteBindConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "bind-test.log")
+ SQLALCHEMY_BINDS = {"archive": f"sqlite:///{tmp_path / 'archive.db'}"}
+
+ app = create_app(SQLiteBindConfig)
+ with app.app_context():
+ try:
+ with db.engines["archive"].connect() as connection:
+ assert connection.exec_driver_sql("PRAGMA foreign_keys").scalar() == 1
+ assert connection.exec_driver_sql("PRAGMA journal_mode").scalar() == "wal"
+ finally:
+ db.engines["archive"].dispose()
+ db.engine.dispose()
+
+
+def test_sqlite_connect_event_works_without_flask(tmp_path: Path) -> None:
+ """SQLAlchemy's Engine connect event configures SQLite directly."""
+ engine = create_engine(f"sqlite:///{tmp_path / 'standalone.db'}")
+ try:
+ with engine.connect() as connection:
+ assert connection.exec_driver_sql("PRAGMA foreign_keys").scalar() == 1
+ assert connection.exec_driver_sql("PRAGMA journal_mode").scalar() == "wal"
+ finally:
+ engine.dispose()
+
+
+def test_sqlite_pragmas_can_be_tuned_per_app(tmp_path: Path) -> None:
+ """Custom settings take effect on fresh connections and survive disposal."""
+
+ class TunedSQLiteConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "tuned-test.log")
+ SQLALCHEMY_DATABASE_URI = f"sqlite:///{tmp_path / 'tuned.db'}"
+ SQLITE_PRAGMAS = {
+ "foreign_keys": False,
+ "journal_mode": "DELETE",
+ "busy_timeout": 9000,
+ "trusted_schema": False,
+ "synchronous": "NORMAL",
+ "cache_size": -8192,
+ "wal_autocheckpoint": 250,
+ "journal_size_limit": 65536,
+ }
+
+ app = create_app(TunedSQLiteConfig)
+ with app.app_context():
+ try:
+ for _ in range(2):
+ with db.engine.connect() as connection:
+ for pragma, expected in (
+ ("foreign_keys", 0),
+ ("journal_mode", "delete"),
+ ("busy_timeout", 9000),
+ ("trusted_schema", 0),
+ ("synchronous", 1),
+ ("cache_size", -8192),
+ ("wal_autocheckpoint", 250),
+ ("journal_size_limit", 65536),
+ ):
+ assert (
+ connection.exec_driver_sql(f"PRAGMA {pragma}").scalar()
+ == expected
+ )
+ db.engine.dispose()
+ finally:
+ db.engine.dispose()
+
+
+def test_in_memory_sqlite_skips_file_only_pragmas(tmp_path: Path) -> None:
+ class MemorySQLiteConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "memory-test.log")
+ SQLITE_PRAGMAS = {
+ "journal_mode": "WAL",
+ "wal_autocheckpoint": 333,
+ "journal_size_limit": 65536,
+ "cache_size": -4096,
+ }
+
+ app = create_app(MemorySQLiteConfig)
+ with app.app_context():
+ try:
+ with db.engine.connect() as connection:
+ assert connection.exec_driver_sql("PRAGMA journal_mode").scalar() == "memory"
+ assert connection.exec_driver_sql("PRAGMA cache_size").scalar() == -4096
+ assert connection.exec_driver_sql("PRAGMA wal_autocheckpoint").scalar() == 1000
+ finally:
+ db.engine.dispose()
+
+
+def test_sqlite_pragma_settings_also_apply_to_binds(tmp_path: Path) -> None:
+ class TunedBindConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "bind-tuned-test.log")
+ SQLITE_PRAGMAS = {"busy_timeout": 1234, "cache_size": -2048}
+ SQLALCHEMY_BINDS = {"archive": f"sqlite:///{tmp_path / 'archive-tuned.db'}"}
+
+ app = create_app(TunedBindConfig)
+ with app.app_context():
+ try:
+ with db.engines["archive"].connect() as connection:
+ assert connection.exec_driver_sql("PRAGMA busy_timeout").scalar() == 1234
+ assert connection.exec_driver_sql("PRAGMA cache_size").scalar() == -2048
+ finally:
+ db.engines["archive"].dispose()
+ db.engine.dispose()
+
+
+def test_sqlite_connect_listener_does_not_touch_other_dbapis() -> None:
+ from webmentions_ssg.extensions import set_sqlite_pragmas
+
+ # Non-SQLite DB-API connections bypass the listener completely.
+ set_sqlite_pragmas(object(), object())
diff --git a/webmentions_ssg/__init__.py b/webmentions_ssg/__init__.py
index 853045d..174250e 100644
--- a/webmentions_ssg/__init__.py
+++ b/webmentions_ssg/__init__.py
@@ -60,6 +60,11 @@ def create_app(config_class: type[DefaultConfig] | None = None) -> Flask:
)
app.config.from_object(config_class)
+ # Flask's nested prefixed environment overrides mutate existing dicts.
+ # Avoid modifying the class-level defaults or another app's configuration.
+ if isinstance(app.config.get("SQLITE_PRAGMAS"), dict):
+ app.config["SQLITE_PRAGMAS"] = app.config["SQLITE_PRAGMAS"].copy()
+
if not app.testing:
# Local development may omit its config file; production must not.
app.config.from_pyfile(app.config["CONFIG_FILE"], silent=app.debug)
diff --git a/webmentions_ssg/config.py b/webmentions_ssg/config.py
index 28ab43d..be54989 100644
--- a/webmentions_ssg/config.py
+++ b/webmentions_ssg/config.py
@@ -13,6 +13,25 @@ from .hostname import normalize_hostname
MIN_SECRET_KEY_LENGTH = 32
+# Only supported, validated PRAGMAs may be passed to SQLite's connect event.
+# None means "leave SQLite's own default unchanged". Override individual
+# entries through SQLITE_PRAGMAS in the deployment config.
+DEFAULT_SQLITE_PRAGMAS: dict[str, bool | int | str | None] = {
+ "foreign_keys": True,
+ "journal_mode": "WAL",
+ "busy_timeout": 5000, # Milliseconds; matches sqlite3's default timeout.
+ "trusted_schema": False,
+ "synchronous": None, # Keep SQLite's durability default unless requested.
+ "cache_size": None, # Positive = pages; negative = approximate KiB.
+ "wal_autocheckpoint": None, # Pages; SQLite defaults to 1000.
+ "journal_size_limit": None, # Bytes; -1 removes the size limit.
+}
+
+SQLITE_JOURNAL_MODES = frozenset({"DELETE", "TRUNCATE", "PERSIST", "WAL"})
+SQLITE_SYNCHRONOUS_MODES = frozenset({"OFF", "NORMAL", "FULL", "EXTRA"})
+SQLITE_INT_MAX = 2**31 - 1
+SQLITE_INT64_MAX = 2**63 - 1
+
class DefaultConfig:
# Application
@@ -34,6 +53,7 @@ class DefaultConfig:
# Database
SQLALCHEMY_DATABASE_URI = "sqlite:///webmentions-ssg.db"
+ SQLITE_PRAGMAS = DEFAULT_SQLITE_PRAGMAS
# Logging
LOG_FILE = "/var/log/webmentions-ssg.log"
@@ -194,6 +214,7 @@ def validated_config_overrides(
unsafe.
"""
normalized: dict[str, object] = {}
+ normalized["SQLITE_PRAGMAS"] = validate_sqlite_pragmas(config["SQLITE_PRAGMAS"])
for key in ("ALLOWED_TARGET_HOSTNAMES", "IGNORED_TARGET_HOSTNAMES"):
try:
value = config[key]
@@ -258,3 +279,57 @@ def validated_config_overrides(
"running in production (memory:// and blackhole:// are unsafe)."
)
return normalized
+
+
+def validate_sqlite_pragmas(value: object) -> dict[str, bool | int | str | None]:
+ """Merge user overrides with safe defaults and reject invalid PRAGMAs.
+
+ SQLite doesn't accept bind parameters in PRAGMA assignments. A strict
+ allowlist and type checks ensure only known values enter the SQL statements.
+ """
+ if not isinstance(value, dict):
+ raise TypeError("SQLITE_PRAGMAS must be a dictionary.")
+
+ pragmas = DEFAULT_SQLITE_PRAGMAS.copy()
+ for raw_name, setting in value.items():
+ # Flask's from_prefixed_env uses the casing in environment variable
+ # names, usually upper case. Accept both forms without exposing SQL.
+ if not isinstance(raw_name, str):
+ raise ValueError(f"SQLITE_PRAGMAS: unsupported PRAGMA {raw_name!r}.")
+
+ if (name := raw_name.lower()) not in pragmas:
+ raise ValueError(f"SQLITE_PRAGMAS: unsupported PRAGMA {raw_name!r}.")
+
+ if name in {"foreign_keys", "trusted_schema"}:
+ if type(setting) is not bool and setting is not None:
+ raise ValueError(f"SQLITE_PRAGMAS[{name!r}] must be a boolean or None.")
+ elif name in {"journal_mode", "synchronous"}:
+ allowed = (
+ SQLITE_JOURNAL_MODES
+ if name == "journal_mode"
+ else SQLITE_SYNCHRONOUS_MODES
+ )
+ if setting is not None:
+ if not isinstance(setting, str) or setting.upper() not in allowed:
+ raise ValueError(
+ f"SQLITE_PRAGMAS[{name!r}] must be one of "
+ f"{', '.join(sorted(allowed))}, or None."
+ )
+ setting = setting.upper()
+ elif setting is not None:
+ if type(setting) is not int:
+ raise ValueError(
+ f"SQLITE_PRAGMAS[{name!r}] must be an integer or None."
+ )
+ if name == "cache_size":
+ valid = -SQLITE_INT_MAX <= setting <= SQLITE_INT_MAX
+ elif name == "journal_size_limit":
+ valid = -1 <= setting <= SQLITE_INT64_MAX
+ else:
+ valid = 0 <= setting <= SQLITE_INT_MAX
+ if not valid:
+ raise ValueError(f"SQLITE_PRAGMAS[{name!r}] is out of range.")
+
+ pragmas[name] = setting
+
+ return pragmas
diff --git a/webmentions_ssg/extensions.py b/webmentions_ssg/extensions.py
index d515d24..b0fbec7 100644
--- a/webmentions_ssg/extensions.py
+++ b/webmentions_ssg/extensions.py
@@ -4,6 +4,9 @@
"""Unbound extensions shared across Flask application instances."""
+import sqlite3
+
+from flask import current_app, has_app_context
from flask_bootstrap import Bootstrap5
from flask_limiter import Limiter
from flask_limiter.util import get_remote_address
@@ -11,8 +14,11 @@ from flask_mail import Mail
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy
from flask_wtf.csrf import CSRFProtect
+from sqlalchemy import event
+from sqlalchemy.engine import Engine
from sqlalchemy.orm import DeclarativeBase
+from .config import DEFAULT_SQLITE_PRAGMAS
from .tasks.extension import FlaskHuey
@@ -27,3 +33,67 @@ migrate = Migrate()
huey = FlaskHuey()
mail = Mail()
limiter = Limiter(key_func=get_remote_address)
+
+
+@event.listens_for(Engine, "connect")
+def set_sqlite_pragmas(connection: object, connection_record: object) -> None:
+ """Apply the configured settings to each new SQLAlchemy SQLite connection.
+
+ App-managed connections use the current Flask app's validated settings.
+ Standalone SQLAlchemy connections use the documented defaults.
+ """
+ if not isinstance(connection, sqlite3.Connection):
+ return
+
+ pragmas = (
+ current_app.config["SQLITE_PRAGMAS"]
+ if has_app_context() and "SQLITE_PRAGMAS" in current_app.config
+ else DEFAULT_SQLITE_PRAGMAS
+ )
+
+ # SQLite ignores foreign_keys inside a transaction. Python's sqlite3
+ # autocommit=False opens a transaction immediately, so temporarily enable
+ # autocommit while configuring the new DB-API connection.
+ previous_autocommit = connection.autocommit
+ connection.autocommit = True
+ try:
+ cursor = connection.cursor()
+ try:
+ cursor.execute("PRAGMA database_list")
+ file_backed = any(
+ name == "main" and bool(filename)
+ for _, name, filename in cursor.fetchall()
+ )
+
+ # Set lock waiting time before the WAL transition, which can lock.
+ if (timeout := pragmas["busy_timeout"]) is not None:
+ cursor.execute(f"PRAGMA busy_timeout = {timeout}")
+
+ for name in ("foreign_keys", "trusted_schema"):
+ if (enabled := pragmas[name]) is not None:
+ cursor.execute(f"PRAGMA {name} = {int(enabled)}")
+
+ # Journal modes apply to the database file, not in-memory SQLite.
+ if file_backed and (journal_mode := pragmas["journal_mode"]) is not None:
+ cursor.execute("PRAGMA journal_mode")
+ if cursor.fetchone()[0].upper() != journal_mode:
+ cursor.execute(f"PRAGMA journal_mode = {journal_mode}")
+ applied_mode = cursor.fetchone()[0].upper()
+ if applied_mode != journal_mode:
+ raise RuntimeError(
+ f"SQLite journal_mode {journal_mode} was not applied "
+ f"(using {applied_mode} instead)."
+ )
+
+ for name in ("synchronous", "cache_size"):
+ if (setting := pragmas[name]) is not None:
+ cursor.execute(f"PRAGMA {name} = {setting}")
+
+ if file_backed:
+ for name in ("wal_autocheckpoint", "journal_size_limit"):
+ if (setting := pragmas[name]) is not None:
+ cursor.execute(f"PRAGMA {name} = {setting}")
+ finally:
+ cursor.close()
+ finally:
+ connection.autocommit = previous_autocommit