The most widely used database in the world

#sqlite #linux #python #go

The most widely used database in the world

Photo by Jan Antonin Kolar on Unsplash

A piece of trivia

It would be an excellent question for a trivia show for programmers (by the way, I would definitely watch that!):

What is the world’s most widely used database?

MySQL
Oracle
MongoDB
Microsoft Access
Something else

The answer is, of course, “something else”, namely SQLite. According to their homepage, there are over 1 trillion (1e12) SQLite databases in active use!

What is SQLite?

SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. Let’s go over these characteristics one by one.

SQLite is stand-alone or self-contained in the sense that it has very few dependencies. It runs on any operating system and the entire SQLite library is encapsulated in a single source code file that requires no special facilities or tools to build. Similarly, a complete SQLite database is nothing more than a single file.

SQLite is serverless, because unlike most SQL database engines it is not implemented as a client-server process. With SQLite, the process that wants to access the database reads and writes directly from the database files on disk. There is no intermediary server process.

This also explains why SQLite is a zero-configuration database. It does not need to be installed before it is used, there is no setup, no need for an administrator to create a new database instance or assign access permissions to users. “SQLite just works”, as the homepage puts it.

Finally, it is a transactional database in which all operations appear to be atomic, consistent, isolated, and durable (ACID) — which are the most vital quality standards for databases. SQLite explicitly guarantees that changes within a single transaction either occur completely or not at all, even if the act of writing the change out to the disk is interrupted by a program crash, OS crash or even a power failure!

To boot, SQLite is public domain, small (about 600 KB), fast (SQLite can be faster than your filesystem), reliable, cross-platform, and offers a full-fledged flavour of the SQL language… No wonder it is indeed the most widely deployed database in the world, with famous users like Adobe, Dropbox, Facebook or Google, who use it for the Android OS and Chrome.

However, despite of all the positive aspects of SQLite there are also some disadvantages. Without doubt, the most important one is that SQLite does not allow concurrent writing processes (reading is no problem). If concurrent writing is what you need, you can take a look at PostgreSQL, which is also open-source. Also, SQLite databases has limited data types (e.g. missing a type for dates or times) and its SQL dialect has some limitations compared to standard SQL.

What is SQLite used for?

SQLite database files are a recommended storage format by the US Library of Congress and a popular and excellent choice as an Application File Format. For instance, let’s say you are writing a Contacts app for smartphones. Besides a pretty user interface you will need a reliable, fast, lightweight database to store your contacts’ data. Et voilà, enter SQLite… And this is precisely what happens. Smartphones, gadgets, desktop applications — there are literally tens of billions of SQLite database files in use daily. To cite only one more example, SQLite is also the default database engine for apps made with Django, a popular Python web framework.

How to use SQLite

For the next section, I will use a basic database to demonstrate how to interact with SQLite databases. Here’s what it looks like in CSV format (the first row contains the column headers):

identifier,product,colour
1,apples,red
2,bananas,yellow
3,grapes,purple

GUI

First of all, we should point out that SQLite can also easily be manipulated by non-technical users. There are several, excellent open-source SQLite “browsers” or “editors” available that allow for a graphical user interface to manipulate SQLite databases. Two notable examples are, DB Browser and Sqlite Viewer.

CLI

The SQLite project provides a simple command-line program called **sqlite3 **that allows the user to manually enter and execute SQL statements against an SQLite database. You can use this program in two ways.

First, you can use it in “direct” mode, i.e. you can use the binary sqlite3 in your terminal environment or in shell scripts. This also implies that you can execute SQLite operations from any other programming language, as long as your language of choice is able to spawn a shell process.

Creating the schema for the above database, for instance, looks like this:

tdeneire@XPS-13-9370:~/tmp$ sqlite3 mydb.sqlite 'CREATE TABLE fruit (identifier integer, product text, colour text)'

Alternatively, SQLite also offers an “interactive” command-line shell, which I personally do not use frequently, unless for didactic purposes and occasionally for debugging.

Inserting our database values, for instance, looks like this:

tdeneire@XPS-13-9370:~/tmp$ sqlite3 mydb.sqlite
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> INSERT INTO fruit VALUES(1,'apples','red');
sqlite> INSERT INTO fruit VALUES(2,'bananas','yellow');
sqlite> INSERT INTO fruit VALUES(3,'grapes','purple');
sqlite> .quit

APIs

There are SQLite APIs for many programming languages, including C/C++, C#, Go, Java, Perl, PHP, Python, … Let’s have a look at the Python module first, which is part of the standard library:

The output of this program is [(2,'bananas')] .

In Go, you need to install the go-sqlite3 package first. After that, it’s pretty straightforward to use as well: