# 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 |
+-------------+-------------+------------------------------+
```
### 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` 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.