SQLite command line: How do you pipe output to a UNIX utility?

Question

The SQLite documentation for its command line client indicates that its possible to filter output of SQLite queries through UNIX utilities:

The default output mode is "list".
[...]
List mode is especially useful when you are going to send the output of a query to another program (such as AWK) for additional processing.

example of output produced at an SQLite command prompt

sqlite> select * from todos;
1|finish reading getting started section of the vim manual
2|finish app feature

then, if I try adding a pipe to the command, I just get a new prompt

sqlite> select * from todos; | grep vim
...> 

Is it possible to send output to a unix utility using a pipe from the SQLite command line, or is it only possible to filter SQLite output if you are actually writing a C application and using the SQLite C library?

Answer 1

You can't pipe output from an interactive SQLite session because it's not a shell. | doesn't do in SQL what it does on a command line. What you probably need to do is something akin to sqlite3 /path/to/mydata.sqlite "select * from todos" | grep vim, which will execute the SQL, and grep the output as you appear to be trying to do.

Answer 2

It's possible to filter the output of a query through a command by prefacing the argument to .output or .once with a |.

.once '|grep vim'
select * from todos;

Unfortunately, this does not interact well with programs that take over your whole terminal, like pagers. For that, I highly recommend litecli.

https://sqlite.org/cli.html#writing_results_to_a_file