> ## Documentation Index
> Fetch the complete documentation index at: https://docs.fim.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# Database
> Choose and configure the right database backend for your deployment
FIM One supports two database backends: **SQLite** (default, zero-config) and **PostgreSQL** (recommended for production). The backend is determined by the `DATABASE_URL` environment variable.
```bash theme={null}
# SQLite (default — no configuration needed)
DATABASE_URL=sqlite+aiosqlite:///./data/fim_one.db
# PostgreSQL (production)
DATABASE_URL=postgresql+asyncpg://user:pass@localhost:5432/fim_one
```
Tables are created automatically on first start — no manual migration step is required.
***
## SQLite vs PostgreSQL
| | SQLite | PostgreSQL |
| ----------------- | -------------------------------------------------------- | ------------------------------------------ |
| **Setup** | Zero-config, file-based | Requires a separate server |
| **Concurrency** | Single-writer (global lock) | Full MVCC, row-level locking |
| **Multi-worker** | Not supported (`WORKERS` must be `1`) | Fully supported |
| **SSE streaming** | Connections held during streams may block other requests | Concurrent reads and writes are unaffected |
| **Backup** | Copy the `.db` file | `pg_dump` or streaming replication |
| **Best for** | Development, single-user, demos | Production, multi-user, teams |
**Local development:** SQLite works out of the box — no database server, no Redis, nothing to configure. Just start coding.
**Production:** Deploy with Docker Compose and PostgreSQL + Redis are provisioned automatically. No manual database setup needed.
***
## Known Limitation: SQLite Concurrent Streaming
**SQLite can become a bottleneck under concurrent load.**
FIM One uses Server-Sent Events (SSE) for streaming AI responses. During streaming, each active SSE connection holds a database connection from the pool for the duration of the stream. SQLite enforces a global write lock, meaning only one write operation can proceed at a time — all other writes queue behind it.
Even though the connection pool supports up to 30 simultaneous connections (`pool_size=20` + `max_overflow=10`), the bottleneck is the lock itself, not the pool. When multiple users are chatting simultaneously, their write operations (saving messages, updating token counts) serialize against each other.
**Symptoms you may observe:**
* Conversation list loads slowly while another user is streaming
* Settings pages feel sluggish during active chat sessions
* API responses are delayed when multiple streams are active
**Recommendation:** If you have more than 2-3 concurrent users, switch to PostgreSQL. PostgreSQL uses MVCC (Multi-Version Concurrency Control) with row-level locking, so concurrent reads and writes proceed independently without blocking each other.
***
## Connection Pool Configuration
FIM One configures SQLAlchemy connection pool settings internally for each backend. These are tuned defaults that do not require environment variables — they are applied automatically based on the `DATABASE_URL` scheme. Understanding them helps explain runtime behavior.
### SQLite Pool Settings
| Setting | Value | Description |
| ---------------- | -------- | ------------------------------------------------------------------------------------------------------------------------ |
| `pool_size` | `20` | Base number of persistent connections in the pool |
| `max_overflow` | `10` | Additional connections created under load (up to 30 total) |
| WAL journal mode | Enabled | Write-Ahead Logging allows concurrent reads while a write is in progress, significantly reducing lock contention |
| `busy_timeout` | `30s` | When the write lock is held, other writers wait up to 30 seconds before raising an error, instead of failing immediately |
| `synchronous` | `NORMAL` | Safe with WAL mode; provides better write throughput than the default `FULL` |
WAL mode and the 30-second busy timeout are set via SQLite PRAGMAs on every new connection. This combination ensures that short-lived reads (loading a conversation list, fetching settings) are not blocked by long-running write transactions, and that concurrent writes queue gracefully instead of failing.
### PostgreSQL Pool Settings
| Setting | Value | Description |
| -------------- | ------- | ----------------------------------------------------------------------------------------------------------------------------------------- |
| `pool_size` | `10` | Base number of persistent connections in the pool |
| `max_overflow` | `20` | Additional connections created under load (up to 30 total) |
| `pool_timeout` | `30s` | Maximum time to wait for a free connection from the pool before raising a timeout error |
| `pool_recycle` | `1800s` | Connections are recycled every 30 minutes to prevent stale connections (important for cloud-hosted databases that close idle connections) |
PostgreSQL handles concurrency natively via MVCC, so the pool settings primarily control resource usage rather than contention. The 30-minute recycle interval avoids issues with firewalls, load balancers, or managed database services that silently drop idle TCP connections.
***
## Switching to PostgreSQL
### Step 1: Start a PostgreSQL Instance
The quickest way is with Docker:
```bash theme={null}
docker run -d \
--name postgres \
-e POSTGRES_PASSWORD=secret \
-e POSTGRES_DB=fim_one \
-p 5432:5432 \
postgres:16-alpine
```
### Step 2: Set the DATABASE\_URL
Add or update the following line in your `.env` file:
```bash theme={null}
DATABASE_URL=postgresql+asyncpg://postgres:secret@localhost:5432/fim_one
```
### Step 3: Restart FIM One
```bash theme={null}
# Local development
./start.sh portal
# Or restart your process manager / systemd service
```
Tables are created automatically on first start. No manual schema migration is needed.
**Existing SQLite data is not migrated automatically.** Switching `DATABASE_URL` from SQLite to PostgreSQL starts with a fresh database. If you have existing conversations, agents, or connectors in SQLite that you need to preserve, see the [Data Migration](#data-migration) section below.
### Docker Compose (Recommended for Production)
If you deploy with Docker Compose, **PostgreSQL and Redis are already included and auto-configured** — there is nothing extra to set up. The `docker-compose.yml` sets `DATABASE_URL` internally — your `.env` value is overridden:
```yaml theme={null}
environment:
DATABASE_URL: postgresql+asyncpg://fim:fim@postgres:5432/fim_one
```
No additional database setup is needed when using Docker Compose.
***
## Data Migration
There is no built-in migration tool from SQLite to PostgreSQL. For most deployments, the recommended approach depends on your situation:
**Fresh deployment (no existing data):** Simply set `DATABASE_URL` to your PostgreSQL connection string and start FIM One. All tables are created automatically.
**Existing data that must be preserved:** Manual export/import is required. The general approach:
1. Export data from SQLite using a tool like `sqlite3` CLI or a Python script
2. Transform the data as needed (SQLite and PostgreSQL have minor type differences)
3. Import into PostgreSQL using `psql`, `pg_restore`, or application-level insert scripts
For most users upgrading from a development setup to production, it is simpler to start fresh with PostgreSQL and re-create your agents and connectors through the UI. Conversation history is typically not critical enough to warrant manual migration.