---
title: "HeliosDB Nano SQLite Compatibility - Frequently Asked Questions"
---

# HeliosDB Nano SQLite Compatibility - Frequently Asked Questions


---

## What "SQLite compatibility" means here

Two separate things travel under this name. Keep them apart:

1. **SQL-dialect compatibility in the engine.** A pre-parser translation layer
   accepts common SQLite-isms so SQLite-shaped SQL runs against Nano. This is
   part of the engine and works over the embedded API, the REPL and the wire
   protocols.
2. **The `heliosdb_sqlite` Python adapter.** A `sqlite3`-shaped Python module
   that drives Nano. It is a separate SDK component with its own, narrower
   scope — see [The Python adapter](#the-python-adapter) below.

Neither of them means Nano is SQLite, and neither means Nano reads SQLite
database files.

---

## General

### Q: Is HeliosDB Nano based on SQLite?

**A:** No. Nano is an independent engine written in Rust. It speaks
PostgreSQL-flavoured SQL and includes a compatibility layer that rewrites common
SQLite syntax before parsing. Sharing syntax is not sharing an implementation.

---

### Q: Can Nano open my existing `.sqlite` / `.db` file?

**A:** No. Nano does not read the SQLite file format, and its 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.

The supported route today is a SQL-level export and reload — `sqlite3 … .dump`,
adjust the dialect, load through the REPL, then count rows on both sides
yourself. The [Migration Guide](https://www.heliosdb.com/docs/nano/guides/migration/) has the full
procedure.

There is a `tools/HELIOSDB_SQLITE_CONVERTER.py` in the Nano engine repository,
but its load path into the engine is **not wired up**: it writes through a mock
connection and verifies row counts against that mock's own tally, so it reports
success without having written a real store. It is a starting point for your own
tooling, not a turnkey converter.

---

### Q: Which SQLite SQL constructs does the engine accept?

**A:** The translation layer handles these before the parser sees the statement:

| SQLite input | Rewritten to |
|---|---|
| `?` positional placeholders | `$1, $2, …` (quote-aware; mixing `?` and `$N` in one statement is an error) |
| `INSERT OR REPLACE INTO t (c1, c2) VALUES …` | `INSERT INTO t (c1, c2) VALUES … ON CONFLICT DO UPDATE SET c1 = EXCLUDED.c1, c2 = EXCLUDED.c2` |
| `INSERT OR IGNORE INTO …` | `INSERT INTO … ON CONFLICT DO NOTHING` |
| `INTEGER PRIMARY KEY AUTOINCREMENT` | `BIGSERIAL PRIMARY KEY` |
| `DATETIME('now')` | `CURRENT_TIMESTAMP` |

`sqlite_master` is served as a system view, and `PRAGMA …` is intercepted at the
parser/protocol entry rather than executed as SQL.

Two caveats worth internalising:

- **`INSERT OR REPLACE` is rewritten to an upsert, which is not what SQLite's
  REPLACE does.** SQLite deletes the conflicting row and inserts a new one:
  unlisted columns revert to their defaults, the rowid changes, and delete
  triggers / `ON DELETE CASCADE` fire. The rewritten `ON CONFLICT DO UPDATE`
  updates the row in place. If your code relies on the delete-then-insert
  behaviour, rewrite it explicitly.
- **The `OR REPLACE` rewrite requires a parenthesised column list.** Without
  one, the statement falls back to a plain `INSERT` and a duplicate row raises a
  unique-violation error instead of replacing anything. Name your columns, or
  write `ON CONFLICT` yourself.

---

### Q: What about type affinity?

**A:** SQLite is dynamically typed; Nano is statically typed like PostgreSQL.
Values SQLite silently accepts are rejected:

```sql
-- SQLite accepts both
INSERT INTO users (id, name) VALUES ('1', 'Alice');
INSERT INTO users (id, name) VALUES (2, 123);

-- Nano: the first coerces or errors depending on the column type,
-- the second errors — 123 is not TEXT
```

This is a real porting task, not a footnote. Audit inserts that rely on SQLite
coercing types for you. See the [Migration Guide](https://www.heliosdb.com/docs/nano/guides/migration/).

---

### Q: Can I use multiple modes (REPL, server, embedded) at the same time?

**A:** Not against the same data. **A data directory is owned by exactly one
running process.** Starting a second process against a directory another process
already has open produces lock errors (`database is locked`) — that is the
documented, expected behaviour, not a bug to tune around.

What you *can* do:

- Run **one** process in whichever access mode you need — embedded library API,
  REPL, foreground server or background daemon.
- If several callers need the same data, run the **server** and have everyone
  connect to it as clients. That is the only way for more than one caller to
  share a dataset.
- Run a server and a REPL side by side as a development convenience — but that
  is **two independent processes with two separate data directories**. Writes in
  one are not visible in the other. They share nothing.

Switching modes is a stop-then-start handoff onto the same data directory, not a
live cutover, and it requires changing the application's access code (embedded
calls become client calls). See [Deployment Modes](https://www.heliosdb.com/docs/nano/guides/deployment_modes/).

---

### Q: Does Nano support concurrent writes?

**A:** It depends on the access mode, and the honest answer has two halves.

- **Concurrent reads** work everywhere — the storage engine is MVCC-based, so
  readers take snapshots and do not block each other or writers.
- **Single-statement autocommit writes** from multiple threads through one
  embedded handle work.
- **Independent overlapping explicit transactions do not work on the embedded
  API.** An embedded handle has one process-global transaction slot, shared
  across clones. Two callers each running `BEGIN … COMMIT` are interleaving into
  one transaction, and the first `COMMIT` or `ROLLBACK` ends it for both.
- **Over a wire connection they do work.** Each PostgreSQL or MySQL connection
  gets its own session and its own transaction state.

Full detail, including what to do instead:
[Concurrency and transactions](https://www.heliosdb.com/docs/nano/guides/concurrency-and-transactions/).

---

### Q: How do transactions work?

**A:** `BEGIN` / `COMMIT` / `ROLLBACK` behave as expected within a single
session. Isolation levels are `READ COMMITTED`, `REPEATABLE READ` (also spelled
`SNAPSHOT`), and `SERIALIZABLE` — with the caveat that `SERIALIZABLE` is served
as snapshot isolation and permits write skew. The
`storage.serializable_policy` setting controls whether that produces a warning
(default) or a hard error.

The constraint that actually bites when porting from SQLite is the one above:
one explicit transaction per embedded process.

---

### Q: How do I back up a Nano database?

**A:** Use the dump/restore path:

```bash
heliosdb-nano dump --data-dir ./my-data --output backup.heliodump
heliosdb-nano restore --input backup.heliodump --target ./restored --verify
```

The same operations exist on the embedded API as `dump_full` and
`restore_from_dump`, with compression options and incremental (append) dumps.

Do **not** treat a file copy of a live data directory as a backup, and do not
use the Python adapter's `backup()` for this — see below.

Branches and time travel are not backups either. Time travel depends on retained
versions, and retention is bounded and configurable.

---

## The Python adapter

### Q: Where do I get `heliosdb_sqlite`?

**A:** It is **not currently published to PyPI**. `pip install heliosdb-sqlite`
resolves to nothing. Install it from the HeliosDB SDKs repository instead:

```bash
pip install "heliosdb-sqlite @ git+https://github.com/HeliosDatabase/HeliosDB-SDKs.git#subdirectory=sdks/python-sqlite"
```

Its embedded mode runs the engine through **`heliosdb-nano-embedded`**, the
published PyO3 binding (`import heliosdb_nano`), which it installs on Linux
x86_64. `heliosdb_sqlite` adds the `sqlite3` API on top: typed results,
`cursor.lastrowid`, transactions, adapters and converters.

```bash
pip install heliosdb-nano-embedded   # the engine binding on its own; import heliosdb_nano
```

Use `heliosdb_sqlite` for code written against `sqlite3`, the binding directly
for its native `query()` / `execute()` API, or `psycopg2` / `asyncpg` against a
Nano server.

---

### Q: Does the adapter run in-process, like `sqlite3` does?

**A:** Yes. Embedded mode runs the Nano engine inside the
Python process through `heliosdb-nano-embedded` and receives typed values
(`int`, `float`, `str`, `bytes`, `None`) with no text parsing. On platforms
without a binding wheel it falls back to a `heliosdb-nano repl` subprocess,
returns values as text and emits a `RuntimeWarning`. Daemon mode talks to a
running server over the PostgreSQL wire protocol.

---

### Q: How are query parameters bound?

**A:** In embedded mode (with `heliosdb-nano-embedded`), as real parameters:
`?`, `?NNN`, `:name`, `@name` and `$name` placeholders become `$1..$n` and the
values are passed to the engine separately from the SQL text.

In daemon mode and in the REPL fallback, values are bound client-side as
escaped SQL literals (placeholders inside strings and comments are left
alone). Validate untrusted input there as you would for any client-side
binding.

---

### Q: Which `sqlite3` APIs are unsupported?

**A:**

| API | Behaviour |
|---|---|
| `create_function()` | Raises `NotSupportedError` |
| `create_aggregate()` | Raises `NotSupportedError` |
| `create_collation()` | Raises `NotSupportedError` |
| `load_extension()` | Raises `NotSupportedError` |
| `enable_load_extension()` | Accepted, does nothing |
| `set_authorizer()` | Accepted, does nothing |
| `set_progress_handler()` | Accepted, does nothing |
| `iterdump()` | Emits schema statements only — no row data |
| `backup()` | Replays `iterdump()` into the target, so it copies **schema only**. Not a data backup |

The PEP 249 exception hierarchy is present (`Error`, `DatabaseError`,
`OperationalError`, `ProgrammingError`, `IntegrityError`, …), but note that
`IntegrityError` is defined and never raised — constraint violations surface as
`DatabaseError`. Do not write `except sqlite3.IntegrityError` and expect it to
catch anything.

Cursors, `Row`, `row_factory`, `executemany`, `executescript`, context-manager
commit/rollback, and `lastrowid` (via an automatic `RETURNING` rewrite) are
implemented.

---

### Q: What connection options actually exist?

**A:** `connect()` takes the standard `sqlite3` signature — `database`,
`timeout`, `detect_types`, `isolation_level`, `check_same_thread`, `factory`,
`cached_statements`, `uri` — plus these HeliosDB-specific keyword arguments:

`mode` (`'embedded'`, `'daemon'`, `'hybrid'`), `data_dir`, `server_host`,
`server_port`, `user`, `password`, `dsn`, `lastrowid_disabled`.

Options you may have seen in older documentation — `enable_vector_search=`,
`enable_time_travel=`, `enable_branching=`, `encryption_key=`,
`heliosdb_mode=`, `enable_cache=`, `cache_size_mb=` — **do not exist**. They are
silently swallowed by `**kwargs` and do nothing.

Likewise, these methods do not exist on a connection: `start_server()`,
`merge_branch()`, `schedule_backup()`, `enable_monitoring()`, `get_metrics()`,
`enable_logging()`, `get_slow_queries()`, `get_connection_stats()`,
`enable_audit_log()`, `set_max_connections()`, `batch_insert()`, and there is no
`connection_pool` module.

The HeliosDB-specific methods that **do** exist are `execute_vector_search()`,
`create_branch()`, and `switch_to_server()` (hybrid mode only).
`switch_branch()` exists but raises `NotSupportedError` — switch branches with
`USE BRANCH` in SQL, or open a new connection.

---

### Q: Can I use it with SQLAlchemy or Django?

**A:** There is no HeliosDB SQLAlchemy dialect and no `heliosdb_django`
backend — those do not exist. For ORM use, run Nano as a server and use the
PostgreSQL driver and dialect your ORM already has:

```python
# SQLAlchemy, against a Nano server
engine = create_engine("postgresql://user:pass@127.0.0.1:5432/heliosdb")
```

```python
# Django settings.py
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "HOST": "127.0.0.1",
        "PORT": 5432,
    }
}
```

This is the supported path for ORM workloads and does not depend on the
unpublished adapter.

---

## Security

### Q: Is encryption on by default?

**A:** No. AES-256-GCM transparent encryption is compiled into the default
build, but it is **disabled at runtime by default** and requires a configured
key source (environment variable or key file; cloud KMS is planned). Seeing "AES-256-GCM" in a feature
list does not mean your database is encrypted. HeliosDB Nano provides zero-knowledge encryption: keys are derived and data encrypted on the client, the server never sees plaintext keys.
A FIPS build mode also exists.

### Q: Is row-level security available?

**A:** It is implemented, but it is reachable only through the embedded API and
the REPL. It is **not** enforced over the PostgreSQL wire protocol, the MySQL
wire protocol, or REST in any release to date. Do not design a multi-tenant
wire-facing application around it today.

### Q: Are branches a security boundary?

**A:** No. Branching is storage-level copy-on-write isolation. It does not
sandbox anything outside the database and is not an access-control mechanism.

---

## Getting help

- **GitHub Issues**: [HeliosDatabase/HeliosDB-Nano](https://github.com/HeliosDatabase/HeliosDB-Nano/issues)
- **Discord**: [discord.gg/yTykuUrFXc](https://discord.gg/yTykuUrFXc)

## Related

- [Deployment Modes](https://www.heliosdb.com/docs/nano/guides/deployment_modes/)
- [Concurrency and transactions](https://www.heliosdb.com/docs/nano/guides/concurrency-and-transactions/)
- [Migration Guide](https://www.heliosdb.com/docs/nano/guides/migration/)
- [Troubleshooting](https://www.heliosdb.com/docs/nano/features/sqlite/heliosdb_sqlite_troubleshooting/)