# How to Perform Database Migration from SQLite to PostgreSQL for AI-Trader

> Migrate AI-Trader data from SQLite to PostgreSQL with the built-in script. This guide covers schema creation, data transfer, and sequence reset for a smooth transition.

- Repository: [✨Data Intelligence Lab@HKU✨/AI-Trader](https://github.com/HKUDS/AI-Trader)
- Tags: migration-guide
- Published: 2026-05-09

---

**You can migrate AI-Trader's data from the default SQLite file to PostgreSQL using the built-in script [`service/server/scripts/migrate_sqlite_to_postgres.py`](https://github.com/HKUDS/AI-Trader/blob/main/service/server/scripts/migrate_sqlite_to_postgres.py), which handles schema creation, bulk data transfer, and sequence reset in a single idempotent operation.**

AI-Trader (HKUDS/AI-Trader) stores all persistent data in `service/server/data/clawtrader.db` by default. For production deployments requiring concurrent access and robustness, migrating this database to PostgreSQL is essential. The repository provides a purpose-built migration utility that executes safely and can be rerun without risking duplicate data.

## Prerequisites and Dependencies

Before executing the database migration from SQLite to PostgreSQL for AI-Trader, verify that your environment meets the following requirements:

- **PostgreSQL driver**: Install `psycopg` (the modern PostgreSQL driver) via the service requirements or directly:

```bash
pip install -r service/requirements.txt

# Or individually:

pip install psycopg[binary]

```

- **Target database**: Ensure the PostgreSQL database exists and is accessible. The migration script will create tables but requires an existing database connection.

- **Environment configuration**: Create a `.env` file in the project root containing your PostgreSQL connection string.

```dotenv

# .env

DATABASE_URL=postgresql://user:password@db-host:5432/ai_trader

```

## Understanding the Migration Architecture

The migration relies on three core modules that coordinate the transfer:

- **[`service/server/scripts/migrate_sqlite_to_postgres.py`](https://github.com/HKUDS/AI-Trader/blob/main/service/server/scripts/migrate_sqlite_to_postgres.py)**: The main executable that orchestrates the entire process, including connection handling, table truncation, data streaming, and sequence reset.
- **[`service/server/database.py`](https://github.com/HKUDS/AI-Trader/blob/main/service/server/database.py)**: Contains the `init_database()` function that defines the PostgreSQL schema structure, ensuring the target database mirrors the SQLite source exactly.
- **[`service/server/config.py`](https://github.com/HKUDS/AI-Trader/blob/main/service/server/config.py)**: Loads the `DATABASE_URL` environment variable from your `.env` file, providing the connection credentials to the migration script.

## Step-by-Step Migration Process

The script implements an eight-stage pipeline designed to handle foreign keys, timestamps, and bulk data efficiently:

### 1. Configuration Loading

The script reads `DATABASE_URL` from [`service/server/config.py`](https://github.com/HKUDS/AI-Trader/blob/main/service/server/config.py) , which parses the project root `.env` file. Alternatively, pass the URL via the `--target` CLI flag to override the environment variable.

### 2. Schema Initialization

Before data transfer begins, the script imports `init_database` from [`service/server/database.py`](https://github.com/HKUDS/AI-Trader/blob/main/service/server/database.py) and executes it against the PostgreSQL connection. This creates all tables (such as `agents`, `agent_messages`, and others) with proper constraints and indexes, ensuring structural parity with the SQLite source.

### 3. Ordered Table Processing

A hard-coded `TABLE_ORDER` list within the migration script defines the precise sequence for data copying. This order respects foreign-key dependencies—for example, copying the `agents` table before `agent_messages` to prevent constraint violations during insertion.

### 4. Target Table Truncation

To guarantee an idempotent migration, the script executes `TRUNCATE ... RESTART IDENTITY CASCADE` on every target table. This removes existing data and resets auto-increment counters, providing a clean slate for the incoming data.

### 5. Timestamp Normalization

The script automatically detects timestamp columns via an internal `TIMESTAMP_COLUMNS` mapping. Each timestamp value passes through a `normalize_timestamp` function that converts local or ambiguous formats into ISO-8601 UTC strings, ensuring temporal consistency between the two database engines.

### 6. Bulk Data Streaming

For each table, the migration selects all rows from the SQLite source, then streams them into PostgreSQL using the `COPY ... FROM STDIN` protocol via the `copy_table` function. This approach avoids loading entire tables into memory and leverages PostgreSQL's optimized bulk-loading pathway for high-performance transfer.

### 7. Sequence Reset

After all rows are inserted, the `reset_sequences` function queries the maximum `id` value for each table and calls `setval` on the associated PostgreSQL serial sequences. This synchronizes the sequence counters so that subsequent application inserts continue from the correct primary key value without collision.

### 8. Connection Cleanup

Finally, the script closes both the SQLite and PostgreSQL connections and prints a completion message. The entire operation is wrapped in transaction logic where possible, ensuring data integrity.

## Configuration Options

You can customize the migration behavior through environment variables or command-line arguments.

**Using `.env` file** (recommended):

```dotenv
DATABASE_URL=postgresql://user:password@db-host:5432/ai_trader

```

**Using CLI flags**:

```bash
python service/server/scripts/migrate_sqlite_to_postgres.py \
    --source /path/to/custom/clawtrader.db \
    --target postgresql://user:pwd@host:5432/ai_trader

```

If both are provided, CLI flags take precedence over the environment variable.

## Running the Migration

Execute the migration from the repository root after configuring your environment:

```bash
python service/server/scripts/migrate_sqlite_to_postgres.py

```

For custom source paths or direct connection strings without a `.env` file:

```bash
python service/server/scripts/migrate_sqlite_to_postgres.py \
    --source service/server/data/clawtrader.db \
    --target postgresql://trader:securepass@localhost:5432/ai_trader

```

## Post-Migration Verification

Confirm the migration succeeded by querying the PostgreSQL database using AI-Trader's connection utilities:

```python
from service.server.database import get_db_connection

with get_db_connection() as conn:
    cur = conn.cursor()
    cur.execute("SELECT COUNT(*) AS cnt FROM agents")
    result = cur.fetchone()
    print(f"Agents migrated to PostgreSQL: {result['cnt']}")

```

You should see counts matching your original SQLite database. Check additional tables like `agent_messages` and any custom tables defined in your schema to ensure complete data integrity.

## Handling Schema Changes

If you modify the SQLite schema (adding columns or tables), update the `init_database()` function in [`service/server/database.py`](https://github.com/HKUDS/AI-Trader/blob/main/service/server/database.py) accordingly before rerunning the migration. The `TRUNCATE ... CASCADE` operation will automatically clear old data, while the updated schema initialization will create the new structure. Because the migration is idempotent, you can safely re-run the script after fixing schema definitions without creating duplicate rows.

## Summary

- **Use the built-in script**: [`service/server/scripts/migrate_sqlite_to_postgres.py`](https://github.com/HKUDS/AI-Trader/blob/main/service/server/scripts/migrate_sqlite_to_postgres.py) handles the complete database migration from SQLite to PostgreSQL for AI-Trader.
- **Install prerequisites**: Ensure `psycopg` is installed and `DATABASE_URL` is configured in your `.env` file or passed via `--target`.
- **Leverage bulk operations**: The script uses `COPY ... FROM STDIN` for efficient data transfer and automatically normalizes timestamps and resets sequences.
- **Verify before deploying**: Use `get_db_connection()` from [`service/server/database.py`](https://github.com/HKUDS/AI-Trader/blob/main/service/server/database.py) to validate row counts and data integrity after migration.
- **Re-run safely**: The idempotent design with table truncation allows repeated execution during development or schema iteration.

## Frequently Asked Questions

### What happens if the migration is interrupted halfway through?

You can safely rerun the script. The migration uses `TRUNCATE ... RESTART IDENTITY CASCADE` at the start to empty target tables, ensuring no duplicate primary keys exist. Since the PostgreSQL schema is recreated via `init_database()` and sequences are reset after data insertion, an interrupted migration can be restarted without manual cleanup.

### Does the migration script preserve foreign key relationships?

Yes. The `TABLE_ORDER` list in [`service/server/scripts/migrate_sqlite_to_postgres.py`](https://github.com/HKUDS/AI-Trader/blob/main/service/server/scripts/migrate_sqlite_to_postgres.py) explicitly sequences table copying to respect foreign key dependencies. Parent tables like `agents` are processed before child tables like `agent_messages`, preventing constraint violations during the bulk copy phase.

### Can I migrate only specific tables instead of the entire database?

The current script is designed for full database migration. It iterates through the complete `TABLE_ORDER` list and truncates all tables before copying. To migrate specific tables, you would need to modify the script's `TABLE_ORDER` definition or temporarily rename unwanted tables in your source SQLite file before running the migration.

### Why are my timestamps showing incorrect values after migration?

The script's `normalize_timestamp` function attempts to convert timestamps to ISO-8601 UTC, but timezone handling depends on how values were stored in SQLite. If your SQLite database stored timestamps in local time without timezone offsets, verify the `TIMESTAMP_COLUMNS` mapping in the migration script includes all relevant columns, and consider standardizing your SQLite data to UTC before migration.