# Use PostgreSQL Instead of SQLite

> Optional: store Resume Matcher's data in PostgreSQL instead of SQLite. Covers Docker Compose, your own server, the security notes and copying an existing install across.
- **Section**: Setup
- **URL**: https://resumematcher.fyi/docs/postgresql/

---

## Do You Need This?

Probably not. Resume Matcher uses SQLite by default and needs no setup: your data sits in one file, `apps/backend/data/resume_matcher.db` on a local install, or in the `resume-data` volume with Docker.

Use PostgreSQL only if you want to back the database up with PostgreSQL tools, or keep it on a separate server you control. It isn't faster, since the app still writes one change at a time.

Setting `DATABASE_URL` switches the backend to PostgreSQL. Leave it unset and nothing changes.

## Security First

Resume Matcher has no user accounts and no authentication, and PostgreSQL doesn't add any. Anyone who can reach the app can read and change your resumes. Run it only for yourself or on a trusted private network, never exposed to the internet.

Before you use it beyond your own machine:

- **Use a least-privilege role.** Connect with a role that isn't a superuser. The Compose file's `resume` user is a superuser. The role does need to create tables, and the schema if you set `DATABASE_SCHEMA`, because the app does that on startup.
- **Use a random, URL-safe password.** Letters, digits and `-._~` only. `openssl rand -hex 32` makes one. Don't use a guessable value.
- **Require TLS for a remote database.** Add `?sslmode=require` to the URL. The URL goes to the driver unchanged and nothing is enforced for you.

## Option A: Docker Compose

This adds a PostgreSQL 17 container next to the app. Run it from a clone of the repo, as in [Docker Installation](/docs/docker-installation/):

```bash
export POSTGRES_PASSWORD="$(openssl rand -hex 32)"
docker compose -f docker-compose.yml -f docker-compose.postgres.yml up -d
```

The override file starts the `postgres` service with its data in the `postgres-data` volume, points `DATABASE_URL` at it, and waits until the database is healthy before the app starts. The database is only reachable inside the Compose network.

`POSTGRES_PASSWORD` is copied into `DATABASE_URL` as-is, so it must be URL-safe.

**Keep the password.** PostgreSQL stores it in the `postgres-data` volume on first start, and every later `docker compose` command needs the same value. Export it again, or put `POSTGRES_PASSWORD=...` in a `.env` file next to `docker-compose.yml`.

**Keep `.secret_key` too.** It stays in the `resume-data` volume and decrypts your stored API keys. Keep it with the database.

## Option B: Your Own PostgreSQL Server

Create an empty database, then set `DATABASE_URL` in `apps/backend/.env`:

```bash
DATABASE_URL=postgresql://user:password@host:5432/dbname

# Optional: keep the tables in their own schema
DATABASE_SCHEMA=resume_matcher
```

For a remote database, end the URL with `?sslmode=require`:

```bash
DATABASE_URL=postgresql://user:password@db.example.com:5432/dbname?sslmode=require
```

Then restart the backend. A few details:

- **Schemes.** `postgres://`, `postgresql://` and `postgresql+psycopg://` all work. Any other scheme stops the backend at startup, with a message to unset `DATABASE_URL` to use SQLite.
- **Special characters.** Percent-encode them in the password. A URL the app can't parse fails with `DATABASE_URL is not a valid URL.` The error never prints the URL itself.
- **`DATABASE_SCHEMA`.** Lowercase letters, digits and underscores, starting with a letter or underscore, up to 63 characters. The app creates the schema on startup. Instances that use different schemas in one database don't block each other.
- **The MCP server.** It reads the same `apps/backend/.env`, so it uses the same database. See [Use Resume Matcher from Claude Code & Claude Desktop (MCP)](/docs/mcp-server/).

## Confirm Which Database Is in Use

Open the status endpoint and look at `database_stats.backend`. It reads `postgresql` or `sqlite`:

- **Local install:** `http://127.0.0.1:8000/api/v1/status`
- **Docker:** `http://localhost:3000/api/v1/status`

## Tables Are Created, Not Migrated

On first start the app creates its tables. It only creates tables that are missing and never alters one that already exists.

On SQLite, the app adds new columns and indexes to older databases at startup. Those steps don't run on PostgreSQL. The one exception is the ATS score column on the improvements table, which the app adds on both if it's missing. So if a later release changes a table, a PostgreSQL database created earlier may need a manual migration. Take a backup before you upgrade.

## Move Existing Data From SQLite

A new PostgreSQL database starts empty. To bring your resumes across, use the copy script. It copies every table, including your stored API keys as ciphertext, in one transaction.

**Stop the app and the MCP server first.** The script reads one snapshot of the SQLite file, and anything written after it starts isn't copied. Keep them stopped until you've switched over.

### Local install

With `DATABASE_URL` set in `apps/backend/.env`:

```bash
cd apps/backend
uv run python -m app.scripts.copy_sqlite_to_postgres
```

The default source is the SQLite file in your data folder, `apps/backend/data/resume_matcher.db` unless `DATA_DIR` moves it. For another SQLite file, pass `--source`:

```bash
uv run python -m app.scripts.copy_sqlite_to_postgres --source /path/to/resume_matcher.db
```

The script refuses to write into a database that already has rows, so a rerun can't mix two databases. Then start the app.

### Docker install

A Docker install keeps its SQLite file in the `resume-data` volume, not on your host. With `POSTGRES_PASSWORD` exported as in Option A, stop the app, run the script inside the container (where that volume is the default source), then start everything on PostgreSQL:

```bash
docker compose stop
docker compose -f docker-compose.yml -f docker-compose.postgres.yml run --rm -w /app/backend resume-matcher python -m app.scripts.copy_sqlite_to_postgres
docker compose -f docker-compose.yml -f docker-compose.postgres.yml up -d
```

Or copy the file out of the stopped container and pass it with `--source` from a host that can reach the database:

```bash
docker cp resume-matcher:/app/backend/data/resume_matcher.db .
```

Keep `.secret_key` in the volume. Without it, the copied API keys can't be decrypted.

The copy only goes one way, from SQLite to PostgreSQL. To go back to SQLite, unset `DATABASE_URL`. Data written to PostgreSQL isn't copied back.

## Troubleshooting

**`DATABASE_URL must be a postgresql:// URL`**

The URL uses another scheme. Use `postgresql://`, or unset the variable to use SQLite.

**`DATABASE_URL is not a valid URL.`**

Usually a special character in the password. Percent-encode it, or use a URL-safe password.

**`set POSTGRES_PASSWORD` from `docker compose`**

The variable isn't set in this shell. Export it again with the original value, or put it in a `.env` file next to `docker-compose.yml`.

**The app can't connect on a later `docker compose` command**

The password probably differs from the one PostgreSQL stored on first start. Use the original value.

**`Target table ... is not empty.`**

The copy script found existing rows in the target and stopped. Point it at an empty database.

**`SQLite database not found`**

There's no SQLite file at the default path. On a Docker install, run the script in the container or pass `--source`, as above.

## Next Steps

- [Installation](/docs/installation/) for the local setup
- [Docker Installation](/docs/docker-installation/) for the container setup
- [Use Resume Matcher from Claude Code & Claude Desktop (MCP)](/docs/mcp-server/) to drive the same database from Claude