DecentDB vs SQLite: When to Choose Which¶

This document helps developers decide between DecentDB and SQLite for embedded database workloads. Both are single-file, embedded relational databases with ACID transactions, but they make different design trade-offs.

Versions compared: DecentDB 2.15.0 workspace behavior vs the SQLite 3.51.x baseline used by the DecentDB comparison harness.

See also: SQL Feature Matrix for a per-feature support grid, and SQL Reference for DecentDB's full SQL surface.

At a Glance¶

When DecentDB Is the Better Fit¶

1. You need guaranteed durability without tuning¶

DecentDB fsyncs on every commit by default. It accepts a safe SQLite-style PRAGMA synchronous compatibility probe, but it does not let SQL downgrade durability at runtime. If your application cannot tolerate data loss on power failure and you do not want to reason about journal modes or sync levels, DecentDB makes the safe thing the default.

-- DecentDB: every COMMIT is durable. No PRAGMA needed.
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 29.99);
COMMIT;  -- fsync'd to disk before returning

With SQLite, the equivalent safety requires explicit configuration:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = FULL;  -- or NORMAL with WAL + checkpoint risks

2. You want crash-safety verification built in¶

DecentDB ships with FaultyVFS and WAL failpoint hooks that let you deterministically inject I/O errors and simulate torn writes in tests. If you're building a system where you need to prove crash correctness, this is a first-class feature rather than something you bolt on externally.

3. Your workload is OLTP with concurrent readers¶

DecentDB is designed for one writer with many concurrent readers. Readers get snapshot isolation: they do not block the writer and do not block each other. For local native database files, the default process_coordination=auto mode uses WAL locks, reader slots, and a rebuildable coordination sidecar to coordinate multiple OS processes when the VFS supports the required locks. SQLite's model is more broadly deployed across tools and platforms, but it also relies on file-level locking that can introduce contention under some concurrent workloads.

use decentdb::{Db, DbConfig};

// DecentDB: readers and writer share one Db handle.
// Readers get snapshots; the writer holds the commit lock.
let db = Db::open("app.ddb", DbConfig::default())?;
// Multiple readers can execute concurrently without an
// application-owned locking protocol.

4. You need INSERT ... RETURNING or UPDATE ... RETURNING¶

Both DecentDB and SQLite 3.35.0+ support RETURNING on INSERT, UPDATE, and DELETE. This is common in API backends that need the generated ID or computed values back immediately.

-- Both DecentDB and SQLite 3.35.0+
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com') RETURNING id, name;

UPDATE users SET name = 'Bob' WHERE id = 1 RETURNING id, name, email;

DELETE FROM users WHERE id = 1 RETURNING id;

If you need to support older SQLite versions (pre-3.35.0), you would need a workaround:

-- SQLite pre-3.35.0 workaround (two statements)
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
SELECT last_insert_rowid();  -- only gives the id, not the full row

5. You need TRUNCATE TABLE¶

DecentDB supports TRUNCATE TABLE for fast bulk deletion. Unlike ordinary DELETE FROM, TRUNCATE: - Does not scan rows individually - Does not fire DELETE triggers - Supports RESTART IDENTITY and CONTINUE IDENTITY - Participates in transaction rollback

SQLite uses DELETE FROM table; syntax. It can optimize some simple full-table deletes, but it does not expose TRUNCATE TABLE syntax or DecentDB's identity options.

-- DecentDB
TRUNCATE TABLE temp_events;

6. You want DISTINCT ON (Postgres-style)¶

DecentDB supports DISTINCT ON for "first row per group" queries without a window function + CTE workaround.

-- DecentDB: get the most recent order per user
SELECT DISTINCT ON (user_id) user_id, id, created_at, amount
FROM orders
ORDER BY user_id, created_at DESC;

SQLite equivalent (verbose):

WITH ranked AS (
  SELECT user_id, id, created_at, amount,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
)
SELECT user_id, id, created_at, amount FROM ranked WHERE rn = 1;

7. You need built-in substring search (trigram indexes)¶

DecentDB offers a native trigram index type designed for fast LIKE '%pattern%' queries. SQLite requires FTS5 or a full table scan.

-- DecentDB: create a trigram index for substring search
CREATE INDEX idx_users_name_trgm ON users USING trigram(name);

-- This query uses the trigram index instead of a full scan
SELECT * FROM users WHERE name LIKE '%alice%';

Without a trigram index, LIKE '%pattern%' requires a full table scan in both databases. SQLite's FTS5 can help with full-text search but is not optimized for arbitrary substring matching.

8. You want a richer set of aggregate and window functions out of the box¶

DecentDB includes several analytical functions that SQLite lacks without extensions:

-- These all work in DecentDB, none work in stock SQLite:
SELECT STDDEV(amount), VARIANCE(amount) FROM orders;
SELECT BOOL_AND(active), BOOL_OR(admin) FROM users;
SELECT MEDIAN(salary) FROM employees;
SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY response_time) FROM requests;
SELECT DATE_DIFF('day', start_date, end_date) FROM projects;
SELECT LAST_DAY('2024-02-15');  -- '2024-02-29'

9. You want native UUID support¶

DecentDB has a first-class UUID type. SQLite stores UUIDs as text or blobs and lacks built-in UUID generation.

-- DecentDB
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT GEN_RANDOM_UUID(),
    user_id INT NOT NULL
);
INSERT INTO sessions (user_id) VALUES (1) RETURNING id;
-- Returns a proper UUID, not a string

10. You prefer Postgres-style SQL syntax¶

DecentDB targets Postgres compatibility for its SQL surface. If your application also talks to Postgres, DecentDB reduces the dialect gap. Features like $1 positional parameters, ILIKE, RETURNING, DISTINCT ON, and STRING_AGG all follow Postgres conventions.

-- DecentDB: Postgres-style positional parameters
SELECT * FROM users WHERE email = $1 AND active = $2;

-- Postgres-style case-insensitive LIKE
SELECT * FROM users WHERE name ILIKE '%alice%';

-- Postgres-style string aggregation
SELECT department, STRING_AGG(name, ', ' ORDER BY name) FROM employees GROUP BY department;

When SQLite Is the Better Fit¶

1. You need a battle-tested, decades-stable file format¶

SQLite is one of the most tested pieces of software ever written. Its file format has been stable for over 20 years. If you need to write a .sqlite file today and read it in 2040 with the widest possible tooling compatibility, SQLite is the safe bet. DecentDB has a stable file format baseline from 2.0.0, but it does not yet have SQLite's decades-long compatibility record.

2. You need broad SQL coverage or arbitrary native extensions¶

SQLite's SQL surface is far wider than DecentDB's, and its extension ecosystem (FTS5, R-Tree, JSON1, virtual tables, custom functions via C) is unmatched in the embedded space.

-- SQLite: FTS5 full-text search and virtual tables
CREATE VIRTUAL TABLE docs USING fts5(title, body);
INSERT INTO docs (title, body) VALUES ('Guide', 'How to use the database');
SELECT * FROM docs WHERE docs MATCH 'database';

-- SQLite: virtual tables, custom collations, loadable extensions
.load ./my_extension

DecentDB supports native full-text and trigram indexes plus sandboxed Lua extension packages for scalar functions, table-valued functions, aggregates, and query-time collations. It does not support arbitrary native extensions, SQLite virtual-table modules, or SQLite-compatible .load.

3. You need the broadest cross-process file-sharing ecosystem¶

SQLite uses mature file-level locking to allow multiple processes and a wide range of external tools to safely share one database file. DecentDB now has native local cross-process WAL coordination for supported VFSes, but SQLite is still the more proven choice when independent tools and processes must open the same file across many platforms and filesystems.

# SQLite: multiple processes can share the file
sqlite3 app.db "INSERT INTO jobs VALUES (1, 'pending')"   # process A
sqlite3 app.db "SELECT * FROM jobs"                        # process B

4. You need an ecosystem of mature language bindings¶

SQLite has the most ubiquitous bindings of any database in history. Every language -- from C to Zig -- has multiple well-maintained SQLite libraries. DecentDB's bindings are growing (Rust, Python, .NET, Go, Java, Node.js, Dart) but SQLite's ecosystem is decades ahead in maturity and coverage.

5. You need ATTACH DATABASE¶

SQLite can query multiple database files in a single statement via ATTACH DATABASE. DecentDB does not support this. If your application needs to join across separate .db files, SQLite handles this natively.

