Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

news.ycombinator.com

Post reply on HN
Print view View original

221–230 of 330 posts

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

#221

Post by marcosdumay »

no_wizard wrote: post #216
Glench wrote: post #32

Here's an all-time great post about why you might consider SQLite in production with data about performance: https://blog.wesleyac.com/posts/consider-sqlite I use SQLite in production for my SaaS[1]. It's really great — saves me money, required basically no setup/configuration/management, and has had no scaling issues whatsoever with a few million hits a month. SQLite is really blazing fast for typical SaaS workloads…

how do you handle things like encryption and access permissions? The only thing I have against using SQLite in production (for my needs) is the lack of at rest encryption and row level permissions by user.

At rest encryption is a complicated subject (in general, I bet most people get negative net security from it). For SQLite, you can either encrypt your disk or get one of the versions with added encryption (I only know of proprietary ones).

You don't do row level permissions on your database. You keep it all on the application layer.

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

#222

Post by a_chris »

I'm working on a self-hosted analytics like Plausible but that allows to use SQLite as primary database. Of course it should be used for small websites and side projects but it will have all the Plausible features. Am I crazy?

This is the project: https://github.com/a-chris/faenz

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

#224

Post by orthecreedence »

jamesrr39 wrote: post #181
post-it wrote: post #162

Earlier quoted context omitted.

- Setting up a MySQL server on both your dev machine and server, and making sure they're the same version (extra fun if they're on different OS versions) - Setting up an out-of-repo config file on the server with your MySQL credentials - Setting up a backup script for your server data It's only about an hour of work total, but it's an hour of work that I hate doing.

docker-compose is the way to go for keeping the dev versions synced with the production version. And for the backup script scheduled mysqldump and copy to storage should see you through quite far, so not really any more effort than copying an SQLite database.

This person is talking about reducing complexity, so I don't think adding more moving pieces to the machine is the way to go.

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

#225

Post by matthewaveryusa »

Mertax wrote: post #179
matthewaveryusa wrote: post #87

I’ve worked on several projects with sqlite, both read and write heavy, all with high concurrency, with databases in the few hundred MB with 400k server clients, and 100 bare-metal servers running at capacity. The sqlite part of our system is never the problem. In our case sqlite has been an alternative to custom files on disk or replacing a spaghetti of hashmaps in memory. we also replaced a single postgresql instan…

What pain points have you experienced with "many sqlites per customer". I'm considering transitioning to something similar but would love to know what pitfalls I might not be considering.

None really, but we have a fairly simple design where all the shared databases we attach are read-only (think big static lookup tables that a separate process takes care of updating.) I would probably avoid having databases attach contextually -- seems complicated and error-prone.

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

#227

Post by djbusby »

robertlagrant wrote: post #213
mekster wrote: post #118

Earlier quoted context omitted.

What is the point of using SQLite under a web service? I thought people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant. (Things seem to be getting better with strict column types these days.) I've recently migrated a smallish service from MySQL to PostgreSQL and figured it's quite a work if you're not careful writing by the SQL standard which mean…

> I thought people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant While "people" complaining is basically meaningless, I don't know why they'd be doing that about SQLite. It's used in most phones, all copies of Windows 10+, and countless other places.

Chrome browser has loads of them in your profile directory.

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

#228

Post by marcosdumay »

post-it wrote: post #162
mekster wrote: post #160

Earlier quoted context omitted.

Which part of running MySQL instead SQLite is over engineering?

- Setting up a MySQL server on both your dev machine and server, and making sure they're the same version (extra fun if they're on different OS versions) - Setting up an out-of-repo config file on the server with your MySQL credentials - Setting up a backup script for your server data It's only about an hour of work total, but it's an hour of work that I hate doing.

Hum... MySQL is one of those labor-generating technologies that a Luddite would fall in love for. Those are best avoided. But for Postgres...

- You make sure the production version is larger or equal than the development, or you make sure to not use new features before they reach production, what is quite easy. There is no problem with different OSes (except for Windows itself not being very reliable, but I imagine you are not using Windows on production, as it's another one of those labor-generating techs).

- Trusting a local user is the same level of security you get with SQLite, no credentials required.

- And setting a backup script... Wait, you don't do that for SQLite? There's something missing here.

Yes, there are a lot of small tasks that add up when setting some new software. It's a pain. But it's a pain you suffer once, and it's over. It's worth optimizing, but not at any ongoing cost.

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

#229

Post by Mertax »

digisign wrote: post #200
Mertax wrote: post #165

Has anyone ever used multiple SQLite databases per tenant/account? For sake of argument, let's say I have a fixed schema/format that will never change and I never need to aggregate queries across multiple customer accounts. Also, let's say writes to a single database are never going to be more than a hundred concurrent users. Why shouldn't I store each tenant's data in its own SQLite database? It makes it very easy f…

> a fixed schema/format that will never change When would this happen for any non-trivial multi-tenant service? The difficultly of performing migrations sounds like it would pretty quickly negate any simplicity gained.

Definitely use case specific, as it wouldn't work generally, especially for domain driven data models. The schema I'm thinking of specifically represents a data model that is flexible enough where migrations are extremely rare[1].

Even if there are migrations, it's treated similar to a file conversion (like upgrading an older excel file format to a new format on demand when the file is accessed).

[1] Maybe something similar to https://www.notion.so/blog/data-model-behind-notion or an EAV model imbedded in a JSON1 column.

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

#230

Post by beiller »

I am using it in production but my product has (almost) no users. Hopefully it will grow. I plan to replace it with Postgres as needed, but it offers some interesting new ways to approach certain problems, and creates interesting new problems like how to scale across multiple app servers? It's so far just a file. I'm sure there is some networking solution. Developing locally is interesting since I can just copy the production database file right to my machine.
Post reply on HN