Comparison: DecentDB vs SQLite vs DuckDB¶

This page summarizes high-level feature differences between DecentDB, SQLite, and DuckDB.

Versions (as of 2026-03-28)¶

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

DecentDB is currently at v2.0.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: single process, one writer, many concurrent reader threads - 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.

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 does support multiple language bindings, but those bindings are about how you call DecentDB, not a general-purpose SQL extension/plugin system.

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 (with DISTINCT modifier) - 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() - JSON functions: JSON_EXTRACT, JSON_ARRAY_LENGTH, json_type, json_valid, json_object, json_array, ->, ->> - Table-valued functions: json_each(), json_tree() - 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 - 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()) - 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 ...)) - DEFAULT and generated columns (STORED + VIRTUAL) - CREATE TEMP TABLE / CREATE TEMP VIEW (session-scoped) - SAVEPOINT / RELEASE SAVEPOINT / ROLLBACK TO SAVEPOINT

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 small compatibility subset of SQLite-style PRAGMAs (page_size, cache_size, integrity_check, database_list, table_info(table)) and intentionally does not implement SQLite's broader PRAGMA surface.

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.