← Blog
A lavender cabinet with two cream drawers and an orange handle on a pale background.
Published on
2026-11-16

DuckDB vs SQLite: choose the right database

Choose SQLite first for application-local state, small transactions, and a database that travels with the application. Evaluate DuckDB first for analytical scans, joins, and aggregates over substantial portions of a dataset. Both can store tables, update records, and run SQL. The useful DuckDB vs SQLite comparison starts with your workload and how processes access the data.

This guide includes a small Python example that performs equivalent operations in both engines. It passed 16 local tests on Windows with CPython 3.12.14, SQLite 3.53.1, and DuckDB 2.0.0. These are correctness checks, not a performance ranking, concurrency test, or Hivenet VM benchmark.

DuckDB vs SQLite at a glance

QuestionSQLiteDuckDB
Where should I start evaluating it?Local application state and transactional updatesAnalytical queries and batch transformations
How does this example run?Inside the Python processInside the Python process
Can it update a row and aggregate a table?YesYes
Which typing rules does the example use?A STRICT table with INTEGER amountsA typed table with BIGINT amounts
What requires a separate decision?Writer concurrency, backups, access and storageWriter process model, backups, access and storage
Does this guide establish a speed winner?NoNo

DuckDB's design emphasizes analytical workloads and column-oriented, vectorized execution. SQLite's use-case guidance includes application storage and data analysis. Those are starting points for evaluation, not a rule that SQLite cannot analyze data or DuckDB cannot modify a record.

Choose around the application, not the file extension

A webhook receiver might need to look up an event ID, record an accepted request, and update a processing status. A nightly report might need to scan millions of events, join reference tables, and calculate totals. Both use SQL, but their query mix, write frequency, and recovery requirements differ.

Our Python webhook guide uses SQLite for a small durable event store. It illustrates application state rather than an analytical database benchmark. Before adopting a similar design, define how many processes write, how long transactions stay open, and what happens when a request is retried.

A team may keep operational state in one store and analyze a controlled snapshot elsewhere. If you choose that arrangement, define the snapshot boundary, acceptable delay, deletion handling, and reconciliation checks. A second database creates another copy to operate; it does not automatically make the system simpler.

Run the same operations in both engines

Use Python 3.12 with its sqlite3 module and install the DuckDB version tested here in your project environment. The DuckDB Python documentation lists Python 3.11 or newer as its requirement. The SQLite library bundled with Python can vary; inspect sqlite3.sqlite_version. This example needs SQLite 3.37 or newer for STRICT tables.

python -m pip install duckdb==2.0.0

Save the following as compare_engines.py. It creates two private temporary database files, inserts six orders, commits one update, rolls back another, and checks results again after closing and reopening each file. It uses integer cents for the small fixture and distinguishes an unknown amount from zero. It opens no existing business database.

"""Compare correctness on isolated local databases, not query speed."""
from contextlib import contextmanager
from pathlib import Path
import sqlite3
from tempfile import TemporaryDirectory
import duckdb

ROWS = [
    ("001", "books", 1200, "café"),
    ("002", "books", 800, None),
    ("003", "tools", 3000, ""),
    ("004", "tools", None, "awaiting price"),
    ("005", "books", 0, "promo"),
    ("006", "tools", 1500, "Zürich"),
]


def connect(engine, path):
    if engine == "sqlite":
        # Explicit SQL BEGIN/COMMIT/ROLLBACK, without implicit transactions.
        return sqlite3.connect(path, isolation_level=None)
    if engine == "duckdb":
        return duckdb.connect(str(path))
    raise ValueError("unknown engine")


@contextmanager
def transaction(con):
    con.execute("BEGIN")
    try:
        yield
    except BaseException:
        con.execute("ROLLBACK")
        raise
    else:
        con.execute("COMMIT")


def initialize(con, engine):
    amount_type = "INTEGER" if engine == "sqlite" else "BIGINT"
    suffix = " STRICT" if engine == "sqlite" else ""
    # Both SQL fragments above are constants selected by the program.
    with transaction(con):
        con.execute(f"""CREATE TABLE orders (
            id TEXT PRIMARY KEY NOT NULL,
            category TEXT NOT NULL,
            amount_cents {amount_type} CHECK (amount_cents >= 0),
            note TEXT
        ){suffix}""")
        con.executemany("INSERT INTO orders VALUES (?, ?, ?, ?)", ROWS)


def snapshot(con):
    rows = con.execute(
        "SELECT id, category, amount_cents, note FROM orders ORDER BY id"
    ).fetchall()
    totals = con.execute("""
        SELECT category, COUNT(*), COUNT(amount_cents), SUM(amount_cents)
        FROM orders GROUP BY category ORDER BY category
    """).fetchall()
    return {"rows": rows, "totals": totals}


def run_database(engine, path):
    # This local demo is intended for a fresh, privately owned temporary path.
    if Path(path).exists():
        raise FileExistsError(path)
    con = connect(engine, path)
    try:
        initialize(con, engine)
        with transaction(con):
            con.execute("UPDATE orders SET amount_cents = ? WHERE id = ?",
                        (3500, "003"))
        con.execute("BEGIN")
        con.execute("UPDATE orders SET amount_cents = ? WHERE id = ?",
                    (999999, "001"))
        con.execute("ROLLBACK")
        before_close = snapshot(con)
    finally:
        con.close()
    reopened = connect(engine, path)
    try:
        after_reopen = snapshot(reopened)
        assert after_reopen == before_close
        return after_reopen
    finally:
        reopened.close()


