Why you should use SQLite

Lift the hood on most any business application, and you’ll reveal some way to store and use structured data. Whether it’s a client-side app, an app with a web front-end, or an edge-device app, chances are it needs an embedded database of some kind.

SQLite is an embeddable open source database, written in C and queryable with conventional SQL, that is designed to cover those use cases and more. SQLite is designed to be fast, portable, and reliable, whether you’re storing only kilobytes of data or multi-gigabyte blobs.

Where you can use SQLite

One of SQLite’s greatest advantages is that it can run nearly anywhere. SQLite has been ported to a wide variety of platforms: Windows, MacOS, Linux, iOS, Android, and more. Windows users in particular can use precompiled binaries for regular Win32, UWP, WinRT, and .Net. Whatever the deployment target is for your app, odds are there’s an edition of SQLite available for it, or a way to port the C source code to that target.

Apps that use SQLite don’t have to be written in any particular language, as long as there is some way to bind and work with external libraries written in C. SQLite’s binaries are self-contained, so they require no particular magic to deploy—they can simply be dropped into the same directory as the application.

Many languages have high-level bindings for SQLite as a library, and can use that in conjunction with other database access layers for the language. Python, for instance, bundles the SQLite library as a standard-issue element with the stock version of the Python interpreter. In addition, third parties have written a wide variety of ORMs and data layers that use SQLite, so you’re not stuck accessing SQLite via raw SQL strings (which is not only clumsy but also potentially dangerous).

Finally, the source code for SQLite is public domain, so it can be reused in other programs with no practical restrictions.

SQLite advantages

The most common and obvious use case for SQLite is serving as a conventional, table-oriented relational database. SQLite supports transactions and atomic behaviors, so a program crash or even a power outage won’t leave you with a corrupted database.

SQLite has features found in higher-end databases such as full-text indexing and support for JSON data. Application data typically stuffed into semi-structured formats like YAML or XML can be stored as SQLite tables, allowing the data to be accessed more easily and processed more quickly.

SQLite also provides a fast and powerful way to store configuration data for a program. Instead of parsing a file format like YAML, a developer can use SQLite as an interface to those files—often far faster than operating on them manually. SQLite can work with in-memory data or external files (e.g., CSV files) as if they were native database tables, providing a handy way to query that data.

Because SQLite is a single standalone binary, it’s easy to deploy with an app and move it with the app as needed. Each database created by SQLite also comprises a single file, which can be compacted or optimized by way of SQL commands.

Third-party binary extensions for SQLite add even more functionality. SQLCipher adds 256-bit AES encryption to SQLite database files. Another, SQLite-Bloomfilter, allows you to create bloom filters from data in a given field.

Many other third-party projects provide additional tooling for SQLite, such as the Visual Studio Code extension that allows browsing databases from within Visual Studio Code, or the LiteCLI interactive command-line for SQLite. A curated list of SQLite resources on GitHub includes many more.

SQLite vs. MySQL 

SQLite is most commonly compared with MySQL (or MariaDB)—the widely used open source database product that is a staple of today’s application stacks. As much as SQLite might resemble MySQL, there is much to set these two databases apart—and good reasons to favor one over the other, depending on the use case.

Data types

SQLite has relatively few data types—BLOB, NULL, INTEGER, and TEXT. MySQL (or MariaDB), on the other hand, has dedicated data types for dates and times, various precisions of integers and floats, and much more.

If you’re storing relatively few data types, or you want to use your data layer to perform validation on the data, SQLite is useful. However, if you want your data layer to provide its own validation and normalization, go with MySQL (or MariaDB).

Configuration and tuning

SQLite’s configuration and tuning options are minimal. Most of the internal or command-line flags for SQLite deal with edge cases or backward compatibility. This fits with SQLite’s overall philosophy of simplicity: Make the default options well-suited to most common use cases.

MySQL (or MariaDB) has a veritable forest of database- and installation-specific configuration options—collations, indexing, performance tuning, etc. This wealth of options is the result of MySQL offering far more features. You may have to tweak MySQL more, but it’s likely because you’re trying to do more in the first place.

Single-user vs. multi-user database

SQLite is best suited for applications with a single concurrent user, such as a desktop or mobile app. MySQL and MariaDB are designed to handle multiple concurrent users. MySQL and MariaDB can also provide clustered and scale-out solutions, whereas SQLite can’t.

SQLite vs. embedded databases

SQLite is far from the only embeddable database. Many others deliver similar features, but emphasize different use cases or deployment models.

• Apache Derby: An embeddable SQL engine, also repackaged by Oracle as Java DB. Since Derby is written in Java and requires the JVM, it’s mainly designed for embedding in Java apps.
• Firebird Embedded: The Firebird database, which runs cross-platform and sports many high-end features, is available as a library that can be embedded in a client application. Its feature set compares well to SQLite, but SQLite has a far larger user community and support base.
• Realm: A high-performance relational database designed for mobile environments, mainly Android, but can also support desktop environments like Windows. However, Realm is object-based, and doesn’t use SQL queries—good if you’d rather not use SQL, but bad if SQL is familiar and comfortable.
• VistaDB: An embedded database for the .Net runtime. VistaDB is available in versions specific to the various flavors and incarnations of .Net and with many enterprise features like full-database encryption. However, it’s a commercial product, not open source.
• Berkeley DB: An Oracle project, nominally a key/value store, but one that uses SQLite in recent editions as a way to handle SQL queries. Berkeley DB’s underlying database engine has performance enhancements that SQLite can’t match, such as being able to handle multiple simultaneous write operations.

When not to use SQLite

SQLite’s design choices make it well-suited for some scenarios but poorly suited for others. Here are some places where SQLite doesn’t work well:

• Apps that use features SQLite doesn’t support. SQLite does not support, and in many cases will not try to support, a number of relational database features. Many are corner cases, but even one of those can break the deal.
• Apps that require scale-out designs. SQLite instances are singular and independent, with no native synchronization between them. They can’t be federated together or made into a cluster. Any software application that uses a scale-out design can’t use SQLite.
• Apps with simultaneous write operations from multiple connections. SQLite locks the database for write operations, so anything involving multiple simultaneous write operations could result in performance issues. Apps with multiple simultaneous reads are generally fast, though. SQLite 3.7.0 and higher provide Write-Ahead Logging mode to make multiple writes work faster, but it comes with some limitations. For an alternative, considered Berkeley DB, mentioned above.
• Apps that need strong data typing. SQLite has relatively few data types—no native datetime type, for instance. This means that enforcing those types will need to be handled by the application. If you want the database, as opposed to the application, to normalize and constrain inputs for datetime values, SQLite may not work for you.