---
title: "SQLite Compatibility"
---

# 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|ivf|…`) still reject multi-column with a clearer error. |
| `\|\|` 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.