SQLite is a lightweight database library suitable for single user or low throughput databases. It is used for storing some user data in Apertis.
- Use SQLite for appropriate use cases: not configuration data (use GSettings).
- Consider your vacuuming policy before committing to using SQLite.
- Avoid SQL injection vulnerabilities by using prepared statements.
When to use SQLite
Even though it is lightweight for a database, SQLite is a fairly heavyweight solution to some problems. It should not be used for configuration data: GSettings should be used for that. Similarly, it is not suitable for more fully featured database systems which require support for concurrent access or advanced SQL support. It fills the middle space, and is best suited to situations where structured user data needs to be stored and fast indexing is required on various columns in the data, as that is hard to implement manually.
Before deciding to use SQLite, you must consider a vacuuming policy for the database, and whether your use case will interact well with SQLite’s vacuuming system. Vacuuming is effectively SQLite’s term for defragmenting the database — if a database is not vacuumed appropriately, performance will degrade and the database size will increase indefinitely. Read this article on vacuuming for more information; please consider it before choosing to use SQLite.
When using SQLite, queries must be constructed using SQLite’s prepared statement and value binding API, rather than by constructing SQL strings then passing them to SQLite to parse. Constructing strings makes SQL injection vulnerabilities very likely, which can give attackers access to arbitrary user data from the database.
Here is an example of using the prepared statement API: