Skip to content
HeliosDB HeliosDB Nano

HeliosDB Nano SQLite Compatibility - Frequently Asked Questions

HeliosDB Nano SQLite Compatibility - Frequently Asked Questions

Applies to: HeliosDB Nano 4.31.1

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 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 ships 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 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 inputRewritten 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 AUTOINCREMENTBIGSERIAL 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:

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

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.

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.

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:

Terminal window
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. Obtain the module from the HeliosDB SDKs repository.

Do not confuse it with heliosdb-nano-embedded, which is published and is a different thing: a PyO3 binding that runs the engine genuinely in-process.

Terminal window
pip install heliosdb-nano-embedded   # published; then: import heliosdb_nano

If what you want is Python access to an embedded Nano database, prefer heliosdb-nano-embedded, or connect to a Nano server with psycopg2 / asyncpg.

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

A: No. In its embedded mode it launches a heliosdb-nano REPL as a subprocess and parses that process’s text output. In daemon mode it talks to a running server. Either way there is a process boundary and a text-parsing step between your code and the engine — with the latency and error-reporting consequences you would expect.

Q: How are query parameters bound?

A: Client-side, by substituting values into the SQL string before sending it — not by server-side parameter binding. ?, :name and @name styles are accepted.

This matters for untrusted input: the safety properties of real parameter binding do not apply. Validate and constrain any value that originates outside your application before it reaches a query.

Q: Which sqlite3 APIs are unsupported?

A:

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

# SQLAlchemy, against a Nano server
engine = create_engine("postgresql://user:pass@127.0.0.1:5432/heliosdb")
# 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, file, or cloud KMS). Seeing “AES-256-GCM” in a feature list does not mean your database is encrypted. A separate zero-knowledge mode and a FIPS build mode exist.

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