Comment by talkingtab | Hacker News Reader

Comment by talkingtab | Hacker News Reader

I have a fair amount of PostgreSQL experience at this point. Enough so the advantages of using SQL and a database are very clear to me. But PostgreSQL is a bulldozer and sometimes I want the tactical benefits of an SQL db in situations where installation of PostgreSQL is not warranted. Throw away, small scale, experimental, etc.

SQLite is doing very well for my use cases. Especially using the experimental Node.js version - all I have been using. So I would highly recommend using it when you get to the point of moving data around.

On the other hand there is one big thing. And this may be only the Node.js version. 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.

The thing is that I have never seen a PostgreSQL DB become corrupt. No matter what horrendous code I wrote. Never. I have not really appreciated how completely bullet proof it has been for me.

Just my 2 cents worth.

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

> Corruption bugs are exceptionally rare in SQLite these days.

I develop a third party software and I receive reports of corrupted SQLite databases roughly every 2 months.

However, without any reproducibility, it might very well be due to hardware faults etc.

Still beats having end-users install something like postgres.

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.

Have you given pglite a try?

Reply on news.ycombinator.com