How and When to Use Sqlite

Sqlite is a very simple and fast open source SQL engine. This tutorial will explain when it is optimal to use Sqlite, as opposed to a full-blown RDBMS such as Mysql or Postgres, how to install it, and will also provide some basic usage examples, covering CRUD - Create, Read, Update, and Delete.

Clearing up a few misconceptions

Don't be deceived into thinking that Sqlite is only for testing and development. For example, it works fine for websites receiving up to 100 000 hits a day. And this is a conservative limit. The maximum size for a Sqlite database is 140 Terabytes (should be enough, right?), and it can be substantially faster than a full-blown RDBMS, as the full database and all other necessary data is stored in a normal file in the host's file system, so no separate server process is needed, cutting out all need for slow inter-process communication.

Optimal Usage of Sqlite

Sqlite is focused on simplicity. Because it is completely internal, it is often significantly faster than alternatives. If you are looking for portability (with regards to both languages and platforms), simplicity, speed, and a small memory footprint, then Sqlite is ideal. Its shortcoming are only apparent if you need high reading or writing concurrency: Sqlite can only support one writer at a time, and the normally high file system latency may be inconvenient if there is a need for many clients to access a Sqlite database simultaneously. A final possible disadvantage is that its syntax, though similar to other SQL systems, is unique, so while it's fairly trivial to move to another system, if you do 'outgrow' Sqlite, there will be some overhead involved in the transition. There are some very good outlines on the pros and cons of Sqlite here.

Installation

The sqlite3 module is part of the standard Python library, so on a standard Ubuntu installation, or any system with Python installed, no further installation is strictly necessary. To install the Sqlite command line interface on Ubuntu, use the commands:

sudo apt-get update
sudo apt-get install sqlite3 libsqlite3-dev

If you need to rather compile it from source, then grab the latest autoconf version from sqlite.org/download.html. At the time of writing:

wget http://sqlite.org/2013/sqlite-autoconf-3080100.tar.gz
tar xvfz sqlite-autoconf-3080100.tar.gz
cd sqlite-autoconf-3080100
./configure
make
make install

(Notes for building from source: 1) Don't do this on a standard Ubuntu installation, as you'll probably get a "header and source version mismatch" error, due to conflict between an already installed version and the newly installed one. 2) If the make command seems to expect further input, just be patient, as the source can take a while to compile.)

Basic Command Line Interface Usage

To create a database, run the command:

sqlite3 database.db

Where 'database' is the name of your database. If the file database.db already exists, Sqlite will open a connection to it; if it does not exist, it will be created. You should see output similar to:

SQLite version 3.8.1 2013-10-17 12:57:35
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Now let's create a table, and insert some data. This table, named "wines" has four columns - for an ID, the wine's producer, the wine's kind, and country of the wine's origin. As it's not Friday yet, we'll insert only three wines into our database:

CREATE TABLE wines (id integer, producer varchar(30), kind varchar(20), country varchar(20)); 
INSERT INTO WINES VALUES (1, "Rooiberg", "Pinotage", "South Africa");
INSERT INTO WINES VALUES (2, "KWV", "Shiraz", "South Africa");
INSERT INTO WINES VALUES (3, "Marks & Spencer", "Pinot Noir", "France");

We've created the database, a table, and some entries. Now press Ctrl + D to exit Sqlite, and type the following (again substituting your database's name for 'database'), which will reconnect to the database we just created:

sqlite3 database.db

Now type:

SELECT * FROM wines;

And you should see the entries we've just made:

1|Rooiberg|Pinotage|South Africa
2|KWV|Shiraz|South Africa
3|Marks & Spencer|Pinot Noir|France

Great. That's it for creating and reading. Let's do an update and delete:

UPDATE wines SET country="South Africa" WHERE country="France";

Which will update the database so all wines which are listed as coming from France will instead be listed as coming from South Africa. Check the result with:

SELECT * FROM wines;

And you should see:

1|Rooiberg|Pinotage|South Africa
2|KWV|Shiraz|South Africa
3|Marks & Spencer|Pinot Noir|South Africa

Now all our wines come from South Africa. Let's drink the KWV in celebration, and delete it from our database:

DELETE FROM wines WHERE id=2;
SELECT * FROM wines;

And we should see one fewer wine listed in our cellar:

1|Rooiberg|Pinotage|South Africa
3|Marks & Spencer|Pinot Noir|South Africa

And that covers all of the basic database operations. Sqlite has wrappers and drivers in all the major languages, and can run on most systems. A list of many of them can be found here. Good luck, and have fun.