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.
The Untold Story of SQLite - CoRecursive Podcast
On today's show, I'm talking to Richard Hipp about surviving becoming core infrastructure for the world. SQLite is…
Key Features of SQLite
It is pretty well described in a docs
Quirks, Caveats, and Gotchas In SQLite
The SQL language is a "standard". Even so, no two SQL database engines work exactly alike. Every SQL implementation has…
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.
The SQLite Amalgamation
Over 100 separate source files are concatenated into a single large file of C-code named "sqlite3.c" and referred to as…
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.
SQL Features That SQLite Does Not Implement
Small. Fast. Reliable.Choose any three. SQLite implements most of the common features of SQL. Rather than try to list…
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.
Quirks, Caveats, and Gotchas In SQLite
The SQL language is a "standard". Even so, no two SQL database engines work exactly alike. Every SQL implementation has…
If you need a type check on data base level use strict tables
STRICT Tables
SQLite strives to be flexible regarding the datatype of the content that it stores. For example, if a table column has…
Dynamic row size
Row has dynamic length and store you data without alignment overhead
Row values
you could use powerful compare operation on types
Row Values
A "value" is a single number, string, BLOB or NULL. Sometimes the qualified name "scalar value" is used to emphasize…
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
Floating Point Numbers
SQLite stores integer values in the 64-bit twos-complement format¹. This gives a storage range of…
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.
JSON Functions And Operators
By default, SQLite supports thirty functions and two operators for dealing with JSON values. There are also two…
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.
Write-Ahead Logging
The default method by which SQLite implements atomic commit and rollback is a rollback journal. Beginning with version…
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.