How to make .sqliterc commands quiet?

Question

The .sqliterc file for sqlite3 is recommended mainly for .this and .that -style dot commands, for example, the ever popular mysql output emulation:

.header on .timer on .mode column 

However, you can put any SQL query in .sqliterc. Once you understand how slow sqlite3 is, by default, when working with large data sets, you will recognize several PRAGMA commands that significantly improve life, for example PRAGMA synchronous = OFF; .

These commands can also be used in your .sqliterc IF you understand that it will affect everything you do with the sqlite3 command line tool, no matter what database! In my case, this is normal. For the Linux account that I use on a particular machine, I want some of these PRAGMA settings permanently.

However, some PRAGMA settings provide confirmation output, such as yes or off or exclusive or memory . This becomes a problem when you do such things, and these extra words of inference silently include:

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

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

To develop, 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 fine, but you 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:

 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 the .sqliterc commands shut up and go out of print for some PRAGMA commands that pollute our stdout ?

Answer 1

The answer is yes! This is the best way to find, at least.

Attention! This is a bit hacky, and hides some of the “warning” reminders you can see if you do the laid-back practice of placing PRAGMA commands in .sqliterc, first!

Sidebar: If you explore this, you can be misleading .echo on and .echo off , but that does not do what we want. By default, Echo is turned off, and this is normal. The .echo parameter remains as an exercise for the reader.

Answer. Use the .output parameter. 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 need some kind of record of what you have selected.)

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 possible inside this wrap output, and you never have to worry about their uncontrolled verbosity!

PS As a bonus, now you have my recommended 5 PRAGMA lines for using sqlite3 with support Improve SQLite performance? INSERT per second? . (I have a cache size of 500 MB, season to taste.)