SQLite – The World’s Most Prevalent RDBMS – Part 1

Warning: Illegal string offset 'filter' in /var/sites/t/theproactiveprogrammer.com/public_html/wp-includes/taxonomy.php on line 1409

Most commercial software applications developed today use a Relational Database Management System (RDBMS) to store and manage data. If you are a programmer and have worked with databases in a commercial environment, you have probably worked with one or more of Microsoft Sql Server, Oracle and MySql. These are the three big players in the database engine space. However, there are other interesting options for storing data, one of which is SQLite. Now SQLite may not be a suitable database choice for your current project, however it is certainly an interesting piece of technology which all developers should be aware of, particularly as SQLite may well be the most widely deployed database system in the world.

To name a few famous users, SQLite is used in the Dropbox desktop application, Android, Google Chrome and on the iPhone.

What is SQLite?

As you may have noticed, I like to begin an exploration of any technology with a definition from its creators. The reason for this is that organisations put a lot of thought into these definitions and in doing so tell us what they most value about their product. So here it is, from sqlite.org:

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

Let’s look at this definition in more detail.

SQLite is Self-Contained

Unlike the more well-known database engines, SQLite is self-contained. This means it doesn’t rely upon any particular framework or operating system. It is written in C and therefore can run on any Windows, Unix or Mac system.

This characteristic makes SQLite a popular choice for embedded devices, but as we will see it is also a good choice in many other scenarios.

SQLite is Serverless

SQLite does not employ a traditional client-server architecture. In a SQLite application, the ‘client’ process that wants to access the database is also the ‘server’ process which reads and writes data. The likes of SQL Server and Oracle host a dedicated database process which listens for requests from client processes. With SQLite there is just the one process. This simplifies the installation, configuration and administration of an application. Despite the fact that SQLite doesn’t use a client-server architecture, it is still possible for multiple applications to access the same database at the same time. This is because a SQLite database exists simply as a file on disk. If your application can access the hard disk, then it can access a SQLite database.

SQLite is Zero-Configuration

If you are working on a client-server RDBMS application, you need to install and configure the database engine somewhere. This is often a complex procedure, resulting in a resource-intensive system. Because SQLite is self-contained and serverless however, there is no need to separately install and set up a database engine.

SQLite is Transactional

Truly useful database engines need to be transactional, and SQLite meets this requirement.

A database transaction is any unit of work performed on a database. This might be an update, a delete or an insert, for example.

A transactional database engine ensures that transactions are atomic, consistentisolated and durable (according to the ACID acronym).

An atomic transaction is “all or nothing”. Either the whole operation completes successfully, or not at all (if something goes wrong, it is rolled back).

A consistent transaction is one which adheres to the “rules” of the database, such as constraints, triggers and cascades.

SQLite implements the highest level of isolation (serializable), which means that dirty reads can’t happen. Read and write locks are acquired prior to writing to the database.

durable transaction is one which keeps its promises in terms of persisting data permanently.

That SQLite is transactional is crucial to its suitability for applications of any significant complexity and value.

The Limitations of SQLite

There are of course scenarios which are inappropriate for the use of SQLite.

Because it is not built on a client-server architecture, it is not suitable for use in situations where multiple ‘client’ applications need to access the database over a network. Under these circumstances, the file locking protocol of the host operating system will be used to manage access to the database file, and this is far less reliable than the client-server architecture model.

Large-scale applications are also generally not a good fit for SQLite. In other words, if your application requires a high volume of concurrent database writes, or a large amount of data, then a client-server database is a more practical choice. One reason for this is that SQLite implements a serializable level of transaction isolation, as mentioned earlier. This means that a queue can build up if many concurrent write requests are made. Furthermore, a SQLite database exists as a single file, so can’t be spread across multiple servers.

SQLite does support databases of up to 140 terabytes, however many operating systems limit the size of file to something smaller than this.

When to Use SQLite

There are many scenarios when SQLite should be considered. The following list of appropriate uses is taken directly from the SQLite website:

  • Embedded devices and the Internet of Things
  • Application File Format (e.g. desktop applications)
  • Websites (low to medium traffic – sqlite.org uses a sqlite database)
  • Data Analysis (import data easily from a CSV, and easily deploy anywhere and transfer to different environments)
  • Cache for Enterprise Data
  • Server-Side Database
  • File Archives (see the SQLite Archiver)
  • Replacement for Ad Hoc Disk Files
  • Internal or Temporary Databases
  • Stand-in for an enterprise database during demos or testing (again due ease of deployment and portability)
  • Education and Training (i.e. for learning SQL)
  • Experimental SQL language extensions

Working with a SQLite Database

Mozilla is part of the SQLite Consortium which sponsors the ongoing development of SQLite, and it is used in Firefox. One great upshot of this is the SQLite Manager add-on for Firefox.

SQLite Manager is an IDE for working with a SQLite database. From within it you can create and manipulate database objects, view data, and run SQL scripts. It is a lightweight and effective tool, and in next week’s post we will use it to create and work with an example SQLite database.

Share Button