Comparison Overview¶

This page summarizes high-level feature differences between DecentDB, SQLite, and DuckDB, then points to deeper comparison guides for other embedded databases that developers may consider instead of DecentDB.

Versions (as of 2026-06-28)¶

This comparison was written against: - SQLite 3.51.2 (sqlite3 CLI) - DuckDB v1.4.3 (duckdb CLI)

DecentDB's current workspace version is v2.15.0. This document describes the current feature set and constraints; details may change as DecentDB continues to evolve.

DecentDB is intentionally scoped around: - Priority #1: durable ACID writes (WAL-based) - Priority #2: fast reads - Concurrency model: one writer, many concurrent readers, with local native cross-process WAL coordination when the VFS supports it - SQL goal: a practical, Postgres-like subset for common application queries

SQLite and DuckDB are used as behavioral baselines for many SQL features, but DecentDB does not aim to be a drop-in replacement for either.

See also: SQL Feature Matrix for a concise per-feature support grid comparing DecentDB, SQLite, and DuckDB. For product-specific decision guides, see the comparison pages listed below.

Quick summary¶

Notes: - SQLite and DuckDB can often match many of these behaviors via extensions or different usage patterns; the point here is what DecentDB bakes in and optimizes for by default.

What “extensions” means here: - The ability to add new SQL features without modifying the database core (e.g., new scalar/aggregate functions, new table-like modules such as SQLite virtual tables, or optional subsystems like full-text search). - DecentDB's extension answer is a sandboxed Lua package model with manifest-declared SQL objects and explicit content-hash trust. It is not SQLite .load, DuckDB native extensions, or host-language callbacks.

Detailed Comparison Pages¶

These pages are decision guides, not scorecards. They focus on fit, tradeoffs, and migration implications.

• DecentDB vs SQLite - the baseline embedded SQL comparison.
• DecentDB vs libSQL - the production-ready SQLite fork maintained by Turso.
• DecentDB vs Turso Database - the newer Rust SQLite-compatible rewrite.
• DecentDB vs PGlite - WASM Postgres for JavaScript, browser, and local-first web workloads.
• DecentDB vs DuckDB - embedded OLTP vs embedded OLAP.
• DecentDB vs H2 - Java/JDBC embedded and in-memory SQL.
• DecentDB vs Firebird Embedded - embedded Firebird SQL and PSQL workflows.
• DecentDB vs LiteDB - .NET embedded document storage.
• DecentDB vs RocksDB, LMDB, LevelDB, and sled - key-value storage-engine alternatives.

Local-first sync¶

DecentDB's biggest product-level differentiator is native local-first sync for embedded applications. It is built into the engine and exposed through the CLI, SQL inspection queries, and the .NET SDK.

The current sync surface includes:

• durable row-level change capture in a sidecar sync journal
• replica IDs, peer catalogs, and peer-to-scope bindings
• manual JSON batch export/import for offline exchange
• localhost/dev HTTP sync run and sync serve workflows
• scoped replication with validated primary-key-based row filters
• conservative conflict recording by default, plus manual show/resolve/reopen workflows and configurable policies
• in-process reactive subscriptions for table, range, query, and change-stream invalidation without polling
• canonical sys.* operational inspection views plus sys_sync_* compatibility queries for journals, peers, scopes, sessions, conflicts, peer lag, retention, and doctor summaries
• retention reports, safe prune dry-runs, explicit data-loss override, and sync doctor guidance
• a typed .NET DecentDBSyncClient for app integration

SQLite and DuckDB can participate in sync architectures when an application or external service layers replication around them. DecentDB's direction is different: offline writes, sync state, conflict inspection, scoped exchange, and operational diagnostics are first-class database capabilities.

See Local-first sync for the user guide and CLI Reference for command details.

SQL surface area¶

