> But several times when I have been doing development work my file based database has become corrupted. I violated some constraint, the sql was poorly formed, etc. Always my fault. I simply remove the file that SQLite is using and create a new one or use a backup copy.
That really shouldn't be able to happen unless you're using the API wrong or have weakened safety with pragmas or database configurations that trade off resilience for performance. Even with crashes, attempts to violate constraints, poorly formed SQL, etc, the file should never become corrupt. If it does, that's a bug with SQLite, not you. Corruption bugs are exceptionally rare in SQLite these days.
Yes, I understand I was "using the API wrong". That is true. But the point I was trying to make was that I have used the PostgreSQL API "wrong" or "worse" and my data has never been corrupted. With SQLite I tried to open an SQLite database several different times where I got an error when dropping a table. If I simply restarted with a clean copy of the file, the code worked perfectly { fix bug, replace copy, restart }. And to be clear this was not data - like the row had a 3 not 2 - this was that running "drop table" on an existing table failed. Internal stuff in the file got broken. It happens when a process crashes.
My point is NOT that users should not adopt SQLite, just the opposite. I have found that using SQL helps me build things much more easily. And better. It help s you think about your code more effectively.
My recent adoption of SQLite and using it in smaller, lighter situations has worked extremely well and I will keep on using it. But... my limited experience and comparison of the two products suggests that as you would expect given the Lite, there are some differences that have real life consequences.
That really shouldn't be able to happen unless you're using the API wrong or have weakened safety with pragmas or database configurations that trade off resilience for performance. Even with crashes, attempts to violate constraints, poorly formed SQL, etc, the file should never become corrupt. If it does, that's a bug with SQLite, not you. Corruption bugs are exceptionally rare in SQLite these days.