Database

Frameleaf keeps everything it knows about your library in a PostgreSQL database: where every file is, albums, people, descriptions, edits and settings. The default installation runs it in the database service, using Frameleaf’s own image, ghcr.io/frameleaf/frameleaf-postgres. The database is named immich by default, for compatibility.

Danger

Avoid changing the database directly whenever you can, and always have a current backup before you do. A wrong query can damage your library in ways that are hard to undo.

Connect with psql

From your Compose folder, open a psql prompt inside the database container. Use the DB_USERNAME and DB_DATABASE_NAME values from your .env file (usually postgres and immich):

docker compose exec -it database psql --dbname=<DB_DATABASE_NAME> --username=<DB_USERNAME>

docker exec -it frameleaf_postgres psql ... works too, if your container has the default name.

To change the database password, run this in psql, then put the same value in DB_PASSWORD in .env and restart:

ALTER USER <DB_USERNAME> WITH ENCRYPTED PASSWORD 'newpasswordhere';

Browse the database with pgAdmin

pgAdmin gives you a graphical view of the database.

1. Save this as docker-compose-pgadmin.yml next to your docker-compose.yml, and change the email and password:

name: immich

services:
  pgadmin:
    image: dpage/pgadmin4
    container_name: pgadmin4_container
    restart: always
    ports:
      - "8888:80"
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: strong-password
    volumes:
      - pgadmin-data:/var/lib/pgadmin

volumes:
  pgadmin-data:

2. Start Frameleaf together with pgAdmin:

docker compose -f docker-compose.yml -f docker-compose-pgadmin.yml up -d

3. Open http://localhost:8888 and sign in with the email and password you set.
4. Right-click Servers, choose Register, then Server…, and on the Connection tab enter:

Field	Value
Host name/address	frameleaf_postgres
Port	5432
Maintenance database	immich (your DB_DATABASE_NAME)
Username	postgres (your DB_USERNAME)
Password	your DB_PASSWORD

5. Click Save.

Caution

pgAdmin can change anything in the database. Don’t publish its port beyond your own network.

Useful queries

These queries only read data, apart from the last one in People. Replace the example values with your own.

Find items by file name or path

originalFileName is the file’s name when it was uploaded, including the extension.

SELECT * FROM "asset" WHERE "originalFileName" = 'PXL_20230903_232542848.jpg';
SELECT * FROM "asset" WHERE "originalFileName" LIKE 'PXL_%';    -- names starting with PXL_
SELECT * FROM "asset" WHERE "originalFileName" LIKE '%_2023_%'; -- names with _2023_ in the middle

SELECT * FROM "asset" WHERE "originalPath" = 'upload/library/admin/2023/2023-09-03/PXL_2023.jpg';
SELECT * FROM "asset" WHERE "originalPath" LIKE 'upload/library/admin/2023/%';

Find items by ID

SELECT * FROM "asset" WHERE "id" = '9f94e60f-65b6-47b7-ae44-a4df7b57f0e9';
SELECT * FROM "asset" WHERE "id"::text LIKE '%ab431d3a%'; -- part of an ID

Find items by checksum

Frameleaf stores a SHA-256 checksum for new uploads, and SHA-1 for items uploaded before that. The checksumAlgorithm column says which (sha256, sha1, or sha1-path for some external library items, which are checksummed from their path). Calculate a file’s checksum with sha256sum <filename> or sha1sum <filename>.

SELECT "id", "checksumAlgorithm", encode("checksum", 'hex') FROM "asset";
SELECT * FROM "asset" WHERE "checksum" = decode('<checksum in hex>', 'hex');

Items with the same checksum, ignoring the trash:

SELECT T1."checksum", array_agg(T2."id") ids FROM "asset" T1
  INNER JOIN "asset" T2 ON T1."checksum" = T2."checksum" AND T1."id" != T2."id" AND T2."deletedAt" IS NULL
  WHERE T1."deletedAt" IS NULL GROUP BY T1."checksum";

Metadata

-- Live Photos
SELECT * FROM "asset" WHERE "livePhotoVideoId" IS NOT NULL;

-- Items with a description
SELECT "asset".*, "asset_exif"."description" FROM "asset_exif"
  JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE TRIM("asset_exif"."description") <> '';

