Embeddable SQLite: Key Features and Insights

In the world of databases, SQLite stands out as a unique and powerful tool, especially in the context of embeddable databases. In a recent video by Volodymyr Pavlyshyn, the key features and practical insights into using SQLite were discussed in detail, highlighting why it is a popular choice for developers.

A Brief History and Overview#

SQLite was born out of necessity on a battleship, where the author encountered issues with existing database servers. This led him to create SQLite from scratch, focusing on simplicity, reliability, and speed. One of the most remarkable aspects of SQLite is that it compiles SQL queries into binary code, which a virtual machine executes, resulting in a blazingly fast and efficient database system.

Key Features of SQLite#

It is pretty well described in a docs

No Dependencies#

SQLite is designed to be self-contained, with no external dependencies. This makes it highly reliable, compact, and easily integrated into various applications.

Amalgamation#

SQLite uses an amalgamation process, combining all the source code into a single C file and a header file. This approach simplifies the inclusion of SQLite into your application, making it a part of your codebase.

Single-User Database#

SQLite is primarily designed as a single-user database, which means it works best when used by one user at a time. While it can be adapted for multi-tenancy, it lacks built-in permissions and privileges, requiring developers to build these layers themselves.

Flexible Typing#

Unlike many other databases, SQLite is flexibly typed, meaning that the type of data stored in a cell can vary. While this can lead to errors, it also provides flexibility, especially when working with dynamic data. Developers can enforce stricter typing by using strict tables, but this requires additional effort.

If you need a type check on data base level use strict tables

Dynamic row size#

Row has dynamic length and store you data without alignment overhead

Row values#

you could use powerful compare operation on types

No Date Time#

SQLite has no DATETIME datatype. Instead, dates and times can be stored in any of these ways:

As a TEXT string in the ISO-8601 format. Example: ‘2018–04–02 12:13:46’.
As an INTEGER number of seconds since 1970 (also known as “unix time”).
As a REAL value that is the fractional Julian day number.

The built-in date and time functions of SQLite understand date/times in all of the formats above, and can freely change between them. Which format you use, is entirely up to your application.

No Money of Decimal#

Real is just a wrapper on float number so be careful you could have a lot of float side effects especially with sums . It is better to convert to minimal precision and work with integers as more reliable type of use some native extensions like

JSON Support#

SQLite supports JSON, allowing developers to store and query JSON data within the database. However, JSON operations are slower than traditional column operations, so developers should use them judiciously.

Foreign Key Enforcement Is Off By Default#

Foreign keys are switched off bu default and accelerate writes

Crash Resilience#

SQLite is well-known for its robustness in handling crashes. The database is designed to be crash-friendly, with strong transaction and recovery mechanisms. However, developers should still be cautious, especially when dealing with interrupted operations.

Practical Considerations#

SQLite’s simplicity and flexibility make it a powerful tool, but some considerations must be considered. For instance, due to its design as a single-writer database, developers need to be careful with concurrent writes, especially in multi-threaded environments. Additionally, while SQLite supports flexible typing, developers should be aware of the potential pitfalls, such as storing inconsistent data types in the same column.

Another important aspect is the handling of dates and booleans. SQLite does not have native date or boolean types, but it provides powerful functions to work with date data and supports true/false values as integers (0 and 1).

Conclusion#

SQLite is a versatile and powerful embeddable database, ideal for applications requiring a lightweight, reliable, self-contained database system. Its unique features, such as no dependencies, flexible typing, and robust crash handling, make it a popular choice among developers. However, its single-user design and the nuances of its flexible typing system require careful consideration when integrating it into your projects.