Bash – how to pass multiple commands to sqlite3 in a one liner shell command

bashbash-expansionshell-scriptsqlite

I'm trying to save clipboard content into an sqlite database. Created database and tables.

I don't want it to create journal file in every clipboard change, so I tried to pass PRAGMA journal_mode = OFF; flag. But it's tricky to pass those commands in a one liner commands because sqlite only accepts two command like

sqlite3 clipboard_archive.db "insert into cb (cb_context) values ('clipboard');"

it works.
I looked for Q&A sites, some advises echoing the commands in the following way.

echo "PRAGMA journal_mode = OFF;" | sqlite3 clipboard_archive.db "insert into cb (cb_context) values ('clipboard');"

But PRAGMA journal_mode = OFF; doesn't take effect in that way though it works within the sqlite3 command prompt.

What's wrong with my one liner script?

Best Answer

Not sure why you want to use SQLite if you don't want the journal (have you considered the much faster WAL mode if speed is a concern?) but you can give multiple commands separated by semicolons:

sqlite3 clipboard_archive.db "PRAGMA journal_mode = OFF; insert into cb (cb_context) values ('clipboard');"

Related Solutions

Bash – How does this su -c “…” command seem to pass two commands instead of one

It's a single command passed to the shell. The shell allows you to set environment variables on a per-command basis, eg:

PGPORT=5433 psql

su invokes the shell with its argument, so:

su -c 'PGPORT=5433 psql'

is like doing:

su
exec bash -c 'PGPORT=5433 psql'

Frankly, I tend to prefer using sudo, which makes setting environment variables easy and handles commands with complex quoting properly because it doesn't go via the shell.

How to redirect sqlite3 output to a file

Run it all as a single command:

$ sqlite3 stylish.sqlite "SELECT * FROM styles;" > somefile.txt

Example

$ sqlite3 addons.sqlite "select * from icon;" > somefile.txt

$ cat somefile.txt 
1|32|https://addons.cdn.mozilla.net/img/uploads/addon_icons/354/354399-32.png?modified=1369154804
1|64|https://addons.cdn.mozilla.net/img/uploads/addon_icons/354/354399-64.png?modified=1369154804

Using tee

If you want to see the output as it's being written to the file you can use tee instead.

$ sqlite3 addons.sqlite "select * from icon;" | tee somefile.txt
1|32|https://addons.cdn.mozilla.net/img/uploads/addon_icons/354/354399-32.png?modified=1369154804
1|64|https://addons.cdn.mozilla.net/img/uploads/addon_icons/354/354399-64.png?modified=1369154804