-- Search descriptions
SELECT "asset".*, "asset_exif"."description" FROM "asset_exif"
  JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."description" ILIKE '%string to match%';

-- Files under 100,000 bytes, smallest first
SELECT * FROM "asset"
  JOIN "asset_exif" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."fileSizeInByte" < 100000
  ORDER BY "asset_exif"."fileSizeInByte" ASC;

Photos and videos

SELECT * FROM "asset" WHERE "asset"."type" = 'VIDEO';
SELECT * FROM "asset" WHERE "asset"."type" = 'IMAGE';

-- Count by type
SELECT "asset"."type", COUNT(*) FROM "asset" GROUP BY "asset"."type";

-- Count by type for each person
SELECT "user"."email", "asset"."type", COUNT(*) FROM "asset"
  JOIN "user" ON "asset"."ownerId" = "user"."id"
  GROUP BY "asset"."type", "user"."email" ORDER BY "user"."email";

Tags

-- Items per tag
SELECT "t"."value" AS "tag_name", COUNT(*) AS "number_assets" FROM "tag" "t"
  JOIN "tag_asset" "ta" ON "t"."id" = "ta"."tagId" JOIN "asset" "a" ON "ta"."assetId" = "a"."id"
  WHERE "a"."visibility" != 'hidden'
  GROUP BY "t"."value" ORDER BY "number_assets" DESC;

-- Items per tag for each person
SELECT "t"."value" AS "tag_name", "u"."email" AS "user_email", COUNT(*) AS "number_assets" FROM "tag" "t"
  JOIN "tag_asset" "ta" ON "t"."id" = "ta"."tagId" JOIN "asset" "a" ON "ta"."assetId" = "a"."id" JOIN "user" "u" ON "a"."ownerId" = "u"."id"
  WHERE "a"."visibility" != 'hidden'
  GROUP BY "t"."value", "u"."email" ORDER BY "number_assets" DESC;

Accounts

SELECT * FROM "user";

-- The owner of an item
SELECT "user".* FROM "user" JOIN "asset" ON "user"."id" = "asset"."ownerId"
  WHERE "asset"."id" = 'fa310b01-2f26-4b7a-9042-d578226e021f';

People

This one changes data: it deletes a person and unlinks their faces.

DELETE FROM "person" WHERE "name" = 'PersonNameHere';

Server

-- Settings saved in the web app (not used when you run with a config file)
SELECT "key", "value" FROM "system_metadata" WHERE "key" = 'system-config';

-- Items without a thumbnail or preview
SELECT * FROM "asset"
WHERE (NOT EXISTS (SELECT 1 FROM "asset_file" WHERE "asset"."id" = "asset_file"."assetId" AND "asset_file"."type" = 'thumbnail')
    OR NOT EXISTS (SELECT 1 FROM "asset_file" WHERE "asset"."id" = "asset_file"."assetId" AND "asset_file"."type" = 'preview'))
AND "asset"."visibility" = 'timeline';

-- File moves that failed
SELECT * FROM "move_history";

Use your own PostgreSQL server

You can run Frameleaf on a PostgreSQL server you already have. This isn’t the recommended set-up, and you should be comfortable with PostgreSQL and the Linux command line. If you aren’t, use the default database container.

Tip

Your own PostgreSQL server can unlock features such as logical replication and continuous write-ahead log backups with tools like pgBackRest or Barman.

Requirements

Component	Supported versions
PostgreSQL	14 or later, below 20
pgvector	0.7 or later, below 0.9
VectorChord	0.3 or later, below 2.0. The server checks this at start-up and refuses to start without a compatible version

1. Install pgvector, which VectorChord needs. On Debian or Ubuntu, add the PostgreSQL apt repository and run apt install postgresql-NN-pgvector, where NN is your PostgreSQL version.
2. Install VectorChord using its installation instructions.
3. Add it to shared_preload_libraries in postgresql.conf, comma-separated if you already have others, for example shared_preload_libraries = 'pg_stat_statements, vchord.so'.

Connect Frameleaf to it

Set DB_URL in your .env file:

DB_URL='postgresql://dbusername:dbpassword@postgreshost:postgresport/databasename'

