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

131–140 of 378 posts

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

#131

Post by RajT88 »

msm_ wrote: post #123
RajT88 wrote: post #58

Earlier quoted context omitted.

And scripting. Most white collar jobs these days involve software, so most white collar jobs probably would benefit from being able to script.

I partially agree, but then I remember my high school IT lessons, where people in my class (our profile was math and IT, mind you) struggled with excel and very basic programming. Scripting may sound trivial for people reading HN, but certainly is not for everyone. Not to mention that to really benefit from scripting you need programs that you can actually execute. As far as I know Windows (which most people use) is…

Powershell and whatever else can that instantiate COM objects can edit excel files.

Windows is a lot more friendly than you realize. Probably powershell is a lot more powerful than you realize.

I'll also make the point that people who are doing OK in excel probably can model things in their head well enough to get into scripting. Powershell also has a one-liner "export to *.csv" cmdlet, which is pretty amazingly handy.

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

#132

Post by anyfoo »

VyseofArcadia wrote: post #127
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.

>learning these things will make you a better developer. Of webapps. Will make you a better developer of webapps. It may surprise you to learn that there are more kinds of software than that. Sometimes the final product is something like a binary that runs outside the browser, and deployment is no more complex that putting an tar.gz'd executable on a website.

I'm not sure what that has to do with sqlite. I'm very much a non-web-developer, I never use Docker and "deployments" and stuff, but sqlite is absolutely trivial to install and run, and use for non-webapp stuff? Even operating system components use it.

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

#133

Post by sroussey »

capableweb wrote: post #112
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…

Depends exactly what you mean with "durable". One machine with RAID10 can be pretty durable and solves the most common problems with disk issues, other risks can be managed too.

Ah, that brings back memories. Had 2 RAID 10 MySQL servers run for a decade without rebooting. One had an app db, the other a stats db, and the two replicated to each other.

Spinning disks and all, I was terrified to reboot them and have the boot disk fail (which was not on RAID).

The main disks failed once or twice which slowed the servers down considerably until rebuild of the raid finished. Very nervous time.

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

#134

Post by JohnBooty »

bogwog wrote: post #86
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…

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

    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.
Yes. True. However, it is also true that we have a limited amount of time per week and a limited number of weeks on Earth. Time spent learning sysadmin-y stuff is less time spent mastering developer-y stuff. Think about what it means to be a "full stack" engineer in 2023:

  - Unix literacy including common tools (awk, sed, grep, etc)
  - Docker, etc.
  - Relational data stores
  - Key/value and document stores
  - Base frontend technologies (HTML, CSS)
  - Frontend frameworks and the associated language
  - Build pipelines to tie it all together
I've never worked with anybody who's deeply knowledgeable at all of them, or even most of them. I would submit that it is practically impossible to master all of them while also holding a full-time job and writing and shipping production code, and staying "current" on all of the above, unless you're willing to go way past 40-hour weeks.

I've grown extremely disenchanted with "modern" software development. Your typical full stack engineer is frankly bad at most of the damn stack and they're not great at any of it. Nobody masters anything anymore. You get people with IQs three standard deviations above normal thinking they need to spin up an armada of AWS crap just to render "Hello World" in 2023. And they still manage to make a mess of it. I can't tell how much of this is brain damage and how much of it is a sociopathic commitment to resume-driven development.

In practicality, I'm a fan of "T-shaped" engineers. Master a piece or two of the stack, and have light knowledge of the adjacent layers and what they do. e.g. If you are an application-level coder you should know what Docket/K8s/etc are and when/why you might use it and when you should eschew them. And honestly I think it should be eschewed fairly often.

The places you can go with a bit of good old vertical scaling are pretty impressive in 2023. A single box with e.g. 24 cores, 64GB of RAM, and fast solid-state storage is a god damn supercomputer when it comes to spitting out web pages. Think about how far Stack Overflow got with that model and at which point you might start to outgrow them.

---

edit: By "mastery" I mean "fairly comprehensive competency" and not "be a recognized global authority." You can fluently use 50-75% of what the tool offers and you know what the other 25%-50% is so you can learn it and utilize it when needed. You can ship code using this tool that is maintainable and performant. For example, perhaps you've never utilized partitioning in Postgres but you know what it is and you understand the appropriate use case for it so you can learn employ it when needed.

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

#135

Post by kcb »

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?

Yea that is my thought as well. With how easy it is these days to spin up managed Postgres or compatible DB services it really seems unnecessary to not just start with that.

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

#136

Post by jay_kyburz »

bob1029 wrote: post #111

SQLite does it all if you look closely enough. Even for performance it can turn out to be the best option. If you dare combine (properly-configured) SQLite with a local NVMe disk, you will find yourself well beyond what many hosted solutions can provide (clustered or otherwise). To be clear - the biggest reason for this is the incredible latency reduction, not raw IO bandwidth or disk IOPS (although this helps massiv…

I really wish I could get reliable fast internet here at home. I would be perfectly happy to be serving my sites from a computer under my desk.

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

#137

Post by chillfox »

irskep wrote: post #52
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.

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…

I haven't looked into how everyone else does migrations in SQLite.

The way I do it is to check the DB version `PRAGMA user_version;` on application start and run migrations if required, before the app starts taking connections. Yes, this means my app will be unavailable for a few seconds when upgrading. If you want a zero downtime solution then you will have to do it in a different way.

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

#138

Post by butlerm »

danielmarkbruce wrote: post #116
sanderjd wrote: post #102

Earlier quoted context omitted.

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

Third paragraph of the article says: "In contrast to many other database management systems, SQLite is not a client-server database engine, but you actually very rarely need that. If your application software runs on the same physical machine as the database, which is what most small to medium sized web applications does, then you probably only need SQLite." That's how we square it. It's right there in the article.

That is a claim, not an explanation. Satisfactory answers need to be provided for the typical requirements that a small to medium web app might have, or the claim is unjustified.

(Nearly) zero down time is a common requirement. Can a live backup be made while transactions are in progress? That appears to be the case. What about schema changes? Can columns or indexes be added without interfering with access to the tables in question? And so on.

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

#139

Post by irjustin »

> If your application software runs on the same physical machine as the database, which is what most small to medium sized web applications does, then you probably only need SQLite.

That's the adoption problem. It has a hard ceiling. Besides embedded, most engineers use a different DB professionally - dare I say pg or mysql? And because of that they'll reach for the tool they already know.

Sure, I get the argument that SQLLite fits "more exactly" for many many applications because the sheer number that never move off of one machine, but it has this hard ceiling of "what if I need more" and well "I could just use this other tool that goes the full distance, in case one day... oh and I already use it at work."

That's why SQLLite excels for embedded applications. It fits perfectly. There is no "what if" and the performance is astounding esp in low power.

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

#140

Post by sroussey »

itake wrote: post #108
killingtime74 wrote: post #78

Earlier quoted context omitted.

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.

Depends on what you are doing. But a hobby project should definitely just do downtime, lol.

If your db does lots of work, I buy new ones, install them in the datacenter, bring them up from backup.

BTW: you skipped a step for a busy database: warming it up with shadow traffic.

Under certain scale just switching like you describe will cause just as much downtime.

Post reply on HN