1. 29

  2. 7

    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?

    1. 4

      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. :)

      1. 2

        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.

        1. 1

          I can’t imagine relying on a database for type safety. That is a much higher level problem IMO.

        2. 4

          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.

          1. 1

            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.

            1. 1

              I have lived through several nightmare scenarios based on the poor quality of the JVM bindings too, but no complaints about the database itself.

              1. 2

                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.

            2. 1

              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.

              1. 1

                A few that come to mind:

                • 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
            3. 1

              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.