How to properly format sqlite shell output?

Question

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

Is there a way to beautify the output from sqlite shell?
Is there an alternative shell that's better than default distribution? (CLI clients only)

truly custom output format: http://stackoverflow.com/questions/23120906/how-to-format-sqlite-select-output — Ciro Santilli OurBigBook.com, Nov 23 '15 at 12:38

score 199 · Accepted Answer · answered Apr 23 '13 at 09:39

199

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

answered Apr 23 '13 at 09:39

Mat

10,079
4
42
40

2

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 07 '15 at 15:32
9

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
9

You can add this to your ~/.sqliterc file if you don't want to manually do this every time. – ijoseph Jul 02 '18 at 00:57
1

@RobW you can add command parameters with the -cmd switch, so no need to echo it. So something like this: sqlite3 database.sqlite -cmd ".mode column" -cmd ".headers on" "select * from sip_foo;" | less -S – Adamsan Nov 10 '20 at 13:16

miken32 · Answer 2 · 2019-04-08T19:07:15.637

58

All of the answers provide settings you can type at the SQLite console or via CLI, but nobody mentions 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

Note I've also added a placeholder for null values, instead of the default empty string.

edited Apr 08 '19 at 19:07

answered Oct 12 '18 at 18:17

miken32

688
6
9

score 20 · Answer 3 · answered Aug 08 '16 at 23:49

20

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

answered Aug 08 '16 at 23:49

jersey bean

301
2
3

1

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
@ruvim or use .mode csv or -csv from CLI – qwr Oct 24 '19 at 21:25

score 18 · Answer 4 · edited Jun 17 '19 at 09:09

18

I always use

.mode line

which prints query results vertically, similar to MySQL's \G modifier.

edited Jun 17 '19 at 09:09

miken32

688
6
9

answered May 07 '19 at 21:13

Geoff_Clapp

281
2
3

e-ruiz · Answer 5 · 2022-10-18T18:39:48.190

Note that SQLite3 has a bunch of .mode options. Let's take a look:

    sqlite> .version
    SQLite 3.34.1 2021-01-20
sqlite&gt; .help .mode 
.import FILE TABLE       Import data from FILE into TABLE
   Options:
     --ascii               Use \037 and \036 as column and row separators
     --csv                 Use , and \n as column and row separators
     --skip N              Skip the first N rows of input
     -v                    &quot;Verbose&quot; - increase auxiliary output
   Notes:
     *  If TABLE does not exist, it is created.  The first row of input
        determines the column names.
     *  If neither --csv or --ascii are used, the input mode is derived
        from the &quot;.mode&quot; output mode
     *  If FILE begins with &quot;|&quot; then it is a command that generates the
        input text.
.mode MODE ?TABLE?       Set output mode
   MODE is one of:
     ascii     Columns/rows delimited by 0x1F and 0x1E
     box       Tables using unicode box-drawing characters
     csv       Comma-separated values
     column    Output in columns.  (See .width)
     html      HTML &lt;table&gt; code
     insert    SQL insert statements for TABLE
     json      Results in a JSON array
     line      One value per line
     list      Values delimited by &quot;|&quot;
     markdown  Markdown table format
     quote     Escape answers as for SQL
     table     ASCII-art table
     tabs      Tab-separated values
     tcl       TCL list elements

For the original question we must look at box, table and of course column.

Well, column needs some help of .headers on to show headers, while box and table modes do not care, they always show headers.

These two modes will break layout when displaying data with big lengths. And, again, box and table modes do not care about .width (which is a pity).

When we need to deal with big lengths in SQLite CLI a good .mode is line. This way every column will be displayed line by line, and instances will be separated by a blank line.

Side note: I'm not sure, but at the time of the question, .mode line was not available in SQLite.

Additionaly (but totally off the main topic), good modes to explore, specially for data exporting, are: csv, json, insert and so on.

So, checkout sqlite> .help .mode and make your own tries.

UPDATE:

We can also limit columns sizes with a little trick applying function substr():

select 
  substr(my_column_with_lot_chars, 1, 30) as my_column_with_lot_chars, 
  other_column, 
  and_so_on 
from my_table;

Always check the docs: https://www.sqlite.org/cli.html#dot_command_execution

=)

Note, not versions prior 3.4. For example 3.2 that ships with Macos Big Sur, does not support those options. — ocodo, May 07 '23 at 16:56

score 2 · Answer 6 · answered Aug 17 '19 at 17:06

2

You can use .mode tabs for convenience.

sqlite> select * from user;
name    age
Bob     18
Ali     19

answered Aug 17 '19 at 17:06

Kevin

121
2

score 1 · Answer 7 · answered May 11 '20 at 04:50

On top of everything already said, you can control the width of your columns shown using .width.

Example

.width 8 0 3 9

Then your output will show your first column with a widht of 8 chars, second column auto-adjusting (see below), third column a width of 3 chars, and the fourth will have a width of 9 chars.

Alternative to .width, use an alias name padded with spaces.

Advantage is that you are adjusting the width on the fly in your query only for those columns that require extra width, while the rest of the columns keep using the auto-adjusting width.

All columns will use the auto-adjusting width, that is based on the sqlite documentation width rule:

If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data. This makes the column width self-adjusting. The default width setting for every column is this auto-adjusting 0 value.

Let's say your table "my_table" has the columns "name", "age" and "address". And you are interested in showing:

"name": first 20 characters
"age": auto-adjusting
"address": first 30 characters

Your query will be:

.mode columns
.headers on

CREATE TABLE my_table (name TEXT, age INTEGER, address TEXT);

INSERT INTO my_table VALUES ("short name",
22, "my house");

INSERT INTO my_table VALUES ("my name is very long",
22, "i live in my house somewhere in the planet Earth");

SELECT name AS "name                ",
       age,
       address AS "address                       "
FROM my_table;

Your output:

name                  age         address                       
--------------------  ----------  ------------------------------
short name            22          my house                      
my name is very long  22          i live in my house somewhere i

Cho-Lung · Answer 8 · 2015-10-20T06:10:54.570

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

score 0 · Answer 9 · answered Nov 12 '17 at 17:32

0

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 well

answered Nov 12 '17 at 17:32

superheron

109
2

2

Which system are you using? on macOS no such issue – ospider Apr 09 '18 at 02:51

How to properly format sqlite shell output?

9 Answers9

UPDATE:

Linked