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 shook hands with SQLite whilst building stuff in Python. At first, I wondered how this fabled database ran, and how I could connect to it, but didn’t press the issue because it just worked. Rails’ “Convention Over Configuration” metholodology resulted in me having a clear picture of the database in my head already.
Then along came the need to actually see some data. I downloaded sqliteman thinking I just need some GUI to connect to some localhost server, as my usual workflow demanded.
Upon trying to use it though, I eventually discovered that the database was not a client-server setup. It was, in fact, powered through some flat file under db/ inside the project itself. I opened it with sqliteman and everything just worked.
Several months later, 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.
It was then that I discovered something was already available from the command line for me from the start. That something was sqlite3 itself. After using it for an hour the other night, I am happy to say that it is part of my Rails workflow now.
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:
sqlite>.show
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:
sqlite>.exit
And that’s all there is to get going with simple, self-contained SQLite.