# Database Configuration Guide

## Overview

Jellyfin's database configuration is stored in the `database.xml` file located in your configuration directory. This file is automatically created on first startup with SQLite as the default database provider.

**As of this update**, the configuration file includes **preset configurations** for both SQLite and PostgreSQL, making it easy to switch between database providers without having to remember all the configuration options.

## Configuration File Location

The `database.xml` file is located in your Jellyfin configuration directory:

- **Linux**: `/etc/jellyfin/database.xml` or `~/.config/jellyfin/database.xml`
- **Windows**: `%ProgramData%\Jellyfin\config\database.xml`
- **Docker**: Typically `/config/database.xml` in your config volume

## Quick Start: Switching Between Databases

### Method 1: Edit database.xml (Recommended)

1. **Stop Jellyfin**
   ```bash
   # Linux
   sudo systemctl stop jellyfin
   
   # Windows
   net stop JellyfinServer
   
   # Docker
   docker stop jellyfin
   ```

2. **Edit database.xml**
   
   Change this line:
   ```xml
   <DatabaseType>Jellyfin-SQLite</DatabaseType>
   ```
   
   To:
   ```xml
   <DatabaseType>Jellyfin-PostgreSQL</DatabaseType>
   ```

3. **Add PostgreSQL connection settings** (uncomment and configure):
   ```xml
   <CustomProviderOptions>
     <PluginName>Jellyfin.Database.Providers.Postgres</PluginName>
     <PluginAssembly>Jellyfin.Database.Providers.Postgres.dll</PluginAssembly>
     <ConnectionString></ConnectionString>
     <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>
     </Options>
   </CustomProviderOptions>
   ```

4. **Start Jellyfin**
   ```bash
   sudo systemctl start jellyfin
   ```

### Method 2: Use Preset Configurations

The database.xml file now includes preset configurations that serve as templates. These presets show you exactly what settings are needed for each database type.

**Available Presets:**
- `SQLite` - Default single-file database
- `PostgreSQL-Local` - PostgreSQL on localhost

To use a preset:
1. Look at the preset configuration in the `<PresetConfigurations>` section
2. Copy the settings to the active configuration at the top of the file
3. Configure any additional options (like PostgreSQL credentials)

## Configuration File Structure

```xml
<?xml version="1.0" encoding="utf-8"?>
<DatabaseConfigurationOptions>
  
  <!-- ACTIVE CONFIGURATION -->
  <DatabaseType>Jellyfin-SQLite</DatabaseType>
  <LockingBehavior>NoLock</LockingBehavior>
  
  <!-- POSTGRESQL OPTIONS (when using PostgreSQL) -->
  <CustomProviderOptions>
    <!-- PostgreSQL connection settings -->
  </CustomProviderOptions>
  
  <!-- BACKUP SETTINGS -->
  <BackupOptions>
    <EnableAutoBackup>false</EnableAutoBackup>
    <BackupRetentionDays>30</BackupRetentionDays>
  </BackupOptions>
  
  <!-- PRESET CONFIGURATIONS (Templates) -->
  <PresetConfigurations>
    <!-- SQLite and PostgreSQL presets -->
  </PresetConfigurations>
  
</DatabaseConfigurationOptions>
```

## Configuration Properties

### DatabaseType
The active database provider to use.

**Options:**
- `Jellyfin-SQLite` - Use SQLite database (default)
- `Jellyfin-PostgreSQL` - Use PostgreSQL database

**Example:**
```xml
<DatabaseType>Jellyfin-PostgreSQL</DatabaseType>
```

### LockingBehavior
How Jellyfin should handle database locking.

**Options:**
- `NoLock` - No locking (recommended for most installations)
- `Pessimistic` - Lock rows before reading
- `Optimistic` - Check for conflicts after reading

**Default:** `NoLock`

**Example:**
```xml
<LockingBehavior>NoLock</LockingBehavior>
```

### CustomProviderOptions
Connection settings for PostgreSQL (not needed for SQLite).

