SQLite 3

What is sqlite3 good for?

Using the sqlite3 CLI can often be a good alternative to python-based solutions when the operations we are performing is easy to express directly with SQL.

How does it work?

1. Commands like .tables are prefixed with a dot.
2. SQL statements select * from AllstarFull end with a semicolon

Useful commands are:

1. .tables: Shows all tables in the db.
2. .timer ON: times how long an operation takes between .timer ON and .timer OFF
3. .schema shows the schema of all the tables in the db

Cookbook

We can dump files to csv via the following syntax

.mode CSV
.headers on
.output table.csv

We can run an SQL script from the command line using the following syntax

sqlite3 database.db < sql_script.sql

After dropping a table, you may have to use VACUUM; to reclaim the space that the dropped table was using.

Dealing with Database or Disk is Full

When dealing with tables that are large compared to the space on disk it’s possible to get an error that says: Error: near line 41: database or disk is full even though the disk isn’t full and even though the database is far smaller than the 120TB size limit.

This could be because either 1) the temporary file directory is filling up or 2) the database is corrupted.

In the case of (1), the solution is either to tell SQLite to put everything in ram via sqlite> prgrama temp_store = 2;. If you don’t have enough ran for the operation, you can change the temporary directory with the following code:

sqlite> pragma temp_store = 1;
sqlite> pragma temp_store_directory = '/spacious/hdd/data';

In the case of (2) hope is still not lost. Search the internet for ways to repair a corrupt SQLite database.

When to use SQLITE

SQLite is much easier to work with than other RBDMS because it is serverless, requires few external libraries, and requires no configuration.

I think of SQLite as being the Python of databases. If you are using an RBDMS for a project, you should have a good reason not to use SQLite. On websites it can handle 100k hits/day with ease and 500k is probably a more realistic upper bound.

The SQLite website offers a few cases when SQLite probably is not the best choice.

1. If your DB is so big that it can’t fit on a single disk, then you probably need something else.
2. Websites with very high write volumes
3. Multiple uers need to write to a db simultaneously.
4. You aren’t storing your RBDMS on the same hard drive as your app.