How to make .sqliterc commands be quiet

Question

The .sqliterc file for sqlite3 is advised primarily for .this and .that -style dot-commands, like the ever-popular mysql output emulation:

.header on
.timer on
.mode column

However, you can put whatever SQL you want into .sqliterc. Once you realize how slow sqlite3 is, by default, when working with large data sets, you learn some PRAGMA commands which make life much better, like PRAGMA synchronous = OFF;.

Those commands can also go in your .sqliterc, IF you understand those will then affect everything you do with the command-line "sqlite3" tool, regardless of which database! In my case, that is fine. For the Linux account I'm using, on a particular machine, I DO want some of those PRAGMA settings all the time.

However, some PRAGMA settings produce confirmation output, like yes or off or exclusive or memory. That becomes a problem when you do things like this, and those extra little words of output get silently included:

echo "select * from blah;" | sqlite3 foo.db > output.txt
echo "select * from blah;" | sqlite3 foo.db | wc -l

If you happen to have 5 PRAGMA statements in .sqliterc, and 2 of them produce output, your line count in that second example (wc -l) will be off by two, and your data in output.txt is not quite what you expect it to be. Those extra 2 lines go to stdout, not stderr, by the way.

To elaborate, with a .sqliterc file containing this:

PRAGMA synchronous = OFF;
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = MEMORY;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA cache_size = -500000;

Everything works great, but you will get SELECT output like this:

off
memory
2904|wan|1417737600|772|108243|0|1946|635589|0
2904|wan|1417737900|765|119478|0|1980|647472|0
2904|wan|1417738200|708|90934|0|1924|622615|0
2904|wan|1417738500|710|105128|0|1914|622634|0

Instead of what you want like this:

2904|wan|1417737600|772|108243|0|1946|635589|0
2904|wan|1417737900|765|119478|0|1980|647472|0
2904|wan|1417738200|708|90934|0|1924|622615|0
2904|wan|1417738500|710|105128|0|1914|622634|0

See the difference? The question is: Can we somehow make .sqliterc commands shut up, and quit printing the result of certain PRAGMA commands which pollute our stdout?

Answer 1

The answer is yes! This is the best way I found, at least.

Warning! This is a bit hackish, and hides some of the reminder "warnings" you might see if you do the non-recommended practice of putting PRAGMA commands in .sqliterc, in the first place!

Sidebar: When researching this, you might get mislead by .echo on and .echo off but that doesn't do what we want. Echo is OFF by default, and that's fine. The .echo setting is left as an exercise for the reader.

Answer: Use the .output setting. Set /dev/null as the output for all commands, and then set the output back to the default stdout at the end. (Or use /tmp/somefile or whatever you want, if you want some record of what you junked.)

Fixed .sqliterc file:

.output /dev/null
PRAGMA synchronous = OFF;
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = MEMORY;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA cache_size = -500000;
.output stdout

Now you can wrap as many .dot and PRAGMA commands as you want inside that output "wrapper", and you will never be bothered by their uncontrollable verbosity!

P.S. As a bonus, you now have my recommended 5 performance PRAGMA lines for using sqlite3, with props to Improve INSERT-per-second performance of SQLite? . (Mine has a 500 MB cache size; season to taste.)

Answer 2

sqlite3 -batch 

quietens things down