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

281–290 of 330 posts

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

#281

Post by adamckay »

ledgerdev wrote: post #261
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…

What about zero downtime deploy of new version of your application? You have to take it down to restart, right?

"(5) ... SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. ..." - https://www.sqlite.org/faq.html

You can start your new version of your application in a new process that opens the same database file, switch your load balancer to the new app, allow the old to drain all requests and then terminate the old app.

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

#282

Post by vatotemking »

qorrect wrote: post #208
uuyi wrote: post #25

Earlier quoted context omitted.

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…

Hey I actually do the same in a mobile app. I dump everything into a local database, when they are connected to the internet it syncs, lets it work offline.

Im working on something similar. Mine is a RFID scanner kiosk that uses a local db when offline. How do you manage sync conflicts?

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

#283

Post by ipaddr »

TheRealPomax wrote: post #252
deckard1 wrote: post #240

Earlier quoted context omitted.

your app has full access to the SQLite file. MySQL/PostgreSQL have users and permissions. Security is about layers, and SQLite is removing one layer of security. You can, for example, put DELETEs or access to certain tables on a separate user that your web app has no access to. With SQLite, if your app gets hacked then they can do anything with the whole DB they want to. In addition, with a separate DB process you ge…

If your server or API can be exploited, it doesn't matter whether there's an auth layer in between. Your SQL server runs as a service to connect to, your sqlite3 file is a file that you need access to. They're the same kind of layer: you need to break through the server's security to ever get to them directly, and if your app gets hacked such that the hackers gain file system access, then: 1. You're fucked. The end.…

With sqlite your server, api or application can be hacked. The most common and likely hack would be somewhere in your application. It really doesn't make sense to use sqlite here.

Setup a separate database server and use it for all of your projects. That one hour pays off each and every project.

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

#284

Post by [deleted] »

mekster wrote: post #118
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…

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…

[deleted]

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

#285

Post by js2 »

oppositelock wrote: post #259
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…

SQLite is far faster than Postgres or MySQL, however, the price you pay for this is having a single writer thread, and it's a library incorporated into your process, not a shared DB. It's faster because those other features of a server have a cost as well, particularly the cost of write arbitration. SQLite is fine when all your load can be served by a single backend process on a single machine. The moment you need mu…

> my sweet spot has become a big honking Postgres instance in RDS

Why do you prefer PostgreSQL RDS over Aurora RDS? Aurora seems better in every way but price[1]. (I know it also had some growing pains at launch.)

[1]: Amazon RDS for PostgreSQL is ideal when you have a small-to-medium intense workload. It works best when you have limited concurrent connections to your database. If you’re moving from commercial database engines such as Oracle or Microsoft SQL Server, Aurora PostgreSQL is a better choice because it provides matching performance with a lower price.

https://aws.amazon.com/blogs/database/is-amazon-rds-for-post...

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

#286

Post by srcreigh »

iampims wrote: post #258
srcreigh wrote: post #254

Earlier quoted context omitted.

For my personal project I'm planning to use a VM with an SSD. Manually I'll use caddy to switch over to a new running backend service with readiness check. As for scaling if I need it I can increase disk space for the app server or scale out horizontally/vertically. Don't need that yet so I'm waiting for more details in the future to decide how to handle that.

Which backend “owns” the sqlite database? That’s what I haven’t seen mentioned anywhere, if the database is part of the application, how do you switch from one version to another without downtime.

The VM owns the database. Multiple backend service versions run on that same VM. Usually just one but 2 while migrating traffic to a new version.

I haven't figured out details regarding having 2 processes both writing and reading to the SQLite DB at once. It might just be fine. With a 1 minute request timeout I can just shut down the previous version after 2 minutes (should receive no new requests after 1 minute) and caddy will have sent requests to the new version for a while.

Not sure which types of errors I'll be seeing but the client may need to retry requests in some cases.

This is all just what I've planned but hopefully kinda makes sense.

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

#287

Post by srcreigh »

nlh wrote: post #193
srcreigh wrote: post #166

Earlier quoted context omitted.

Interesting problem. Did you try grouping up transactions? Ex instead of a few hundred million txns, do a few hundred thousand 1000op chunk txns. SQLite is much much faster within a txn. Edit: a several hundred million txns over a few hours math. How many per second? ~500 According to here (question 19), for old HDDs you could expect 3 orders of magnitude improvement by using bigger txns. Not sure SSD wise but worth…

I have not tried that yet (I was being lazy since it all ran so fast on my M1), but that's a good idea for something to investigate. That way I suppose I can run the whole thing on a less-beefy instance and avoid the scale-up/scale-down cycle.

Yeah I think if you are doing 1 txn per second roughly instead of 100s, it may just work fine. I'm curious how it goes so shoot me an email if you want with updates

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

#288

Post by post-it »

mekster wrote: post #264
TheRealPomax wrote: post #182

Earlier quoted context omitted.

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 (a…

The risk is as I had written previously that it takes some effort to move away from a db to another when the need arises when I see no benefit in choosing SQLite in the beginning. I'm not a professional db engineer but one point is that there doesn't seem to be a way to create functions in SQLite which would mean creating triggers on various tables can cause excessive amount of duplicate code. If I rely on PostgreSQL…

If a project of mine ever gets enough traffic to force me away from SQLite, I expect I'll be rolling in dough and willing to put in the effort.

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

#289

Post by djbusby »

JohnBooty wrote: post #269
djbusby wrote: post #212

Earlier quoted context omitted.

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)

Interesting, that's super cool to read. Tooling for dumping and restoring into a new schema are easy/simple/fast. Any resources you can point to that expand on this? Is this standard SQLite tooling? I'm curious how it would perform with large-ish databases - a few hundred GB or perhaps several TB. (This is one of those things where I can "just Google it" but I was wondering if perhaps there was a particularly useful…

I'm just using shell scripts and the SQLite CLI. I dump/restore files to the same FS so I can mv into place when ready.

Checkout the PRAGMA user_version, I just modify on schema change, so app knows where stuff is.

https://www.sqlite.org/pragma.html

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

#290

Post by ttfkam »

marcosdumay wrote: post #221
no_wizard wrote: post #216

Earlier quoted context omitted.

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.

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

You say this as some sort of objective truth. Keeping security about data at the data layer can often be a really good idea and just as appropriate as GRANT/REVOKE at the table and column levels.

Post reply on HN