SQLite: The Universal SQL Database Engine

By Steven J. Vaughan-Nichols

Hidden away in code, SQLite brings database power to many programs.

Today’s quiz is: What’s the most popular database of all? MySQL? Nope. Oracle Database? Nah. Microsoft SQL Server? Try again. IBM DB/2? Wrong. Arguably the most popular database of all is SQLite.

Never even heard of it? Find that hard to believe? SQLite’s inside every smartphone, macOS and Windows; and the Chrome, Firefox, and Opera web browser. The reason you haven’t heard of it is because it’s hidden inside the code of numerous operating systems and programs. There, the SQLite C library provides database services for applications.

This public-domain — not open-source — software is not a database server. Instead, embedded within programs, it provides standard SQL-92 database management server (DBMS) servers without the server part.

While that may sound like an oxymoron, it really is an atomicity, consistency, isolation, and durability (ACID) compliant database. SQLite uses PostgresSQL as its reference platform.

D. Richard Hipp created in 2000 SQLite while working as a US Navy contractor. His goal was to create a library that could deliver database functionality without a server or a database administrator. He was successful, which is why SQLite is so omnipresent today.

Technically, SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It’s a zero-conf database. In other words, the user doesn’t need to do anything to set up the database or interact with it. All that must be taken care of by the developer.

As you’d expect from a C library, SQLite is a small library. With all features enabled, the SQLite library size can fit in under 500KB of RAM. If optional features are omitted, SQLite can be fit in under 300KB. SQLite can also be made to run in minimal stack space (4KB) and minimal heap (100KB). This makes SQLite popular for memory-constrained devices such as smartphones.

As with any database, there’re tradeoffs between memory usage and speed. SQLite runs faster with more memory, but even in minimal memory environments it performs well.

Of course, you don’t get everything with SQLite that you get with a full-scale DBMS. For example, SQLite stores all its definitions, tables, indices, and the data in a single database, which, in turn is held within a single file.

In addition, when the database is edited, there is no record locking. Instead the database file is locked during writing. Thus, while you can have multiple simultaneous reads of a SQLite database, writes must be performed one at a time sequentially. Starting with version 3.7, SQLite can use write-ahead logging in some, but not all, situations for simultaneous reads and writes.

So with all that in mind, when should you use SQLite and when should you avoid it?

According to SQLite’s developers, the library works well with embedded and Internet of Things devices, desktop applications, and low to medium traffic websites. Where it doesn’t work well is with client-server applications, high-volume websites, very large data-sets, or high-concurrency applications.

The bottom line is that for many programs SQLite gives you all the database power you need without any of the headaches of incorporating a full-powered DBMS into your application. It’s simple, lightweight, and fast. Consider trying it for your next project. I think you’ll be pleased with the results.

Please feel free to share below any comments or insights about your experience with or questions about using SQLlite. And if you found this blog useful, please consider sharing it through social media.

About the blogger: Steven J. Vaughan-Nichols is a veteran IT journalist whose estimable work can be found on a host of channels, including ZDNet.com, PC Magazine, InfoWorld, ComputerWorld, Linux Today and eWEEK. Steven’s IT expertise comes without parallel — he has even been a Jeopardy! clue. And while his views and cloud situations are solely his and don’t necessarily reflect those of Linode, we are grateful for his contributions. He can be followed on Twitter (@sjvn).