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

181–190 of 330 posts

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

#181

Post by jamesrr39 »

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.

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.

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

#182

Post by TheRealPomax »

mekster wrote: post #172
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.

So you trade for some risk for an hour.

Please do explain what risk you're thinking of, as anyone smart enough to write their own SaaS would not put resources in the web server's file system tree? You stick your db file in an normal secure location outside the server's root, chmodded appropriately so that it suffers the exact same risks as any other file on the OS. It's no more or less risky than /etc/shadow, while being considerably easier to work with (and less failure-prone for light db work) than an independently running database service.

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

#183

Post by vimsee »

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…

If the users are never aware of other users and they only care about their own data, sure. Maybe you have a service where a user can sign up to do a specific task that never requires interaction with another user.

However, ff you want interaction across users (messaging, user-roles etc.) then you might want to have them in one database.

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

#184

Post by iampims »

Glench wrote: post #127
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…

> What is the point of using SQLite under a web service? Take a look at the Consider SQLite post I linked. They address your performance questions too. For me, SQLite was a nice way to simplify launching and running my SaaS business and has had no downsides.

Any tips you can share on how to deploy your saas app without downtime when using sqlite?

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

#185

Post by pjs_ »

I know a lot of people hate ORMs but I tend to find them useful, and if you do go down that route, it is pretty easy to write an application that will work with either a Postgres or SQlite backend.

I use SQLAlchemy and write applications where by just swapping out the database URI I can use either SQLite or Postgres. SQLite is nice for local development and easy testing, (you can even run tests using :memory: to accelerate CI/CD) and then I use hosted Postgres in prod. That said, based on what I have seen I would not be at all afraid to use SQLite in prod for internal tools etc.

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

#186

Post by jjoonathan »

srcreigh wrote: post #169
jjoonathan wrote: post #89

It blew up big time. I would have saved myself lots of trouble if I had just gone with postgres from the getgo. The workload was simple (single node work tracking) and I didn't expect it to become a bottleneck. Unfortunately, there were some default settings in the storage backend (tiny page size or WAL or something) that caused severe thrashing and a dearth of tooling to track down the issue. After making a custom b…

More details please if you have them. What was the throughput? number of transactions? Data size? If you have that thread would be great to see it as well.

It was chugging at ~100 inserts per second on about ~300k rows of The problem itself didn't concern me nearly as much as "no perf tools + no perf foolproofing." That's a rough combo. If a problem this simple required this much debugging, extrapolations to problems of any complexity are terrifying. I knew that simplicity implied limitations, but this lesson taught me that simplicity could also imply danger.

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

#187

Post by probotect0r »

I use it as the data store for my company's Grafana instances. I back up the SQLite store to an S3 bucket with litestream.io, allowing me to treat the servers as 'cattle'. It has worked perfectly without any issues, and saved the cost of a full RDS instance on AWS.

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

#188

Post by therealdrag0 »

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…

> hits a month

Is not a very useful performance metric. What is your peak hits per second?

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

#189

Post by fum52882 »

Slightly off-topic, because we use PostgreSQL on the backend, but because StoryArk is offline-first, we heavily rely on SQLite in our mobile app. The backend database is mostly just there to backup your local data and to sync it across all of your devices. So there aren't that many queries being run on the backend database.

I can't really count how many times I've been pleasantly surprised by how extensive the feature set of SQLite is. I mean, it even has window functions (https://www.sqlite.org/windowfunctions.html). And being able to quickly open up the app's SQLite file in a database browser is also quite helpful during development.

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

#190

Post by jjoonathan »

dolmen wrote: post #161
jjoonathan wrote: post #89

It blew up big time. I would have saved myself lots of trouble if I had just gone with postgres from the getgo. The workload was simple (single node work tracking) and I didn't expect it to become a bottleneck. Unfortunately, there were some default settings in the storage backend (tiny page size or WAL or something) that caused severe thrashing and a dearth of tooling to track down the issue. After making a custom b…

Could you tell us more about that configure option?

This happened in 2013. A few years later the fix went through. The bug itself is water under the bridge, what you're hearing is that I'm still leery of "no perf tools + no perf foolproofing." These judgements may be obsolete -- once burned, twice shy.
Post reply on HN