Ask HN: Have you used SQLite as a primary database?

Post by LAC-Tech » Thu, Apr 28, 2022, 6:56 AM UTC

uuyi wrote: post #25

samwillis wrote: post #5

Simon Willison has written about using SQLite for a "Baked in data" architecture which is a super interesting method for some situations: https://simonwillison.net/2021/Jul/28/baked-data/ As he notes https://www.mozilla.org/ uses this pattern: > They started using SQLite back in 2018 in a system they call Bedrock ... Their site content lives in a ~22MB SQLite database file, which is built and uploaded to S3 and then…

I designed something that used a local SQLite database on the client and a remote postgresql instance as the master. It used read and write queues at each end for sync and was eventually consistent. Unfortunately it was far too advanced for the org and no one else understood it so it was canned in favour of a connected solution under the guise of ubiquitous internet access being available. This is proving to be a poo…

Post by TheRealPomax » Thu, Apr 28, 2022, 4:49 PM UTC

mekster wrote: post #273

TheRealPomax wrote: post #267

Earlier quoted context omitted.

That's not a risk, that's just an inefficiency further down the line (migrating data from sqlite to a "real" database can indeed be quite a chore, but far less so if you formalized your schema and constraints beforehand, so that a migration mostly involves exporting to SQL, rewriting where needed, and then importing into whatever dbms you end up using later on in the lifetime of your project). When we're talking abou…

Inefficiency or not, if you start down the path of SQLite, you need to invest good amount of time refactoring into another DB if you feel like migrating away. When I realized SQLite would store any type of data in any kind of column type, it was obvious SQLite is different from others. They only added strict types about a year ago but scared me enough not to use it. And how is SQLite any less secure? You can flat out…

Post by datavirtue » Fri, Apr 29, 2022, 12:18 PM UTC

markoman wrote: post #90

datavirtue wrote: post #78

Earlier quoted context omitted.

I just make key-value store tables and write a small interface to simplify access in H2 or SQLite.

But why not just use Redis (which is almost surely faster even vs. SQLite in-memory) instead of creating key-value tables?

Post by ricg » Fri, Apr 29, 2022, 3:45 PM UTC

config_yml wrote: post #36

I use it together with Rails and the horizontal sharding feature. Each customer has it's own sqlite database running in WAL mode. Since the app is internally used, traffic/writes are pretty predictable. I also do backups periodically with ActiveJob using `.backup` on the sqlite3 client. It's simple and nice because I just have to worry about running the app, and nothing else.

Post by tianzhou » Sat, Apr 30, 2022, 4:32 PM UTC

djbusby wrote: post #212

JohnBooty wrote: post #206

Earlier quoted context omitted.

This was so inspiring to read. It's a very balanced take about the pros and cons of using SQLite vs. Postgres at scale. I say "inspiring" because using SQLite reminds me of the simplicity and productivity from coding for the "early web" that lost 10-15 years ago. The days when you could spin up a website without worrying about a bunch of ancillary services and focus on the app itself. For me, SQLite's lack of online…

I have this beef too. Tooling for dumping and restoring into a new schema are easy/simple/fast. So, these schema migrations can happen w/o issue. Some tricks with the PRAGMA directive in SQLite so you can roll out changes (eg: code supports old/new schema while migrating)