DecentDB's current baseline includes: - DDL: CREATE TABLE, CREATE INDEX, CREATE TRIGGER, CREATE VIEW, CREATE TEMP TABLE, CREATE TEMP VIEW, DROP TABLE, DROP INDEX, DROP TRIGGER, DROP VIEW, ALTER TABLE, ALTER VIEW ... RENAME TO ... - DML: SELECT, INSERT, UPDATE, DELETE, INSERT ... RETURNING, INSERT ... ON CONFLICT - Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, NATURAL JOIN - Clauses: WHERE, ORDER BY, LIMIT, OFFSET, FETCH, GROUP BY, HAVING, DISTINCT, DISTINCT ON - Aggregates: COUNT, SUM, AVG, MIN, MAX, TOTAL, GROUP_CONCAT, STRING_AGG, STDDEV, VARIANCE, BOOL_AND, BOOL_OR, ARRAY_AGG, MEDIAN, PERCENTILE_CONT, PERCENTILE_DISC (with supported DISTINCT and aggregate ORDER BY forms) - Set operations: UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL - CTEs: WITH ... AS (recursive and non-recursive) - Window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE() with OVER (...) - Predicates: comparisons (=, !=, <>, <, <=, >, >=), AND/OR/NOT, LIKE/ILIKE, IN, BETWEEN, EXISTS, IS NULL/IS NOT NULL - Math functions: ABS, ROUND, CEIL/CEILING, FLOOR, SQRT, POWER/POW, MOD, SIGN, LOG, LN, EXP, RANDOM - String functions: LENGTH, LOWER, UPPER, TRIM, LTRIM, RTRIM, REPLACE, SUBSTRING/SUBSTR, INSTR, LEFT, RIGHT, LPAD, RPAD, REPEAT, REVERSE, CHR, HEX - Date/time functions: NOW(), CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, date(), datetime(), strftime(), EXTRACT(), DATE_TRUNC(), DATE_PART(), DATE_DIFF(), LAST_DAY(), NEXT_DAY(), MAKE_DATE(), MAKE_TIMESTAMP(), TO_TIMESTAMP(), AGE(), and timestamp/date INTERVAL arithmetic - JSON functions: JSON_EXTRACT, JSON_ARRAY_LENGTH, json_type, json_valid, json_object, json_array, ->, ->> - Table-valued functions: json_each(), json_tree(), generate_series(), and SQLite-compatible pragma_*() introspection helpers - Compatibility catalog views: sqlite_schema / sqlite_master, temp schema aliases, and minimal information_schema.schemata, information_schema.tables, and information_schema.columns - Query-time compatibility syntax: main. / temp. qualified names for local objects, and COLLATE BINARY|NOCASE|RTRIM for ordering and comparisons - Generated columns: GENERATED ALWAYS AS (expr) in STORED and VIRTUAL modes - Other functions: COALESCE, NULLIF, CAST, CASE, GEN_RANDOM_UUID, UUID_PARSE, UUID_TO_STRING, PRINTF, current_database(), current_schema(), database(), schema(), version() - SQLite-style PRAGMAs for safe configuration probes and schema introspection, including foreign_keys, journal_mode, synchronous, wal_checkpoint, user_version, application_id, table_xinfo, table_list, index_list, index_info, index_xinfo, and foreign_key_list - Operators: +, -, *, /, % (modulo), || (string concatenation) - Transaction control: BEGIN, BEGIN IMMEDIATE/BEGIN EXCLUSIVE (treated as BEGIN), COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT, ROLLBACK TO SAVEPOINT - Parameters: positional $1, $2, ... (Postgres-style) - EXPLAIN / EXPLAIN ANALYZE plan output

SQLite and DuckDB generally include all of the above, plus substantial additional SQL.

Views¶

SQL roadmap¶

