Friendly SQLite output

SQLite is extremely handy for embedded, desktop and development uses. However with default settings it can seem somewhat less friendly to use directly than the server based databases like MySQL and PostgreSQL.

With defaults:

SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from users;
1|Administrator|admin|2010-10-24 23:09:52|2010-10-25 07:24:34
2|John|jhawthorn|2010-10-24 23:09:52|2010-10-24 23:09:52
sqlite>

Under these settings I found sqlite too awkward to use regularly. not particularly friendly when compared to mysql’s default output

mysql> select * from users;
+----+---------------+-----------+---------------------+---------------------+
| id | first_name    | username  | created_at          | updated_at          |
+----+---------------+-----------+---------------------+---------------------+
|  1 | Administrator | admin     | 2010-10-24 23:09:52 | 2010-10-25 07:24:34 |
|  2 | John          | jhawthorn | 2010-10-24 23:09:52 | 2010-10-24 23:09:52 |
+----+---------------+-----------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql>

Adding a couple lines to ~/.sqliterc, which is run as sqlite opens, makes sqlite’s output more like MySql’s.

-- ~/.sqliterc
.header on    -- show column names
.mode column  -- show results in column format
.timer on     -- show execution time after each query

the result being

sqlite> select * from users;
id          first_name     login       created_at           updated_at                
----------  -------------  ----------  -------------------  -------------------
1           Administrator  admin       2010-10-24 23:09:52  2010-10-25 07:24:34
2           John           jhawthorn   2010-10-24 23:09:52  2010-10-24 23:09:52       
CPU Time: user 0.000000 sys 0.000000
sqlite>

With this tweak, I now prefer using sqlite for development, Giving me familiarity as long time MySQL user and having sqlite’s advantages and simplicity.