SQLite Compatibility

HeliosDB Nano accepts a deliberate subset of SQLite-flavoured SQL so that existing sqlite3-driven applications can be retargeted with a one-line import swap. The table below is the authoritative reference for what is supported, where the rewriting happens, and what to expect when a feature isn’t covered.

Result types

heliosdb_sqlite (the sqlite3-compatible Python package) returns the Python types sqlite3 returns for the same data:

Column type	Python value
INTEGER, BIGINT, SMALLINT	int
REAL, FLOAT, DOUBLE	float
NUMERIC / DECIMAL	int when integral and within 64 bits, else float (SQLite NUMERIC affinity)
BOOLEAN	int 1 / 0
BLOB / BYTEA	bytes
NULL	None
TEXT, VARCHAR, DATE, TIME, TIMESTAMP, UUID, JSON, other types	str (TIMESTAMP / DATETIME as the stored text, e.g. 2026-10-07 12:34:56)

SELECT id, code FROM t gives (7, 'A2'), as with sqlite3.

How the types are decided:

• SQLite keeps a storage class per value; CPython’s sqlite3 reads it with sqlite3_column_type() for every value (INTEGER to int, REAL to float, TEXT to str, BLOB to bytes, NULL to None) and applies register_converter() converters only when detect_types asks for them. Rust’s rusqlite exposes the same per-value type as ValueRef::{Integer, Real, Text, Blob, Null}.
• Embedded mode runs Nano in the Python process through the heliosdb-nano-embedded binding, whose values arrive already typed (int, float, str, bytes, None), so nothing is parsed from text. Parameters are bound natively as $1..$n.
• Daemon mode receives values over the PostgreSQL wire protocol together with each column’s type OID and converts by that OID, never by what the text looks like (the text '7' in a TEXT column stays '7').

detect_types=PARSE_DECLTYPES / PARSE_COLNAMES, register_converter() (including the default date and timestamp converters), register_adapter() and Connection.text_factory work as in sqlite3. With PARSE_DECLTYPES, converters are looked up by the declared type as written in CREATE TABLE (INTEGER, REAL, DATETIME, BOOLEAN, or a custom name such as POINT), like sqlite3_column_decltype(). The declared type is that of the column a result column comes from, whatever it is named: SELECT d AS name gets d’s converter, while SELECT 'x' AS d and other expressions get none. Converters receive the stored value’s bytes (b'1' for a true BOOLEAN).

Result columns

cursor.description and Row.keys() use sqlite3’s names: the alias when there is one, the declared column name in its declared case for a column reference or * (Id, not id), and the expression as written otherwise (count(*), sum(a)/2). Every result column is returned, also when several share a name, as in SELECT p.id, k.id FROM p JOIN k ..., SELECT * FROM p JOIN k ... or SELECT count(*), count(name), so positional access reads the same column as with sqlite3.

Aggregates return sqlite3’s types: sum() over REAL values is a float, over integers an int, and avg() and total() are float.

Parameters follow sqlite3’s binding rules in both modes. A type sqlite3 cannot bind (object(), dict, set, …) raises the error sqlite3 raises: ProgrammingError on Python 3.11+, InterfaceError before. float('inf') round-trips, and NaN binds as NULL.

heliosdb-nano-embedded is installed with heliosdb_sqlite on Linux x86_64. Where it is not available, embedded mode drives a heliosdb-nano repl subprocess instead and warns that values come back as text.

Feature matrix

