Migration Guide to HeliosDB Nano

Migration Guide to HeliosDB Nano

Applies to: HeliosDB Nano 4.31.1

Three different things get called “migration”, and they have almost nothing in common. Work out which one you are doing before reading further:

	What moves	What it costs
1. Another engine → Nano	Your data and your SQL	Schema conversion, dialect differences, type strictness, query re-testing
2. Nano embedded → Nano server	Neither — the same data directory, a different process model	Application access code, authentication, a stop-then-start handoff
3. Nano → Lite / Full	Product tier	Unverified — talk to us first

---

1. Another engine → Nano

From SQLite

Nano is not a drop-in replacement for SQLite, and planning as though it were is the main way this migration goes wrong. Nano is an independent engine with PostgreSQL-flavoured SQL, a compatibility layer for common SQLite syntax, and static typing. The syntax layer absorbs a lot; the typing difference is real work.

It cannot open your SQLite file

Nano does not read the SQLite file format, and its on-disk store is a data directory (a RocksDB store plus sidecar directories), not a single file. Your data has to be exported from SQLite and reloaded into a Nano data directory.

On the converter script in the engine repository. There is a tools/HELIOSDB_SQLITE_CONVERTER.py in the Nano repository, and its schema extraction and rollback logic are real — but its load path into the engine is not wired up. It writes through a mock in-process connection that collects SQL in a list, creates empty placeholder marker files instead of a real store, and “verifies” row counts against that same mock’s own tally, so it reports success without having written anything. Treat it as a starting point for your own tooling, not as a turnkey converter. Use the SQL-level workflow below.

The SQL-level workflow

# 1. Export from SQLite
sqlite3 mydb.sqlite .dump > dump.sql

# 2. Record the ground truth on the SQLite side, per table
sqlite3 mydb.sqlite "SELECT 'users', COUNT(*) FROM users
                     UNION ALL SELECT 'orders', COUNT(*) FROM orders;"
sha256sum mydb.sqlite

# 3. Adjust the dump: it is SQLite's dialect, not Nano's
#    - AUTOINCREMENT / OR REPLACE / OR IGNORE / DATETIME('now') are rewritten
#      by the compatibility layer
#    - anything SQLite-specific beyond that (ATTACH, extension tables,
#      virtual tables) has to be removed or rewritten by hand
#    - columns that relied on type affinity need real types

# 4. Load into a fresh Nano data directory
heliosdb-nano init ./heliosdb-data
heliosdb-nano repl --data-dir ./heliosdb-data < dump.sql

# 5. Verify independently — count on the Nano side yourself
heliosdb-nano repl --data-dir ./heliosdb-data

SELECT 'users' AS t, COUNT(*) FROM users
UNION ALL SELECT 'orders', COUNT(*) FROM orders;

Compare those numbers against step 2. Do not accept any tool’s own claim that a conversion succeeded — count the rows on both sides yourself, per table, and spot-check the widest and most type-loose tables.

A large dump is worth loading table by table rather than in one pass, so that a statement the compatibility layer does not accept identifies itself instead of aborting a multi-hour load.

Type affinity is the real work

SQLite is dynamically typed; Nano is not.

-- SQLite accepts this
INSERT INTO users (id, name) VALUES (2, 123);

-- Nano rejects it: 123 is not TEXT

Every insert or update that relied on SQLite coercing a value for you needs auditing. This is not an edge case — it is the most common source of post- migration errors.

Auto-increment

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

-- Nano (the compatibility layer also rewrites the SQLite form to this)
CREATE TABLE users (id BIGSERIAL PRIMARY KEY, name TEXT);

Type mapping

SQLite	HeliosDB Nano
INTEGER	INTEGER, BIGINT
REAL	REAL, DOUBLE PRECISION
TEXT	TEXT, VARCHAR
BLOB	BYTEA

INSERT OR REPLACE is not ON CONFLICT DO UPDATE

