By Serdar Yegulalp
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:
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.
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:
