Querying a CSV File in SQLite Without Importing It
Compile SQLite's CSV virtual table, query a file in place, and compare the cost with importing it and with DuckDB, including the typed schema surprises.
Sometimes the question is small and the file is not: you want the total for one country out of a 130 MB export, once. Importing the file into a database to ask it is a lot of ceremony. SQLite can read a CSV where it sits, through a virtual table, if you are willing to compile one small extension. This article builds it, uses it, and then measures it against importing and against DuckDB, which does the same job with less work and a faster engine. The other route, importing, is covered in the guide to importing CSV files into SQLite.
What you need
A C compiler and the source file. The SQLite project ships a CSV virtual table in its source tree as ext/misc/csv.c, and the CSV virtual table page documents it. The standard command line shell does not include it, which you find out like this:
sqlite3 :memory: "CREATE VIRTUAL TABLE temp.cust USING csv(filename='customers.csv', header=YES)" 2>&1Error in 2nd command line argument: no such module: csvno such module: csv. SQLite's loadable extension mechanism, described on the run-time loadable extensions page, is how you add it. Compile the file into a shared library:
gcc -g -fPIC -shared -I/usr/local/include sqlite-src-3540100/ext/misc/csv.c -o /usr/local/lib/csv.so && ls -l /usr/local/lib/csv.so | awk '{print $5 " bytes", $9}'100376 bytes /usr/local/lib/csv.soThat produced a csv.so of about 100 KB. On macOS the command differs (-dynamiclib and a .dylib name), and on a distribution that ships the shell without extension loading, .load can be disabled, in which case you need a shell built with it.
Querying in place
Load the extension and create a virtual table pointing at the file. header=YES takes the column names from the first row:
sqlite3 :memory: <<'SQL'
.load /usr/local/lib/csv
CREATE VIRTUAL TABLE temp.cust USING csv(filename='customers.csv', header=YES);
SELECT * FROM temp.cust WHERE balance > 10;
SQL1|Ana Silva|[email protected]|2025-01-15|120.50
4|Cy Dubois|[email protected]|2025-03-01|15.75The two customers with a balance above 10 came back, with no import and no database file: the virtual table lives in the temporary schema and disappears with the connection. The file is read again for every query, so there is no stale copy and no cost to keep one in step. The same fact is the downside, as the benchmark below shows.
Types: everything is text unless you say
The default columns of a CSV virtual table are untyped, so values are text and comparisons behave as text, which the numbers as text article shows costs you in sorting and comparing. The schema parameter takes a CREATE TABLE statement that declares names and types for the columns, and columns gives their count. I tried it and checked what came back, using a script that creates one table without a schema and one with:
sqlite3 :memory: < ../../vt_types.sqlrows without a schema: 4
rows with a schema: 5
first row with a schema: id / name
typeof(balance) with a schema: text
rows with balance > 100 (should be 1): 3Three results deserve attention. Without a schema, the table has 4 rows, the four customers, with the header taken for names. With a schema and header=YES, it had 5 rows, and the first of them is id / name: on this build, the header line came back as data when a schema was given, although the CSV virtual table page says a true header argument makes the first row a header. And typeof(balance) is text even though I declared balance REAL, because the extension hands every value back as text. A filter on balance > 100 should match one customer (120.50) and matched three, since the header's balance and two other text values compared greater than 100 as text.
With a schema, count the rows and look at the first one before you trust any query, and cast in the query (CAST(balance AS REAL) > 100) when you need numbers. For a typed view of the data, import it, or use an engine that infers types from the file.
How fast is it
The virtual table reads and parses the whole file on every query. For a question asked once, that is the same cost as an import, and for a question asked several times it is the cost of an import each time. I ran the same query, the sum of amount by country over 2,000,000 rows returning the top country, four ways:
python bench_virtual.pymethod median s answer
csv virtual table, typed schema 1.72 FR|22969870.61|286777
csv virtual table, default (text) columns 1.64 FR|22969870.61|286777
import into a fresh database, then query 2.66 FR|22969870.61|286777
DuckDB read_csv, no import 0.45 FR|22969870.61|286777The virtual table took 1.72 s with a typed schema and 1.64 s with default columns, and a fresh import followed by the query took 2.66 s. So the virtual table is faster than importing for one query, and slower than a loaded table for the second. DuckDB's read_csv answered in 0.45 s with no setup, parsing in parallel and inferring types as it read, about four times faster than the virtual table. All four returned the same answer, FR with 22969870.61 over 286,777 rows.
A fifth way, which is neither: stream a few columns into SQLite with the pipe form of .import. .import --csv '|qsv select id,balance customers.csv' small read two selected columns into a table and sum(balance) returned 136.25, from a command that needs no extension and loads only what the question needs:
sqlite3 :memory: ".import --csv '|qsv select id,balance customers.csv' small" "SELECT sum(balance) FROM small"136.25The DuckDB comparison
For the same file and the same small customers question, DuckDB needs one line and returns a typed result:
duckdb -box -c "SELECT id, name FROM read_csv('customers.csv') WHERE balance > 10"┌────┬───────────┐
│ id │ name │
├────┼───────────┤
│ 1 │ Ana Silva │
│ 4 │ Cy Dubois │
└────┴───────────┘read_csv sniffed the delimiter, the header and the types, and filtered on balance > 10 as a number. That is the argument for using DuckDB for "look at this file" work, covered in querying CSV files with DuckDB, and I would reach for it before compiling an extension. The cases for the SQLite virtual table are narrow: you are already inside SQLite, you want to join a CSV against tables that live in a SQLite database, or you cannot install anything else.
How to tell it worked
Check that the header did not leak into the data: SELECT count(*) FROM temp.cust should be the number of records, one less than the number of lines, and it was one too many when I gave the table a schema. Compare a sum with a figure from another tool. And look at typeof for the columns you rely on, since the declared types did not change what the extension returned, and cast explicitly in every numeric comparison.
When importing or DuckDB is the better route
If you will ask more than a couple of questions, import the file; a query on the loaded, typed table runs in a fraction of a second, as the 0.60 s aggregate in the import guide shows, and the import is the cost of two queries. If the file is large and the question is an aggregate, DuckDB is faster in both setup and execution. If the file is changing and you need the freshest copy each time, the virtual table has a real edge, since there is nothing to refresh. And if the file has multi-line fields or an unusual dialect, check that the extension parses it the way you expect: its documentation lists the parameters for the delimiter and quote, and it is a small parser, not a full CSV library.
