The Simplicity of SQLite

SQLite consists of a single file as the database and a set of CLI commands. “Like all magnificent things, it’s very simple.”

I first brushed up against SQLite while building stuff in Ruby on Rails.

The weird thing about SQLite, as a database, is that it’s not a client-server setup. It fact, it is just a file.

You literally just open it in something like sqliteman.

Several months after that first encounter though, I had the distinct feeling that a GUI was too much for something that felt as minimal as SQLite. All of my data is just sitting right there in that file, after all.

Introducing: the sqlite3 command.

Rails Example

From inside a Rails project, open the sqlite database inside the db/ folder:

cd db/
sqlite3 development.sqlite3

This results in a sqlite> prompt.

Pretty Output

By default, SELECT queries will show a jumbled mess of data, which especially sucks for select * queries that are pulling back a lot of columns. Enable a prettier, more readable display with:

sqlite>.header on
sqlite>.mode column
sqlite>.timer on

Current options can be verified with:


Meat and Potatoes Commands

The essential commands for me are:

sqlite>.show # display sqlite3 commands
sqlite>.tables #list all tables in the database
sqlite>.schema table_name # show schema for some **table_name**

To exit sqlite3 and go back to the regular command line:


And that’s all there is to get going with simple, self-contained SQLite.