SQLite is a relational database compatible with SQL. Unlike other SQL-based systems such as MySQL and PostgreSQL, SQLite does not use a client-server architecture. The whole program is contained in a C library, which is integrated into the applications. The database becomes an integral part of the program, eliminating resource-intensive stand-alone processes.
SQLite stores its data in a single cross-platform file. Since there is no dedicated server or specialized file system, “deploying” SQLite is as easy as linking its library and creating a new regular file.
This simplicity has led to the massive adoption of SQLite as the database system of choice for applications and embedded devices. This is thought the total number SQLite deployments exceed that of any other database engine combinedbecause it comes bundled with all major operating systems, most programming languages, an extensive list of on-board hardware, and many major software products.
Benefits of SQLite
SQLite is focused on providing a powerful SQL compatible database with no overheads or dependencies. As the name suggests, this is a lightweight solution that can run on almost anything that supports C and persistent file storage. Bindings are available for the most popular high level programming languages.
As SQLite databases are simple files, they are highly portable and easy to back up. The lack of a server component makes SQLite much easier to configure, even if you are not in a full-fledged development environment. There is no lengthy process to monitor, restart, or inspect 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 all 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 an operation in a transaction fails, all successful operations are also rolled back, returning the database to its original state.
SQLite is also resilient to storage errors and out of memory scenarios. Databases can recover from total system outages and power outages, which helps protect data. The code base is covered by a remarkably ubiquitous test suite with 100% coverage.
SQLite supports most of the features of the SQL92 standard language. There are discrepancies and engine specific quirks, although this is also true for all other major SQL database engines. However, there are some limitations specific to SQLite that should be taken into account.
SQLite takes a loose approach to handling data types, which goes against the strong typing of other engines. It does not preclude inserting invalid values, so you can write “sqlite” (a
string) in one
integer column. Data types are extremely flexible and sometimes unpredictable, especially if you are coming from another database system.
SQLite also lacks support for some data types. There’s no
DATETIME, so text or integer values should be used instead. These obstacles can cause headaches if you ever move away from SQLite for an alternative platform. Your database may contain invalid SQL values that will not be accepted elsewhere.
The flat file design restricts the use cases to which SQLite is applicable. You cannot realistically scale or distribute a database because all connections write to the same underlying file. Concurrent writes are not possible because the database is locked for each. This reduces performance in write-heavy scenarios because operations pile up in a write queue.
SQLite also does not support multiple users. In database engines such as MySQL and PostgreSQL, you can create user accounts in the database under which clients connect. This helps you restrict access to specific patterns and operations.
User management at the database level is less applicable to SQLite because each schema is stored as a file on standard disk. SQLite databases are not natively accessible over the network, so separate user accounts are less relevant. It makes more sense to use operating system permissions to restrict read and write permissions instead. Nonetheless, the lack of users remains a concern for applications working with sensitive data, where multiple isolated database accounts could strengthen protections.
When to use SQLite?
SQLite works best when you want to combine the robust querying and storage capabilities of SQL with the ease of use of conventional file system access. In these scenarios, it provides increased performance and resiliency compared to normal reads and writes.
The system works well in environments where end users should never be aware of the existence of the database. SQLite does not require any maintenance or administration, which makes it ideal for mobile applications and IoT devices. A client-server database engine can cause problems if the server goes down or there is a network problem.
SQLite also works well as a backend for server-side applications and websites. As long as the system is biased towards reads and not writes, SQLite performance can match or even exceed that of other database engines. It runs a process without network exchanges, thus eliminating traditional database overheads.
You should not use SQLite if you are processing very large amounts of data. Although the hard limit for the database is 281 TB, in practice, datasets larger than 1 GB should be more suited to server-based technology. The lack of concurrent write support also precludes the use of SQLite for rapidly changing datasets handled by multiple clients.
SQLite is an SQL compatible database engine that stores everything in a single physical file. There is no server to run, so SQLite can be compiled into your own applications. It’s free, open source, and released in the public domain, so you don’t have to worry about licensing.
The simplicity, portability and reliability of the engine 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 works invisibly for end users.
SQLite has grown through the increased use of mobile devices and IoT products. Adoption and awareness is expected to expand further as these technologies continue to expand their reach. The key to SQLite’s success has been its universal compatibility: if you have the SQLite library and storage space, you can use the power of SQL without any other dependencies.