Migration Guide to HeliosDB Nano

Migration Guide to HeliosDB Nano

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

Table of Contents

• From SQLite
• From MySQL
• From PostgreSQL
• Common Migration Steps
• ORM Migration
• Performance Tuning

---

From SQLite

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

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

Data Types

SQLite	HeliosDB Nano	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 Nano 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 Nano (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 Nano

Embedded mode:

use heliosdb_nano::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-nano init ./mydb
heliosdb-nano repl ./mydb < helios_schema.sql
heliosdb-nano 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 Nano:

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

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

Data Types

MySQL	HeliosDB Nano	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 Nano (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 Nano:

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 Nano
Stored Procedures	✅	❌
Triggers	✅	🔜 Phase 3
Views	✅	🔜 Phase 2
Full-Text Search	✅	🔜 Phase 2
JSON Functions	✅	🔜 Phase 2
Spatial Data	✅	✅ (as Vector)

---

From PostgreSQL

HeliosDB Nano aims for 95%+ PostgreSQL compatibility.

Schema Differences

Mostly Compatible

Most PostgreSQL schemas work as-is:

-- This works in both PostgreSQL and HeliosDB Nano
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 Nano:

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 Nano

Most PostgreSQL SQL works directly:

heliosdb-nano init ./mydb
heliosdb-nano repl ./mydb < schema.sql
heliosdb-nano 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 Nano (embedded):

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

HeliosDB Nano (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 Nano
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_nano::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 Nano - server mode):

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

Diesel (Rust)

Before (SQLite):

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

After (HeliosDB Nano - 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 Nano):

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 Nano requires explicit types.

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

-- After (HeliosDB Nano):
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 Nano):
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 Nano format
• Export data from source database
• Create HeliosDB Nano database
• Load schema into HeliosDB Nano
• Load data into HeliosDB Nano
• 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