sqlitepipe 0.2.1

A simple tool for piping the output of a command into sqlite databases.
Documentation

sqlitepipe

A simple tool for piping the output of a command into sqlite databases.

Usage

Basic usage example

date | sqlitepipe

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.:

sqlite3 -table stdin.data.sqlite3 'select blob from data;'

+------------------------------+
|             blob             |
+------------------------------+
| Wed Mar  4 20:25:10 CET 2026 |
+------------------------------+

Another invocation will add another row:

date | sqlitepipe
sqlite3 -table stdin.data.sqlite3 'select blob from data;'

+------------------------------+
|             blob             |
+------------------------------+
| Wed Mar  4 20:25:10 CET 2026 |
| Wed Mar  4 20:25:32 CET 2026 |
+------------------------------+

Piping line based input

ps | sqlitepipe -l

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.)

sqlite3 -table stdin.data.sqlite3 'select rowid, line from data;'

+-------+--------------------------------------+
| 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 |
+-------+--------------------------------------+

Another invocation will append the new data:

ps | sqlitepipe -l
sqlite3 stdin.data.sqlite3 'select rowid, line from data;'

+-------+--------------------------------------+
| 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:

for f in Cargo.*; do stat $f | sqlitepipe -v "file=$f"; done
sqlite3 -table stdin.data.sqlite3 'select file, blob from data;'

+------------+--------------------------------------------------------------------------+
|    file    |                                   blob                                   |
+------------+--------------------------------------------------------------------------+
| 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:

date | sqlitepipe -v 'invocation1=1'
date | sqlitepipe -v 'invocation2=2'
sqlite3 -table stdin.data.sqlite3 'select invocation1, invocation2, blob from data;'

+-------------+-------------+------------------------------+
| invocation1 | invocation2 |             blob             |
+-------------+-------------+------------------------------+
| 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 value, which provides optimized storage for JSON values and validation.

JSON data can be inserted with the -j/--json-input flag:

for f in *.rs; do 
  stat --format='{"file": "%n", "size": %s, "permissions": "%a", "type": "%F"}' "$f" | sqlitepipe -j
done

sqlite3 -table stdin.data.sqlite3 "select blob ->> '$.file', blob ->> '$.size' from data;"

+-------------------+-------------------+
| blob ->> '$.file' | blob ->> '$.size' |
+-------------------+-------------------+
| 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:

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;"

+-------------------+-------------------+
| line ->> '$.file' | line ->> '$.size' |
+-------------------+-------------------+
| sanitizing.rs     |              1676 |
| column.rs         |              3511 |
| lib.rs            |             15858 |
| main.rs           |             11411 |
| stmt.rs           |              4109 |
| txmgmt.rs         |             12929 |
+-------------------+-------------------+

Just inserting values

You can also just insert values without reading from stdin using the -n/--stdin-none flag:

sqlitepipe -n -v 'test1=1' -v 'test2=2'
sqlite3 -table stdin.data.sqlite3 'select test1, test2 from data;'

+-------+-------+
| test1 | test2 |
+-------+-------+
| 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:

date | sqlitepipe -o time_data.sqlite3 -t timestamps -b=date_cmd

sqlite3 -table time_data.sqlite3 'select date_cmd from timestamps;'

+------------------------------+
|           date_cmd           |
+------------------------------+
| Wed Mar  4 20:29:27 CET 2026 |
+------------------------------+

Resetting the table

You can reset the table (clear all data and remove all column definitions) using the -r/--reset flag:

date | sqlitepipe -v 'invocation=1'
ps | sqlitepipe -r -l -v 'extra_data=2'

sqlite3 -table stdin.data.sqlite3 'select * from data;'

+------------+--------------------------------------+
| extra_data |                 line                 |
+------------+--------------------------------------+
| 2          |     PID TTY          TIME CMD        |
| 2          |  147492 pts/2    00:00:10 fish       |
| 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:

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.