John Hawthorn

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:

$ sqlite3 db/dev.sqlite3 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.