The most widely used database in the world
Tags: sqlite, linux, python, go
Published: 2023-02-11 08:23:25

---
title: The most widely used database in the world
published: true
description: SQLite, the most widely used database in the world
canonical_url: https://tomdeneire.medium.com/the-most-widely-used-database-in-the-world-d0cd87f7c482
tags: sqlite,linux,python,go

---


# The most widely used database in the world

![Photo by [Jan Antonin Kolar](https://unsplash.com/@jankolar?utm_source=medium&utm_medium=referral) on [Unsplash](https://unsplash.com?utm_source=medium&utm_medium=referral)](https://cdn-images-1.medium.com/max/7718/0*vO4CRDZ4v82rjT0m)
Photo by [Jan Antonin Kolar](https://unsplash.com/@jankolar?utm_source=medium&utm_medium=referral) on [Unsplash](https://unsplash.com?utm_source=medium&utm_medium=referral)

## 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?*

 1. MySQL

 2. Oracle

 3. MongoDB

 4. Microsoft Access

 5. Something else

The answer is, of course, “something else”, namely SQLite. According to their [homepage](https://www.sqlite.org/index.html), there are over 1 *trillion* (1e12) SQLite databases in active use!

## What is SQLite?

[SQLite](https://sqlite.org/about.html) 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](https://sqlite.org/selfcontained.html) 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](https://sqlite.org/serverless.html), 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](https://sqlite.org/zeroconf.html) 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](https://sqlite.org/transactional.html) database in which all operations appear to be atomic, consistent, isolated, and durable ([ACID](http://en.wikipedia.org/wiki/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](https://sqlite.org/copyright.html), [small](https://www.sqlite.org/footprint.html) (about 600 KB), [fast](https://www.sqlite.org/fasterthanfs.html) (SQLite can be faster than your filesystem), [reliable](https://www.sqlite.org/hirely.html), cross-platform, and offers a full-fledged flavour of the [SQL](https://www.sqlite.org/fullsql.html) language… No wonder it is indeed the most widely deployed database in the world, with [famous users](https://sqlite.org/famous.html) 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](https://www.postgresql.org/), 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](https://sqlite.org/locrsf.html) by the US Library of Congress and a popular and excellent choice as an [Application File Format](https://sqlite.org/appfileformat.html). 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](http://www.djangoproject.com/), 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](https://sqlitebrowser.org/) and [Sqlite Viewer](https://www.sqliteviewer.com/).

![DB Browser](https://cdn-images-1.medium.com/max/2078/1*ZQpPjjk8OpbR-Nas0rrttA.png)

### 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:
{% gist https://gist.github.com/TomDeneire/3986044635d898e2dfc8647efedbdcdc.js %}
The output of this program is [(2,'bananas')] .

In Go, you need to [install](https://github.com/mattn/go-sqlite3) the go-sqlite3 package first. After that, it’s pretty straightforward to use as well:
{% gist https://gist.github.com/TomDeneire/2c8797664298decf01662909439a0d40.js %}
## More from the SQLite team

This seems like a good point to conclude our introduction of SQLite. However, a lot still remains to be said about this handy technology!

Moreover, the SQLite [team](https://sqlite.org/crew.html) (lead by the brilliant [D. Richard Hipp](https://en.wikipedia.org/wiki/D._Richard_Hipp)) are responsible for many more interesting tools. For instance:

* [Fossil](http://www.fossil-scm.org/) is a distributed version control system designed specifically to support SQLite development, but very usable as an alternative to git. Fossil uses SQLite as for storage.

* [SQLite Archiver](http://www.sqlite.org/sqlar/) is a ZIP-like archive program that uses SQLite for storage. It allows users to archive files and to include any number of database tables with data that might be relevant for the archive.

So next time when you need to store some data for an application, why not consider SQLite?

---
*Hi! 👋 I’m Tom. I’m a software engineer, a technical writer and IT burnout coach. If you want to get in touch, check out [https://tomdeneire.github.io](https://tomdeneire.github.io/)*