Query a SQLite file from the command line with a .NET tool

onelite is a SQL shell used like sqlite3, installed as a .NET tool, with no native SQLite. In its docs’ words: “the same command line, the same dot-commands for the common jobs, and, for the output modes it implements, the same bytes.”

dotnet tool install -g RecursiveAI.OneLite.Cli

The tool needs the .NET 10 runtime. It installs the command onelite:

onelite -version

1.0.0 (OneLite; SQLite-compatible, behaviour of SQLite 3.53.4)

Create, query, change modes

Each argument after the file name is a statement or a dot-command, run in order. A file that doesn’t exist is created.

onelite shop.db "create table products (id integer primary key, name text, price real)" \
  "insert into products (name, price) values ('Kettle', 24.5), ('Teapot', 18), ('Mug', 6.25)"
onelite -table shop.db "select name, price from products order by price"

+--------+-------+
|  name  | price |
+--------+-------+
| Mug    |  6.25 |
| Teapot |  18.0 |
| Kettle |  24.5 |
+--------+-------+

onelite -json shop.db "select * from products where price < 20"
onelite -csv -header shop.db "select name, price from products"

[{"id":2,"name":"Teapot","price":18.0},
{"id":3,"name":"Mug","price":6.25}]
name,price
Kettle,24.5
Teapot,18.0
Mug,6.25

With no SQL arguments it reads standard input, so onelite shop.db < queries.sql runs a script, and onelite shop.db alone opens an interactive prompt. A failing statement stops the run with exit code 1 and the shell’s message:

onelite shop.db "select nmae from products" 2>&1 || echo "exit code $?"

Parse error in 2nd command line argument: no such column: nmae
  select nmae from products
         ^--- error here
  did you mean: name?
exit code 1

Dump to SQL and import a CSV

.dump writes the database as SQL; “For every file tried the dump is identical to the shell’s, and a dump loaded by either program gives a database whose dump is identical and that passes PRAGMA integrity_check”:

onelite shop.db .dump > shop.sql
onelite copy.db < shop.sql
onelite copy.db "select count(*) from products" "pragma integrity_check"

3
ok

.import loads a CSV; if the table doesn’t exist it is created from the header row:

printf 'name,city\nAda,London\n"Grace, R.",Arlington\n' > people.csv
onelite people.db ".import --csv people.csv people" "select * from people"

Ada|London
Grace, R.|Arlington

The files are ordinary SQLite databases. The sqlite3 shell (3.53.4, from sqlite.org) prints the same bytes for the same query, and checks the file:

onelite -table shop.db "select name, price from products order by price" > a.txt
sqlite3 -table shop.db "select name, price from products order by price" > b.txt
cmp a.txt b.txt && echo "same output"
sqlite3 people.db "pragma integrity_check"

same output
ok

What else it does

• Modes. For list, csv, tabs, ascii, line, json, column, box, table, markdown, quote and html, “output is byte for byte the output of the sqlite3 3.53.4 shell” on the project’s own generated test tables.
• .recover. onelite damaged.db .recover > recovered.sql reads a damaged file “with no locks and no writes, so the files are byte identical afterwards”, and writes live rows as SQL plus records carved from free space into a lost_and_found table. “.recover does not read encrypted databases.”
• Encrypted files. -key PASSPHRASE opens “a database in the SQLCipher 4 format (the same as PRAGMA key)”.

Limits

From the CLI docs:

• Not implemented: “.backup, .save, .restore, .clone, .archive, .parameter, .load, .dbconfig and the other maintenance commands of the sqlite3 shell”.
• Known display differences, “all in the display of unusual input”: .width with text longer than the column (“onelite cuts at the column width”), aligned modes with blobs that are not valid UTF-8, the column gaps of .tables and .indexes, fewer tested cases for .mode tcl, qbox and insert, and .eqp, which “prints OneLite’s plan tree, not the shell’s QUERY PLAN text”.
• “Statements are run by the OneLite engine, so every difference listed in docs/compatibility.md applies” (compatibility).

CLI docs · Getting started · All recipes