DuckDB vs SQLite: Which One Fits Your CSV and JSON Work?

DuckDB and SQLite look similar from far away: single file, embedded, no server to run, SQL in your app. Up close they solve opposite problems. SQLite competes with fopen() — reliable row-by-row storage for apps. DuckDB competes with Pandas and data warehouses — fast analytical scans over CSV, JSON, and Parquet.

Pick wrong and you feel it: load a 5M-row CSV into SQLite row-by-row and aggregations crawl; use DuckDB as a concurrent order-entry store and you fight its write model. This post gives a practical decision rule for file work, with the no-install browser equivalent on RividTech. Pair it with Run SQL on CSV in Your Browser and DuckDB 2.0 Alpha: What's New for CSV and JSON.

TL;DR

	SQLite	DuckDB
Built for	OLTP: apps, devices, transactional state	OLAP: analytics over files
Storage	Row-oriented, B-tree	Columnar, vectorized execution
CSV query	Import first (.import), then query	Query in place (SELECT * FROM 'orders.csv')
JSON	TEXT + json1 functions, manual	Native JSON + VARIANT shredding in v2.0
Typing	Dynamic, type affinity	Strict, schema enforced
Writes	One writer at a time, full ACID + WAL	MVCC, great bulk load; not a multi-writer app DB
Best when	You need a durable app database	You need to filter / aggregate / join files fast

If you just have one CSV to filter, convert, or share, you need neither — use Data Process or CSV Preview and skip the install. See Why Browser-Only CSV Tools Are Safer.

What they share

• Zero-config, embedded. Link a library, open a file. No daemon, no port, no password rotation.
• Single-file portability. Email it, put it on USB, attach it. SQLite's own docs list this as a data-transfer format and app file format.
• SQL access. Both speak SQL with transactions. Both have CLIs, Python bindings, and Wasm builds that run in the browser.
• They interoperate. DuckDB's sqlite extension can ATTACH a SQLite file and query it directly — more on that below.

Where they diverge

1. Engine design: rows vs columns

SQLite stores rows together. That is ideal for SELECT * FROM users WHERE id = 42 or UPDATE orders SET status = 'paid' WHERE id = 7 — pointer lookups and small transactions.

DuckDB stores columns together and executes vectorized (a batch of values per CPU call). That is ideal for SELECT region, SUM(amount) FROM orders.csv GROUP BY region over millions of rows — it only reads the two columns it needs and aggregates in parallel.

Rule: many small reads/writes → SQLite; few big scans/aggregates → DuckDB.

2. Typing: flexible vs strict

SQLite uses type affinity: CREATE TABLE t(i INTEGER) still accepts 'hello' in column i. Flexible for messy app input, dangerous for analysis.

DuckDB is strictly typed and rejects bad values at read time. When reading SQLite files it maps affinity to BIGINT / VARCHAR / DOUBLE / DATE / TIMESTAMP and throws a mismatch error on dirty rows (opt out with sqlite_all_varchar = true before attach). Strictness is what you want before a GROUP BY silently drops garbage.

Browser equivalent: catch the dirt before the database with Validate CSV and Fix Delimiter. Full workflow in How to Clean Messy CSV Files.

3. Concurrency

SQLite allows unlimited readers but one writer at a time per file. For phones, desktops, and low-traffic sites (<100K hits/day per SQLite docs) writers just take turns in milliseconds. High-concurrency multi-writer apps need client/server Postgres/MySQL instead.

DuckDB has MVCC transactions and has always been transactional, but it is optimized for single-user analytics. v2.0 adds a Quack client/server protocol and CONNECT for long-running shared use — still analytical, not a replacement for an OLTP fleet.

For a shared CSV someone edits by hand, neither concurrency model helps. Version it with Diff CSV and dedupe keys with Remove Duplicates instead.

CSV showdown

DuckDB: query the file directly. Auto-sniffing handles delimiters, headers, and types in most cases (CSV Import docs):

-- no import step
SELECT * FROM 'orders.csv' LIMIT 5;

SELECT region, SUM(amount) AS revenue, COUNT(*) AS orders
FROM 'orders.csv'
WHERE status = 'paid'
GROUP BY region;

-- materialize once inferred
CREATE TABLE orders AS SELECT * FROM 'orders.csv';
COPY orders TO 'clean.csv';

SQLite: import, then query. The CLI needs .mode + .import, and header handling is explicit:

.mode csv
.import --csv --skip 1 orders.csv orders

SELECT region, SUM(amount) AS revenue, COUNT(*) AS orders
FROM orders
WHERE status = 'paid'
GROUP BY region;

.once clean.csv
SELECT * FROM orders;

csvkit's csvsql uses SQLite under the hood for csvsql --query "..." data.csv — see Tools to Process CSV Files in the Terminal.

Browser mapping for the same three verbs: filter in Data Process, pivot in Pivot Table, join two files in Merge CSV. Generate the load script with CSV to SQL or TSV to SQL when the answer must live in a real database.

JSON showdown

SQLite: store JSON as TEXT, query with json1 (json_extract, ->>, json_each for arrays). Works, but nested structures stay awkward and every query re-parses text.

DuckDB: native JSON readers (read_json, FROM 'events.json'), struct/list unnesting, and in v2.0 VARIANT shredding plus json_merge_patch_diff, json_deep_merge, json_normalize, json_strip_nulls — detailed in DuckDB 2.0 Alpha: What's New.

Browser mapping: inspect with JSON Preview, format with JSON Formatter, flatten with Flatten JSON, then JSON to CSV or JSON to Excel. Background in Flatten Nested JSON for Spreadsheets.

Use them together: DuckDB reads SQLite

You do not have to choose at query time. Attach and federate:

INSTALL sqlite;
LOAD sqlite;

ATTACH 'app.db' (TYPE sqlite);
USE app;

-- SQLite tables appear as DuckDB tables
SELECT status, COUNT(*) FROM orders GROUP BY status;

-- bulk-export a SQLite table to Parquet/CSV with DuckDB speed
COPY app.orders TO 'orders.parquet';

Type-affinity caveat from the extension docs: declare clean types in SQLite or set sqlite_all_varchar before attach, then cast in DuckDB.

Decision guide for RividTech users

• One-off file, share with a human? Neither. Preview (CSV Preview / JSON Preview), clean (Column Tools, Find & Replace), export (CSV to Excel / CSV to JSON). Private by design.
• Repeatable 10M-row aggregation in code/CI? DuckDB. SELECT FROM 'file.csv' with no import step, Parquet output.
• App needs durable local state with concurrent reads and occasional writes? SQLite. Single file, WAL mode, battle-tested on billions of devices.
• Need INSERT scripts for Postgres/MySQL/SQLite? Generate them: CSV to SQL / JSON to SQL. Check counts first in Data Stats, slice big files with Sample Data and Split CSV.

Getting started

Open the file in CSV Preview, run Validate CSV, then do one SQL-style step from SQL on CSV in Browser: filter (Data Process), aggregate (Pivot Table), join (Merge CSV). When the pipeline becomes daily and huge, graduate to DuckDB; when the app needs local transactions, graduate to SQLite. Everything before that stays on your device — see our Privacy Policy.