Database Schema¶
This schema description is for illustrative purposes only and should not be used to update or replace an existing production database.
Entity-Relationship Diagram¶
↪ docs.photoprism.app/developer-guide/database/schema/
Mermaid Markup¶
With Mermaid.js you can generate visual diagrams from this markup file:
MariaDB SQL Dump¶
An SQL schema dump can be created using the command shown below, for example:
If you need an updated dump, you can run this command in your development environment:
mariadb-dump --no-data --skip-add-locks --skip-comments \
--skip-opt --skip-set-charset photoprism > mariadb.sql
Please note that the dump we provide is only updated at irregular intervals and should therefore not be used to update or replace an existing production database.
PostgreSQL (Experimental)¶
Development builds can also use PostgreSQL 16 or later as database; we test with PostgreSQL 18. To try it in the development environment, start the optional postgres service with docker compose --profile postgres up -d. Then run this command in a terminal to start PhotoPrism with it, since compose.yaml configures MariaDB by default:
./photoprism --database-driver=postgres --database-server=postgres:4002 \
--database-user=photoprism start
The schema is created by GORM from the models in internal/entity and differs from MariaDB in a few ways:
- Columns that are
VARBINARYon MariaDB, such as UIDs, paths, and hashes, arevarcharwith the"C"collation, so they compare and sort byte by byte. They cannot hold invalid UTF-8, so a file name that is not valid UTF-8 cannot be indexed. - Other text columns use the database collation, and
=compares them case-sensitively. For a sort order similar to MariaDB's, create the database with an ICU locale, e.g.POSTGRES_INITDB_ARGS="--locale-provider=icu --icu-locale=und"for a new server. - Timestamps are
timestamptzand are read in UTC.
Since the schema may still change without migrations, databases created with earlier development versions are not upgraded. If PhotoPrism refuses to use a database because of an unsupported schema, create a new database and index your originals again, as described in Using PostgreSQL.
Schema Migrations¶
↪ docs.photoprism.app/developer-guide/database/migrations/
↪ github.com/photoprism/photoprism/tree/develop/internal/entity/migrate