I'm just as much a corner-cutter as the next "just yeet it in prod and we'll deal with it later" lazy grug brained dev, AND I fucking love SQLite...
But saying it SQLite good because no latency is weird, and saying that THAT is good because you can continue writing avoidable "I am not just lazy but I don't care about thinking about my database for 30 minutes" code... now that's a leaning tower of pisa of arguments that I can't follow.
Yes. We have a bunch of n+1’s in our product, but they are in places where it literally does not matter. A single record getting pulled down, on a page deep in a UX flow.
It’s not worth our time unless they work into pages with large data sets or core flows.
I have "ignore this n+1 warning" in a few places in our unit tests for exactly this reason (details page, so n will never be more than 1). But these don't cause a problem on any database. So, again, not sure how this is a win for SQLite.
AFAIK SQLite can scale quite a bit with WAL2, and then probably it is easy to upgrade it to Postgres. If your data model was bad from the start up, it doesn't matter if you used Postgres initially.
If you find out your data model was bad, and is a constraint to scaling, you can find it out without spending a lot on insanely scaled RDB instances.
I'm for the build one to throw away approach, and SQLite is often a good tool in the toolbelt for the initial attempt of this.
Now we're getting silly. If you have n+1 issues you already have a bad data model which is why what you are really saying is "SQLite lets you have a bad data model which gives you velocity!!!!!" which is obviously (I hope it's obvious) not a good thing.
From a business perspective: You might not have to solve that problem at all in 6 months, because you might not get so many users for the product, that scalability is a problem. You often need velocity more than clean code, because you are not sure if the idea will get traction at all. In that case, from a business perspective, to be able to experiment you need velocity more than a good data model.
I did this kind of exploratory development both with SQLite and Postgres, both had different strengths and weaknesses. Had the purist clean data model with Postgres cost more than it should have when the product had to pivot the product. Also have seen the scalability limits of SQLite. Just shutting down a POC project backed by SQLite, which failed to get traction mostly because of business execution problems, and SQLite saved a lot of effort for the implementation, and using a "cleaner" and "more scalable" solution would have only reduced velocity, but would not have got us more users, the system worked fine this way (even has N+1 queries in some places). Should we got to the point that SQLite starts to become a bottleneck we could have afforded to clean the data layer (we had to rework countless times already as requirements were in a flux) up a bit and move to more scalable solution. Others might stick Firebase or MongoDB there, and it also does work for a while, and that is also a valid decision IMHO from a business perspective.
I think this is a kind of topic where the premature optimization is the root of all evil meme can apply, depending on the product you are making and the available resource pool. Not for the hello world examples of course, and not for the well defined well funded project, but there are lot of exploratory attempts out of the bigco/vc funded unicorn world.
