Skip to content

Advanced Database Setup

PhotoPrism is compatible with SQLite 3 and MariaDB 10.5.12+. Older databases using the same dialect, such as MySQL 8, may work but are not officially supported.

Never store database files on a shared network drive or open them with two server instances at the same time.

Info

Our docker-compose.yml examples include tested and working database configurations. These docs are for advanced users only.

MySQL

Official support for MySQL is discontinued as Oracle seems to have stopped shipping new features and improvements. As a result, the testing effort required before each release is no longer feasible.

MariaDB

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 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"

Note

mariadb:3306 needs to be replaced with the actual database server host and port, unless you're using our docker-compose.yml example without modifications.

Database Migrations

MariaDB to Sqlite

  • Install https://github.com/techouse/mysql-to-sqlite3 on your host.
  • Stop Photoprism: docker-compose stop photoprism
  • Add the port to the MariaDB service
  • On the host now run sudo mysql2sqlite -f <PATH_TO_STORAGE_MOUNT>/storage/index.db -d photoprism -u root --mysql-password 'insecure'
  • Shutdown your current stack fully: docker-compose down
  • Edit your docker-compose.yml:
  • Remove the MariaDB service.
  • Change in the Photoprism settings to use the sqlite driver and remove the other database settings
  • Start your stack again with docker-compose up -d
  • If this worked you may want to delete the old mountpoint for the MariaDB database.

Sqlite to MariaDB

  • Install https://github.com/techouse/sqlite3-to-mysql on your host. (openSUSE: zypper in python-sqlite3-to-mysql)
  • Shutdown your current stack: docker-compose down
  • Add the current snippet of the MariaDB to your Sqlite Photoprism Docker-Compose with the addition of the extra ports section where you expose port 3306 to the Host. In my case this looked like attachment 1.
  • Start the stack again: docker-compose up -d
  • Stop Photoprism: docker-compose stop photoprism
  • On the host now run sudo sqlite3mysql -f <PATH_TO_STORAGE_MOUNT>/storage/index.db -d photoprism -u root --mysql-password 'insecure'
  • Shutdown your current stack again: docker-compose down
  • Edit your docker-compose.yml so it uses the MariaDB database you added before. Don't forget to remove the ports section of the MariaDB Container.
  • Start your stack again with docker-compose up -d
  • If this worked you may want to delete the file index.db in the storage mount since it contains out of date information.

Attachment 1:

mariadb:
    image: mariadb:10.6
    container_name: mariadb
    restart: unless-stopped
    ports:
      - 3306:3306
    security_opt:
      - seccomp:unconfined
      - apparmor:unconfined
    command: mysqld --transaction-isolation=READ-COMMITTED --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --max-connections=512 --innodb-rollback-on-timeout=OFF --innodb-lock-wait-timeout=120
    volumes:
      - "./database:/var/lib/mysql" # never remove
    environment:
      MYSQL_ROOT_PASSWORD: insecure
      MYSQL_DATABASE: photoprism
      MYSQL_USER: photoprism
      MYSQL_PASSWORD: insecure