# Require an SSL connection
# DB_URL='postgresql://dbusername:dbpassword@postgreshost:postgresport/databasename?sslmode=require'

# Require SSL, but don't check the certificate name
# DB_URL='postgresql://dbusername:dbpassword@postgreshost:postgresport/databasename?sslmode=require&sslmode=no-verify'

Then remove the database service from your Compose file.

With superuser permission

Frameleaf normally expects superuser permission on its database. Grant it at the psql prompt:

ALTER USER <dbusername> WITH SUPERUSER;

Without superuser permission

Caution

For advanced users only. It often needs manual steps when you upgrade Frameleaf, and the automatic database backups need superuser permission, because they use pg_dumpall.

Prepare the database at the psql prompt:

CREATE DATABASE <databasename>;
\c <databasename>
BEGIN;
ALTER DATABASE <databasename> OWNER TO <dbusername>;
CREATE EXTENSION vchord CASCADE;
CREATE EXTENSION earthdistance CASCADE;
COMMIT;

When you install a new version of VectorChord, update the extension and rebuild the indexes yourself, connected to the Frameleaf database:

ALTER EXTENSION vchord UPDATE;
REINDEX INDEX face_index;
REINDEX INDEX clip_index;

Move to VectorChord

VectorChord replaced pgvecto.rs, which isn’t supported from Frameleaf 3.0, and gives faster, better smart search and face recognition with less memory. If you use the default database container, follow Upgrading instead. These steps are for your own PostgreSQL server.

From pgvecto.rs, with both extensions installed

1. Keep pgvecto.rs installed.
2. Install pgvector (0.7 or later, below 0.9).
3. Install VectorChord.
4. Set shared_preload_libraries = 'vchord.so, vectors.so' in postgresql.conf. Include both, plus any others you need.
5. Restart PostgreSQL.
6. If Frameleaf doesn’t have superuser permission, run CREATE EXTENSION vchord CASCADE;.
7. Start Frameleaf and wait for Reindexed face_index and Reindexed clip_index in the logs.
8. If Frameleaf doesn’t have superuser permission, run DROP EXTENSION vectors;.
9. Drop the old schema: DROP SCHEMA vectors;.
10. Remove vectors.so from shared_preload_libraries.
11. Restart PostgreSQL.
12. Uninstall pgvecto.rs, for example apt-get purge vectors-pg14 on Debian (change pg14 to match). Keep pgvector, because VectorChord uses its data types.

From pgvecto.rs, without both installed at once

1. While pgvecto.rs is still installed, note the number this returns:

SELECT atttypmod as dimsize
  FROM pg_attribute f
  JOIN pg_class c ON c.oid = f.attrelid
  WHERE c.relkind = 'r'::char
  AND f.attnum > 0
  AND c.relname = 'smart_search'::text
  AND f.attname = 'embedding'::text;

2. Remove the references to pgvecto.rs:

DROP INDEX IF EXISTS clip_index;
DROP INDEX IF EXISTS face_index;
ALTER TABLE smart_search ALTER COLUMN embedding SET DATA TYPE real[];
ALTER TABLE face_search ALTER COLUMN embedding SET DATA TYPE real[];

3. Install VectorChord.
4. Change the columns back to vector types, using the number from step 1:

CREATE EXTENSION IF NOT EXISTS vchord CASCADE;
ALTER TABLE smart_search ALTER COLUMN embedding SET DATA TYPE vector(<number>);
ALTER TABLE face_search ALTER COLUMN embedding SET DATA TYPE vector(512);

5. Start Frameleaf and let it create new indexes with VectorChord.

From pgvector

1. Make sure you have pgvector 0.7.0 or later. If not, upgrade it and run ALTER EXTENSION vector UPDATE;.
2. Install VectorChord as in Requirements.
3. If Frameleaf doesn’t have superuser permission, run CREATE EXTENSION vchord CASCADE;.
4. Remove DB_VECTOR_EXTENSION=pgvector from your environment, or Frameleaf keeps using pgvector.
5. Start Frameleaf and let it create new indexes with VectorChord.

Don’t uninstall pgvector afterwards: VectorChord uses its types.