csvdb

Query CSV files with SQL. Written from scratch with no dependencies beyond the standard library: the lexer, parser, binder and executor are all our own.

go build -o csvdb ./cmd/csvdb

Usage

# register a table; its name defaults to the file name
csvdb -f users.csv "SELECT name, age FROM users WHERE age >= 18 ORDER BY age DESC LIMIT 10"

# explicit name / a path literal / standard input
csvdb -f u=testdata/users.csv "SELECT u.name FROM u WHERE u.vip"
csvdb "SELECT * FROM 'testdata/orders.csv' WHERE amount > 100"
cat a.csv | csvdb -f t=- --format json "SELECT DISTINCT city FROM t"

# rewrite files
csvdb -f users.csv "UPDATE users SET vip = true WHERE score > 90"
csvdb -f users.csv --dry-run "DELETE FROM users WHERE age IS NULL"

A table name not registered with -f is looked up as a .csv of the same name in the current directory, so csvdb "SELECT * FROM users" just works when users.csv is there.

Option	Meaning
-f name=path	register a table; repeatable; a path of - means standard input
--format	table (default), csv, json, jsonl
-o	write output to a file
--delim	field separator; tab is accepted
--no-header	the first line is data; columns are named c1, c2, ...
--sniff	rows to sniff for types; 1000 by default, all reads the whole file
--col-type	force a column type, e.g. --col-type zip=text; repeatable
--lazy-quotes	tolerate malformed quoting
--dry-run	for writes, report how many rows would change without touching the file

Supported SQL

[WITH <name> AS (<query>) [, ...]]
SELECT [DISTINCT] <expr> [AS alias], ...
FROM <table|'path.csv'|(<query>)> [alias]
     [, <table> | [INNER|LEFT [OUTER]|CROSS] JOIN <table> ON <expr>]...
[WHERE <expr>]
[GROUP BY <expr>, ...]
[HAVING <expr>]
[ORDER BY <expr|position|alias> [ASC|DESC], ...]
[LIMIT n [OFFSET m]]

<query> UNION|INTERSECT|EXCEPT [ALL] <query>

INSERT INTO <table> [(<col>, ...)] VALUES (<expr>, ...) [, ...]
INSERT INTO <table> [(<col>, ...)] <query>
UPDATE <table> [alias] SET <col> = <expr> [, ...] [WHERE <expr>]
DELETE FROM <table> [alias] [WHERE <expr>]

Expressions: column references, literals, + - * / %, ||, comparisons, AND/OR/NOT, IN, BETWEEN, LIKE ... ESCAPE, IS [NOT] NULL/TRUE/FALSE, CASE and CAST.

Scalar functions: upper lower trim ltrim rtrim length substr replace abs round floor ceil concat coalesce nullif ifnull.

Aggregates: count sum avg min max, all accepting DISTINCT. count(*) counts rows while count(x) counts only non-NULL values.

Subqueries: scalar (SELECT ...), IN (SELECT ...) and EXISTS (SELECT ...), correlated or not. An uncorrelated subquery is computed once; a correlated one is computed once per outer row.

Window functions: row_number rank dense_rank lag lead first_value last_value, plus the window form of every aggregate, written as f(...) OVER (PARTITION BY ... ORDER BY ...). Explicit frame specifications (ROWS/RANGE BETWEEN) are not supported; the frame takes the SQL default: without an ORDER BY inside OVER it is the whole partition, and with one it runs from the start of the partition to the end of the current row's peer group. So sum(x) OVER (ORDER BY t) is a running total, and peer rows share a value.

Joins pick their own strategy: conjuncts of ON that can be arranged as equalities become hash join keys, with the right side built into a hash table and the left side probing it as it streams, while the remaining conjuncts are checked per candidate pair. With no equality conjunct at all it falls back to a nested loop.

Not supported yet: recursive CTEs, RIGHT/FULL JOIN, LATERAL, explicit window frames, GROUPING SETS. Using one produces a clear error rather than quietly doing something else.

Memory: scans, filters and the probe side of a join are streaming. ORDER BY, DISTINCT, GROUP BY, window functions, CTEs and the build side of a join hold their data in memory, so a sort or window computation over more than a million rows should be budgeted at roughly the size of the table.

Three deliberate conventions

1. Unquoted column names are case insensitive; double quoting them demands an exact match. CSV headers are often written as UserName, and forcing people to remember the case only invites confusion. An ambiguous name reports ambiguous column name.
2. ORDER BY puts NULLs at the end of an ascending sort and at the front of a descending one, matching the PostgreSQL default.
3. Integer division: 7 / 2 is 3, which is SQL semantics. Write 7 / 2.0 or CAST(a AS float) / b when you want a fraction.

Two more behaviours that are not deviations but are worth knowing: groups come out in first-seen order, so the same input always gives the same row order; and sum stays integral on an integer column until it overflows or meets a float, while avg always returns a float.

Guarantees when rewriting files

A CSV row cannot be edited in place, so UPDATE and DELETE rewrite the whole file. Four rules follow from that:

0. INSERT appends rather than rewriting: adding n rows costs O(n). When the original does not end with a newline, one is added first. (Only -o requires copying the table and following it with the new rows.)
1. Cells that were not assigned are written back verbatim, never through a round of parsing into a value and formatting back to text. Otherwise one UPDATE ... SET name = ... would incidentally turn every 1.50 in the file into 1.5 and every 00123 into 123.
2. A temporary file in the same directory is written first, then renamed atomically over the original. An error partway through, such as an expression failing on some row, leaves the original untouched by a single byte, and the temporary file is removed. The original's permission bits are preserved.
3. Every right-hand side of SET is evaluated against the row as it was, so SET a = b, b = a is one swap rather than two assignments.

A NULL is written as an empty field, mirroring the rule that an empty field reads back as NULL. -o sends the result elsewhere and leaves the original alone; --dry-run only reports the affected row count. Standard input cannot be rewritten in place.

Two side effects to be aware of: a rewrite re-quotes according to standard CSV rules, so a file that quoted every field comes back with quotes only where they are needed, and line endings are normalized to \n. Field contents themselves are unaffected.

Type sniffing

By default the first 1000 rows are scanned to infer the type of each column, and an empty field is always NULL. Two protective rules:

• a digit string with a leading zero (007, 0755) is treated as text, or the zeros would be lost
• a digit string longer than 18 characters is treated as text, or precision beyond the safe range of an int64 would be lost

When a row outside the sniffing sample conflicts with the inferred type, the error names the file, the row and the column, and suggests --sniff all or --col-type. It never quietly becomes NULL.

Layout

cmd/csvdb/        CLI entry point
internal/lex/      lexing: a hand written state machine
internal/ast/      syntax tree; String() fully parenthesizes, handy for testing
                   precedence
internal/parse/    recursive descent for statements, Pratt for expressions
internal/bind/     name resolution, function checks, CASE normalization ->
                   evaluation trees
internal/eval/     compiled expressions; LIKE has its own backtracking matcher
internal/types/    Value, Schema, three-valued logic, arithmetic, conversion
internal/csvsrc/   CSV scanning and type sniffing
internal/exec/     volcano operators: Scan/Cache/Rows/Rename/Filter/Sort/Project
                   /Distinct/Limit/Aggregate/HashJoin/NestedLoopJoin
                   /SetOp/Window
internal/plan/     syntax tree -> operator tree; CTE materialization and
                   subquery compilation
internal/mutate/   the write path for INSERT/UPDATE/DELETE (no operator tree)
internal/render/   table/csv/json/jsonl output