Developers
September 10, 2020

SQLite: The Embedded Database Workhorse

SQLite is the most popular database on the planet, even if it doesn’t get the name recognition others do.

SQLite is the most widely used database in the world. Every day, billions of people use SQLite, many without even realizing it.

Why is SQLite so popular? How does it work? When should you use it as a developer?

The History of SQLite

SQLite was designed by D. Richard Hipp in 2000. Hipp was working on a General Dynamics contract for the US Navy and wanted a simple database that did not require the overhead, configuration and administration that comes with a traditional client-server database.

As a result, “SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.” It requires no configuration, no database administrator and no database management system.

This has led to SQLite’s adoption in operating systems, mobile phones, web browsers and countless applications, significantly adding to its overall usage. Anyone who has used an iPhone or Android device has used SQLite to some degree or another.

Benefits of SQLite

Because of its serverless approach, there are a number of benefits to SQLite.

Portability is one of the biggest advantages. Because it does not rely on the traditional client-server relationship that defines its competitors, SQLite databases are extremely portable from one platform to another.

Its lightweight nature make SQLite a speed demon when performing many tasks. In fact, it can be significantly faster than traditional file storage. Reading and writing small blobs, such as thumbnail images, can be as much as 35% faster than reading or writing directly to the filesystem. Just as impressive, an SQLite database containing this kind of data can take as much as 20% less space than the same files saved directly to the filesystem.

That performance also applies to how the database reads data. SQLite databases are stored in a single file. Rather than reading the entire file into memory, however, SQLite only loads the data that is needed. This significantly reduces memory requirements, especially with large databases. Given that an SQLite database can be 140 terabytes, this is an important feature for performance and stability.

SQLite is also a transactional database. SQLite’s developers describe it this way:

”A transactional database is one in which all changes and queries appear to be Atomic, Consistent, Isolated, and Durable (ACID). SQLite implements serializable transactions that are atomic, consistent, isolated, and durable, even if the transaction is interrupted by a program crash, an operating system crash, or a power failure to the computer.

“We here restate and amplify the previous sentence for emphasis: All changes within a single transaction in SQLite either occur completely or not at all, even if the act of writing the change out to the disk is interrupted by

•      a program crash,

•      an operating system crash, or

•      a power failure.

”The claim of the previous paragraph is extensively checked in the SQLite regression test suite using a special test harness that simulates the effects on a database file of operating system crashes and power failures.”

In addition, small edits to the data only result in that portion of the database file being overwritten, rather than the entire file. This not only improves reliability, but also reduces hard drive wear.

Because of this inherent reliability, not to mention the portability of the file format, SQLite is one of only four formats recommended by the Library of Congress for long-term storage, the others being XML, JSON, and CSV.

Disadvantages of SQLite

Despite its many advantages, there are a few disadvantages to SQLite.

One of the biggest disadvantages is how the database performs write locks. Although SQLite can handle multiple read connections, it can only handle one write at a time. As a result, SQLite locks the entire database file any time a write is being performed. While this level of concurrency may seem rudimentary compared to client-server databases, it is still much better than many other embedded databases, as the developers point out:

”We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once.”

Another disadvantage is SQLite’s network performance. Compared with other databases, it is rather slow and best used for a local embedded application.

SQLite’s journaling can be another disadvantage. Journaling is one of the ways the database protects against corruption, such as during a power interruption. But a roll back journal can pose a security risk. To avoid this, it’s best to encrypt the database when it’s being used for sensitive information. One option is the SQLCipher extension that can be used to handle database encryption.

Conclusion

SQLite has gained widespread use for good reason. Few embedded databases can offer its combination of features and advantages. This makes it the ideal solution for its target market. While SQLite does have a few disadvantages, these are few and far between and, in most cases, can be worked around.

Whether you’re a new or experienced developer, SQLite is a tool that should have a permanent place in your development toolbox.

TagsSQLiteDatabasesDeveloper
Matt Milano
Technical Writer
Matt is a tech journalist and writer with a background in web and software development.

Related Articles

