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

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

Convert the schema by hand:

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

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

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:

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

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: connect with a PostgreSQL driver and switch ? placeholders to $1, $2 …:

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:

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

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:

Start a Lite server and load the files with psql:

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

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:

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

-- 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://postgres@127.0.0.1:5432/heliosdb')

Diesel (Rust)

Before (SQLite):

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

After (HeliosDB Lite - server mode): use Diesel’s PostgreSQL backend:

let connection = PgConnection::establish("postgresql://postgres@127.0.0.1:5432/heliosdb")?;

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 for complete ORM examples.

---

Performance Tuning

Optimize for Write-Heavy Workloads

heliosdb.toml

[storage]
wal_sync_mode = "group_commit"  # batch WAL flushes

Optimize for Read-Heavy Workloads

[storage]
cache_size = 1073741824  # 1 GB cache

Optimize for Large Datasets

[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.

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

---

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
• Discord: discord.gg/yTykuUrFXc
• Support: support@heliosdb.com

---

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