Anthony Rose
459372815c
* feat: add Alembic database migration framework Integrate Alembic for versioned schema management so future updates can modify database schemas incrementally without requiring full resets. - Add alembic dependency and migration directory structure - Add baseline no-op migration (0001) that existing DBs are stamped to - Add _alembic_cfg(), _stamp_alembic_head(), migrate_db(), backup_db() - Stamp database at Alembic head during startup_db() - backup_db() supports SQLite (file copy) and MySQL (mysqldump via MYSQL_PWD env var for security, with port parsing) - 15 tests covering infrastructure, backup, pre-Alembic upgrade path, real migration apply/downgrade, failed migration rollback, and schema parity verification - Update CHANGELOG and database documentation Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com> * fix: address PR review feedback for Alembic integration - Only stamp untracked databases at baseline "0001" (not "head" on every boot), so future migrations are not silently skipped - Move Alembic stamp outside DB-setup try/except so failures get a specific error message instead of misleading "run --clean" advice - Use --defaults-extra-file with temp .cnf (mode 0600) for MySQL backup credentials; quote passwords with special characters - Use sqlite3.Connection.backup() API for WAL-safe SQLite backups - Narrow FileNotFoundError catch to just subprocess.run; add broad except for unexpected MySQL backup failures with cleanup - Handle unknown DB type with log warning; clean up partial files on all failure paths; use stderr.decode(errors='replace') - Narrow _is_expected_diff filter to specific named items - Add 6 new tests: MySQL success (verifies --defaults-extra-file, cnf permissions/cleanup), MySQL dump failure, missing mysqldump, unknown DB type, port parsing, startup no-restamp - Add conftest safety net for stale test migration files - Use poetry run in docs; add explanatory pyproject.toml comments Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com> * fix: handle unique_check index in autogenerate diff filter The MySQL unique_check generated column also has a unique index named "unique_check" that Alembic detects as a remove_index diff. Add it to the expected index names alongside agent_checkin_idx. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com> --------- Co-authored-by: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Database
Beginning in Empire 5.0, MySQL is the default database. Trying to support multi-user setups and expanding our usage of the database started to reach limitations in SQLite. SQLite is still supported, and we run the full test suite against it, but is not recommended.
For database configuration see the Configuration section.
Setup
The install script will install MySQL if you install on one of the supported operating systems. If you want to switch between MySQL and SQLite, change the use property in the database section of the configuration file.
Config values can also be set via environment variables. The convention is to use EMPIRE_TOPLEVEL__NEXT_SETTING for each setting in config.yaml.
For example: EMPIRE_DATABASE__USE='mysql' will override the database.use setting in config.yaml.
Migrations
Empire uses Alembic for database schema migrations. This allows schema changes to be applied incrementally without requiring a full database reset.
On startup, startup_db() stamps the database at the current Alembic head revision. When future updates include schema changes, migrations can be applied via the migrate_db() function without data loss.
Creating a new migration
After modifying SQLAlchemy models in empire/server/core/db/models.py, generate a migration:
poetry run alembic revision --autogenerate -m "describe the change"
Review the generated file in empire/server/core/db/alembic/versions/ to ensure the upgrade() and downgrade() functions are correct, then commit it with your model changes.
Backing up before migrations
The backup_db() function creates a timestamped backup before applying migrations:
- SQLite: copies the database file to
~/.local/share/empire/backups/ - MySQL: runs
mysqldumpto the same backup directory
Docker
The Docker image still defaults to SQLite. To use MySQL, you can change config.yaml or utilize the DATABASE_USE enviornment variable. For example docker run -p 3306:3306 -p 1337:1337 -e DATABASE_USE='mysql' -it bcsecurity/empire:latest.
The Docker image does not contain MySQL, so you will need to run a MySQL container or install MySQL on the host machine.