ironclaw/docs/drafts/setup/database.mdx

---
title: Database Backends
description: PostgreSQL vs libSQL — choosing your database
---

<!-- ⚠️ SUPERSEDED — this page has been promoted to capabilities/database.mdx with corrections. Do not edit this file. -->

IronClaw supports two database backends: **PostgreSQL** and **libSQL** (embedded SQLite). Choose based on your deployment needs.

## Quick Comparison

| Feature | PostgreSQL | libSQL |
|---------|------------|--------|
| **Setup** | Requires PostgreSQL server | Zero-dependency, auto-created |
| **Best For** | Production, multi-user | Personal use, single-user |
| **Search** | Hybrid (FTS + vector) | FTS only (vector via Turso) |
| **Scaling** | Horizontal (read replicas) | Single node |
| **Backup** | pg_dump, replication | File copy, Turso sync |
| **Size** | 100MB+ installed | ~5MB binary |

## PostgreSQL

Recommended for production deployments, multi-user scenarios, and high-throughput use cases.

### Requirements

- PostgreSQL 15 or later
- pgvector extension for embeddings

### Installation

```bash
# Ubuntu/Debian
sudo apt install postgresql-15 postgresql-15-pgvector

# macOS (Homebrew)
brew install postgresql
brew install pgvector

# Start PostgreSQL
sudo systemctl enable --now postgresql  # Linux
brew services start postgresql          # macOS
```

### Configuration

```bash
# Create database
sudo -u postgres psql -c "CREATE DATABASE ironclaw;"
sudo -u postgres psql -c "CREATE USER ironclaw WITH PASSWORD 'your-password';"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE ironclaw TO ironclaw;"

# Enable pgvector
sudo -u postgres psql -d ironclaw -c "CREATE EXTENSION IF NOT EXISTS vector;"
```

### IronClaw Configuration

```bash
export DATABASE_BACKEND=postgres
export DATABASE_URL="postgres://ironclaw:your-password@localhost/ironclaw"
```

Or in the wizard:
1. Select "PostgreSQL"
2. Enter connection string
3. Test connection

### SSL Modes

| Mode | Behavior | Use Case |
|------|----------|----------|
| `disable` | Never use TLS | Local development |
| `prefer` | Try TLS, fallback to plaintext | **Default** — works everywhere |
| `require` | Require TLS | Production with TLS |

```bash
export DATABASE_SSLMODE=require
```

## libSQL

Recommended for personal use, development, and single-user deployments. Zero setup required.

### How It Works

libSQL is an embedded SQLite-compatible database:
- Database is a single file (`~/.ironclaw/ironclaw.db`)
- No separate server process
- Auto-created on first connection
- Full SQLite feature set

### IronClaw Configuration

```bash
export DATABASE_BACKEND=libsql
export LIBSQL_PATH="/home/user/.ironclaw/ironclaw.db"
```

Or just use the wizard defaults:
1. Select "libSQL"
2. Accept default path
3. Done!

### Turso Cloud Sync

libSQL supports syncing to Turso for cloud backup:

```bash
export DATABASE_BACKEND=libsql
export LIBSQL_PATH="/home/user/.ironclaw/ironclaw.db"
export LIBSQL_URL="libsql://your-db.turso.io"
export LIBSQL_AUTH_TOKEN="your-auth-token"
```

This keeps a local copy with automatic cloud sync.

## Feature Comparison

### Hybrid Search

**PostgreSQL:** Full hybrid search (FTS + vector via RRF)
```
Keyword matches + semantic similarity
Reciprocal Rank Fusion ranking
```

**libSQL:** FTS only (text search)
```
Keyword matching via FTS5
Vector search via Turso cloud only
```

### Embeddings

Both backends support embeddings, but with different implementations:

| Backend | Embeddings | Notes |
|---------|------------|-------|
| PostgreSQL | Yes | pgvector for vector storage |
| libSQL local | FTS only | No local vector storage |
| libSQL + Turso | Yes | Via Turso vector indexes |

<Warning>
**Encryption at rest:** The local SQLite database stores conversation and workspace data in plaintext. Only secrets (API tokens) are encrypted with AES-256-GCM. If you handle sensitive data, use full-disk encryption (FileVault, LUKS, BitLocker) or choose PostgreSQL with TDE.
</Warning>

## Migration

### From libSQL to PostgreSQL

1. **Export from libSQL:**
   ```bash
   sqlite3 ~/.ironclaw/ironclaw.db ".dump" > ironclaw.sql
   ```

2. **Import to PostgreSQL:**
   ```bash
   psql -d ironclaw -f ironclaw.sql
   ```

3. **Update IronClaw config:**
   ```bash
   export DATABASE_BACKEND=postgres
   export DATABASE_URL="postgres://user:pass@localhost/ironclaw"
   ```

4. **Restart IronClaw**

### From PostgreSQL to libSQL

1. **Export:**
   ```bash
   pg_dump -h localhost -U ironclaw ironclaw > ironclaw.sql
   ```

2. **Convert and import to SQLite** (requires conversion tools)

3. **Update IronClaw config**

## When to Choose Which

### Choose libSQL if:

- Running IronClaw on a personal laptop/desktop
- Single-user deployment
- Want zero database administration
- Don't need horizontal scaling
- FTS-only search is sufficient

### Choose PostgreSQL if:

- Production multi-user deployment
- Need hybrid (FTS + vector) search locally
- High-throughput scenario
- Existing PostgreSQL infrastructure
- Require advanced backup/recovery
- Team or shared deployment

## Backup

### PostgreSQL

```bash
# Backup
pg_dump -h localhost -U ironclaw ironclaw > backup.sql

# Restore
psql -d ironclaw -f backup.sql
```

### libSQL

```bash
# Backup (simple file copy)
cp ~/.ironclaw/ironclaw.db ~/.ironclaw/ironclaw.db.backup

# Restore
cp ~/.ironclaw/ironclaw.db.backup ~/.ironclaw/ironclaw.db

# With Turso: automatic cloud backup
```

## Troubleshooting

<AccordionGroup>
  <Accordion title="pgvector not found" icon="x-circle">
    ```bash
    # Install pgvector
    sudo apt install postgresql-15-pgvector

    # Or compile manually
    git clone https://github.com/pgvector/pgvector.git
    cd pgvector
    make
    sudo make install
    ```
  </Accordion>

  <Accordion title="Database file is locked" icon="lock">
    ```bash
    # Find and kill process
    lsof ~/.ironclaw/ironclaw.db
    kill -9 <pid>

    # Or wait for it to release
    ```
  </Accordion>

  <Accordion title="Connection refused (PostgreSQL)" icon="network">
    ```bash
    # Check PostgreSQL is running
    sudo systemctl status postgresql

    # Check listen addresses
    sudo -u postgres psql -c "SHOW listen_addresses;"

    # Should be '*' or 'localhost'
    ```
  </Accordion>
</AccordionGroup>

## Next Steps

<CardGroup cols={2}>
  <Card title="Configuration" icon="settings" href="/setup/configuration">
    Full environment variable reference
  </Card>

  <Card title="VPS Install" icon="server" href="/install/vps">
    Production deployment guide with PostgreSQL
  </Card>
</CardGroup>