SQLite is a relational database that’s compatible with SQL. Unlike other SQL-based systems such as MySQL and PostgreSQL, SQLite doesn’t use a client-server architecture. The whole program is contained in a C library, which gets embedded into applications. The database becomes an integral part of the program, eliminating resource-intensive standalone processes.
SQLite stores its data in a single cross-platform file. As there’s no dedicated server or specialized filesystem, “deploying” SQLite is as simple as linking its library and creating a new regular file.
This simplicity has resulted in massive adoption of SQLite as the database system of choice for applications and embedded devices. It’s thought that the total count of SQLite deployments exceeds that of every other database engine combined, as it’s bundled with all major operating systems, most programming languages, a vast list of embedded hardware, and many major software products.
SQLite’s Benefits
SQLite focuses on providing a powerful SQL-compatible database without overheads or dependencies. As the name implies, it’s a lightweight solution that can run on almost anything that supports C and persistent file storage. Bindings are available for most popular high-level programming languages.
As SQLite databases are plain files, they’re highly portable and easy to back up. The absence of a server component makes SQLite much simpler to set up, too, even if you’re not in a fully fledged development environment. There’s no long-lived process to monitor, restart, or inspect for security issues.
Applications using SQLite benefit from increased resiliency and reduced development time. Using an SQLite database instead of text files for configuration and storage unifies data access across devices and helps maintain consistent performance.
Databases come with protections against corruption that you don’t get from regular files. SQLite is atomic and transactional, so you can avoid partial success events. If one operation in a transaction fails, any successful ones are reverted as well, putting the database back into its original state.
SQLite is also resilient to storage errors and out-of-memory scenarios. Databases can recover from total system crashes and power outages, helping to keep data safe. The codebase is covered by a remarkably pervasive test suite with 100% coverage.
Limitations
SQLite offers support for most of the SQL92 standard language features. There are some discrepancies and engine-specific quirks, although this is also true of all other leading SQL database engines. With that said, there are some SQLite-specific limitations that are worth bearing in mind.
SQLite takes a loose approach to datatype handling, which goes against the strong typing of other engines. It doesn’t object to the insertion of invalid values, so you could write “sqlite” (a string) into an integer column. Datatypes are extremely flexible and sometimes unpredictable, especially if you’re coming from another database system.
SQLite also lacks support for some datatypes altogether. There’s no BOOLEAN or DATETIME, so text or integer values must be used instead. These hindrances can cause headaches if you ever move away from SQLite to an alternative platform. Your database could contain invalid SQL values that won’t be accepted elsewhere.
The flat-file design restricts the use cases that SQLite is applicable to. You can’t realistically scale or distribute a database, as all connections write to the same underlying file. Simultaneous writes are impossible, as the database is locked for each one. This reduces performance in write-heavy scenarios, as operations stack up into a write queue.
SQLite doesn’t support multiple users, either. In database engines such as MySQL and PostgreSQL, you can create in-database user accounts that clients connect as. This helps you restrict access to specific schemas and operations.
Database-level user management is less applicable to SQLite, as each schema is stored as a regular on-disk file. SQLite databases can’t be natively accessed over the network, so distinct user accounts hold less relevance. It makes more sense to use operating system permissions to restrict read and write permissions instead. Nonetheless, the absence of users is still a concern for applications working with sensitive data, where multiple isolated database accounts could reinforce protections.
When to Use SQLite?
SQLite works best when you want to combine SQL’s robust querying and storage abilities with the ease of use of conventional filesystem access. In these scenarios, it offers increased performance and resiliency compared to regular reads and writes.
The system works well in environments where end users should never be aware of the database’s existence. SQLite requires no maintenance or administration, which makes it ideal for mobile apps and IoT devices. A client-server database engine could pose problems if the server shut down or if there was a networking issue.
SQLite also works well as the backend for server-side applications and websites. Provided that the system is skewed toward reads and not writes, SQLite’s performance can match or even exceed that of other database engines. It runs one process with no network exchanges, eliminating traditional database overheads.
You shouldn’t use SQLite if you’re handling very large amounts of data. Although the hard database limit is 281 TB, in practice, datasets above 1 GB are expected to be more suited to a server-based technology. The lack of concurrent write support also rules out using SQLite for rapidly changing datasets manipulated by multiple clients.
Summary
SQLite is an SQL-compatible database engine that stores everything in a single physical file. There’s no server to run, so SQLite can be compiled into your own applications. It’s free, open-source, and published in the public domain, so you don’t need to worry about licensing.
The engine’s simplicity, portability, and reliability have made it the storage system of choice for modern operating systems and embedded platforms. It consumes almost no resources, is ubiquitous and easy to use for developers, and runs invisibly to end users.
SQLite has grown on the back of the increased use of mobile devices and IoT products. Adoption and awareness should expand further as these technologies continue to broaden their reach. The key to SQLite’s success has been its universal compatibility—if you have the SQLite library and some storage, you can use the power of SQL without any other dependencies.