-- SQLite: query across two database files
ATTACH DATABASE 'analytics.db' AS analytics;
SELECT u.name, a.revenue
FROM main.users u JOIN analytics.revenue a ON u.id = a.user_id;

6. You need WITHOUT ROWID tables or rowid access¶

SQLite's WITHOUT ROWID tables and implicit rowid pseudo-column are useful for certain schema designs. DecentDB does not expose rowid to SQL and has no WITHOUT ROWID concept.

-- SQLite
SELECT rowid, * FROM users WHERE rowid BETWEEN 100 AND 200;

CREATE TABLE lookup (key TEXT PRIMARY KEY, val TEXT) WITHOUT ROWID;

DecentDB alternative: use an explicit INT PRIMARY KEY (which auto-assigns on INSERT).

7. You need window frame clauses (ROWS BETWEEN ...)¶

SQLite supports ROWS BETWEEN and RANGE BETWEEN frame specifications in window functions. DecentDB does not currently support frame clauses.

-- SQLite: rolling 3-row average
SELECT date, amount,
       AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg
FROM sales;

-- DecentDB: frame clauses are not supported
-- You would need to compute this in application code

8. You need a small, portable, zero-dependency library¶

SQLite compiles to a single C file (sqlite3.c) with no external dependencies. It runs on virtually every platform ever made -- from embedded microcontrollers to mainframes. DecentDB is a Rust crate with a more conventional build system and a larger binary footprint.

9. You need PRAGMA configuration¶

SQLite offers dozens of PRAGMAs for tuning cache size, page size, journal mode, locking mode, foreign key enforcement, and more. DecentDB exposes a practical compatibility subset for safe probes, schema discovery, application metadata, queued-write timeout defaults, WAL checkpointing, and plan-cache flushes. Assignments are accepted only when they match DecentDB's actual behavior or set DecentDB-owned metadata.

-- SQLite: extensive runtime tuning
PRAGMA cache_size = -64000;    -- 64 MB cache
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;

If your application depends on these tuning knobs, SQLite is the right tool.

10. You need SQLite's broader legacy compatibility surface¶

DecentDB supports multiset set operations such as INTERSECT ALL and EXCEPT ALL, but SQLite still has a much broader long-tail compatibility surface: more PRAGMAs, virtual tables, loadable native extensions, ATTACH workflows, and decades of third-party tool assumptions.

-- Both DecentDB and SQLite: multiset difference (preserves duplicate counts)
SELECT item_id FROM inventory EXCEPT ALL SELECT item_id FROM sold;

Side-by-Side SQL Examples¶

UPSERT (both supported, different syntax feel)¶

-- DecentDB (Postgres-style)
INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@new.com')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;

-- SQLite (same feature, compatible syntax)
INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@new.com')
ON CONFLICT (id) DO UPDATE SET name = excluded.name, email = excluded.email;

String aggregation¶

-- DecentDB: both styles
SELECT STRING_AGG(name, ', ' ORDER BY name) FROM employees;
SELECT GROUP_CONCAT(name, ', ') FROM employees;

-- SQLite: GROUP_CONCAT only
SELECT GROUP_CONCAT(name, ', ') FROM employees;

Date/time arithmetic¶

-- DecentDB: rich date/time functions
SELECT DATE_TRUNC('month', '2024-03-15 14:30:45');     -- '2024-03-01 00:00:00'
SELECT DATE_DIFF('day', '2024-03-10', '2024-03-15');    -- 5
SELECT LAST_DAY('2024-02-11');                           -- '2024-02-29'
SELECT '2024-03-15'::timestamp + INTERVAL '1 month';     -- '2024-04-15'

-- SQLite: limited date/time, no INTERVAL arithmetic
SELECT date('2024-03-15', '+1 month');                   -- '2024-04-15'
SELECT strftime('%Y-%m', '2024-03-15');                  -- '2024-03'

Statistical aggregates¶

-- DecentDB: built-in statistics
SELECT STDDEV(salary), VARIANCE(salary), MEDIAN(salary),
       PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary)
FROM employees;

-- SQLite: not available without extensions
-- Requires custom C extension or application-level computation

Summary Decision Matrix¶

Both databases are solid choices for embedded workloads. Pick the one whose defaults and constraints align with your application's requirements.