---
title: "Migration Guide to HeliosDB Lite"
---

# Migration Guide to HeliosDB Lite

This guide helps you migrate your existing database to HeliosDB Lite from SQLite, MySQL, or PostgreSQL.

## Table of Contents

- [From SQLite](#from-sqlite)
- [From MySQL](#from-mysql)
- [From PostgreSQL](#from-postgresql)
- [Common Migration Steps](#common-migration-steps)
- [ORM Migration](#orm-migration)
- [Performance Tuning](#performance-tuning)

---

## From SQLite

HeliosDB Lite is designed to be a drop-in replacement for SQLite with PostgreSQL compatibility.

### Schema Differences

#### Auto-Increment

**SQLite:**
```sql
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);
```

**HeliosDB Lite:**
```sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);
```

#### Data Types

| SQLite | HeliosDB Lite | Notes |
|--------|---------------|-------|
| INTEGER | INT, BIGINT | Use BIGINT for large numbers |
| REAL | REAL, DOUBLE PRECISION | Supported |
| TEXT | TEXT, VARCHAR | Both supported |
| BLOB | BYTEA | Supported |
| NULL | NULL | Fully supported |

#### Type Affinity

SQLite uses dynamic typing, while HeliosDB Lite uses static typing like PostgreSQL:

**SQLite (flexible):**
```sql
INSERT INTO users (id, name) VALUES ('1', 'Alice');  -- String '1' OK
INSERT INTO users (id, name) VALUES (2, 123);        -- Number 123 OK
```

**HeliosDB Lite (strict):**
```sql
INSERT INTO users (id, name) VALUES (1, 'Alice');    -- Correct
INSERT INTO users (id, name) VALUES (2, 123);        -- Error: 123 is not TEXT
```

### Migration Steps

#### 1. Export SQLite Data

```bash
# Export schema
sqlite3 mydb.sqlite .schema > schema.sql

# Export data as SQL
sqlite3 mydb.sqlite .dump > data.sql
```

#### 2. Convert Schema

Convert the schema by hand:

```bash
#### 3. Load into HeliosDB Lite

Start a Lite server and load the files with `psql`:

```bash
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"   # required by heliosdb-lite start
heliosdb-lite start --data-dir ./mydb --daemon

psql -h 127.0.0.1 -p 5432 -U postgres -f helios_schema.sql
psql -h 127.0.0.1 -p 5432 -U postgres -f data.sql
```

#### 4. Update Application Code

**SQLite (using rusqlite):**
```rust
let conn = Connection::open("mydb.sqlite")?;
let mut stmt = conn.prepare("SELECT * FROM users WHERE id = ?")?;
let user: User = stmt.query_row([1], |row| {
    Ok(User {
        id: row.get(0)?,
        name: row.get(1)?,
    })
})?;
```

**HeliosDB Lite:** connect over the PostgreSQL wire protocol with a PostgreSQL driver (for
Rust, `tokio-postgres` or `sqlx`) and use `$1`, `$2` ... placeholders instead of `?`:

```rust
let (client, connection) = tokio_postgres::connect(
    "host=127.0.0.1 port=5432 user=postgres",
    NoTls,
).await?;
tokio::spawn(connection);
let row = client.query_one("SELECT id, name FROM users WHERE id = $1", &[&1i32]).await?;
```

### SQLite Feature Compatibility

| Feature | SQLite | HeliosDB Lite |
|---------|--------|---------------|
| ATTACH DATABASE | Yes | No |
| Full-text search (FTS5) | Yes | Planned |
| JSON functions | Yes | Yes (`json_extract`, `json_array_length`, `json_type`) |
| R*Tree spatial index | Yes | No (HNSW vector indexes for similarity search) |
| Window functions | Yes | Yes |
| CTEs (`WITH`) | Yes | Yes |

---

## From MySQL

### Schema Differences

#### Auto-Increment

**MySQL:**
```sql
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);
```

**HeliosDB Lite:**
```sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);
```

#### Data Types

| MySQL | HeliosDB Lite | Notes |
|-------|---------------|-------|
| TINYINT | INT | Use INT or SMALLINT |
| MEDIUMINT | INT | Use INT |
| INT | INT | Direct mapping |
| BIGINT | BIGINT | Direct mapping |
| FLOAT | REAL | Supported |
| DOUBLE | DOUBLE PRECISION | Supported |
| DECIMAL | DECIMAL | Supported |
| VARCHAR | VARCHAR, TEXT | Both supported |
| TEXT | TEXT | Direct mapping |
| BLOB | BYTEA | Supported |
| DATE | DATE | Supported |
| DATETIME | TIMESTAMP | Supported |
| JSON | JSON, JSONB | Supported |

#### Quoting

**MySQL (backticks):**
```sql
SELECT `user`.`name` FROM `users` WHERE `id` = 1;
```

**HeliosDB Lite (double quotes or none):**
```sql
SELECT "user"."name" FROM users WHERE id = 1;
-- Or without quotes:
SELECT users.name FROM users WHERE id = 1;
```

### Migration Steps

#### 1. Export MySQL Data

```bash
# Export schema
mysqldump -u root -p --no-data mydb > schema.sql

# Export data
mysqldump -u root -p --no-create-info mydb > data.sql
```

#### 2. Convert Schema

```bash
# Replace backticks with double quotes
sed 's/`/"/g' schema.sql > helios_schema.sql

# Convert AUTO_INCREMENT
sed 's/AUTO_INCREMENT/SERIAL/g' helios_schema.sql > helios_schema_fixed.sql

# Remove MySQL-specific clauses
sed 's/ ENGINE=InnoDB//g' helios_schema_fixed.sql > helios_schema_final.sql
sed -i 's/ DEFAULT CHARSET=utf8mb4//g' helios_schema_final.sql
```

#### 3. Update Application Code

**MySQL (using mysql crate):**
```rust
let pool = Pool::new("mysql://root:password@localhost/mydb")?;
let mut conn = pool.get_conn()?;
let users: Vec<User> = conn.query_map(
    "SELECT id, name FROM users WHERE id = ?",
    |id, name| User { id, name },
)?;
```

**HeliosDB Lite:** connect with a PostgreSQL driver and switch `?` placeholders to `$1`, `$2` ...:

```rust
let (client, connection) = tokio_postgres::connect(
    "host=127.0.0.1 port=5432 user=postgres",
    NoTls,
).await?;
tokio::spawn(connection);
let rows = client.query("SELECT id, name FROM users WHERE id = $1", &[&1i32]).await?;
```

### MySQL Feature Compatibility

| Feature | MySQL | HeliosDB Lite |
|---------|-------|---------------|
| Stored procedures | Yes | Functions in SQL and PL/pgSQL; MySQL procedure syntax is not supported |
| Triggers | Yes | Yes |
| Views | Yes | Yes |
| Full-text search | Yes | Planned |
| JSON functions | Yes | Yes |
| Spatial data types | Yes | No (vector columns for embeddings) |

---

## From PostgreSQL

HeliosDB Lite aims for 95%+ PostgreSQL compatibility.

### Schema Differences

#### Mostly Compatible

Most PostgreSQL schemas work as-is:

```sql
-- This works in both PostgreSQL and HeliosDB Lite
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

#### Not Yet Supported

- ENUM types → Use TEXT with CHECK constraint
- ARRAY column types → Planned (store lists as JSON meanwhile)
- Custom types → Use built-in types
- Extensions → Not loadable; vector search, encryption and time travel are built in

#### Workarounds

**PostgreSQL ENUM:**
```sql
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TABLE person (
    name TEXT,
    current_mood mood
);
```

**HeliosDB Lite:**
```sql
CREATE TABLE person (
    name TEXT,
    current_mood TEXT CHECK (current_mood IN ('happy', 'sad', 'neutral'))
);
```

### Migration Steps

#### 1. Export PostgreSQL Data

```bash
# Export schema
pg_dump -s -U postgres mydb > schema.sql

# Export data
pg_dump -a -U postgres mydb > data.sql

# Or export to CSV
psql -U postgres -d mydb -c "COPY users TO '/tmp/users.csv' CSV HEADER"
```

#### 2. Load into HeliosDB Lite

Most PostgreSQL SQL works directly:

Start a Lite server and load the files with `psql`:

```bash
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"   # required by heliosdb-lite start
heliosdb-lite start --data-dir ./mydb --daemon

psql -h 127.0.0.1 -p 5432 -U postgres -f schema.sql
psql -h 127.0.0.1 -p 5432 -U postgres -f data.sql
```

#### 3. Update Application Code

Minimal changes needed if using PostgreSQL wire protocol:

**PostgreSQL (using tokio-postgres):**
```rust
let (client, connection) = tokio_postgres::connect(
    "postgresql://postgres@localhost/mydb",
    NoTls,
).await?;

let rows = client.query("SELECT * FROM users WHERE id = $1", &[&1]).await?;
```

**HeliosDB Lite:** the same code works over the PostgreSQL wire protocol; change only the
connection string:

```rust
let (client, connection) = tokio_postgres::connect(
    "postgresql://postgres@127.0.0.1:5432/heliosdb",
    NoTls,
).await?;

let rows = client.query("SELECT * FROM users WHERE id = $1", &[&1]).await?;
```

### PostgreSQL Feature Compatibility

| Feature | PostgreSQL | HeliosDB Lite |
|---------|------------|---------------|
| ACID transactions | Yes | Yes |
| MVCC | Yes | Yes |
| Indexes | Yes | Yes (B-tree and HNSW vector) |
| Views | Yes | Yes, plus materialized views |
| CTEs | Yes | Yes |
| Window functions | Yes | Yes |
| JSON/JSONB | Yes | Yes |
| Full-text search | Yes | Planned |
| Extensions (pgvector) | Yes | Not loadable; vector search is built in |

---

## Common Migration Steps

### 1. Data Export

Choose export format:

- **SQL dumps** - Best for small to medium databases (<10GB)
- **CSV** - Best for large datasets, fastest import

### 2. Schema Conversion

Convert the schema by hand, using the per-source lists above (auto-increment, data types,
quoting, unsupported types).

### 3. Data Import

Load SQL files with `psql -f`, and CSV files with `COPY ... FROM STDIN` over the PostgreSQL
wire (`psql -c "\copy users FROM 'users.csv' CSV HEADER"`). For large datasets, load in
batches of a few thousand rows per transaction.

### 4. Verify Migration

```sql
-- Check row counts
SELECT 'users' as table_name, COUNT(*) as row_count FROM users
UNION ALL
SELECT 'orders', COUNT(*) FROM orders;

-- Compare checksums (if applicable)
SELECT MD5(CAST(ROW(users.*) AS TEXT)) FROM users ORDER BY id;

-- Validate constraints
SELECT * FROM users WHERE email IS NULL;  -- Should be empty if NOT NULL
```

### 5. Update Application

- Update connection strings
- Update library dependencies
- Test all queries
- Run integration tests
- Monitor performance

---

## ORM Migration

Most ORMs work with PostgreSQL compatibility:

### SQLAlchemy (Python)

**Before (SQLite):**
```python
engine = create_engine('sqlite:///mydb.db')
```

**After (HeliosDB Lite - server mode):**
```python
engine = create_engine('postgresql://postgres@127.0.0.1:5432/heliosdb')
```

### Diesel (Rust)

**Before (SQLite):**
```rust
let connection = SqliteConnection::establish("mydb.db")?;
```

**After (HeliosDB Lite - server mode):** use Diesel's PostgreSQL backend:
```rust
let connection = PgConnection::establish("postgresql://postgres@127.0.0.1:5432/heliosdb")?;
```

### Prisma (TypeScript)

**Before (PostgreSQL):**
```prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
```

**After (HeliosDB Lite):**
```prisma
datasource db {
  provider = "postgresql"  // Same!
  url      = env("DATABASE_URL")  // Just change connection string
}
```

See [ORM Support](/docs/lite/features/orm_support/) for complete ORM examples.

---

## Performance Tuning

### Optimize for Write-Heavy Workloads

```toml
# heliosdb.toml
[storage]
wal_sync_mode = "group_commit"  # batch WAL flushes
```

### Optimize for Read-Heavy Workloads

```toml
[storage]
cache_size = 1073741824  # 1 GB cache
```

### Optimize for Large Datasets

```toml
[storage]
compression = "Zstd"  # Zstd, Lz4 or None
```

## Troubleshooting

### Issue: Type Mismatch Errors

**Problem:**
```
Error: Type mismatch: expected INT, got TEXT
```

**Solution:**
SQLite allows flexible types. HeliosDB Lite requires explicit types.

```sql
-- Before (SQLite):
INSERT INTO users VALUES ('1', 'Alice');

-- After (HeliosDB Lite):
INSERT INTO users VALUES (1, 'Alice');
```

### Issue: Syntax Errors

**Problem:**
```
Error: Unexpected token: `
```

**Solution:**
Replace MySQL backticks with double quotes or remove them:

```sql
-- Before (MySQL):
SELECT `name` FROM `users`

-- After (HeliosDB Lite):
SELECT name FROM users
-- Or:
SELECT "name" FROM "users"
```

### Issue: Missing Features

**Problem:** a PostgreSQL schema uses a type Lite does not support, such as `CREATE TYPE ... AS ENUM`.

**Solution:**
Rewrite it with supported features, for example a `TEXT` column with a `CHECK` constraint
(see [Workarounds](#workarounds)).

---

## Migration Checklist

- [ ] Export schema from source database
- [ ] Convert schema to HeliosDB Lite format
- [ ] Export data from source database
- [ ] Create HeliosDB Lite database
- [ ] Load schema into HeliosDB Lite
- [ ] Load data into HeliosDB Lite
- [ ] Verify row counts match
- [ ] Test all application queries
- [ ] Update application connection strings
- [ ] Run integration tests
- [ ] Benchmark performance
- [ ] Deploy to production

---

## Getting Help

- **Documentation**: [www.heliosdb.com/docs/lite](https://www.heliosdb.com/docs/lite/)
- **Discord**: [discord.gg/yTykuUrFXc](https://discord.gg/yTykuUrFXc)
- **Support**: [support@heliosdb.com](mailto:support@heliosdb.com)

---

**Migration Support**: Need help migrating? Contact us at support@heliosdb.com