**Required Properties:**
- `PluginName` - Always set to `Jellyfin.Database.Providers.Postgres`
- `PluginAssembly` - Always set to `Jellyfin.Database.Providers.Postgres.dll`
- `ConnectionString` - Leave empty (connection built from Options)
- `Options` - List of key-value pairs for connection settings

**PostgreSQL Options:**

| Key | Description | Default | Example |
|-----|-------------|---------|---------|
| `host` | PostgreSQL server hostname | `localhost` | `localhost`, `192.168.1.100`, `db.example.com` |
| `port` | PostgreSQL server port | `5432` | `5432` |
| `database` | Database name | `jellyfin` | `jellyfin`, `jellyfin_prod` |
| `username` | Database username | `jellyfin` | `jellyfin`, `postgres` |
| `password` | Database password | *(empty)* | `your_secure_password` |
| `connection-timeout` | Connection timeout in seconds | `15` | `15`, `30` |

**Example:**
```xml
<CustomProviderOptions>
  <PluginName>Jellyfin.Database.Providers.Postgres</PluginName>
  <PluginAssembly>Jellyfin.Database.Providers.Postgres.dll</PluginAssembly>
  <ConnectionString></ConnectionString>
  <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>MySecurePassword123</Value>
    </CustomDatabaseOption>
  </Options>
</CustomProviderOptions>
```

### BackupOptions
Configure automatic database backups (optional).

**Properties:**
- `EnableAutoBackup` - Enable/disable automatic backups
- `BackupRetentionDays` - How many days to keep backups

**Example:**
```xml
<BackupOptions>
  <EnableAutoBackup>true</EnableAutoBackup>
  <BackupRetentionDays>30</BackupRetentionDays>
</BackupOptions>
```

### PresetConfigurations
Template configurations for quick reference. These are **NOT** active configurations - they're just examples stored in the file for your convenience.

## Complete Examples

### Example 1: SQLite Configuration

```xml
<?xml version="1.0" encoding="utf-8"?>
<DatabaseConfigurationOptions>
  <DatabaseType>Jellyfin-SQLite</DatabaseType>
  <LockingBehavior>NoLock</LockingBehavior>
  
  <BackupOptions>
    <EnableAutoBackup>false</EnableAutoBackup>
    <BackupRetentionDays>30</BackupRetentionDays>
  </BackupOptions>
  
  <PresetConfigurations>
    <!-- Presets stored here for reference -->
  </PresetConfigurations>
</DatabaseConfigurationOptions>
```

### Example 2: PostgreSQL Configuration (Localhost)

```xml
<?xml version="1.0" encoding="utf-8"?>
<DatabaseConfigurationOptions>
  <DatabaseType>Jellyfin-PostgreSQL</DatabaseType>
  <LockingBehavior>NoLock</LockingBehavior>
  
  <CustomProviderOptions>
    <PluginName>Jellyfin.Database.Providers.Postgres</PluginName>
    <PluginAssembly>Jellyfin.Database.Providers.Postgres.dll</PluginAssembly>
    <ConnectionString></ConnectionString>
    <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>YourSecurePassword</Value>
      </CustomDatabaseOption>
    </Options>
  </CustomProviderOptions>
  
  <BackupOptions>
    <EnableAutoBackup>true</EnableAutoBackup>
    <BackupRetentionDays>30</BackupRetentionDays>
  </BackupOptions>
</DatabaseConfigurationOptions>
```

### Example 3: PostgreSQL Configuration (Remote Server)

```xml
<?xml version="1.0" encoding="utf-8"?>
<DatabaseConfigurationOptions>
  <DatabaseType>Jellyfin-PostgreSQL</DatabaseType>
  <LockingBehavior>NoLock</LockingBehavior>
  
  <CustomProviderOptions>
    <PluginName>Jellyfin.Database.Providers.Postgres</PluginName>
    <PluginAssembly>Jellyfin.Database.Providers.Postgres.dll</PluginAssembly>
    <ConnectionString></ConnectionString>
    <Options>
      <CustomDatabaseOption>
        <Key>host</Key>
        <Value>db.example.com</Value>
      </CustomDatabaseOption>
      <CustomDatabaseOption>
        <Key>port</Key>
        <Value>5432</Value>
      </CustomDatabaseOption>
      <CustomDatabaseOption>
        <Key>database</Key>
        <Value>jellyfin_production</Value>
      </CustomDatabaseOption>
      <CustomDatabaseOption>
        <Key>username</Key>
        <Value>jellyfin_user</Value>
      </CustomDatabaseOption>
      <CustomDatabaseOption>
        <Key>password</Key>
        <Value>VerySecurePassword123!</Value>
      </CustomDatabaseOption>
      <CustomDatabaseOption>
        <Key>connection-timeout</Key>
        <Value>30</Value>
      </CustomDatabaseOption>
    </Options>
  </CustomProviderOptions>
  
  <BackupOptions>
    <EnableAutoBackup>true</EnableAutoBackup>
    <BackupRetentionDays>7</BackupRetentionDays>
  </BackupOptions>
</DatabaseConfigurationOptions>
```

## Migration Between Databases

### From SQLite to PostgreSQL

1. **Backup your current SQLite database**
   ```bash
   cp jellyfin.db jellyfin.db.backup
   ```

2. **Set up PostgreSQL server and create database**
   ```sql
   CREATE DATABASE jellyfin;
   CREATE USER jellyfin WITH PASSWORD 'your_password';
   GRANT ALL PRIVILEGES ON DATABASE jellyfin TO jellyfin;
   ```

3. **Stop Jellyfin**

4. **Edit database.xml** to use PostgreSQL (see examples above)

5. **Start Jellyfin** - Migrations will run automatically

6. **Note**: This creates a fresh database. To migrate data, you'll need to use Jellyfin's backup/restore features or export/import your library.

### From PostgreSQL to SQLite

1. **Stop Jellyfin**

2. **Edit database.xml** - Change DatabaseType to `Jellyfin-SQLite` and remove CustomProviderOptions

3. **Start Jellyfin** - A new SQLite database will be created

4. **Note**: Data migration requires backup/restore or manual export/import

## Troubleshooting

### Configuration not taking effect

**Solution**: Ensure you've stopped Jellyfin before editing the configuration file, and that the file is saved with proper XML formatting.

### PostgreSQL connection fails

**Check:**
1. PostgreSQL server is running: `sudo systemctl status postgresql`
2. Database exists: `psql -l`
3. User has permissions: `psql -U jellyfin -d jellyfin`
4. Firewall allows connection on port 5432
5. Password is correct in database.xml

### Cannot find database.xml

**Solution**: The file is created on first startup. If missing:
1. Start Jellyfin once to generate it
2. Or copy from `database.xml.example` in the source repository

### Preset configurations not working

**Important**: Presets are templates only! They don't activate automatically. You must:
1. Copy the DatabaseType from the preset to the active configuration
2. Add CustomProviderOptions if needed for PostgreSQL
3. Configure the actual connection settings

## Security Considerations

1. **Protect database.xml** - It contains passwords
   ```bash
   chmod 600 /etc/jellyfin/database.xml
   chown jellyfin:jellyfin /etc/jellyfin/database.xml
   ```

2. **Use strong PostgreSQL passwords**

3. **Restrict network access** to PostgreSQL server

4. **Enable automatic backups** for production systems

5. **Don't commit database.xml** to version control with real passwords

## See Also

- [FILE_BASED_STARTUP_CONFIG.md](FILE_BASED_STARTUP_CONFIG.md) - Path configuration
- [TROUBLESHOOTING_EF_PENDING_CHANGES.md](TROUBLESHOOTING_EF_PENDING_CHANGES.md) - Database migration issues
- [startup.json.example](startup.json.example) - Path configuration template
- [database.xml.example](database.xml.example) - Database configuration template
