If I go to mysql shell and type SELECT * FROM users I get -

+--------+----------------+---------------------------------+----------+-----------+--------------------+--------------------+
| USERID | NAME           | EMAILID                         | PASSWORD | USER_TYPE | CONTACT_ID_FKUSERS | COMPANY_ID_FKUSERS |
+--------+----------------+---------------------------------+----------+-----------+--------------------+--------------------+
|    137 | X              | b@cc.com                        | #        | ADMIN     |                166 |                110 |
|    138 | Kshitiz        | ksharma@aaa.com                 | asdf     | ADMIN     |                167 |                111 |
+--------+----------------+---------------------------------+----------+-----------+--------------------+--------------------+

Oracle sqlplus shows -

USERID     NAME  EMAILID    PASSWORD   USER_TYPE  CONTACT_ID_FKUSERS COMPANY_ID_FKUSERS
---------- ----- ---------- ---------- ---------- ------------------ ------------------
137        X     b@cc.com   #          ADMIN                     166                110
137        X     b@cc.com   #          ADMIN                     166                110

Sqlite shell shows -

137|X|b@cc.com|#|ADMIN|166|110
138|Kshitiz|ksharma@aaa.com|asdf|ADMIN|167|111
  1. Is there a way to beautify the output from sqlite shell?
  2. Is there an alternative shell that's better than default distribution? (CLI clients only)
up vote 77 down vote accepted

For "human readable" output, you can use column mode, and turn header output on. That will get you something similar to the sqlplus output in your examples:

sqlite> select * from foo;
234|kshitiz|dba.se
sqlite> .mode column
sqlite> select * from foo;
234         kshitiz     dba.se
sqlite> .headers on
sqlite> select * from foo;
bar         baz         baf
----------  ----------  ----------
234         kshitiz     dba.se
  • Beautiful, thanks! The content did not fit (horizontally), and there doesn't seem to be a built-in pager, so I had to use echo -e '.mode column\n.headers on\nselect * from sip_foo;\n' | sqlite3 database.sqlite | less -S to get one row per line without word-wrap. – Rob W Aug 7 '15 at 15:32
  • 4
    Note, however, that you may need to use the .width command to make you columns wider. Else your content will be truncated visually. – mlissner Sep 25 '15 at 23:30
  • You might also want to add .separator ROW "\n", so that the rows are separated by line breaks. Mine was not, and the output was unreadable. – Boxuan May 11 '17 at 15:08
  • 2
    You can add this to your ~/.sqliterc file if you don't want to manually do this every time. – ijoseph Jul 2 at 0:57

For those that are interested in getting the same results, except running sqlite from command line. I found that the following doesn't work:

$ sqlite3 <dbfile> ".headers on;.mode column;select * from MyTable"
Error: mode should be one of: ascii column csv html insert line list tabs tcl

Instead, you have to use the options -column and -header with the sqlite command as follows:

$ sqlite3 -column -header <dbfile> "select * from MyTable"

Using:

$ sqlite3 --version 3.8.11.1 2015-07-29 20:00:57 cf538e2783e468bbc25e7cb2a9ee64d3e0e80b2f
  • Although there is no option to set columns width, the following workaround is possible: echo -e ".headers on \n.mode column \n.width 10 20 500 \n select * from MyTable" | sqlite3 <dbfile> — i.e., send the commands to the stdin. – ruvim Aug 24 '17 at 14:35
  • I think your error was using semicolons instead of ".headers on\n.mode column\n etc – deed02392 Nov 17 '17 at 14:41

Mine looked like a messed with no line breaks. @Boxuan comment on

You might also want to add .separator ROW "\n", so that the rows are separated by line breaks. Mine was not, and the output was unreadable. – Boxuan May 11 at 15:08

Fixed my issue with it as wellenter image description here

  • Which system are you using? on macOS no such issue – ospider Apr 9 at 2:51

As I can't comment yet... In addition to the great answers already provided by Mat and mlissner, if in any case the content of a column is truncated, after giving the right format to the sqlite shell (using .mode column and .headers on as stated above), there is also the possibility to use .explain so the full content of a column is shown.

The only downside to this command is that the columns headers shrink, thus not reading them properly and the output can be quite messy (in a visual scenario), then you can use .explain off to return to the previous format and view it with a more "human readable" format once more.

This approach can be used in conjunction with output formatters commands, and as a temporary solution to view full data of a database/column, as with the use of .width you always have to give the precise number of characters in order to get the full output of a column's data.

For more info on changing output formats, a quick reference to the default CLI documentation:

https://www.sqlite.org/cli.html

None of the answers mention that these settings can be put into an RC file to avoid having to type them all the time. Save this as ~/.sqliterc:

.mode column
.headers on
.separator ROW "\n"
.nullvalue NULL

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.