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: