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 MySQL
From PostgreSQL
Common Migration Steps
ORM Migration
Performance Tuning

From SQLite¶

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

Schema Differences¶

Auto-Increment¶

SQLite:

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

HeliosDB Lite:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

Data Types¶

SQLite	HeliosDB Lite	Notes
INTEGER	INT, BIGINT	Use BIGINT for large numbers
REAL	FLOAT4, FLOAT8	Coming soon
TEXT	TEXT, VARCHAR	Both supported
BLOB	BYTEA	Coming soon
NULL	NULL	Fully supported

Type Affinity¶

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

SQLite (flexible):

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):

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¶

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

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

2. Convert Schema¶

# Use helper script (coming soon)
python3 scripts/convert_sqlite_schema.py schema.sql > helios_schema.sql

# Or manually convert:
# - AUTOINCREMENT → SERIAL
# - Remove IF NOT EXISTS (use migrations)
# - Add explicit types

3. Load into HeliosDB Lite¶

Embedded mode:

use heliosdb_lite::EmbeddedDatabase;

let db = EmbeddedDatabase::new("./heliosdb.db")?;

// Load schema
db.execute(&std::fs::read_to_string("helios_schema.sql")?)?;

// Load data (in batches for large datasets)
db.execute(&std::fs::read_to_string("data.sql")?)?;

Command line:

heliosdb-lite init ./mydb
heliosdb-lite repl ./mydb < helios_schema.sql
heliosdb-lite repl ./mydb < data.sql

4. Update Application Code¶

SQLite (using rusqlite):

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:

let db = EmbeddedDatabase::new("mydb.db")?;
let results = db.query("SELECT * FROM users WHERE id = $1", &[&1])?;
let user = User::from_tuple(&results[0])?;

SQLite Feature Compatibility¶

Feature	SQLite	HeliosDB Lite
ATTACH DATABASE	✅	❌
FTS5	✅	🔜 Phase 2
JSON1	✅	🔜 Phase 2
R*Tree	✅	✅ (as Vector Index)
Window Functions	✅	🔜 Phase 2
CTEs	✅	❌

From MySQL¶

Schema Differences¶

Auto-Increment¶

MySQL:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

HeliosDB Lite:

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	FLOAT4	Coming soon
DOUBLE	FLOAT8	Coming soon
DECIMAL	DECIMAL	Coming soon
VARCHAR	VARCHAR, TEXT	Both supported
TEXT	TEXT	Direct mapping
BLOB	BYTEA	Coming soon
DATE	DATE	Coming soon
DATETIME	TIMESTAMP	Coming soon
JSON	JSONB	Coming soon (Phase 2)

Quoting¶

MySQL (backticks):

SELECT `user`.`name` FROM `users` WHERE `id` = 1;

HeliosDB Lite (double quotes or none):

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¶

# 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¶

# 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):

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:

let db = EmbeddedDatabase::new("./mydb.db")?;
let results = db.query("SELECT id, name FROM users WHERE id = $1", &[&1])?;
let users: Vec<User> = results.iter()
    .map(|row| User::from_tuple(row))
    .collect::<Result<Vec<_>>>()?;

MySQL Feature Compatibility¶

Feature	MySQL	HeliosDB Lite
Stored Procedures	✅	❌
Triggers	✅	🔜 Phase 3
Views	✅	🔜 Phase 2
Full-Text Search	✅	🔜 Phase 2
JSON Functions	✅	🔜 Phase 2
Spatial Data	✅	✅ (as Vector)

From PostgreSQL¶

HeliosDB Lite aims for 95%+ PostgreSQL compatibility.

Schema Differences¶

Mostly Compatible¶

Most PostgreSQL schemas work as-is:

-- 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 types → Coming in Phase 2
Custom types → Use built-in types
Extensions → Built-in equivalents coming

Workarounds¶

PostgreSQL ENUM:

CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TABLE person (
    name TEXT,
    current_mood mood
);

HeliosDB Lite:

CREATE TABLE person (
    name TEXT,
    current_mood TEXT CHECK (current_mood IN ('happy', 'sad', 'neutral'))
);