Back
DevelopersSeptember 10, 2020
SQLite: The Embedded Database Workhorse
SQLite is the most popular database on the planet, even if it doesn’t get the name recognition others do.

SQLite is the most widely used database in the world. Every day, billions of people use SQLite, many without even realizing it.

Why is SQLite so popular? How does it work? When should you use it as a developer?

The History of SQLite

SQLite was designed by D. Richard Hipp in 2000. Hipp was working on a General Dynamics contract for the US Navy and wanted a simple database that did not require the overhead, configuration and administration that comes with a traditional client-server database.

As a result, “SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.” It requires no configuration, no database administrator and no database management system.

This has led to SQLite’s adoption in operating systems, mobile phones, web browsers and countless applications, significantly adding to its overall usage. Anyone who has used an iPhone or Android device has used SQLite to some degree or another.

Benefits of SQLite

Because of its serverless approach, there are a number of benefits to SQLite.

Portability is one of the biggest advantages. Because it does not rely on the traditional client-server relationship that defines its competitors, SQLite databases are extremely portable from one platform to another.

Its lightweight nature make SQLite a speed demon when performing many tasks. In fact, it can be significantly faster than traditional file storage. Reading and writing small blobs, such as thumbnail images, can be as much as 35% faster than reading or writing directly to the filesystem. Just as impressive, an SQLite database containing this kind of data can take as much as 20% less space than the same files saved directly to the filesystem.

That performance also applies to how the database reads data. SQLite databases are stored in a single file. Rather than reading the entire file into memory, however, SQLite only loads the data that is needed. This significantly reduces memory requirements, especially with large databases. Given that an SQLite database can be 140 terabytes, this is an important feature for performance and stability.

SQLite is also a transactional database. SQLite’s developers describe it this way:

”A transactional database is one in which all changes and queries appear to be Atomic, Consistent, Isolated, and Durable (ACID). SQLite implements serializable transactions that are atomic, consistent, isolated, and durable, even if the transaction is interrupted by a program crash, an operating system crash, or a power failure to the computer.

“We here restate and amplify the previous sentence for emphasis: All changes within a single transaction in SQLite either occur completely or not at all, even if the act of writing the change out to the disk is interrupted by

•      a program crash,

•      an operating system crash, or

•      a power failure.

”The claim of the previous paragraph is extensively checked in the SQLite regression test suite using a special test harness that simulates the effects on a database file of operating system crashes and power failures.”

In addition, small edits to the data only result in that portion of the database file being overwritten, rather than the entire file. This not only improves reliability, but also reduces hard drive wear.

Because of this inherent reliability, not to mention the portability of the file format, SQLite is one of only four formats recommended by the Library of Congress for long-term storage, the others being XML, JSON, and CSV.

Disadvantages of SQLite

Despite its many advantages, there are a few disadvantages to SQLite.

One of the biggest disadvantages is how the database performs write locks. Although SQLite can handle multiple read connections, it can only handle one write at a time. As a result, SQLite locks the entire database file any time a write is being performed. While this level of concurrency may seem rudimentary compared to client-server databases, it is still much better than many other embedded databases, as the developers point out:

”We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once.”

Another disadvantage is SQLite’s network performance. Compared with other databases, it is rather slow and best used for a local embedded application.

SQLite’s journaling can be another disadvantage. Journaling is one of the ways the database protects against corruption, such as during a power interruption. But a roll back journal can pose a security risk. To avoid this, it’s best to encrypt the database when it’s being used for sensitive information. One option is the SQLCipher extension that can be used to handle database encryption.

Conclusion

SQLite has gained widespread use for good reason. Few embedded databases can offer its combination of features and advantages. This makes it the ideal solution for its target market. While SQLite does have a few disadvantages, these are few and far between and, in most cases, can be worked around.

Whether you’re a new or experienced developer, SQLite is a tool that should have a permanent place in your development toolbox.

SQLite
Databases
Developer
About the author
Matt Milano -Technical Writer
Matt is a tech journalist and writer with a background in web and software development.

Related Articles