Advanced Database Setup¶
While we believe this contributed content may be helpful to advanced users, we have not yet thoroughly reviewed it. If you have suggestions for improvement, please let us know by clicking to send a pull request.
Compatibility¶
PhotoPrism is compatible with SQLite 3 and MariaDB 10.5.12+. Official support for MySQL 8 is discontinued as Oracle seems to have stopped shipping new features and enhancements. As a result, the testing effort required before each release is no longer feasible.
Our configuration examples are generally based on the current stable version to take advantage of performance improvements. This does not mean that older versions are no longer supported and you must upgrade immediately. We recommend not using the :latest tag for the MariaDB Docker image and to upgrade manually by changing the tag once we had a chance to test a new major version.
Storage¶
Local Solid-State Drives (SSDs) are best for databases of any kind. Never store database files on an unreliable device such as a USB flash drive, SD card, or shared network folder. These may also have unexpected file size limitations, which is especially problematic for databases that do not split data into smaller files.
Please do not use a named or anonymous Docker volume for storing MariaDB database files and check the mount path of the volume if you use a custom database image (it may not always be /var/lib/mysql), as both can lead to data loss when the database container is recreated, e.g. after an update of the Docker image.
Configuration¶
When creating a new database, make sure to set the charset and collation as follows:
CREATE DATABASE photoprism
CHARACTER SET = 'utf8mb4'
COLLATE = 'utf8mb4_unicode_ci';
Now create a user and grant privileges for this new database:
CREATE USER 'photoprism'@'%' IDENTIFIED BY 'insecure';
GRANT ALL PRIVILEGES ON photoprism.* to 'photoprism'@'%';
FLUSH PRIVILEGES;
Set the database environment variables for PhotoPrism and MariaDB as follows:
services:
photoprism:
environment:
PHOTOPRISM_DATABASE_DRIVER: "mysql"
PHOTOPRISM_DATABASE_SERVER: "mariadb:3306"
PHOTOPRISM_DATABASE_NAME: "photoprism"
PHOTOPRISM_DATABASE_USER: "photoprism"
PHOTOPRISM_DATABASE_PASSWORD: "insecure"
mariadb:
environment:
MARIADB_AUTO_UPGRADE: "1"
MARIADB_INITDB_SKIP_TZINFO: "1"
MARIADB_DATABASE: "photoprism"
MARIADB_USER: "photoprism"
MARIADB_PASSWORD: "insecure"
MARIADB_ROOT_PASSWORD: "insecure"
Set strong passwords if the database is exposed to an external network. Never expose your database to the public Internet in this way, for example, if it is running on a cloud server.
Schema Migrations¶
An index schema migration is performed automatically every time PhotoPrism is (re)started. The following instructions may be helpful in special cases, such as when a temporary problem has prevented a successful migration:
Change Database¶
Migrate from SQLite to MariaDB ›
Migrate from MariaDB to SQLite ›
Bad Performance
Many users reporting poor performance and high CPU usage have migrated from SQLite to MariaDB, so their database schema is no longer optimized for performance. For example, MariaDB cannot handle rows with text columns in memory and always uses temporary tables on disk if there are any.
If this is the case, please make sure that your migrated database schema matches that of a fresh, non-migrated installation. It may help to run the migrations manually in a terminal using the migrations subcommands. However, this does not guarantee that all issues such as missing indexes are resolved.
Due to the amount of time required to review each report, we can only offer this to eligible members and business customers, and not to users who have chosen our free community edition.