I love the idea of having an SQL database just be a file read by a library. However, I can’t phantom why dynamic typing in a database is a good idea. If a row is declared as an integer, I don’t want random string data to silently appear there. I don’t want a subtle bug in my application silently put garbage in my database. I can to some degree understand the appeal of dynamically typed language, but surely you want your database to protect you from storing broken data?
The obvious solution is to write your application in C and have a struct type for each row. Then if you have an int in your struct, you can only put an int in the database. :)
To the extent that it makes sense for a language it makes sense for a database too, I’d say. If you’re following the principle of alternating hard and soft layers, then if you have all your validation and typing in a relatively strict language then it may make sense to use a relatively loose storage layer.
As an Android developer who has to simultaneously support 5+ years worth of SQLite versions, “rock-solid stability” got a good chuckle out of me. Don’t get me wrong, all the other virtues are quite true, but a magic, stable, bugfree DB has not yet been created by humankind. I have almost as many “make the DB happy” hacks in my code with SQLite as I ever did with MySQL, though they do generally seem more simple.
I have had pretty bad experience with sqlite being very slow with the native python bindings (python 2.x).
I do recall hearing that something got fixed with python3.x though, and that it isn’t so awful now.
I’d be curious to discuss and dissect an example or two. I had a few issues, but this was many years ago, and generally could be dealt with in a straightforward manner, where the workaround was not obviously worse way of doing things.
in an app that made pretty heavy use of one table with lots of inserts and deletes and upserts through the day but that never got down to zero rows, I had to write logic to determine a good time when the user wasn’t around to lock the table and run a VACUUM, or else the DB slowed down to an absoute crawl within a few weeks
in another app that made heavy use of upserts, I discovered that in several SQLite versions, the upserts will silently no-op if run in a transaction (but the only way to get good speed on most inserts is to batch them in a transaction)
in almost every app I have ever maintaned (since WAL mode still has near-zero availability), having to do manual synchro of write access is a big enough pain, but also having to account for the fact that opening and closing a DB also counts as write access and can cause crashes … not enough vodka in the world to kill the memory of those debug sessions
I love the idea of having an SQL database just be a file read by a library. However, I can’t phantom why dynamic typing in a database is a good idea. If a row is declared as an integer, I don’t want random string data to silently appear there. I don’t want a subtle bug in my application silently put garbage in my database. I can to some degree understand the appeal of dynamically typed language, but surely you want your database to protect you from storing broken data?
The obvious solution is to write your application in C and have a struct type for each row. Then if you have an int in your struct, you can only put an int in the database. :)
To the extent that it makes sense for a language it makes sense for a database too, I’d say. If you’re following the principle of alternating hard and soft layers, then if you have all your validation and typing in a relatively strict language then it may make sense to use a relatively loose storage layer.
I can’t imagine relying on a database for type safety. That is a much higher level problem IMO.
As an Android developer who has to simultaneously support 5+ years worth of SQLite versions, “rock-solid stability” got a good chuckle out of me. Don’t get me wrong, all the other virtues are quite true, but a magic, stable, bugfree DB has not yet been created by humankind. I have almost as many “make the DB happy” hacks in my code with SQLite as I ever did with MySQL, though they do generally seem more simple.
I have had pretty bad experience with sqlite being very slow with the native python bindings (python 2.x).
I do recall hearing that something got fixed with python3.x though, and that it isn’t so awful now.
I have lived through several nightmare scenarios based on the poor quality of the JVM bindings too, but no complaints about the database itself.
I’ve run into fatal flaws with about every binding I’ve used (and I can count at least four). It’s not hard to write better bindings, at least.
I’d be curious to discuss and dissect an example or two. I had a few issues, but this was many years ago, and generally could be dealt with in a straightforward manner, where the workaround was not obviously worse way of doing things.
A few that come to mind:
Bonus: you can use it in Javascript too. The whole db is just one file of C code, so the Emscripten folks compiled it into JS.
https://github.com/kripken/sql.js/