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

SQLite the only database you will ever need in most cases (2021)

unixsheikh.com

Post reply on HN
Print view View original

51–60 of 378 posts

Re: SQLite the only database you will ever need in most cases (2021)

#51

Post by cldellow »

bravura wrote: post #35

I would love to use SQLite for all my Django webapps that have only several simultaneous users, but this article suggests there are too many footguns for me to be able to do that. Is there a "using SQLite for a multi-threaded webapp for dummies" package that does all the config I need so I can just drop it in and go and not tune anything? Paging fly.io founders etc! If I have a persistent volume can my fly.io apps us…

IMO, the config you need is:

1) When you open the database:

    pragma journal_mode = wal;
    pragma synchronous = normal;
2) When you want to do a transaction that does writes, use `BEGIN IMMEDIATE`, not `BEGIN`.

3) Don't have long-running transactions.

4) Have some process to do backups.

(3) might be a big ask for some systems. Long-running transactions should be avoided even in systems like Postgres, but on a SQLite system with writers, they're the difference between an amazing experience and a garbage one.

I'm hopeful that Fly can eventually make (4) painless by having super-easy out-of-the-box litestream and S3 backups. Until then, roll your own cron scripts or what-have-you.

Re: SQLite the only database you will ever need in most cases (2021)

#52

Post by irskep »

danielmarkbruce wrote: post #36
irskep wrote: post #29

This sentiment pops up regularly on HN, and I've seen at least one article per month for the past few months, but the trouble is, none of them seem to help you actually deploy it. They assume you're comfortable spinning up public web servers. If you want to use a PaaS to deploy an app, because you don't want to spend your time learning to be a sysadmin, then all the tutorials are going to put you on the Postgres path…

It's a c library. Other languages will have a library/package/whatever to use it. You point it at a file.

The sarcasm and intentionally-missing-the-point here is not really in the spirit of HN, but I'll try to address what you seem to be trying to say, which is that "it's obvious and I'm an idiot for not seeing that it's obvious":

- Will multithreading make it break? (Not with WAL mode, but you have to set it manually, as this article suggests.)

- When using a PaaS, you need to explicitly add a volume and mount it on your server machine, which you might not think to do if you're a brand new bootcamp grad

- You can't(?) run migrations from another process (or at least, people don't seem to talk about doing this), so you need to prevent your server from writing while you run the migration. Even if I'm wrong, and I would love to be, it's frustrating that people don't talk about the completely ordinary need to run migrations on a database.

- Backups just mean copying the file somewhere, which is nice, but you might need to configure that yourself instead of just using somebody's managed Postgres backup feature

So, there is at least some complexity that comes with managing a "real" SQLite web server.

(I'm probably at least 25% wrong on some details above, but that's kind of the point, it's not always easy to figure this stuff out.)

Re: SQLite the only database you will ever need in most cases (2021)

#53

Post by deathclassic »

adamkf wrote: post #10

>The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients. This caveat covers "most cases". If there's only a single machine, then any data stored is not durable. Additionally, to my knowledge SQLite doesn't have a solution for durability other than asynchr…

This project comes to mind https://github.com/rqlite/rqlite but I've never used it, and I'm not sure if it would count as "pure sqlite" like the op advocated anymore.

Re: SQLite the only database you will ever need in most cases (2021)

#54

Post by deathclassic »

bravura wrote: post #35

I would love to use SQLite for all my Django webapps that have only several simultaneous users, but this article suggests there are too many footguns for me to be able to do that. Is there a "using SQLite for a multi-threaded webapp for dummies" package that does all the config I need so I can just drop it in and go and not tune anything? Paging fly.io founders etc! If I have a persistent volume can my fly.io apps us…

It would probably be harder to bend Django to use SQLite as a backend then it would be to just setup MySQL or PostGRES and use the existing Django tooling for it.

Re: SQLite the only database you will ever need in most cases (2021)

#55

Post by samtho »

paranoidrobot wrote: post #44
danielmarkbruce wrote: post #36

Earlier quoted context omitted.

It's a c library. Other languages will have a library/package/whatever to use it. You point it at a file.

"Just point it a file" skips all the bits you need for a production system. How do you back it up, replicate it, handle two different processes/containers/servers wanting to access the same data. Using a PAAS solution for a database, you get all that functionality.

I think the point to be learned here is that SQLite fundamentally does not fit in a PaaS model. They are even transparent with this limited use case[0]. I work with embedded systems so I use it a lot, and all the web work I do nowadays is one-off project site and small utilities that are usually a single process so I end up use SQLite 90% of the time I’m reaching for a solution.

0: https://www.sqlite.org/whentouse.html

Re: SQLite the only database you will ever need in most cases (2021)

#56

Post by danielmarkbruce »

paranoidrobot wrote: post #44
danielmarkbruce wrote: post #36

Earlier quoted context omitted.

It's a c library. Other languages will have a library/package/whatever to use it. You point it at a file.

"Just point it a file" skips all the bits you need for a production system. How do you back it up, replicate it, handle two different processes/containers/servers wanting to access the same data. Using a PAAS solution for a database, you get all that functionality.

That's why "most cases".

If you are trying to replicate or make available to a bunch of machines or similar - it's likely the wrong thing (although there are tools to do this, I've never used them). If you are just trying to back it up there is a pretty simple back up command.

Re: SQLite the only database you will ever need in most cases (2021)

#57

Post by doodlesdev »

Gigachad wrote: post #41
xwdv wrote: post #37

Why learn SQLite when you could just learn Postgres and have a database that is virtually guaranteed to be enough in almost all cases?

HN users pride themselves on finding the least capable tool for the job that only just works for the task but no more. It’s not about logic or practicality. It’s that they feel some kind of mental pain using Postgres as it is too “bloated”.

To be fair, if you were to consider yourself an engineer (which I imagine many of HNers would) that's essentially your whole job, overall what you want is to get the requirements fulfilled with the least complexity, cost, time, etc. If deployment difficulty or hardware usage is a consideration in the requirements then it makes sense to try and use a lighter-weight "serverless" database (SQLite doesn't use a client-server model, so it's serverless, got it??? I'll see myself out).

Re: SQLite the only database you will ever need in most cases (2021)

#59

Post by russelg »

Gigachad wrote: post #41
xwdv wrote: post #37

Why learn SQLite when you could just learn Postgres and have a database that is virtually guaranteed to be enough in almost all cases?

HN users pride themselves on finding the least capable tool for the job that only just works for the task but no more. It’s not about logic or practicality. It’s that they feel some kind of mental pain using Postgres as it is too “bloated”.

Or maybe its because SQLite is just... easier to deploy? Cheaper? There are many reasons to choose it over a "fatter" solution.
Post reply on HN