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

101–110 of 378 posts

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

#101

Post by adamckay »

deathclassic wrote: post #54
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.

I take it you're not aware that the default database of new Django projects created by the admin commands is SQLite?

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

#102

Post by sanderjd »

danielmarkbruce wrote: post #62
irskep wrote: post #52

Earlier quoted context omitted.

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 you…

It's not sarcasm. The fact that it's a library and you point it at a file matters, and should be thought about. It implies that it's not built for distributed systems. It's not supposed to be a managed service. It's not a good option for what you appear to want. You deploy it as part of your application.. it's a library.

> It's not a good option for what you appear to want.

Ok but the article you're replying in the comments to says "SQLite is all you need for nearly everything", and what the comment you're replying to is describing is, to use their very apt word choice, entirely ordinary.

So how do we square this circle of somebody being told both "SQLite is all you need for everything" and "it is not a good option for your totally ordinary use case"?

I think the answer is that you are a different person than the author of the article and you don't agree with the article's claim. Which, great, that was irskep's entire point!

I tend to agree with irskep that this SQLite architecture is really interesting, but I also don't quite get it. There seem to be a lot of missing details for totally bog standard applications, that people frustratingly seem to just not even mention - like the migrations thing, or how to do zero downtime deploys in general, or a few other things - as if those concepts are irrelevant or uncommon. But they aren't, they're important and typical things to think about.

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

#103

Post by irskep »

guggle wrote: post #96
bogwog wrote: post #86

Earlier quoted context omitted.

> I'm an application developer and I do not want to become a release engineer. I resent even having to learn Docker. :-) Sorry, but that's a terrible attitude to have. You don't need to be a full-blown sysadmin to know how to do basic deployments, and learning these things will make you a better developer.

I don't know why this is downvoted, it's probably the best advice on this page. Learning a few things about deployment definitely helps to make better software.

Well, he did cherry-pick one joking comment out of a well-intentioned post in order to say I have a bad attitude, so that's not exactly welcoming. And very patronizing, considering I have actually done that kind of work with Salt Stack a few years ago, and just don't want to bother with it at the moment because I have other priorities.

I learn new things every day, and one day, Docker was that thing. I am now better at release engineering, cool! But I also lost a day of working on shipping value to users and actually having fun, which is less cool. That's why PaaS has been helpful to certain kinds of people; it makes the curve of progressive disclosure necessary to deploy stuff a little flatter. My hope is that we can make more kinds of things really easy to do the right way, and not yell at people who just want to have some fun.

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

#104

Post by crazygringo »

chillfox wrote: post #76
jxf wrote: post #28

> 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. Am I misunderstanding this or is this not the vast, vast majority of all cases?

Most of the time people separate the app and database into two different VMs that the infrastructure team then runs on the same box. edit: This is done not because of any considered technical reasons, but because that's how one learned to deploy apps.

It's because the very first step in scaling will usually be separate machines for webserver and database.

And it costs almost nothing to write it that way from the start, but it's a pain to separate them out later.

I'd call that a considered technical reason.

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

#105

Post by ripdog »

Arathorn wrote: post #92
ripdog wrote: post #71

I have wondered why Synapse, the most feature-complete Matrix homeserver, so vehemently recommends against use of SQLite as it's backing db. They say that the performance is insufficient and it's only appropiate for testing purposes. That would make sense if you assume that Synapse is only going to be used in instances with hundreds/thousands of users, but plenty of people host their own instances for themselves only…

The problem is that even a single user matrix server can be very resource intensive if that user joins big rooms with thousands of users spread over thousands of servers. Synapse is very database heavy, so the parallelism in Postgres helps a lot - plus some of the hot DB paths have special cased queries for Postgres to use some of its more obscure features that Sqlite lacks. Finally, we don’t dogfood or optimise Syna…

Thank you, Matthew! This would be fantastic information to include in the Synapse documentation, for those of us being seduced by the operational simplicity of SQLite, and posts like this. :)

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

#106

Post by mythz »

Also worth mentioning Fly.io's work on LiteStream [1] and LiteFS [2] giving SQLite important S3 DR/reliability & multi-node replication and scalability - opening SQLite up to even more use-cases.

We're making use of this ourselves in https://blazordiffusion.com which runs entirely on SQLite, using Litestream to replicate it to Cloudflare's R2 object storage which is running on a single Hetzner US Cloud VM at €13 /mo.

As we believe SQLite + Litestream is a very cost effective solution that can support a large number of App's data requirements we've added first-class support to add SQLite + Litestream support in our .NET Project templates [3] which uses GitHub Actions to run Docker compose App deployments along with setting up Litestream replication to AWS S3, Azure Blob Storage and SFTP in a sidecar container that also includes support running DB Migrations on Server with Rollback on failure. If anyone's looking to do something similar, the GitHub Actions + Docker compose configuration that enable this are being maintained at https://github.com/ServiceStack/mix/tree/master/actions

[1] https://litestream.io

[2] https://fly.io/blog/introducing-litefs/

[3] https://docs.servicestack.net/ormlite/litestream

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

#107

Post by VintageCool »

chillfox wrote: post #76
jxf wrote: post #28

> 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. Am I misunderstanding this or is this not the vast, vast majority of all cases?

Most of the time people separate the app and database into two different VMs that the infrastructure team then runs on the same box. edit: This is done not because of any considered technical reasons, but because that's how one learned to deploy apps.

If you are running your webserver and your database on the same box, how are you big enough to have an infrastructure team?

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

#108

Post by itake »

killingtime74 wrote: post #78
itake wrote: post #22

Earlier quoted context omitted.

even with litestream, how do you do deployments? do you just terminate the process and re-launch it on the same machine?

I guess this is an interview level question. 1) Drain connections from your instance. Stop taking new connections and let all existing requests timeout. This could be by removing it from a load-balancer or dns. This ensures your litestream backup is "up-to-date". 2) Bring up the new deployment, it restores by litestream. When restore is complete, register it with the load balancer (if you are using one) or dns. 3) De…

Yes... and all I see here is downtime. How do we do this without services failure? With a postgres db, you can spin up and down ec2 instances to your hearts content. Every 1-4 years, you can upgrade your db by using a replica with no down time.

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

#109

Post by cutler »

guggle wrote: post #96
bogwog wrote: post #86

Earlier quoted context omitted.

> I'm an application developer and I do not want to become a release engineer. I resent even having to learn Docker. :-) Sorry, but that's a terrible attitude to have. You don't need to be a full-blown sysadmin to know how to do basic deployments, and learning these things will make you a better developer.

I don't know why this is downvoted, it's probably the best advice on this page. Learning a few things about deployment definitely helps to make better software.

Agreed. Too much reliance on AWS, Heroku etc. can leave you without vital Linux skills. Linux cli tools are not just for getting a job done, though they're great at what they do. No, they're part of the *nix culture which is what open source software is based on. Only recently have I become aware that it was better to have entered software development in or before the early 2000s, ie. before The Cloud took off. You were forced to learn Linux/BSD cli tools just to swim in the water. I can't imagine, back then, knowing Perl and not knowing Linux or FreeBSD.

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

#110

Post by sanderjd »

lelandbatey wrote: post #95
paranoidrobot wrote: post #44

Earlier quoted context omitted.

"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.

It's a file is relevant. It allows you to think of it like other files: - how do you back up a JPG? You copy it, cause it's a file - how do you replicate it? You copy it, cause it's a file. Unless you're talking about fancy DB replication, in which case well, that's not really a thing we do with files much. You'll have to do more research. but that's cause you're trying to do non-file things to a file. - how do you h…

I don't update and append to jpgs though...
Post reply on HN