DecentDB has implemented many previously planned baseline features, including: - Richer expression support (IS NULL, CASE, CAST, BETWEEN, IN, EXISTS, LIKE ... ESCAPE, ||, core scalar functions) - UPSERT and DML conveniences (INSERT ... ON CONFLICT DO NOTHING/DO UPDATE, INSERT ... RETURNING) - Recursive and non-recursive CTEs, set operations (UNION ALL, UNION, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL) - CHECK constraints, FK CASCADE / SET NULL actions, table-level FOREIGN KEY constraints - Broader ALTER TABLE (ADD COLUMN, RENAME COLUMN, DROP COLUMN, ALTER COLUMN TYPE) - Trigger subsets (AFTER row triggers on tables, INSTEAD OF row triggers on views) - Window functions (ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE() with OVER (...)) - Date/time functions (NOW(), CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, date(), datetime(), strftime(), EXTRACT(), DATE_TRUNC(), DATE_PART(), DATE_DIFF(), LAST_DAY(), NEXT_DAY(), MAKE_DATE(), MAKE_TIMESTAMP(), TO_TIMESTAMP(), AGE(), and timestamp/date INTERVAL arithmetic) - Math functions (SQRT, POWER/POW, MOD, SIGN, LOG, LN, EXP, RANDOM) - String functions (LTRIM, RTRIM, LEFT, RIGHT, LPAD, RPAD, REPEAT, REVERSE, CHR, HEX, INSTR) - JSON functions (JSON_EXTRACT, JSON_ARRAY_LENGTH, json_type, json_valid, json_object, json_array, ->, ->>, json_each(), json_tree()) - Indexing options (multi-column, partial v0 subset, expression index v0 subset) - EXPLAIN / EXPLAIN ANALYZE plan output - Join types (INNER, LEFT, RIGHT, FULL OUTER, CROSS, NATURAL) - DISTINCT ON, DISTINCT aggregates (COUNT(DISTINCT ...), SUM(DISTINCT ...), AVG(DISTINCT ...), plus supported forms for statistical and collection aggregates) - DEFAULT and generated columns (STORED + VIRTUAL) - CREATE TEMP TABLE / CREATE TEMP VIEW (session-scoped) - SAVEPOINT / RELEASE SAVEPOINT / ROLLBACK TO SAVEPOINT - SQL and PRAGMA compatibility quick wins, including safe SQLite-style PRAGMA probes, sqlite_schema, minimal information_schema, generate_series, main./temp. qualifiers, and query-time built-in collations

For remaining roadmap and deferred capabilities, see: - Road to RTM - ADRs

Data types and functions¶

DecentDB’s baseline types cover the most common application needs: - NULL, INT64, BOOL, FLOAT64, TEXT (UTF-8), BLOB, UUID, DECIMAL(p,s) / NUMERIC(p,s), DATE, TIMESTAMP

SQLite and DuckDB both offer larger built-in ecosystems of types and functions. DuckDB, in particular, has many analytics-oriented types and functions (nested types, extensive math/statistics), while SQLite’s strength is portability, flexibility, and a long list of optional extensions.

Indexing and search¶

DecentDB emphasizes predictable behavior, durability, and correctness testing rather than broad operational surface area.

SQLite-Specific Features: Explicit Decisions¶

DecentDB intentionally does not support certain SQLite-specific features. This section documents those decisions and provides alternatives where applicable.

PRAGMA¶

DecentDB supports a practical compatibility subset of SQLite-style PRAGMAs for tooling probes and schema discovery. These PRAGMAs are intentionally safe: configuration PRAGMAs report DecentDB's actual behavior, and assignment forms are accepted only as no-ops or for DecentDB-owned metadata.

rowid / rowid Pseudo-Columns¶

SQLite exposes implicit rowid as a queryable pseudo-column. DecentDB has an internal rowid but does not expose it to SQL.

Recommendation: Use an explicit INT64 primary key (e.g. id INT PRIMARY KEY). If the table has a single INT64 primary key column, omitting the value on INSERT will auto-assign an ID.

WITHOUT ROWID Tables¶

SQLite optimization for tables where the PRIMARY KEY is the clustering key. Not applicable to DecentDB's storage architecture.

ATTACH DATABASE¶

SQLite's mechanism for querying multiple database files simultaneously. Not supported.

Recommendation: Use application-level multi-database coordination.

Recursive CTEs¶

WITH RECURSIVE is supported for iterative fixpoint queries (counting, tree/graph traversal). Recursive CTEs use UNION or UNION ALL between anchor and recursive terms and are limited to 1000 iterations per statement.