Database

lnkify uses PostgreSQL 16 as its database, accessed through Prisma ORM. The database runs in its own Docker container and persists data to a named volume.

Service Definition

The db service in docker-compose.yml:

db:
  image: postgres:16-alpine
  environment:
    POSTGRES_USER: ${POSTGRES_USER:-postgres}
    POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:?POSTGRES_PASSWORD is required (set it in .env)}
    POSTGRES_DB: ${POSTGRES_DB:-lnkify}
  volumes:
    - db-data:/var/lib/postgresql/data
  healthcheck:
    test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER:-postgres} -d ${POSTGRES_DB:-lnkify}"]
    interval: 5s
    timeout: 5s
    retries: 10

Key aspects:

• Alpine-based image — Smaller footprint than the Debian-based image.
• Environment variables — Credentials are read from the .env file with sensible defaults for user and database name.
• Healthcheck — pg_isready runs every 5 seconds (10 retries). The server waits for this to pass before starting.
• Database credentials — The default user is postgres and default database is lnkify. Override via .env variables.
• Volume — Data persists in db-data, surviving container restarts and recreations.

Connection String

The server connects to the database using the DATABASE_URL environment variable:

DATABASE_URL=postgresql://postgres:password@db:5432/lnkify

The hostname db is the Docker Compose service name, resolved via Docker's internal DNS. The port 5432 is PostgreSQL's default.

Connection String Format

postgresql://<user>:<password>@<host>:<port>/<database>

If you're using an external PostgreSQL instance (not the Docker service), set the host to the external server's address:

DATABASE_URL=postgresql://postgres:password@192.168.1.100:5432/lnkify

Prisma ORM

lnkify uses Prisma for database access. The schema is defined in server/prisma/schema.prisma.

Applying Migrations

lnkify ships committed migrations in server/prisma/migrations/. The server container applies any pending migrations automatically on startup (docker-start.sh runs prisma migrate deploy before booting), so a fresh docker compose up brings the schema fully up to date with no manual step.

To apply migrations manually (e.g. against an external database):

docker compose run --rm server pnpm run db-init

db-init runs prisma migrate deploy, which applies the committed migration files in order. Unlike the old prisma db push flow, this gives a reviewable, append-only schema history and a safe roll-forward path — never an unaudited diff against your data.

Prisma Studio

You can explore your database visually with Prisma Studio:

docker compose run --rm --service-ports server pnpm exec prisma studio

Follow the URL printed in the output to open the browser-based database explorer.

Supported Databases

PostgreSQL is the canonical, tested database, and it is what Docker Compose runs. The committed migrations are PostgreSQL-specific SQL, so switching to another provider (MySQL, SQLite) is not a simple provider edit — it would require regenerating the entire migration history against that engine and is unsupported. Run lnkify on PostgreSQL.

Data Persistence

The db-data volume stores all PostgreSQL data files. This volume is not removed by docker compose down unless you explicitly pass --volumes:

docker compose down          # stops containers, preserves data
docker compose down --volumes # stops containers, deletes all data

To see where the volume data is stored on the host:

docker volume inspect lnkify_db-data

Backups

Creating a Backup

Use pg_dump from within the db container:

docker compose exec db pg_dump -U postgres lnkify > backup-$(date +%Y%m%d).sql

For a compressed backup:

docker compose exec db pg_dump -U postgres lnkify | gzip > backup-$(date +%Y%m%d).sql.gz

Restoring from a Backup

# Uncompressed
docker compose exec -T db psql -U postgres lnkify < backup-20260101.sql

# Compressed
gunzip -c backup-20260101.sql.gz | docker compose exec -T db psql -U postgres lnkify

Automated Backups

Add a cron job on the host:

0 2 * * * cd /opt/lnkify && docker compose exec -T db pg_dump -U postgres lnkify | gzip > /backups/lnkify-$(date +\%Y\%m\%d).sql.gz

pgAdmin / External Tools

To connect external tools like pgAdmin, temporarily publish the database port:

docker compose run --service-ports db

Or add a port mapping to the db service in docker-compose.yml (not recommended for production):

db:
  ports:
    - "5432:5432"

Migration Strategy

lnkify uses Prisma's migration workflow with a committed, version-controlled history:

• Applying — prisma migrate deploy (via db-init, run automatically on container start) applies every pending migration in server/prisma/migrations/ in order. It is idempotent: already-applied migrations are skipped.
• History — migrations are committed to the repo, so every schema change is reviewable and reproducible. Pulling a new image and starting it applies exactly the migrations that shipped with it.
• No db push in production — prisma db push is reserved for local throwaway development. The production/self-hosted path never diffs the schema straight onto your data.
• Authoring changes — if you fork and modify the schema, generate a migration with prisma migrate dev --name <change> against a local Postgres and commit the result.

Before upgrading, always back up your database. See Upgrading for the upgrade workflow.

Next: Scaling