> ## Documentation Index
> Fetch the complete documentation index at: https://docs.scrapai.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Configuration

> Configure SQLite or PostgreSQL database for scrapai CLI

## Overview

scrapai stores crawl metadata, spider configurations, and queue data in a relational database.

## Database Options

### SQLite (Default)

**Best for:** Development, small to medium projects (\< 1M items), single-user usage.

**Pros:** No setup, zero configuration, file-based, built into Python.

**Cons:** Limited concurrency, single writer, not suitable for distributed systems.

### PostgreSQL

**Best for:** Production, large projects (1M+ items), multi-user environments, distributed crawling.

**Pros:** Excellent concurrency, scales to billions of rows, advanced indexing.

**Requires:** PostgreSQL 12+ installed and running.

## SQLite Configuration

### Default Setup

SQLite is configured by default. Run `./scrapai setup` to create `scrapai.db` in the project root.

### Custom Path

Update `.env` to use a different location:

```bash theme={null}
DATABASE_URL=sqlite:///scrapai.db                    # Relative
DATABASE_URL=sqlite:////absolute/path/to/scrapai.db  # Absolute
```

<Tip>
  Use three slashes `///` for relative paths and four slashes `////` for absolute paths.
</Tip>

### Optimization

scrapai automatically applies optimized SQLite settings (WAL mode, 64MB cache) in `core/db.py`.

## PostgreSQL Configuration

### Installation

<Tabs>
  <Tab title="macOS">
    ```bash theme={null}
    # Install PostgreSQL
    brew install postgresql@15

    # Start service
    brew services start postgresql@15
    ```
  </Tab>

  <Tab title="Ubuntu/Debian">
    ```bash theme={null}
    # Install PostgreSQL
    sudo apt-get update
    sudo apt-get install postgresql postgresql-contrib

    # Start service
    sudo systemctl start postgresql
    sudo systemctl enable postgresql
    ```
  </Tab>

  <Tab title="Docker">
    ```bash theme={null}
    # Run PostgreSQL container
    docker run -d \
      --name scrapai-postgres \
      -e POSTGRES_PASSWORD=yourpassword \
      -e POSTGRES_DB=scrapai \
      -p 5432:5432 \
      postgres:15
    ```
  </Tab>
</Tabs>

### Create Database

```bash theme={null}
createdb scrapai
```

Or with custom user:

```sql theme={null}
CREATE DATABASE scrapai;
CREATE USER scrapai_user WITH PASSWORD 'secure_password';
GRANT ALL PRIVILEGES ON DATABASE scrapai TO scrapai_user;
```

### Configure Connection

Update `.env` with your connection string:

```bash theme={null}
DATABASE_URL=postgresql://user:password@localhost:5432/scrapai
```

Format: `postgresql://[user]:[password]@[host]:[port]/[database]`

Add `?sslmode=require` for SSL connections.

### Run Migrations

Initialize the database schema:

```bash theme={null}
./scrapai db migrate
```

## Migrating from SQLite to PostgreSQL

<Warning>
  Backup your data first: `cp scrapai.db scrapai.db.backup`
</Warning>

### Steps

1. Install and configure PostgreSQL
2. Update `.env` with PostgreSQL URL
3. Run migrations: `./scrapai db migrate`
4. Transfer data: `./scrapai db transfer sqlite:///scrapai.db`
5. Verify: `./scrapai verify`

<Tip>
  For large databases, use `--skip-items` to transfer only configs and metadata.
</Tip>

The transfer command reads from the source URL (argument) and writes to the current `DATABASE_URL` in `.env`.

## Database Maintenance

### Backup

<Tabs>
  <Tab title="SQLite">
    ```bash theme={null}
    cp scrapai.db scrapai.db.backup
    cp scrapai.db scrapai.db.$(date +%Y%m%d_%H%M%S)
    ```
  </Tab>

  <Tab title="PostgreSQL">
    ```bash theme={null}
    pg_dump scrapai > scrapai_backup.sql
    pg_dump scrapai | gzip > scrapai_backup.sql.gz
    ```
  </Tab>
</Tabs>

### Restore

<Tabs>
  <Tab title="SQLite">
    ```bash theme={null}
    cp scrapai.db.backup scrapai.db
    ```
  </Tab>

  <Tab title="PostgreSQL">
    ```bash theme={null}
    psql scrapai < scrapai_backup.sql
    ```
  </Tab>
</Tabs>

### Optimize

<Tabs>
  <Tab title="SQLite">
    ```bash theme={null}
    sqlite3 scrapai.db "VACUUM; ANALYZE;"
    ```
  </Tab>

  <Tab title="PostgreSQL">
    ```bash theme={null}
    psql scrapai -c "VACUUM ANALYZE;"
    ```
  </Tab>
</Tabs>

## Database Schema

scrapai uses SQLAlchemy ORM with Alembic migrations. Schema defined in `core/models.py`.

**Key Tables:** spiders, projects, crawls, items, queue, analysis

### View Schema

<Tabs>
  <Tab title="SQLite">
    ```bash theme={null}
    sqlite3 scrapai.db ".schema"
    ```
  </Tab>

  <Tab title="PostgreSQL">
    ```bash theme={null}
    psql scrapai -c "\dt"
    ```
  </Tab>
</Tabs>

## Performance Tuning

### SQLite

scrapai automatically applies optimal settings. For extreme performance, increase cache size in `core/db.py`.

### PostgreSQL

Edit `postgresql.conf` to tune memory settings:

```ini theme={null}
shared_buffers = 256MB          # 25% of RAM
effective_cache_size = 1GB      # 50-75% of RAM
work_mem = 16MB
random_page_cost = 1.1          # For SSD
```

Restart: `sudo systemctl restart postgresql`

## Troubleshooting

### Connection Failed

**SQLite:** Check file exists and permissions. Recreate with `rm scrapai.db* && ./scrapai setup`.

**PostgreSQL:** Test connection with `psql -U user -d scrapai`. Check service status and verify `DATABASE_URL`.

### Database Locked (SQLite)

```bash theme={null}
lsof scrapai.db           # Find processes
pkill -f scrapai          # Kill stuck processes
rm scrapai.db-wal *.db-shm  # Clear WAL files
```

### Migration Failed

Check version with `./scrapai db version`. Reset with `rm scrapai.db && ./scrapai setup` or run `alembic upgrade head`.

### Transfer Failed

Verify source is readable and target is accessible. Try with `--skip-items`.

## Security

### PostgreSQL Security

1. Use strong passwords
2. Restrict network access in `pg_hba.conf`
3. Enable SSL: Add `?sslmode=require` to `DATABASE_URL`
4. Set up automated backups

## Related Documentation

* [Environment Setup](/configuration/environment)
* [Installation](/getting-started/installation)
* [CLI Reference](/cli/database)