def main():
    with TemporaryDirectory() as directory:
        root = Path(directory)
        sqlite_result = run_database("sqlite", root / "orders.sqlite")
        duckdb_result = run_database("duckdb", root / "orders.duckdb")
        assert sqlite_result == duckdb_result
        expected_rows = [r if r[0] != "003" else
                         ("003", "tools", 3500, "") for r in ROWS]
        assert sqlite_result["rows"] == expected_rows
        assert sqlite_result["totals"] == [
            ("books", 3, 3, 2000), ("tools", 3, 2, 5000)]
        print("SQLite and DuckDB results match")
        print("Rows after reopen:", len(sqlite_result["rows"]))
        print("Committed update (003): 3500 cents")
        print("Rolled-back update (001): 1200 cents")
        for category, count, priced, total in sqlite_result["totals"]:
            print(f"{category}: rows={count}, priced={priced}, total={total} cents")


if __name__ == "__main__":
    main()

Run it:

python compare_engines.py

Expected output:

SQLite and DuckDB results match
Rows after reopen: 6
Committed update (003): 3500 cents
Rolled-back update (001): 1200 cents
books: rows=3, priced=3, total=2000 cents
tools: rows=3, priced=2, total=5000 cents

The same SQL subset works here, with an explicit table definition for each engine. SQLite's connection uses isolation_level=None so the code controls transaction boundaries through SQL. Values are passed through placeholders. The two inserted SQL fragments are program constants, not user input. See the Python sqlite3 documentation and DuckDB transaction documentation for the relevant APIs.

Read the results without overstating them

After reopening, both databases contain six identical rows. Order 003 retains its committed amount of 3,500 cents; order 001 keeps 1,200 cents because its attempted change was rolled back. The tools category has three rows but only two known amounts. Its total is 5,000 cents. An unknown amount is not treated as a priced order worth zero.

The test suite also checks Unicode, empty strings, NULL, bound text containing SQL-like characters, rejected constraints, an inconvertible amount, and rollback of an earlier write when a later statement fails. The existing-path check verifies that the demo refuses an already present output file. It is intended for a private temporary directory, not as a concurrent file-creation protocol.

Identical results on this fixture do not establish complete SQL compatibility. Type conversion, supported types, extensions, query plans, and numeric limits need their own checks. SQLite STRICT still allows supported lossless conversions; it is not a rule that every differently typed input must fail. The example uses a narrow shared contract rather than claiming the engines have identical type systems.

Closing and reopening checks persistence after a normal shutdown. It does not simulate power loss or validate backup recovery. The script does not measure query time, memory, multiple writers, remote storage, or a Linux deployment.

Make the concurrency boundary explicit

SQLite permits one writer at a time per database file. With write-ahead logging, readers and a writer can overlap, but that does not create multiple simultaneous writers. WAL also assumes participating processes are on the same host. The example leaves journal configuration at its default and does not test WAL.

For DuckDB 2.0 in embedded mode, multiple writer threads can operate within one writer process, subject to conflict rules. The current documentation also describes a separate Quack client-server route for multiple processes. Do not turn the embedded-mode restriction into a claim about every DuckDB deployment. Quack is outside this example's scope.

Write down the number of processes, transaction duration, conflict behavior, and recovery policy before deciding. If many independent applications need shared database access, evaluate a supported server architecture rather than treating a database file on shared storage as an interchangeable substitute.

Plan backups and analytical copies separately

A running database and a completed export serve different purposes. SQLite provides a backup API for obtaining a consistent copy; DuckDB documents database export and import. DuckDB 2.0.0 exports omit triggers, which must be recreated as documented. Select a procedure that matches the engine and test restoration, including the application checks needed to prove the restored data is usable.

Copying a busy database file into an object bucket is not, by itself, a backup procedure. Journal files, open writes, and snapshot consistency matter. Nor should a completed analytical export be described as a full application backup unless you have verified what it preserves.

For a fair workload comparison, use representative data and include loading, query execution, writes, output, and transfer work where relevant. Record versions, settings, thread limits, cold or warm runs, and repeat measurements. A short query over six rows cannot justify a production size or a claim that one engine is several times faster.

Evaluate the CPU and storage your workload needs

Compute with Hivenet provides CPU infrastructure for customer-operated applications and processing jobs. Hivenet Object Storage supplies S3-compatible storage for datasets and completed application files. Your database configuration, backup procedure, and access controls remain part of the workload you operate.

Bring the dataset size, query mix, writer count, run frequency, memory measurements, and recovery requirements to a CPU workload evaluation. Start with the engine that fits those requirements, then test the proposed VM and storage connection. Keep live database access separate from the handling of completed exports or verified backup artifacts.

Your next workload belongs on Hivenet.

Pick one AI, compute, or storage workload and see the difference for yourself. Spin it up in minutes, or let our team map your fastest path to production.