The compatibility layer rewrites INSERT OR REPLACE INTO t (c1, c2) VALUES … to INSERT INTO t (c1, c2) VALUES … ON CONFLICT DO UPDATE SET c1 = EXCLUDED.c1, c2 = EXCLUDED.c2.

That is an in-place update. SQLite’s REPLACE deletes the conflicting row and inserts a new one, which means:

• columns you did not list revert to their defaults (the rewrite leaves them untouched);
• the rowid changes (it does not under the rewrite);
• DELETE triggers and ON DELETE CASCADE fire (they do not under the rewrite).

If your application depends on any of those effects, write the delete and insert explicitly instead of relying on the rewrite.

The rewrite also requires a parenthesised column list. Without one the statement degrades to a plain INSERT, and a duplicate raises a unique-violation error rather than replacing anything.

Other SQLite features

• ATTACH DATABASE is not supported.
• SQLite extension modules (FTS5, JSON1, R*Tree) have no drop-in equivalent. Nano has its own full-text/vector search features with different APIs — verify behaviour against your queries rather than assuming parity.
• Window functions and CTEs are supported.
• User-defined functions, custom aggregates, custom collations and loadable extensions are not available through the Python adapter (they raise NotSupportedError).

Once the data is loaded and counted, re-test the application’s queries and transactions against Nano — including the inserts that relied on type coercion, and anything using INSERT OR REPLACE.

From MySQL

Auto-increment

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

-- Nano
CREATE TABLE users (id BIGSERIAL PRIMARY KEY, name VARCHAR(255));

Type mapping

MySQL	HeliosDB Nano
TINYINT, MEDIUMINT, INT	SMALLINT, INTEGER
BIGINT	BIGINT
FLOAT / DOUBLE	REAL / DOUBLE PRECISION
DECIMAL(p,s)	NUMERIC(p,s)
VARCHAR / TEXT	TEXT, VARCHAR
BLOB	BYTEA
DATETIME	TIMESTAMP
JSON	JSONB

Nano’s MySQL wire listener (--mysql) accepts many MySQL type aliases directly, so an application connecting over that listener may need less schema rewriting than a dump-and-load migration does.

Quoting

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

-- Nano
SELECT "user"."name" FROM users WHERE id = 1;

Steps

mysqldump -u root -p --no-data mydb > schema.sql
mysqldump -u root -p --no-create-info mydb > data.sql

sed 's/`/"/g' schema.sql \
  | sed 's/ ENGINE=InnoDB//g' \
  | sed 's/ DEFAULT CHARSET=utf8mb4//g' > helios_schema.sql

Convert AUTO_INCREMENT columns to BIGSERIAL by hand — a blind sed will also rewrite the table-level AUTO_INCREMENT=<n> clause into something invalid.

heliosdb-nano init ./mydb
heliosdb-nano repl --data-dir ./mydb < helios_schema.sql
heliosdb-nano repl --data-dir ./mydb < data.sql

From PostgreSQL

Most PostgreSQL DDL loads unchanged, and any PostgreSQL client or ORM can talk to a Nano server over the wire protocol. Compatibility is not total, so test rather than assume:

• ENUM types — use TEXT with a CHECK constraint.
• Custom types and PostgreSQL extensions — use built-in equivalents.
• Collation is byte-order (C) only; there is no locale-aware collation. Ordering of non-ASCII text will differ from a locale-configured PostgreSQL.
• Row-level security exists but is not enforced over the wire protocols — see below.

pg_dump -s -U postgres mydb > schema.sql
pg_dump -a -U postgres mydb > data.sql

heliosdb-nano init ./mydb
heliosdb-nano repl --data-dir ./mydb < schema.sql
heliosdb-nano repl --data-dir ./mydb < data.sql

Verifying any engine migration

-- Row counts, per table, on both sides
SELECT COUNT(*) FROM users;

-- Constraint sanity
SELECT * FROM users WHERE email IS NULL;

Then run the application’s own integration tests against Nano. Row counts matching is necessary, not sufficient — type strictness shows up at write time, not at load time.

What does not carry over

