There is a C++ standard proposal asking for std::bless due to issues with foreign memory in C++ (:
I found the idea of blessing memory to make it usable quite funny.
But, unlike most databases, SQLite has a single writer model. You cannot have more than one concurrent writer.
This was also changed recently in 2010 by adding WAL mode.
Also it was not actually changed: WAL mode still only allows one writer, the change is that you can now have readers and that one writer concurrently, whereas in the older rollback mode you can have readers or one writer.
(18) Since 3.37.0 (2021-11), STRICT tables are available.
(24) I believe that “Ess-Cue-El-Lite” is a wrong interpretation, instead should be “Ess-Cu-E-lite” similar to “Man-ga-nite”. Though DRH does not seem to care too strongly, as he said himself.
The fact that SQLite types are just “suggestions” has a very useful feature: it’s a great spreadsheet backend.
In a spreadsheet, you are allowed to store wrong types. They light up in bright red as an error, but they’re stored all the same. This storing-the-type-error behaviour is something that works well for spreadsheets and a feature that spreadsheet users expect.
It’s for this reason that Grist, which is a spreadsheet interface over a database, is more or less forced to use SQLite as its database engine. We could use another engine instead, but that would require some artful contortions that would separate the spreadsheet representation from the database storage.
What about duckdb? I don’t know how it enforces types but it has an advantage of being better at analytical queries, which sounds useful for spreadsheet.
The key to performance of any OLAP database is rigorous enforcement of types so columns could be efficiently encoded. So duckdb is a very bad fit given description above.
(18) SQLite has types: integer, double, text, blob, null. It’s just that values are typed, not columns. This is the same dynamic type system used by any number of common languages, from JavaScript to Python, and many databases like MongoDB, CouchDB and Couchbase; I’m not sure why people get bent out of shape about it in SQLite. Just because it’s different than other SQL databases?
the pejorative interpretation is fairly appropriate given that even in a discussion about the most widely-deployed and successful database in human history, the mere mention of dynamic types is enough to draw people out of the woodwork to complain and act like dynamic types should be understood as objectively bad.
TBF the dynamic types have always been the weakest part of sqlite and the main reason it wasn’t suggested as a production db by many communities for years.
Among other reasons, people use relational databases in order to have guarantees on the structure of data. For many domains, the type of values is part of that guarantee.
Now that it has strict tables, it’s way less of an issue.
I’m pretty sure most real-world uses of SQLite are as a replacement for text files (like INI files) or as a simple key-value store (like Berkeley DB). Dynamic typing is a better fit for this.
I have made a mini in-house career at work out of using sqlite to log data where “json one line at a time” would not be a good fit. It lets me get basically the same functionality (one blob per row, ordered and timestamped at write time), write binary data, and not have to worry about figuring out how to start reading at a random point. Sqlite is a really good alternative to fopen sometimes.
One case is that in other SQL DBs you define the type and if the Database supports it, it is accepted. If the DB doesn’t know about your type (boolean), then it fails. SQLITE will simply accept everything by default (that can be a feature for various reasons).
Next issue: You define a column type as integer, take your favorite ORM and write date rows. Turns out it just silently inserts strings (ISO date) instead of the expected integer (without STRICT). Surprise surprise, your column type definition was more like a tag/hint. Add the default ROWID plus ignored foreign keys and you get a mix of foot guns. Maybe a future version will introduce something like a edition which enables foreign keys and STRICT by default, plus WITHOUT ROWID in case a primary key exists.
I used it in pure n:m relationship tables and when you have a ton of entries with a defined primary key. Even the docs mention this as a storage and performance optimization as your primary key is otherwise just a unique, but not the lookup key.
That one probably not until sqlite’s storage is re-engineered to rely less on rowid (aka without rowid tables are made “more first class”).
Most notably, WITHOUT ROWID tables are regular btrees with the entire row as key. Rowid tables have their own implementation where only the rowid is part of the btree key.
This would be solved by implementing “proper” covering indexes in sqlite (INCLUDE clause) and making both rowid and without rowid tables use that, but I don’t think I’ve ever seen work on that. And in a way they’re less necessary than in other RDBMS because by not having advanced or user-defined types sqlite does not have un-ordered types, which is one of the features which need INCLUDE clauses.
An other consideration is that secondary indexes use the “true” primary key as data. For rowid tables that’s the rowid, but for without rowid tables that’s the primary key. For large / composite primary keys, this can increase the cost of the index (/ decreases its gain) quite a bit.
rowid tables are implemented as B*-Trees where all content is stored in the leaves of the tree, whereas WITHOUT ROWID tables are implemented using ordinary B-Trees with content stored on both leaves and intermediate nodes.
hence earlier in the paragraph
A good rule-of-thumb is that the average size of a single row in a WITHOUT ROWID table should be less than about 1/20th the size of a database page. That means that rows should not contain more than about 50 bytes each for a 1KiB page size or about 200 bytes each for 4KiB page size.
They specifically mention using your primary key as the lookup.
Yes? By definition btrees support prefix search. So you can look up the tuple by the primary key via prefix search, and you’ll get either 0 or 1 entry as it’s essentially a unique index.
I really enjoyed working with sqlite in a university project due to its extensibility. We were writing a data processing DSL on top of Nim that was translated into an AST at compile time (through a macro) and then constructed SQL queries from this AST at runtime. The fact we could directly plug-in native Nim functions that were called from these SQL queries was great. The library I wrote for exposing Nim functions to sqlite is open source: https://github.com/niklaskorz/nim-exporttosqlite3
Nonetheless, I do agree with the author that the very loose typing and weird type name substring matching are a considerable downside of sqlite. I am quite hopeful that libsql will improve on the good foundation that is sqlite.
I’m very much not religious, but I still like the idea of starting a source file with a blessing.
“A software system just like a cathedral: first we build it, then we pray.”
Makes me think of the WH40K universe, and I’m sure others that have taken the “technology-as-divine-favor” angle.
There is a C++ standard proposal asking for std::bless due to issues with foreign memory in C++ (: I found the idea of blessing memory to make it usable quite funny.
blessreminds me of Perlthis “recently” is almost old enough to drive
We just don’t want to feel old, ok?
Also it was not actually changed: WAL mode still only allows one writer, the change is that you can now have readers and that one writer concurrently, whereas in the older rollback mode you can have readers or one writer.
Giving you the divine power of deadlocking..
The power of
SQLITE_BUSY, mostly.(18) Since 3.37.0 (2021-11), STRICT tables are available.
(24) I believe that “Ess-Cue-El-Lite” is a wrong interpretation, instead should be “Ess-Cu-E-lite” similar to “Man-ga-nite”. Though DRH does not seem to care too strongly, as he said himself.
Very nice list, (19) gave me a good chuckle!
Awesome! I didn’t know this. It should be fact #0.
The fact that SQLite types are just “suggestions” has a very useful feature: it’s a great spreadsheet backend.
In a spreadsheet, you are allowed to store wrong types. They light up in bright red as an error, but they’re stored all the same. This storing-the-type-error behaviour is something that works well for spreadsheets and a feature that spreadsheet users expect.
It’s for this reason that Grist, which is a spreadsheet interface over a database, is more or less forced to use SQLite as its database engine. We could use another engine instead, but that would require some artful contortions that would separate the spreadsheet representation from the database storage.
What about duckdb? I don’t know how it enforces types but it has an advantage of being better at analytical queries, which sounds useful for spreadsheet.
The key to performance of any OLAP database is rigorous enforcement of types so columns could be efficiently encoded. So duckdb is a very bad fit given description above.
(18) SQLite has types: integer, double, text, blob, null. It’s just that values are typed, not columns. This is the same dynamic type system used by any number of common languages, from JavaScript to Python, and many databases like MongoDB, CouchDB and Couchbase; I’m not sure why people get bent out of shape about it in SQLite. Just because it’s different than other SQL databases?
People get bent out of shape about dynamic typing in those languages all the time!
I feel like “bent out of shape” is being used as a pejorative here.
There are really good reasons to dislike dynamic typing!
Also to like them. The right tools for the right job.
Not really
the pejorative interpretation is fairly appropriate given that even in a discussion about the most widely-deployed and successful database in human history, the mere mention of dynamic types is enough to draw people out of the woodwork to complain and act like dynamic types should be understood as objectively bad.
TBF the dynamic types have always been the weakest part of sqlite and the main reason it wasn’t suggested as a production db by many communities for years.
Among other reasons, people use relational databases in order to have guarantees on the structure of data. For many domains, the type of values is part of that guarantee.
Now that it has strict tables, it’s way less of an issue.
I’m pretty sure most real-world uses of SQLite are as a replacement for text files (like INI files) or as a simple key-value store (like Berkeley DB). Dynamic typing is a better fit for this.
I have made a mini in-house career at work out of using sqlite to log data where “json one line at a time” would not be a good fit. It lets me get basically the same functionality (one blob per row, ordered and timestamped at write time), write binary data, and not have to worry about figuring out how to start reading at a random point. Sqlite is a really good alternative to
fopensometimes.Those are both terrible times to use sqlite though…
One case is that in other SQL DBs you define the type and if the Database supports it, it is accepted. If the DB doesn’t know about your type (boolean), then it fails. SQLITE will simply accept everything by default (that can be a feature for various reasons).
Next issue: You define a column type as integer, take your favorite ORM and write date rows. Turns out it just silently inserts strings (ISO date) instead of the expected integer (without STRICT). Surprise surprise, your column type definition was more like a tag/hint. Add the default
ROWIDplus ignored foreign keys and you get a mix of foot guns. Maybe a future version will introduce something like a edition which enables foreign keys and STRICT by default, plusWITHOUT ROWIDin case a primary key exists.What’s wrong with the ROWID being used by default? (Definitely agree that the other things are annoying footguns.)
I used it in pure n:m relationship tables and when you have a ton of entries with a defined primary key. Even the docs mention this as a storage and performance optimization as your primary key is otherwise just a unique, but not the lookup key.
That one probably not until sqlite’s storage is re-engineered to rely less on rowid (aka
without rowidtables are made “more first class”).Most notably,
WITHOUT ROWIDtables are regular btrees with the entire row as key. Rowid tables have their own implementation where only the rowid is part of the btree key.This would be solved by implementing “proper” covering indexes in sqlite (
INCLUDEclause) and making both rowid and without rowid tables use that, but I don’t think I’ve ever seen work on that. And in a way they’re less necessary than in other RDBMS because by not having advanced or user-defined types sqlite does not have un-ordered types, which is one of the features which needINCLUDEclauses.An other consideration is that secondary indexes use the “true” primary key as data. For rowid tables that’s the rowid, but for without rowid tables that’s the primary key. For large / composite primary keys, this can increase the cost of the index (/ decreases its gain) quite a bit.
I can’t find that in the documentation nor did I ever hear about this. They specifically mention using your primary key as the lookup.
https://www.sqlite.org/withoutrowid.html#when_to_use_without_rowid
hence earlier in the paragraph
Yes? By definition btrees support prefix search. So you can look up the tuple by the primary key via prefix search, and you’ll get either 0 or 1 entry as it’s essentially a unique index.
I really enjoyed working with sqlite in a university project due to its extensibility. We were writing a data processing DSL on top of Nim that was translated into an AST at compile time (through a macro) and then constructed SQL queries from this AST at runtime. The fact we could directly plug-in native Nim functions that were called from these SQL queries was great. The library I wrote for exposing Nim functions to sqlite is open source: https://github.com/niklaskorz/nim-exporttosqlite3
Nonetheless, I do agree with the author that the very loose typing and weird type name substring matching are a considerable downside of sqlite. I am quite hopeful that libsql will improve on the good foundation that is sqlite.