aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorDennis Fink2026-10-27 23:16:27 +0100
committerDennis Fink2026-10-27 23:18:45 +0100
commit17ab56a3c7d3dbb3d66edf380ab637a61cefe50c (patch)
tree1626e3e1d402a6614a1cec4c2c6719d301f39bac
parent5afb245d13b91047d68d7b7343245c41b6460953 (diff)
downloadwebmentions-ssg-17ab56a3c7d3dbb3d66edf380ab637a61cefe50c.tar.gz
webmentions-ssg-17ab56a3c7d3dbb3d66edf380ab637a61cefe50c.zip
feat(core): add database CLI
Make database initialization and upgrades explicit so new installations cannot silently modify an existing or unversioned schema. Keep Flask-Migrate commands available separately and package the migration environment with the application for reliable deployment.
-rw-r--r--tests/cli/test_database.py199
-rw-r--r--webmentions_ssg/__init__.py22
-rw-r--r--webmentions_ssg/cli/database.py49
-rw-r--r--webmentions_ssg/cli/mail.py4
-rw-r--r--webmentions_ssg/cli/users.py4
-rw-r--r--webmentions_ssg/migrations/README (renamed from migrations/README)0
-rw-r--r--webmentions_ssg/migrations/alembic.ini (renamed from migrations/alembic.ini)0
-rw-r--r--webmentions_ssg/migrations/env.py (renamed from migrations/env.py)14
-rw-r--r--webmentions_ssg/migrations/script.py.mako (renamed from migrations/script.py.mako)0
-rw-r--r--webmentions_ssg/migrations/versions/681a2a9a1bc0_webmention_sender.py (renamed from migrations/versions/681a2a9a1bc0_webmention_sender.py)0
-rw-r--r--webmentions_ssg/migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py (renamed from migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py)0
-rw-r--r--webmentions_ssg/migrations/versions/f63641044cc1_initial_database_schema.py (renamed from migrations/versions/f63641044cc1_initial_database_schema.py)0
12 files changed, 270 insertions, 22 deletions
diff --git a/tests/cli/test_database.py b/tests/cli/test_database.py
new file mode 100644
index 0000000..385e1e6
--- /dev/null
+++ b/tests/cli/test_database.py
@@ -0,0 +1,199 @@
+# SPDX-FileCopyrightText: 2026 Dennis Fink <me+coding@dennisfink.me>
+#
+# SPDX-License-Identifier: BSD-3-Clause
+
+"""Tests for the simplified database CLI and Flask-Migrate integration."""
+
+import logging
+from collections.abc import Iterator
+from pathlib import Path
+
+import pytest
+import sqlalchemy as sa
+from alembic.script import ScriptDirectory
+from flask import Flask
+from flask_migrate import upgrade as migrate_upgrade
+from sqlalchemy import inspect
+
+from webmentions_ssg import DATABASE, MIGRATE, create_app
+from webmentions_ssg.config import TestingConfig
+
+
+@pytest.fixture
+def database_app(tmp_path: Path, monkeypatch: pytest.MonkeyPatch) -> Iterator[Flask]:
+ monkeypatch.chdir(tmp_path)
+
+ class DatabaseTestingConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "database-cli.log")
+ SQLALCHEMY_DATABASE_URI = f"sqlite:///{tmp_path / 'database.sqlite'}"
+
+ app = create_app(DatabaseTestingConfig)
+ yield app
+
+ with app.app_context():
+ DATABASE.session.remove()
+ DATABASE.engine.dispose()
+
+
+def _head_revision(app: Flask) -> str:
+ with app.app_context():
+ return ScriptDirectory.from_config(MIGRATE.get_config()).get_current_head()
+
+
+def _database_revision(app: Flask) -> str:
+ with app.app_context(), DATABASE.engine.connect() as connection:
+ return connection.execute(
+ sa.text("SELECT version_num FROM alembic_version")
+ ).scalar_one()
+
+
+def test_database_commands_are_registered_without_hiding_flask_migrate(
+ database_app: Flask,
+) -> None:
+ runner = database_app.test_cli_runner()
+
+ result = runner.invoke(args=["db", "--help"])
+ assert result.exit_code == 0, result.output
+ assert "create-db" in result.output
+ assert "upgrade" in result.output
+
+ result = runner.invoke(args=["migrate", "--help"])
+ assert result.exit_code == 0, result.output
+ assert "downgrade" in result.output
+ assert "revision" in result.output
+
+
+def test_create_db_builds_schema_and_records_latest_migration(
+ database_app: Flask,
+) -> None:
+ result = database_app.test_cli_runner().invoke(args=["db", "create-db"])
+
+ assert result.exit_code == 0, result.output
+ assert "Database created" in result.output
+ assert _database_revision(database_app) == _head_revision(database_app)
+
+ with database_app.app_context():
+ table_names = set(inspect(DATABASE.engine).get_table_names())
+ assert {"alembic_version", "users", "received_webmentions"} <= table_names
+
+
+def test_create_db_refuses_existing_database_without_modifying_it(
+ database_app: Flask,
+) -> None:
+ runner = database_app.test_cli_runner()
+ assert runner.invoke(args=["db", "create-db"]).exit_code == 0
+ revision_before = _database_revision(database_app)
+
+ result = runner.invoke(args=["db", "create-db"])
+
+ assert result.exit_code != 0
+ assert "already contains tables" in result.output
+ assert _database_revision(database_app) == revision_before
+
+
+def test_create_db_refuses_unversioned_existing_tables(database_app: Flask) -> None:
+ with database_app.app_context(), DATABASE.engine.begin() as connection:
+ connection.execute(sa.text("CREATE TABLE legacy_data (id INTEGER PRIMARY KEY)"))
+
+ result = database_app.test_cli_runner().invoke(args=["db", "create-db"])
+
+ assert result.exit_code != 0
+ assert "already contains tables" in result.output
+ with database_app.app_context():
+ assert "legacy_data" in inspect(DATABASE.engine).get_table_names()
+ assert "alembic_version" not in inspect(DATABASE.engine).get_table_names()
+
+
+def test_upgrade_requires_an_initialized_database(database_app: Flask) -> None:
+ result = database_app.test_cli_runner().invoke(args=["db", "upgrade"])
+
+ assert result.exit_code != 0
+ assert "flask db create-db" in result.output
+
+
+def test_upgrade_refuses_unversioned_existing_database(database_app: Flask) -> None:
+ with database_app.app_context(), DATABASE.engine.begin() as connection:
+ connection.execute(sa.text("CREATE TABLE legacy_data (id INTEGER PRIMARY KEY)"))
+
+ result = database_app.test_cli_runner().invoke(args=["db", "upgrade"])
+
+ assert result.exit_code != 0
+ assert "No Flask-Migrate revision table" in result.output
+ with database_app.app_context():
+ assert "legacy_data" in inspect(DATABASE.engine).get_table_names()
+
+
+def test_upgrade_applies_pending_migrations_and_preserves_data(
+ database_app: Flask,
+) -> None:
+ # Start with the first migration, as if this were an older deployment.
+ with database_app.app_context():
+ base_revision = ScriptDirectory.from_config(MIGRATE.get_config()).get_base()
+ assert base_revision is not None
+ assert base_revision != _head_revision(database_app)
+ migrate_upgrade(revision=base_revision)
+
+ with DATABASE.engine.begin() as connection:
+ connection.execute(
+ sa.text(
+ "INSERT INTO users (username, password_hash) "
+ "VALUES (:username, :password_hash)"
+ ),
+ {"username": "existing-user", "password_hash": "existing-hash"},
+ )
+
+ result = database_app.test_cli_runner().invoke(args=["db", "upgrade"])
+
+ assert result.exit_code == 0, result.output
+ assert "Database upgraded" in result.output
+ assert _database_revision(database_app) == _head_revision(database_app)
+
+ with database_app.app_context(), DATABASE.engine.connect() as connection:
+ assert (
+ connection.execute(
+ sa.text("SELECT password_hash FROM users WHERE username = :username"),
+ {"username": "existing-user"},
+ ).scalar_one()
+ == "existing-hash"
+ )
+ assert "sent_webmentions" in inspect(connection).get_table_names()
+
+
+def test_upgrade_is_safe_to_run_when_already_current(database_app: Flask) -> None:
+ runner = database_app.test_cli_runner()
+ assert runner.invoke(args=["db", "create-db"]).exit_code == 0
+
+ result = runner.invoke(args=["db", "upgrade"])
+
+ assert result.exit_code == 0, result.output
+ assert _database_revision(database_app) == _head_revision(database_app)
+
+
+def test_create_db_does_not_report_success_when_migration_fails(
+ database_app: Flask, monkeypatch: pytest.MonkeyPatch
+) -> None:
+ from webmentions_ssg.cli import database as database_cli
+
+ def fail_upgrade() -> None:
+ raise RuntimeError("simulated migration failure")
+
+ monkeypatch.setattr(database_cli, "migrate_upgrade", fail_upgrade)
+
+ result = database_app.test_cli_runner().invoke(args=["db", "create-db"])
+
+ assert result.exit_code != 0
+ assert isinstance(result.exception, RuntimeError)
+ assert "Database created" not in result.output
+
+
+def test_create_db_preserves_logging(
+ database_app: Flask, caplog: pytest.LogCaptureFixture
+) -> None:
+ with caplog.at_level(logging.WARNING):
+ result = database_app.test_cli_runner().invoke(args=["db", "create-db"])
+
+ assert result.exit_code == 0, result.output
+
+ database_app.logger.warning("Logging still works after migration")
+
+ assert "Logging still works after migration" in caplog.text
diff --git a/webmentions_ssg/__init__.py b/webmentions_ssg/__init__.py
index 3dda31b..7eb8b98 100644
--- a/webmentions_ssg/__init__.py
+++ b/webmentions_ssg/__init__.py
@@ -9,10 +9,9 @@ from pathlib import Path
import coloredlogs
from flask import Flask
-from flask.cli import AppGroup
from flask_bootstrap import Bootstrap5
from flask_mail import Mail
-from flask_migrate import Migrate, upgrade
+from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy
from flask_wtf.csrf import CSRFProtect
from sqlalchemy.orm import DeclarativeBase
@@ -36,8 +35,6 @@ MIGRATE = Migrate()
HUEY = Huey()
MAIL = Mail()
-DB_CLI = AppGroup("db", help="Handle database")
-
def create_app(config_class: type[DefaultConfig] | None = None) -> Flask:
"""
@@ -103,7 +100,12 @@ def create_app(config_class: type[DefaultConfig] | None = None) -> Flask:
)
DATABASE.init_app(app)
- MIGRATE.init_app(app, db=DATABASE)
+ MIGRATE.init_app(
+ app,
+ db=DATABASE,
+ directory=str(Path(__file__).resolve().parent / "migrations"),
+ command="migrate",
+ )
CSRF.init_app(app)
MAIL.init_app(app)
@@ -134,12 +136,8 @@ def create_app(config_class: type[DefaultConfig] | None = None) -> Flask:
app.cli.add_command(MAIL_CLI)
- return app
+ from .cli.database import DB_CLI
+ app.cli.add_command(DB_CLI)
-@DB_CLI.command("create")
-def create_db() -> None:
- """
- Upgrade the database to the latest migration revision.
- """
- upgrade()
+ return app
diff --git a/webmentions_ssg/cli/database.py b/webmentions_ssg/cli/database.py
new file mode 100644
index 0000000..e918600
--- /dev/null
+++ b/webmentions_ssg/cli/database.py
@@ -0,0 +1,49 @@
+# SPDX-FileCopyrightText: 2026 Dennis Fink <me+coding@dennisfink.me>
+#
+# SPDX-License-Identifier: BSD-3-Clause
+
+"""Simple database commands built on Flask-Migrate."""
+
+import click
+from flask.cli import AppGroup
+from flask_migrate import upgrade as migrate_upgrade
+from sqlalchemy import inspect
+
+from .. import DATABASE
+
+DB_CLI = AppGroup("db", help="Handle database")
+
+
+def _table_names() -> set[str]:
+ """Return existing table names without modifying the database schema."""
+ with DATABASE.engine.connect() as connection:
+ return set(inspect(connection).get_table_names())
+
+
+@DB_CLI.command("create-db", help="Create a new database using all migrations")
+def create_db() -> None:
+ """Initialize an empty database and record its Alembic revision."""
+ if _table_names():
+ raise click.ClickException(
+ "The database already contains tables. "
+ "Use 'flask db upgrade' for an existing migrated database."
+ )
+
+ # Applying migrations (rather than create_all()) keeps alembic_version in sync.
+ migrate_upgrade()
+ click.echo("Database created at the latest migration revision.")
+
+
+@DB_CLI.command("upgrade", help="Upgrade an existing database to the latest revision")
+def upgrade_db() -> None:
+ """Apply pending migrations to a previously initialized database."""
+ if "alembic_version" not in _table_names():
+ raise click.ClickException(
+ "No Flask-Migrate revision table found. "
+ "For a new, empty database run 'flask db create-db'. "
+ "For a legacy database, inspect its schema and use "
+ "'flask migrate stamp' only if appropriate."
+ )
+
+ migrate_upgrade()
+ click.echo("Database upgraded to the latest migration revision.")
diff --git a/webmentions_ssg/cli/mail.py b/webmentions_ssg/cli/mail.py
index 07dc50b..3ea903e 100644
--- a/webmentions_ssg/cli/mail.py
+++ b/webmentions_ssg/cli/mail.py
@@ -1,3 +1,7 @@
+# SPDX-FileCopyrightText: 2026 Dennis Fink <me+coding@dennisfink.me>
+#
+# SPDX-License-Identifier: BSD-3-Clause
+
import click
from flask import current_app
from flask.cli import AppGroup
diff --git a/webmentions_ssg/cli/users.py b/webmentions_ssg/cli/users.py
index 2dcb17e..4ece2f4 100644
--- a/webmentions_ssg/cli/users.py
+++ b/webmentions_ssg/cli/users.py
@@ -1,3 +1,7 @@
+# SPDX-FileCopyrightText: 2026 Dennis Fink <me+coding@dennisfink.me>
+#
+# SPDX-License-Identifier: BSD-3-Clause
+
import click
import sqlalchemy as sa
from flask.cli import AppGroup
diff --git a/migrations/README b/webmentions_ssg/migrations/README
index 0e04844..0e04844 100644
--- a/migrations/README
+++ b/webmentions_ssg/migrations/README
diff --git a/migrations/alembic.ini b/webmentions_ssg/migrations/alembic.ini
index bdfa231..bdfa231 100644
--- a/migrations/alembic.ini
+++ b/webmentions_ssg/migrations/alembic.ini
diff --git a/migrations/env.py b/webmentions_ssg/migrations/env.py
index fa912c3..e2e0ec9 100644
--- a/migrations/env.py
+++ b/webmentions_ssg/migrations/env.py
@@ -3,7 +3,6 @@
# SPDX-License-Identifier: BSD-3-Clause
import logging
-from logging.config import fileConfig
from alembic import context
from flask import current_app
@@ -12,19 +11,14 @@ from flask import current_app
# access to the values within the .ini file in use.
config = context.config
-# Interpret the config file for Python logging.
-# This line sets up loggers basically.
-fileConfig(config.config_file_name)
+# Flask already configures application logging.
+# Do not let Alembic replace existing logging handlers,
+# including pytest's log capture handler.
logger = logging.getLogger("alembic.env")
def get_engine():
- try:
- # this works with Flask-SQLAlchemy<3 and Alchemical
- return current_app.extensions["migrate"].db.get_engine()
- except TypeError, AttributeError:
- # this works with Flask-SQLAlchemy>=3
- return current_app.extensions["migrate"].db.engine
+ return current_app.extensions["migrate"].db.engine
def get_engine_url():
diff --git a/migrations/script.py.mako b/webmentions_ssg/migrations/script.py.mako
index 2c01563..2c01563 100644
--- a/migrations/script.py.mako
+++ b/webmentions_ssg/migrations/script.py.mako
diff --git a/migrations/versions/681a2a9a1bc0_webmention_sender.py b/webmentions_ssg/migrations/versions/681a2a9a1bc0_webmention_sender.py
index 23bf2dd..23bf2dd 100644
--- a/migrations/versions/681a2a9a1bc0_webmention_sender.py
+++ b/webmentions_ssg/migrations/versions/681a2a9a1bc0_webmention_sender.py
diff --git a/migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py b/webmentions_ssg/migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py
index 4bb7a7c..4bb7a7c 100644
--- a/migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py
+++ b/webmentions_ssg/migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py
diff --git a/migrations/versions/f63641044cc1_initial_database_schema.py b/webmentions_ssg/migrations/versions/f63641044cc1_initial_database_schema.py
index 5017bc3..5017bc3 100644
--- a/migrations/versions/f63641044cc1_initial_database_schema.py
+++ b/webmentions_ssg/migrations/versions/f63641044cc1_initial_database_schema.py