pgsql-jellyfin/docs/DATABASE_CONFIGURATION.md

# Database Configuration Guide

Jellyfin supports multiple database providers. The database configuration is stored in `<config-directory>/config/database.xml`.

## Supported Built-in Providers

### 1. SQLite (Default)
```xml
<?xml version="1.0" encoding="utf-8"?>
<DatabaseConfigurationOptions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <DatabaseType>Jellyfin-SQLite</DatabaseType>
  <LockingBehavior>NoLock</LockingBehavior>
</DatabaseConfigurationOptions>
```

### 2. PostgreSQL (Built-in)
```xml
<?xml version="1.0" encoding="utf-8"?>
<DatabaseConfigurationOptions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <DatabaseType>Jellyfin-PostgreSQL</DatabaseType>
  <LockingBehavior>NoLock</LockingBehavior>
  <CustomProviderOptions>
    <Options>
      <CustomDatabaseOption>
        <Key>host</Key>
        <Value>localhost</Value>
      </CustomDatabaseOption>
      <CustomDatabaseOption>
        <Key>port</Key>
        <Value>5432</Value>
      </CustomDatabaseOption>
      <CustomDatabaseOption>
        <Key>database</Key>
        <Value>jellyfin</Value>
      </CustomDatabaseOption>
      <CustomDatabaseOption>
        <Key>username</Key>
        <Value>jellyfin</Value>
      </CustomDatabaseOption>
      <CustomDatabaseOption>
        <Key>password</Key>
        <Value>your_secure_password</Value>
      </CustomDatabaseOption>
      <CustomDatabaseOption>
        <Key>pooling</Key>
        <Value>True</Value>
      </CustomDatabaseOption>
      <CustomDatabaseOption>
        <Key>command-timeout</Key>
        <Value>30</Value>
      </CustomDatabaseOption>
      <CustomDatabaseOption>
        <Key>connection-timeout</Key>
        <Value>15</Value>
      </CustomDatabaseOption>
    </Options>
  </CustomProviderOptions>
</DatabaseConfigurationOptions>
```

## PostgreSQL Setup Steps

### 1. Install PostgreSQL
Install PostgreSQL 12 or higher on your system.

### 2. Create Database User

✨ **New Feature:** Jellyfin now automatically creates the database if it doesn't exist!

You only need to create the user with `CREATEDB` privilege:

```sql
-- Create user with database creation privilege
CREATE USER jellyfin WITH PASSWORD 'your_secure_password' CREATEDB;
```

**That's it!** Jellyfin will automatically:
- Check if the `jellyfin` database exists
- Create it if missing
- Grant all privileges
- Apply migrations (create tables)

<details>
<summary>📖 Manual Database Creation (Optional)</summary>

If you prefer to create the database manually or your user cannot have `CREATEDB` privilege:

```sql
CREATE DATABASE jellyfin;
CREATE USER jellyfin WITH ENCRYPTED PASSWORD 'your_secure_password';
GRANT ALL PRIVILEGES ON DATABASE jellyfin TO jellyfin;

-- PostgreSQL 15+: Also grant schema privileges
GRANT ALL PRIVILEGES ON SCHEMA public TO jellyfin;
ALTER DATABASE jellyfin OWNER TO jellyfin;
```

</details>

See [AUTOMATIC_DATABASE_CREATION.md](AUTOMATIC_DATABASE_CREATION.md) for detailed information.

### 3. ~~Generate PostgreSQL Migrations~~ ✅ Already Generated!

✅ **Good News!** PostgreSQL migrations have been generated and are included in the codebase.

The migration files are located at:
```
src\Jellyfin.Database\Jellyfin.Database.Providers.Postgres\Migrations\
  ├── 20260222222702_InitialCreate.cs
  ├── 20260222222702_InitialCreate.Designer.cs
  └── JellyfinDbContextModelSnapshot.cs
```

These migrations create **30 tables** with PostgreSQL-optimized types and indexes.

> **For Development:** If you need to regenerate migrations, see [GENERATE_POSTGRESQL_MIGRATIONS.md](GENERATE_POSTGRESQL_MIGRATIONS.md)

### 4. Configure Jellyfin
- Stop Jellyfin server (if running)
- Create or edit `<config-directory>/config/database.xml` with the PostgreSQL configuration above
- Update the connection parameters (host, port, database, username, password)
- Start Jellyfin server

### 5. Automatic Schema Creation
On first startup with PostgreSQL configured, Jellyfin will:
1. Connect to the database
2. Apply all pending migrations
3. Create all tables, indexes, and constraints
4. Initialize the schema

**No manual SQL scripts needed!** The migration system handles everything.

See [DATABASE_SCHEMA_CREATION.md](DATABASE_SCHEMA_CREATION.md) for detailed information.

## Locking Behaviors

- **NoLock**: No application-level locking (recommended for PostgreSQL - database handles concurrency)
- **Pessimistic**: Explicit application-level locking (use with caution, mainly for SQLite compatibility)
- **Optimistic**: EF Core optimistic concurrency control

### Recommended Locking by Database Type:
- **SQLite**: `NoLock` or `Pessimistic` (single file database)
- **PostgreSQL**: `NoLock` (PostgreSQL has built-in MVCC and transaction isolation)
- **MySQL**: `NoLock` (MySQL has built-in transaction management)

## PostgreSQL Configuration Options

| Option | Default | Description |
|--------|---------|-------------|
| host | localhost | PostgreSQL server hostname |
| port | 5432 | PostgreSQL server port |
| database | jellyfin | Database name |
| username | jellyfin | Database username |
| password | (empty) | Database password |
| pooling | True | Enable connection pooling |
| command-timeout | 30 | Command timeout in seconds |
| connection-timeout | 15 | Connection timeout in seconds |
| EnableSensitiveDataLogging | False | Enable detailed SQL logging (debug only) |

## Migration from SQLite to PostgreSQL

⚠️ **Warning**: Migration between database providers requires data export/import. There is no automatic migration tool.

1. Backup your current Jellyfin data
2. Export your library metadata (if needed)
3. Configure PostgreSQL as described above
4. Restart Jellyfin (will create fresh database schema)
5. Re-scan your media library

## Troubleshooting

### Connection Issues
- Verify PostgreSQL is running: `systemctl status postgresql` (Linux) or check Windows Services
- Check firewall allows connections on port 5432
- Verify `pg_hba.conf` allows connections from your Jellyfin server

### Migration Issues
- Check logs in `<config-directory>/logs/` for detailed error messages
- Ensure PostgreSQL user has sufficient privileges

### Performance Issues
- Consider increasing `command-timeout` if you have large libraries
- Enable connection pooling (`pooling=True`)
- For multi-server setups, use `Pessimistic` locking behavior

## Command Line Override (Development/Testing)

You can temporarily override the database provider using command line:
```bash
./jellyfin --migration-provider "Jellyfin-PostgreSQL"
```

This is useful for running migrations without modifying configuration files.