# sqlitepipe
A simple tool for piping the output of a command into sqlite databases.
## Usage
### Basic usage example
```sh
This will pipe the output of the `date` command into a sqlite database called `stdin.data.sqlite3` into a table named `data` into a column named `blob` into a new row.
To view the result of above command you can do e.g.:
```sh
sqlite3 -table stdin.data.sqlite3 'select blob from data;'
```
```plain
+------------------------------+
| Wed Mar 4 20:25:10 CET 2026 |
+------------------------------+
```
Another invocation will add another row:
```sh
```
```plain
+------------------------------+
| Wed Mar 4 20:25:10 CET 2026 |
| Wed Mar 4 20:25:32 CET 2026 |
+------------------------------+
```
### Piping line based input
```sh
This will create a row for each line in the output of the `ps` command in a database called `stdin.data.sqlite3` in a table named `data` into a column named `line`.
You can check the created output with e.g.: (**Note**: This assumes `stdin.data.sqlite3` did not exist before invocation.)
```sh
sqlite3 -table stdin.data.sqlite3 'select rowid, line from data;'
```
```plain
+-------+--------------------------------------+
| 1 | PID TTY TIME CMD |
| 2 | 147492 pts/2 00:00:09 fish |
| 3 | 190832 pts/2 00:00:00 ps |
| 4 | 190833 pts/2 00:00:00 sqlitepipe |
+-------+--------------------------------------+
```
Another invocation will append the new data:
```sh
```
```plain
+-------+--------------------------------------+
| rowid | line |
+-------+--------------------------------------+
| 1 | PID TTY TIME CMD |
| 2 | 147492 pts/2 00:00:09 fish |
| 3 | 190832 pts/2 00:00:00 ps |
| 4 | 190833 pts/2 00:00:00 sqlitepipe |
| 5 | PID TTY TIME CMD |
| 6 | 147492 pts/2 00:00:10 fish |
| 7 | 190990 pts/2 00:00:00 ps |
| 8 | 190991 pts/2 00:00:00 sqlitepipe |
+-------+--------------------------------------+
```
**Note**: You cannot use `-l`, `-b` (and `-n`, see below) in the same invocation.
### Additional values
You can pass additional values using the `-v`/`--value` command line option:
```sh
for f in Cargo.*; do stat $f | sqlitepipe -v "file=$f"; done
sqlite3 -table stdin.data.sqlite3 'select file, blob from data;'
```
```plain
+------------+--------------------------------------------------------------------------+
| Cargo.lock | File: Cargo.lock |
| | Size: 11576 Blocks: 24 IO Block: 4096 regular file |
| | Device: 0,49 Inode: 6255936 Links: 1 |
| | Access: (0644/-rw-r--r--) Uid: ( 1000/ user) Gid: ( 1000/ user) |
| | Access: 2026-03-04 13:22:45.964713030 +0100 |
| | Modify: 2026-02-17 10:16:20.623469982 +0100 |
| | Change: 2026-02-17 10:16:20.623469982 +0100 |
| | Birth: 2026-01-21 11:09:00.851084440 +0100 |
+------------+--------------------------------------------------------------------------+
| Cargo.toml | File: Cargo.toml |
| | Size: 338 Blocks: 8 IO Block: 4096 regular file |
| | Device: 0,49 Inode: 6793366 Links: 1 |
| | Access: (0644/-rw-r--r--) Uid: ( 1000/ user) Gid: ( 1000/ user) |
| | Access: 2026-03-04 19:19:04.765588403 +0100 |
| | Modify: 2026-03-04 19:19:04.710589094 +0100 |
| | Change: 2026-03-04 19:19:04.710589094 +0100 |
| | Birth: 2026-02-17 10:16:16.873469949 +0100 |
+------------+--------------------------------------------------------------------------+
```
**Note**: Any column that doesn't exist yet will be added automatically:
```sh
sqlite3 -table stdin.data.sqlite3 'select invocation1, invocation2, blob from data;'
```
```plain
+-------------+-------------+------------------------------+
| 1 | | Wed Mar 4 20:23:34 CET 2026 |
| | 2 | Wed Mar 4 20:23:34 CET 2026 |
+-------------+-------------+------------------------------+
```
### Inserting JSON
If your input data is JSON, you can insert it as a [JSONB](https://sqlite.org/json1.html#jsonb) value, which provides optimized storage for JSON values and validation.
JSON data can be inserted with the `-j`/`--json-input` flag:
```sh
for f in *.rs; do
sqlite3 -table stdin.data.sqlite3 "select blob ->> '$.file', blob ->> '$.size' from data;"
```
```plain
+-------------------+-------------------+
| column.rs | 3511 |
| lib.rs | 15858 |
| main.rs | 11411 |
| sanitizing.rs | 1676 |
| stmt.rs | 4109 |
| txmgmt.rs | 12929 |
+-------------------+-------------------+
```
Or if your input is line based:
```sh
find . -type f -printf '{"file": "%f", "size":%s, "permissions":"%m", "mtime":"%TY-%Tm-%Td %TH:%TM"}\n' | sqlitepipe -l -j
sqlite3 -table stdin.data.sqlite3 "select line ->> '$.file', line ->> '$.size' from data;"
```
```plain
+-------------------+-------------------+
| sanitizing.rs | 1676 |
| column.rs | 3511 |
| lib.rs | 15858 |
| main.rs | 11411 |
| stmt.rs | 4109 |
| txmgmt.rs | 12929 |
+-------------------+-------------------+
```
### Inserting files
If you have a list of files you want to insert into a sqlite database you can use the `-f`/`--stdin-files` mode.
This mode reads a (`\n` terminated) list of files from stdin and inserts the contents of each of them into a table, together with it's filename.
For example, to insert all `*.log` files in a directory you can use the following commands:
```sh
find . -type f -name '*.log' | sqlitepipe -f
sqlite3 -table stdin.data.sqlite3 "select filename, blob from data;"
```
```plain
+----------------+----------------------------------------------------------------------------------------+
| ./app.log | 2026-04-04 11:20:01 INFO [MainThread] App initialized successfully. |
| | 2026-04-04 11:21:15 DEBUG [DatabaseConn] Connection pool size increased to 10. |
| | 2026-04-04 11:22:45 WARN [FileWatcher] High memory usage detected: 85%. |
| | 2026-04-04 11:23:12 ERROR [PaymentGateway] Timeout connecting to provider 'Stripe'... |
+----------------+----------------------------------------------------------------------------------------+
| ./htaccess.log | 192.168.1.45 - - [04/Apr/2026:10:12:01 +0000] "GET /index.html HTTP/1.1" 200 4532 |
| | 192.168.1.45 - - [04/Apr/2026:10:12:05 +0000] "GET /styles/main.css HTTP/1.1" 200 1240 |
| | 172.16.0.12 - - [04/Apr/2026:10:13:42 +0000] "POST /api/login HTTP/1.1" 401 231 |
| | 192.168.1.10 - - [04/Apr/2026:10:14:15 +0000] "GET /im... |
+----------------+----------------------------------------------------------------------------------------+
```
If all files contain JSON data you can use the `-j`/`--json-input` flag to insert each file as a JSONB blob.
**Note**: This currently does not work in transaction mode.
### Just inserting values
You can also just insert values without reading from stdin using the `-n`/`--stdin-none` flag:
```sh
sqlitepipe -n -v 'test1=1' -v 'test2=2'
sqlite3 -table stdin.data.sqlite3 'select test1, test2 from data;'
```
```plain
+-------+-------+
| 1 | 2 |
+-------+-------+
```
**Note**: You cannot use `-l`, `-b`, `-j` and `-n` in the same invocation.
**Note**: When using `-n`, you need to provide values using `-v`. Not providing any values will result in an error.
### Overriding defaults
You can use the following command line flags to change defaults:
- Name of the database: `-o <name>`/`--output <name>`
- Name of the table: `-t <name>`/`--table <name>`
- Name of the blob column: `-b=<name>`/`--stdin-blob=<name>`
- Name of the line column: `-l=<name>`/`--stdin-lines=<name>`
To insert the output of the `date` command into a database called `time_data.sqlite3`, a table called `timestamps` and a column called `date_cmd` you can use the following:
```sh
sqlite3 -table time_data.sqlite3 'select date_cmd from timestamps;'
```
```plain
+------------------------------+
| date_cmd |
+------------------------------+
```
### Resetting the table
You can reset the table (clear all data and remove all column definitions) using the `-r`/`--reset` flag:
```sh
sqlite3 -table stdin.data.sqlite3 'select * from data;'
```
```plain
+------------+--------------------------------------+
| extra_data | line |
+------------+--------------------------------------+
| 2 | 192558 pts/2 00:00:00 ps |
| 2 | 192559 pts/2 00:00:00 sqlitepipe |
+------------+--------------------------------------+
```
**Note**: All other tables are left untouched.
### Transaction management
`sqlitepipe` can be used to open a transactions as a daemon and receive data via UNIX sockets. This allows fast bulk insertion of lots of data.
For example to insert all pictures in a folder into a database, you can use the following basic flow:
```sh
sqlitepipe --start-transaction # This will create a socket `sqlitepipe_tx.socket` and forks a daemon into the background
# Insert every png file into the database using the transaction socket.
for f in *.png; do
sqlitepipe -v file="$f" --transaction < $f
done
# Commit the transaction. This will cause the daemon to commit the transaction, delete the socket file and quit.
sqlitepipe --commit-transaction
```
**Note**: This is still in beta and might not work in some scenarios. Also there is currently no error reporting from the daemon available.