• Locale collation. Byte-order only.
• Row-level security over the wire. RLS is implemented, but reachable only through the embedded API and the REPL — not over PostgreSQL wire, MySQL wire or REST in any release to date.
• ATTACH DATABASE / cross-database queries.

---

2. Nano embedded → Nano server

This is not a data migration. The data directory is already in the right format — what changes is which process owns it and how your application reaches it.

It is a stop-then-start handoff, not a live cutover. A data directory is owned by exactly one process; you cannot run the embedded application and a server against the same directory at the same time.

1. Stop the embedded application. Let it shut down cleanly so close-time work runs.

2. Keep the engine version identical across the switch. Change one thing at a time: a mode change combined with a version bump turns two independently debuggable events into one. (Version 4.31.1 itself fixes a data-loss-on-open bug that affected upgrades — do the upgrade separately, deliberately, from a verified backup.)

3. Start the server against the same data directory:

   heliosdb-nano start --data-dir ./my-data --port 5432 --listen 127.0.0.1

4. Configure a real authentication method if the listener is not on loopback. --auth trust is refused on a non-loopback address; every other method requires --password.

   heliosdb-nano start --daemon \
     --data-dir /var/lib/heliosdb \
     --listen 0.0.0.0 --port 5432 \
     --auth scram-sha-256 --password "$HELIOSDB_PASSWORD" \
     --pid-file /run/heliosdb.pid

5. Change the application’s access code. This is real work, not a configuration flip: embedded calls become client calls.

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

   // After: PostgreSQL client
   let (client, connection) =
       tokio_postgres::connect("postgresql://user@127.0.0.1:5432/heliosdb", NoTls).await?;
   let rows = client.query("SELECT * FROM users WHERE id = $1", &[&1]).await?;

6. Validate the new path before decommissioning the old one — connect, run the application’s read and write paths, check authentication behaves as intended.

What you gain

Independent per-connection sessions and transactions, network access, and any PostgreSQL or MySQL client tooling. See Concurrency and transactions and Deployment Modes.

What you lose

REPL meta commands over the connection, and row-level security — which is not enforced over the wire protocols.

---

3. Nano → HeliosDB Lite or Full

Unverified. Whether Lite or Full can import a Nano data directory has not been established, and a shared wire protocol is not evidence of a shared on-disk format. Do not plan a tier migration on that assumption.

If you are approaching the limits of Nano and considering a larger tier, contact us and we will work out the actual path for your data before you commit to it: support@heliosdb.com.

---

ORM notes

Run Nano as a server and use your ORM’s existing PostgreSQL driver and dialect. There is no HeliosDB-specific SQLAlchemy dialect or Django backend.

# SQLAlchemy
engine = create_engine("postgresql://user:pass@127.0.0.1:5432/heliosdb")

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

---

Troubleshooting

Type mismatch errors

Error: Type mismatch: expected INT, got TEXT

SQLite coerced the value; Nano does not. Fix the value at the call site or change the column type deliberately.

Syntax error on a backtick

Error: Unexpected token: `

MySQL backticks are not identifier quotes here. Use double quotes, or no quotes.

Unique-violation instead of a replace

An INSERT OR REPLACE without a parenthesised column list does not get the upsert rewrite. Name the columns, or write ON CONFLICT DO UPDATE explicitly.

database is locked

A second process tried to open a data directory another process already owns. One process per data directory — use server mode if several callers need the same data.

---

Migration checklist

• Identify which of the three transitions you are doing
• Take a verified backup of the source (heliosdb-nano dump … --verify on the Nano side)
• Convert or export the schema
• Load schema, then data
• Compare row counts per table
• Re-test writes that relied on type coercion
• Re-test anything using INSERT OR REPLACE, upserts, or triggers
• Run the application’s integration tests against Nano
• Update connection strings and access code
• Confirm authentication on any non-loopback listener

---

Getting help

• Documentation: docs.heliosdb.com/docs/nano
• GitHub Issues: HeliosDatabase/HeliosDB-Nano
• Discussions: HeliosDatabase/HeliosDB-Nano
• Email: support@heliosdb.com