Migration Steps¶

1. Export PostgreSQL Data¶

# 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:

heliosdb-lite init ./mydb
heliosdb-lite repl ./mydb < schema.sql
heliosdb-lite repl ./mydb < data.sql

3. Update Application Code¶

Minimal changes needed if using PostgreSQL wire protocol:

PostgreSQL (using tokio-postgres):

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 (embedded):

let db = EmbeddedDatabase::new("./mydb.db")?;
let rows = db.query("SELECT * FROM users WHERE id = $1", &[&1])?;

HeliosDB Lite (server mode - coming soon):

// Will work with tokio-postgres as-is:
let (client, connection) = tokio_postgres::connect(
    "postgresql://heliosdb@localhost/mydb",
    NoTls,
).await?;

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

PostgreSQL Feature Compatibility¶

Feature	PostgreSQL	HeliosDB Lite
ACID Transactions	✅	✅
MVCC	✅	✅
Indexes	✅	🔜 Phase 2
Views	✅	🔜 Phase 2
CTEs	✅	❌
Window Functions	✅	🔜 Phase 2
JSON/JSONB	✅	🔜 Phase 2
Full-Text Search	✅	🔜 Phase 2
Extensions (pgvector)	✅	✅ 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
Parquet - Best for analytical workloads (coming soon)

2. Schema Conversion¶

Use our migration tool (coming soon):

heliosdb-migrate convert --from sqlite --to heliosdb schema.sql
heliosdb-migrate convert --from mysql --to heliosdb schema.sql
heliosdb-migrate convert --from postgres --to heliosdb schema.sql

3. Data Import¶

For small datasets (<1M rows):

let db = EmbeddedDatabase::new("./mydb.db")?;
db.execute(&std::fs::read_to_string("data.sql")?)?;

For large datasets:

use heliosdb_lite::bulk::BulkImporter;

let db = EmbeddedDatabase::new("./mydb.db")?;
let importer = BulkImporter::new(&db);

// Import in batches
importer.import_csv("users.csv", "users", 10000)?;

4. Verify Migration¶

-- 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):

engine = create_engine('sqlite:///mydb.db')

After (HeliosDB Lite - server mode):

engine = create_engine('postgresql://localhost:5432/mydb')

Diesel (Rust)¶

Before (SQLite):

let connection = SqliteConnection::establish("mydb.db")?;

After (HeliosDB Lite - embedded):

// Coming in Phase 2
let connection = HeliosDbConnection::establish("mydb.db")?;

Prisma (TypeScript)¶

Before (PostgreSQL):

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

After (HeliosDB Lite):

datasource db {
  provider = "postgresql"  // Same!
  url      = env("DATABASE_URL")  // Just change connection string
}

See ORM_SUPPORT.md for complete ORM examples.

Performance Tuning¶

Optimize for Write-Heavy Workloads¶

# heliosdb.toml
[storage]
write_buffer_size = 134217728  # 128MB
max_background_jobs = 8

Optimize for Read-Heavy Workloads¶

[performance]
cache_size_mb = 1024  # 1GB cache

Optimize for Large Datasets¶

[storage]
compression = "zstd"  # Better compression
max_open_files = 10000

Benchmark After Migration¶

# Run performance tests
cargo test --release --test performance_tests

# Or use pgbench (when wire protocol is ready)
pgbench -i -s 10 postgresql://localhost:5432/mydb
pgbench -c 10 -j 2 -t 1000 postgresql://localhost:5432/mydb

Troubleshooting¶

Issue: Type Mismatch Errors¶

Problem:

Error: Type mismatch: expected INT, got TEXT

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

-- 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:

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

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

Issue: Missing Features¶

Problem:

Error: Window functions not supported

Solution: Rewrite query using supported features or wait for Phase 2:

-- Before:
SELECT name, ROW_NUMBER() OVER (ORDER BY id) FROM users

-- Workaround:
-- Store results and add row numbers in application code

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: docs.heliosdb.com/lite
Discord: discord.gg/heliosdb
GitHub Issues: github.com/heliosdb/heliosdb/issues

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