Feature	Result	Where it’s handled	Notes
? positional placeholders	Yes	heliosdb_sqlite (embedded, daemon); src/sql/sqlite_compat.rs::rewrite_question_placeholders (engine)	The SDK binds ?, ?NNN, :name, @name and $name like sqlite3, skipping text inside strings, quoted identifiers and comments. In embedded mode values are bound as $n parameters, not spliced into SQL. A wrong number of bindings raises ProgrammingError.
INSERT OR REPLACE INTO t (cols) VALUES …	Yes	src/sql/sqlite_compat.rs::rewrite_insert_or_replace	Rewritten to INSERT … ON CONFLICT DO UPDATE SET col = EXCLUDED.col for every named column. Falls back to a plain INSERT if no column list is given.
INSERT OR IGNORE INTO …	Yes	src/sql/sqlite_compat.rs::rewrite_insert_or_ignore	Rewritten to INSERT … ON CONFLICT DO NOTHING.
INTEGER PRIMARY KEY (rowid alias)	Yes, via SDK	heliosdb_sqlite DDL type mapping	Created as BIGINT PRIMARY KEY DEFAULT nextval('<table>_<column>_rowid_seq'). An INSERT that omits the key, or gives NULL / None, gets one more than the largest key used so far, also after explicit keys (explicit 2 then automatic gives 3). The SDK moves the sequence past every explicit key it inserts, including through executemany(), INSERT … SELECT and UPDATE … SET id = …. PRIMARY KEY (id) on an INTEGER column is handled the same way. Automatic keys are never reused after the largest key is deleted.
INTEGER PRIMARY KEY AUTOINCREMENT	Yes	SDK; engine	Through the SDK: as INTEGER PRIMARY KEY above. Through other clients: a 64-bit (INT8) key assigned when omitted, from a counter that does not yet continue above explicitly inserted keys.
Integer, REAL, BLOB, DATETIME column types	Yes, via SDK	heliosdb_sqlite DDL type mapping	SQLite stores every integer in 64 bits and every REAL as a double. In CREATE TABLE / ALTER TABLE … ADD COLUMN, the SDK creates INT/INTEGER/TINYINT/SMALLINT/MEDIUMINT/BIGINT as BIGINT, REAL/FLOAT/DOUBLE as DOUBLE PRECISION, BLOB as BYTEA DATETIME as TIMESTAMP and BOOL as BOOLEAN (sqlite_types=False sends DDL unchanged). Through other clients, INTEGER is 32-bit INT4, REAL is 4-byte FLOAT4, and BLOB / DATETIME / TINYINT need their PostgreSQL names.
Any type name (POINT, MYTYPE, MONEY, NCHAR(5), …)	Yes, via SDK	heliosdb_sqlite DDL type mapping	A name the engine does not know is created by SQLite’s affinity rules: it becomes BIGINT if it contains INT; TEXT if it contains CHAR, CLOB or TEXT; BYTEA if it contains BLOB; DOUBLE PRECISION if it contains REAL, FLOA or DOUB; otherwise TEXT. Columns without a type (CREATE TABLE t (a, b)) are not accepted.
Declared column types (sqlite3_column_decltype)	Yes, via SDK	heliosdb_sqlite	The SDK records each column’s declared type at CREATE TABLE / ADD COLUMN in a small heliosdb_sqlite_decltypes table, much as SQLite keeps sqlite_sequence. PARSE_DECLTYPES uses it. declared_types=False turns the recording off.
BOOLEAN / TEXT / NUMERIC / DATE / TIMESTAMP types	Yes	engine	BOOLEAN values come back as 1 / 0 through the SDK.
DATETIME('now')	Yes	src/sql/sqlite_compat.rs::rewrite_datetime_now	Mapped to CURRENT_TIMESTAMP. Other DATETIME(arg) forms pass through unchanged.
sqlite_master introspection	Yes	src/sql/system_views.rs, src/sql/phase3/system_views.rs	Returns one row per user table / materialised view, columns: type, name, tbl_name, rootpage, sql. The sql column is best-effort and intended for WHERE name = ? filtering, not faithful DDL.
PRAGMA table_info(t)	Yes	server and REPL: src/protocol/postgres/handler.rs::pragma_table_info, src/repl/shell.rs; embedded SDK: answered from information_schema	Returns SQLite-shaped rows (cid, name, type, notnull, dflt_value, pk).
PRAGMA foreign_keys = …, journal_mode = …, synchronous = …, busy_timeout = …, other pragmas	Yes, no-op	parse_pragma; SDK	Acknowledged silently, as SQLite ignores unknown pragmas. Foreign keys are always on; journal mode is RocksDB-managed.
Transactions	Yes	SDK	As in sqlite3: implicit BEGIN before INSERT/UPDATE/DELETE/REPLACE, commit() / rollback(), isolation_level=None autocommit, in_transaction, with conn:. A failing statement leaves the transaction usable.
executescript("a; b; c;")	Yes	SDK	Commits a pending transaction first, then runs each statement; ; inside strings and comments does not split.
cursor.lastrowid, rowcount, description	Yes	SDK	lastrowid is the key of the last row inserted through the connection into a table with an integer primary key (the SDK appends RETURNING <pk>), set after every execute() as in sqlite3; rowcount is -1 after SELECT and the total after executemany(); description uses sqlite3’s column names and is filled for queries that return no rows.
Implicit rowid / oid	No	—	Nano tables have no implicit rowid. SELECT rowid raises OperationalError, and lastrowid is None after an insert into a table without an integer primary key. Declare id INTEGER PRIMARY KEY where you need one.
total(), group_concat()	Yes, via SDK	heliosdb_sqlite	The SDK provides total() (a float, 0.0 for no rows) and returns NULL from group_concat() when no non-NULL value was aggregated, as SQLite does. Through other clients, group_concat() returns '' for no values and total() is not available.
ORDER BY t.col / ORDER BY <n> in joins	Yes, via SDK	heliosdb_sqlite	The SDK sorts such joins by select-list aliases, which Nano handles correctly. Through other clients, order joins by an output alias (SELECT k.id AS kid ... ORDER BY kid DESC).
List / tuple parameters	Yes (HeliosDB extension)	SDK	A list of numbers binds as a VECTOR (sqlite3 rejects lists). VECTOR columns read back as lists of floats in both modes.
Errors	Yes	SDK	Constraint violations raise IntegrityError; syntax errors and missing tables raise OperationalError, as in sqlite3.
DB-API globals, total_changes	Yes	SDK	apilevel ('2.0'), threadsafety (1), paramstyle ('qmark'), and Connection.total_changes.
One database per file path	Yes	SDK	connect('app.db') keeps its data in the directory app.db/ (Nano stores a database as a directory), so every path is its own database. data_dir= chooses another directory. A shared heliosdb-data/ directory created by an older SDK version is still opened, with a RuntimeWarning, when the path does not exist.
Multi-column CREATE INDEX (B-tree)	Partial	src/sql/planner.rs	Accepted; only the leading column is indexed today. Vector indexes (`USING hnsw
|| string concat, IFNULL, COALESCE, LENGTH, LIKE	Yes	evaluator	Same semantics as PostgreSQL.
typeof(), STRFTIME(fmt, …) / JULIANDAY(d)	No	—	SQLite-specific functions. Use the PostgreSQL surface: pg_typeof, TO_CHAR(d, fmt), EXTRACT(EPOCH FROM d) / 86400, DATE_TRUNC, DATE_PART, AGE, MAKE_DATE, MAKE_TIMESTAMP.
register_adapter / register_converter	Yes	SDK	Applied in the Python process, as in sqlite3.
User-defined Python functions and aggregates	No	SDK raises NotSupportedError	create_function / create_aggregate are not bridged into the engine.
Custom collations, loadable extensions	No	SDK raises NotSupportedError	—

How rewriting works

Parser::parse() (in src/sql/parser.rs) runs SQLite-compat preprocessing before any other rewrite, so downstream stages — including time-travel rewriting, STORAGE clause stripping and the sqlparser front end — operate on canonical PostgreSQL syntax. The PostgreSQL wire handler short-circuits PRAGMA before the parser sees it; EmbeddedDatabase short-circuits inside query() and execute(); the REPL formats PRAGMA results with the SQLite-shaped schema directly.

Embedded vs daemon mode

	Embedded (mode='embedded', default)	Daemon (mode='daemon')
How it runs	The Nano engine inside the Python process (heliosdb-nano-embedded)	psycopg2 sessions to a running heliosdb-nano start server
Result types	Typed by the engine	Typed by the column type OID
Storage	connect('app.db') uses the directory app.db/	The server’s data directory
Connections	:memory: is private per connection; connections to one database path in a process share the engine, and wait (up to timeout) while another holds a transaction	Many processes and hosts, one shared server
Setup	pip install only	Run and secure a server
Recommended for	Applications, scripts and tests that own their data	Shared, multi-process or remote access

Without heliosdb-nano-embedded (platforms with no wheel yet), embedded mode drives a heliosdb-nano repl subprocess and returns values as text; the SDK emits a RuntimeWarning when that happens.

Known gaps

• Persistent ART pages — indexes are rebuilt from data on open (O(rows + indexes) one-time cost). For multi-million-row data directories, that startup cost can be material. Moving to a RocksDB column family per index, so opens are O(small), is planned.
• STRFTIME / JULIANDAY engine functions — intentionally not added. The PostgreSQL surface (TO_CHAR, EXTRACT, DATE_TRUNC, DATE_PART, AGE, MAKE_DATE, MAKE_TIMESTAMP, etc.) is the canonical home. SDKs can rewrite SQLite-specific names if desired.
• Numeric TO_CHAR(123.45, '9,999.00') — engine support is client layer.
• sum() next to group_concat() over no rows — in the current release (and the heliosdb-nano-embedded binding), sum(x) over no rows returns 0 instead of NULL when the same query also uses group_concat() / string_agg(). Run the two aggregates in separate queries if the distinction matters.

See also

• docs/guides/sqlite_drop_in_tutorial.md — runnable end-to-end port.
• tests/sqlite_compat_tests.rs — every item in this matrix has at least one assertion against a live EmbeddedDatabase.
• src/sql/sqlite_compat.rs — pre-parser rewrites + 16 unit tests.