Why you should use SQLite

Lift the hood on most business applications, and you’ll find they have 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 a business application needs a database. In many cases, an embedded database will do. Embedded databases are lightweight, compact, and portable—and for some applications, they are a better choice than a traditional server.

SQLite is an embeddable open source database, written in C and queryable with conventional SQL. SQLite is designed to be fast, portable, and reliable, whether you’re storing only kilobytes of data or multi-gigabyte blobs. We’ll take a look at SQLite, including where and when to use it and how it compares to alternatives such as MySQL, MariaDB, and other popular embedded databases.

What is SQLite used for?

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 also has other features found in higher-end databases, such as full-text indexing, and support for large databases—up to 281 terabytes with row sizes up to 1GB.

SQLite also provides a fast and powerful way to store configuration data for a program. Instead of parsing a file format like JSON or 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. It also natively supports JSON data, so data can be stored as JSON or queried in-place.

Advantages of SQLite

SQLite has many advantages, starting with its platform and language portability. Here are the main benefits of using SQLite:

• It’s cross-platform: 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 your app’s deployment target is, odds are there’s an edition of SQLite available for it, or a way to port the C source code to that target.
• It’s compatible with most programming languages: Applications 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—you can simply drop them into the same directory as your application.
• Yes, SQLite works with Python: 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).
• Self-contained: Because SQLite is a single standalone binary, it’s easy to deploy with an app and then move with the app as needed. Each database created by SQLite also comprises a single file, which can be compacted or optimized using SQL commands.
• Third-party extensions: Third-party binary extensions for SQLite add even more functionality. SQLCipher adds 256-bit AES encryption to SQLite database files. Another, sqlean, expands SQLite’s native functions to include many more not available by default, such as generating UUIDs or regular expression matching.
• Extensive tooling: 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 options.
• SQLite is open source: Finally, the source code for SQLite is public domain, so it can be reused in other programs with no practical restrictions.

SQLite vs. MySQL

SQLite is frequently compared to MySQL, the widely used open source database product that is a staple of today’s application stacks. As much as SQLite resembles MySQL, there are good reasons to favor one over the other, depending on the use case. The same is true for MariaDB, another popular database that is sometimes compared to SQLite.

Data types

SQLite has relatively few native data types—BLOB, NULL, INTEGER, REAL, and TEXT. Both MySQL and MariaDB, on the other hand, have 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 its internal or command-line flags deal with edge cases or backward compatibility. This fits with SQLite’s overall philosophy of simplicity: the default options are well-suited to most common use cases.

MySQL and MariaDB offer a veritable forest of database- and installation-specific configuration options—collations, indexing, performance tuning, storage engines, etc. The plethora of options is because these database products offer far more features. You may have to tweak them 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 in desktop or mobile apps. MySQL and MariaDB are designed to handle multiple concurrent users. They can also provide clustered and scale-out solutions, whereas SQLite can’t.

Some projects add scaling features to SQLite, although not as a direct substitute for MySQL or MariaDB. Canonical has created its own variant of SQLite, dqlite, designed to scale out across a cluster. Data is kept consistent across nodes by way of a Raft algorithm, and deploying dqlite has only marginally more administrative overhead than SQLite.

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 Apache 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) that is also able to support desktop environments like Windows. Realm is object-based, however, and doesn’t use SQL queries—good if you’d rather not use SQL, but bad if SQL is familiar and comfortable. Realm is now a MongoDB project, and comes with the caveat that it is “not in itself an ‘end-user’ product with a publicly stable and supported API.”
• 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. Berkeley DB is dual-licensed, under either the GNU Affero GPL 3 or via a commercial license, depending on your use case.

Limitations of 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—various 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. As noted above, some third parties have extended SQLite to add those features, but they’re not native to SQLite’s design.
• 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 the application must enforce most types. If you want the database, as opposed to the application, to normalize and constrain inputs for datetime values, SQLite may not work for you.