SQLite the only database you will ever need in most cases

Published on 2021-04-14. Modified on 2023-02-16.

The name SQLite is a nice name, but the "lite" part is misleading, it sounds like it is only useful for tiny things - which is very wrong. SQLite should be named AwesomeSQL, because that is what it is. SQLite is probably the only database you will ever need in most cases.

SQLite is a relational database management system contained in a C library. It is ACID-compliant and implements most of the SQL standard.

It is a popular choice as embedded database software for storage in application software, such as web browsers and mobile phones, and it is the most widely deployed database engine in the world. However, contrary to popular belief SQLite isn't only useful in embedded devices. Whenever you build a website that doesn't need to scale to several machines, then you will in most cases be fine using only SQLite, and not only that, but you will find that your job has just become much easier with SQLite.

In contrast to many other database management systems, SQLite is not a client-server database engine, but you actually very rarely need that. If your website runs on the same physical machine as the database, which is what most small to medium sized websites does, then you probably only need SQLite.

Several processes or threads may access the same database concurrently and read accesses can be run in parallel. Because the database consists of a single file, a write access can only be satisfied if no other accesses are currently writing to the database. However, with a configurable busy timeout set and with write-ahead logging (WAL) enabled, SQLite enables concurrent reads and writes. This means that writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few milliseconds.

SQLite is very fast and it is very well documented.

Even if you start out small and later need to upscale, as long as your web application can run on the same machine as the database, which it can in 99% of the time, you can just upgrade the hardware to a beefier machine and keep business as usual.

The only time you need to consider a client-server setup is:

Still, I have run SQLite as a website database with thousands concurrent writes every second, coming from different HTTP requests, without any delays or issues. This is because even on a very busy site, the hardware is extremely fast and fully capable of handling that.

If you setup SQLite correctly, then in about 99% of the time you actually only need that.

But, what exactly is so great about SQLite, you might ask?

Well, first of all, all database administration tasks becomes much easier. You don't need any database account administration, the database is just a single file. Furthermore, you don't need a separate database process running that you need to monitor. If your application is up, then your database is also up!

You can split up your database into multiple separate databases, and you can even give each user his own database on the server. Depending on the application type, you can have a single database that contains all user account information, such as username and password, and you use that for login sessions. Then when the login has been confirmed, you use another personal database for the work the client is doing. Should the client ever need a copy of his database, simply copy the file and ship it.

Please note that it is only safe to make a copy of an SQLite database file as long as there are no transactions in progress by any processes. If the previous transaction failed, then it is important that any rollback journal (the "*-journal" file) or write-ahead log (the "*-wal" file) be copied together with the database file itself. The recommended way to make a backup is to use the Online Backup API.

Take a look at the Command Line Shell For SQLite to see some of the really cool stuff you can do with SQLite.

Differences you need to be aware of

There are two things you need to pay special attention to when you come from one of the popular client-server based databases and that is that the AUTOINCREMENT command is different, and also that SQLite uses a dynamically and weakly typed SQL syntax that does not guarantee the domain integrity.

The AUTOINCREMENT command in SQLite means that on an INSERT, if a INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ids in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used. However, if the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, then that changes the assignment algorithm to prevent the reuse of ids over the lifetime of the database. This means that the purpose of AUTOINCREMENT in SQLite is to prevent the reuse of ids from previously deleted rows.

Dynamic and weakly types SQL syntax means that you can insert a string into a column defined as an integer. SQLite will attempt to convert data between formats where appropriate, the string "123" into an integer in this case, but it does not guarantee such conversions and will store the data as-is if such a conversion is not possible. This is not a bug, but it is considered a feature (see under "Manifest typing").

Useful settings

You can list all supported PRAGMA keys for setup with:

PRAGMA pragma_list;

The settings below are some of the settings I use when I deploy SQLite on a web application. Many other relevant settings exist and your millage may vary.

PRAGMA journal_mode = wal;

WAL and busy_timeout are the most important setting when working with a web application.

The busy_timeout is associated with each connection to the database and as such you need to set the timeout for each connection.

If e.g. you're using the native SQLite driver in PHP, you need to set the following for each connection:

$sqlite_db->busyTimeout(30000);

Where the number in the parentheses is the timeout in milliseconds, in this case 30 seconds.

If you're using PHP PDO instead, then you need to set it like this:

$pdo = new PDO('sqlite:my_database.sqlite', '', '',
    array(
        PDO::ATTR_TIMEOUT => '30000',
        ...
    )
);

Other options that might be relevant, depending on your specific needs are PRAGMA temp_store and PRAGMA synchronous

Be sure you know and understand what each option means.

Final notes

SQLite is one of those projects that I wish I had known about long before I did. I had heard about it, but for many years I never thought about taking a serious look at it because I was under the false impression that is was a tiny database only useful for personal address books and small embedded devices.

I highly recommend you take a look at SQLite!

Relevant reading