This is a card in Dave's Virtual Box of Cards.

SQLite3 CLI notes

Created: 2024-11-29

Back to sqlite.

Display tables

Use .schema To get the current schema (tables and columns):

sqlite> .schema
CREATE TABLE users (id, name, picture, login);
CREATE TABLE times (user, type, start, end);

Display output with headers and left-aligned columns

Use .headers on and .mode columns to get result headers and line up query output by columns.

Before:

sqlite> select * from times;
1|1|1732732847|1732898680
1|3|1732732847|1732898680
3|1|1732898389|
1|2|1732898432|

After:

sqlite> .headers on
sqlite> .mode columns
sqlite> select * from times;
user  type  start       end
----  ----  ----------  ----------
1     1     1732732847  1732898680
1     3     1732732847  1732898680
3     1     1732898389
1     2     1732898432

To make this the default display, write it to ~/.sqliterc:

$ cat ~/.sqliterc
.headers on
.mode column

There are a bunch of other nice options and features. See either .help or the man page man sqlite3.