---
title: "Query a SQLite file from the command line with a .NET tool"
url: https://recursiveai.org/onelite/cookbook/sqlite3-shell-dotnet-tool/
description: "A sqlite3-style SQL shell you install with dotnet tool install: query a SQLite .db file from the command line in box, JSON or CSV, dump it to SQL and import a CSV, with no native SQLite. Output modes match the sqlite3 3.53.4 shell byte for byte."
version: "1.0.0"
---

# 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.”

```shell
dotnet tool install -g RecursiveAI.OneLite.Cli
```

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

```shell
onelite -version
```

```text
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.

```shell
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"
```

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

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

```text
[{"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:

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

```text
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`”:

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

```text
3
ok
```

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

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

```text
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:

```shell
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"
```

```text
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](https://recursiveai.org/onelite/docs/cli/):

- 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](https://recursiveai.org/onelite/docs/compatibility/)).

[CLI docs](https://recursiveai.org/onelite/docs/cli/) · [Getting started](https://recursiveai.org/onelite/docs/getting-started/) · [All recipes](https://recursiveai.org/onelite/cookbook/)