Databases

Databases

Supported engines, settings and live migration.

UniversalProtect supports four engines. Set the engine with database.type in config.yml.

Type	Best for	Notes
duckdb	Default, local storage	Fast for large queries, single file database.duckdb
sqlite	Small local servers	File database.db. Existing installations keep using it automatically
mysql	Networks, several servers	Needs host, port, user and password
clickhouse	Very large amounts of data	Deletes and updates run as synchronous mutations

Connection settings

database:
  type: duckdb
  sqlite:
    file: database.db
  duckdb:
    file: database.duckdb
  mysql:
    host: localhost
    port: 3306
    database: universalprotect
    username: root
    password: ''
    parameters: useSSL=false&allowPublicKeyRetrieval=true
  clickhouse:
    host: localhost
    port: 8123
    database: universalprotect
    username: default
    password: ''
    ssl: false

Note: For MySQL and ClickHouse the database itself must already exist. The plugin creates the tables.

Live migration

Run in the console only:

/up migrate-db <sqlite|mysql|duckdb|clickhouse> [--full-validation]

How it works

1. Data is copied in chunks of 10,000 rows. The server keeps running and new entries are carried along.
2. Row counts and the highest ID are validated. With --full-validation every row is compared as well.
3. On success the plugin switches to the new database atomically and saves database.type in config.yml.
4. If anything fails, the old database stays active.

Example

/up migrate-db mysql --full-validation

Tip: Back up the old database file before migrating.

Maintenance

/up purge #optimize compacts the database after deleting data. See Purge.

Which one should I choose?

Situation	Recommendation
Single server, any size	DuckDB
Tiny server, maximum compatibility	SQLite
Several servers sharing one log	MySQL
Hundreds of millions of rows	ClickHouse

Compaction and recovery

/up compact summarises old block entries to save space, see Purge. Crash recovery for interrupted rollbacks uses a journal table and is not available with ClickHouse.

---

Related pages: Configuration · Purge · Troubleshooting