DATABASE.md

Database Guide

This guide covers database backends, migration, and how to switch an existing installation between SQLite and MySQL/MariaDB.

Supported Backends

dmarcreport supports:

• sqlite for small and simple installations
• mysql for server-backed installations
• mariadb for server-backed installations

For MySQL/MariaDB, keep the setup on the shared subset so it stays easy to move between the two:

• engine: InnoDB
• charset: utf8mb4
• collation: utf8mb4_swedish_ci preferred

If your database already uses utf8mb4_unicode_ci, that is also acceptable.

Direct mysql <-> mariadb migration is intentionally blocked for now. The supported migration paths are:

• sqlite -> mysql
• sqlite -> mariadb
• mysql -> sqlite
• mariadb -> sqlite

Basic Config Examples

SQLite:

database:
  driver: sqlite
  path: ./data/dmarcreport.db
  retention_days: 365

MariaDB:

database:
  driver: mariadb
  host: 127.0.0.1
  port: 3306
  username: dmarcreport
  password: change-me
  name: dmarcreport
  charset: utf8mb4
  collation: utf8mb4_swedish_ci

MySQL:

database:
  driver: mysql
  host: 127.0.0.1
  port: 3306
  username: dmarcreport
  password: change-me
  name: dmarcreport
  charset: utf8mb4
  collation: utf8mb4_swedish_ci

Provisioning MySQL/MariaDB

Example SQL:

CREATE DATABASE dmarcreport
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_swedish_ci;

CREATE USER 'dmarcreport'@'localhost' IDENTIFIED BY 'change-me';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP
  ON dmarcreport.* TO 'dmarcreport'@'localhost';

FLUSH PRIVILEGES;

Required privileges:

• Runtime: SELECT, INSERT, UPDATE, DELETE
• Schema setup/migration: CREATE, ALTER, INDEX, DROP

Checking the Active Backend

Validate the current config:

dmarcreport check config

Inspect the current database connection:

dmarcreport check db

check db reports:

• active driver/backend
• connection success
• database or file in use
• visible tables
• schema version
• active retention policy
• SQLite connection settings such as journal mode, temp storage, and cache size
• best-effort privilege diagnostics for MySQL/MariaDB

If you want to run backend-specific maintenance on demand:

dmarcreport db optimize

That runs conservative maintenance:

• SQLite: PRAGMA optimize, ANALYZE, and a WAL checkpoint when WAL mode is active
• MySQL/MariaDB: ANALYZE TABLE on the application tables

Migration Workflow

The migration command uses:

• the active config (-c or default config) as the source
• --target-config as the destination

Command shape:

dmarcreport -c source.yaml db migrate --to mariadb --target-config target.yaml

Rules:

• the target database must already exist
• the target must be empty
• same-backend migration is blocked
• mysql <-> mariadb direct migration is blocked for now

Switching from SQLite to MariaDB/MySQL

This is the common path when an installation outgrows SQLite.

1. Back up your current SQLite database file.
2. Keep your current SQLite config in a separate file, for example:

database:
  driver: sqlite
  path: ./data/dmarcreport.db

3. Update your main config to use driver: mariadb or driver: mysql.
4. Run:

dmarcreport check db

Expected result before migration:

• connection succeeds
• target database is empty or only has an empty schema
• permissions are OK

5. Run the migration:

dmarcreport -c dmarcreport-sqlite.yaml db migrate --to mariadb --target-config dmarcreport.yaml

6. Verify the target:

dmarcreport check db

7. Run a normal application command against the server database, for example:

dmarcreport generate

8. Run a short post-migration verification:

• dmarcreport check db
• dmarcreport generate
• one normal fetch cycle if you want end-to-end confirmation
• one notification test if mail delivery is part of the installation

9. Update cron, systemd, Task Scheduler, wrapper scripts, or deployment scripts if they still point to an old SQLite-specific config file.

10. Keep the old SQLite database and source config until you are satisfied that the MariaDB/MySQL-backed installation is working normally.

At that point, your installation is switched. New fetches, reports, alerts, and site generation will use MariaDB/MySQL through the active config.

Switching from MariaDB/MySQL back to SQLite

The reverse path is similar:

1. Create a SQLite target config.
2. Keep the server-backed config as the active source.
3. Run:

dmarcreport -c dmarcreport.yaml db migrate --to sqlite --target-config dmarcreport-sqlite.yaml

4. Point your active runtime config back to SQLite.
5. Verify with dmarcreport check db.

Practical Advice

• Do not delete the old database immediately after migration.
• Do not try to merge databases with db migrate; the target must be empty.
• Prefer one clearly named config file per backend when migrating.
• If you want routine cleanup, set database.retention_days and let normal fetch runs prune old data automatically.
• If you want to skip that cleanup for one import, use dmarcreport fetch --skip-prune.
• The real dmarcreport.yaml is environment-local and should stay that way. Code push only includes dmarcreport.yaml.example, not your live config file.
• On Linux and Windows, the migration command works the same way; only path syntax and scheduler tooling differ.