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

# Migration Guide to HeliosDB Nano


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](#1-another-engine--nano)** | Your data and your SQL | Schema conversion, dialect differences, type strictness, query re-testing |
| **2. [Nano embedded → Nano server](#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](#3-nano--heliosdb-lite-or-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

```bash
# 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
```

```sql
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.

```sql
-- 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

```sql
-- 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

```sql
-- 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

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

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

#### Steps

```bash
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.

```bash
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 is enforced through the embedded API and the REPL; SQL
  `CREATE POLICY` and enforcement for wire clients are planned — see
  [below](#what-does-not-carry-over).

```bash
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

```sql
-- 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.** In the stock Nano server, row-level
  security is enforced through the embedded API and the REPL; SQL
  `CREATE POLICY` and enforcement for REST and wire clients are planned.
- **`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. (Releases can fix upgrade-path bugs, including a
   past data-loss-on-open bug; read the [changelog](https://www.heliosdb.com/docs/nano/releases/changelog/)
   and do the upgrade separately, deliberately, from a verified backup.)
3. **Start the server against the same data directory:**

   ```bash
   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`.

   ```bash
   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.

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

   ```rust
   // 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](https://www.heliosdb.com/docs/nano/guides/concurrency-and-transactions/) and
[Deployment Modes](https://www.heliosdb.com/docs/nano/guides/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.

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

```prisma
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**: [www.heliosdb.com/docs/nano](https://www.heliosdb.com/docs/nano/)
- **GitHub Issues**: [HeliosDatabase/HeliosDB-Nano](https://github.com/HeliosDatabase/HeliosDB-Nano/issues)
- **Discord**: [discord.gg/yTykuUrFXc](https://discord.gg/yTykuUrFXc)
- **Email**: support@heliosdb.com