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

241–250 of 378 posts

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

#241

Post by hummus_bae »

lerax wrote: post #143

saying the obvious for the new comers, sometimes is really necessary in that generation of master of overengineers and not only that, but with pretty useless and unecessary abstractions

What the author says is true, but the implementation in the linked sqlite isn't.

The cache is implementation defined.

The locking is implementation defined.

The index does not work on foreign keys.

The planner isn't thread safe.

The only reasons for 90% of use cases to use sqlite are that it can be embedded and it has an sql parser.

In a major application written by a company with an actual software engineering department, you would use Postgres or MySQL.

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

#242

Post by sanderjd »

naniwaduni wrote: post #178
sanderjd wrote: post #158

Earlier quoted context omitted.

I've realized that this whole thing comes down to the word "most" doing too much duty here. I don't think it's true that most applications just run a single db-and-application node. I've never worked on such an application. You and the author do seem to think this is true. It would be difficult for either of us to support our intuition empirically, so this is where the divide comes from.

> I don't think it's true that most applications just run a single db-and-application node. I've never worked on such an application. Rather than applications you've worked on (many of us spend years on end working on a narrow range of applications), consider software you use (most of us flip between multiple applications every day spanning the gamut of uses). Ignore, for a moment, whatever you know about their imple…

Yes I think pretty much all the applications I interact with require fault tolerance and uptime that (to me) seems simpler to implement with separation between application and database nodes.

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

#243

Post by berdon »

JohnBooty wrote: post #199
berdon wrote: post #151

Earlier quoted context omitted.

Fwiw, I’ve used all of those in my career, extensively here and there. I’ve written many thousands of lines of bash scripts. Even written my own php-for-bash-script style code tags that support arbitrary shells. I’ve written my own log based distributed kv store, gone down the YouTube trail of writing my own db, gone through the angular and react iterations, deep dove in docker, docker compose, k8s, crds and custom h…

I've dabbled in the whole stack as you've said. I've written a web framework, done some Docker, done some fun Javascript demos, shipped web and desktop code in a bunch of languages. Built and maintained my own physical servers back when that was a thing people did. Windows, Linux. Currently relearning C and 68K game development (at a glacial pace) as a side project. Did assembly language in college. Ran a business. D…

I heard a story that the inventor of python was once asked in an interview at Amazon to rate himself from 1-10 in python. The interviewer didn’t know who he was and it was just a standard question. He said 7.

I don’t think you have to be a 10 to be a good. But you might need to be 10 to be exceptional. And I will agree that it would be very challenging to be a 7, let alone an 10, across multiple domains at the same time. Certainly, across months/years one’s focus might wander and thus their exceptionality might as well. Being able to adapt and pick up new things at a 7 level though - is the real FSD.

But I see your point and agree with you about the shallowing of the FSD term.

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

#244

Post by srcreigh »

benhoyt wrote: post #186
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…

Just this morning I was wondering about where to host the next iteration of my side project (GiftyWeddings.com). It currently uses Go + SQLite, and I deploy it to a small $9/month instance on AWS, but the setup involves a bunch more Ansible and messing around than I want, and I'm not even sure the Ansible scripts I wrote a few years ago would work anymore (or work a second time). A simpler PaaS-like system that works…

I don’t get this. Go can be compiled to a single binary containing SQLite. Scp the executable and run it. This will work on any Linux vm without any setup…

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

#245

Post by sanderjd »

yawaramin wrote: post #221
sanderjd wrote: post #167

Earlier quoted context omitted.

It is honestly difficult for me to imagine what kind of applications people are working on that have these - to me - very lax reliability constraints. But we're just disagreeing over what "most" applications are like, based on our experiences, without any empirical data to say either way, so :shrug:.

WordPress is the obvious example of an application that can run like 99% of instances just fine with SQLite.

Interesting!

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

#246

Post by jbc1 »

SloopJon wrote: post #195
ericHosick wrote: post #67

Earlier quoted context omitted.

> Of course, you'll then end up paying $15+/mo for Postgres, which is hilarious for most hobby projects storing 50MB of data. Supabase ( https://supabase.com/pricing ) has an amazing free tier for PostgreSQL which gives you up to a 500MB database. Note: I'm not affiliated in any way with supabase.com.

One significant caveat: "Free projects are paused after 1 week of inactivity." I'm not complaining, but I reckon a lot of hobby projects would see sporadic activity. For example, I used to upload a list of my CDs to a VPS, so that when I found something interesting at a music store, I could check to see whether I already own it (#FirstWorldProblems).

If you're going a week without activity then SQLite is basically as overkill as PostgreSQL is.

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

#247

Post by aww_dang »

cutler wrote: post #98
irskep wrote: post #80

Earlier quoted context omitted.

Hey that's really cool, thanks! I'll consider adding a link to it in the repo. I'm a little skeptical that any given PostgreSQL free tier will stick around indefinitely, after what happened with Heroku. And once you hit 500MB, you jump immediately to $25/mo, so if you're running a hobby project, your choice is either to delete data or start paying $300/year. On the other hand, I'd expect a well-optimized read-heavy S…

PasS my ass. What's so difficult about setting up a VPS and installing PostgreSQL, MySQL or whatever floats your boat? At Hetzner.com (no I don't work for them) you can get a dual-CPU VPS with 4Gb RAM, 40Gb SSD and 20TB traffic. That'll get you off to the races with Spring Boot and PostgreSQL if you limit the JVM to half the available RAM. For something like Rails, Laravel or Express even easier.

For that price you can get a $10 dedicated server with similar specs. Installing and configuring Postgres isn't a big deal. There are several helpful guides available. For small projects the config isn't important at all. For everything else it is just editing a few lines in a text file.

I'm not sure where the impression that this is some arcane art comes from. For a typical Debian system it is just an apt-get. Yes, the default config has tiny limits for work_mem. Just edit the .conf

Part of this might be that AWS or whichever other proprietary environment has captured developer knowledge at this point. Instead of sane INSTALL files, projects tell users to use a prepacked Docker image.

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

#248

Post by srcreigh »

chasil wrote: post #48
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…

The problem is when you start to approach oltp. This is not a space for sqlite. I have thought about jacking inotify into sqlite_busy_handler() to get the exclusive writers doing better than random waits, but this full api isn't exposed to PHP (where I need it), and doing it at C would suggest alternate approaches, maybe even with xargs at the shell. Oracle has a DBWR process that manages itself, but a write-heavy ap…

I don’t get this. SQLite can perform 500k writes/s (or 5k write txns/s). What app are you building which requires more than this?

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

#249

Post by danielmarkbruce »

paranoidrobot wrote: post #237
danielmarkbruce wrote: post #56

Earlier quoted context omitted.

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.

I think we disagree on the "most cases" aspect that For most of the things I touch, having a single-point-of-failure data store is typically not acceptable for production environments, except perhaps caching.

If you are a good software engineer, you probably work at a place that needs good software engineers, which by definition tends to be a place with a system that needs high availability or low latency or both or something else, while serving a lot of customers or load or something, all of which basically results in a pretty complicated distributed system...

So in your day to day job as a good software engineer, yeah sqlite isn't going to be a good choice. It doesn't work well for the sorts of systems you work on. But if you were going to build a new social network for your college or something trivial like that, and you just want to move fast and have a crazy simple deployment setup, you might well use sqlite.

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

#250

Post by benhoyt »

srcreigh wrote: post #244
benhoyt wrote: post #186

Earlier quoted context omitted.

Just this morning I was wondering about where to host the next iteration of my side project (GiftyWeddings.com). It currently uses Go + SQLite, and I deploy it to a small $9/month instance on AWS, but the setup involves a bunch more Ansible and messing around than I want, and I'm not even sure the Ansible scripts I wrote a few years ago would work anymore (or work a second time). A simpler PaaS-like system that works…

I don’t get this. Go can be compiled to a single binary containing SQLite. Scp the executable and run it. This will work on any Linux vm without any setup…

That's what I do now. But there's a bunch more setup:

- Install and configure Caddy to terminate the SSL. Caddy is great, but still stuff to think about and 20 lines of config to figure out.

- Configure systemd to run Caddy and my Go server. Not rocket science, but required me figuring out systemd for the first time and the appropriate 25-line config file for each server.

- Scripts to upgrade Caddy when a new version comes out (it wasn't in the apt repos when I did this).

- Ansible setup scripts to clone the repo, create users and groups for Caddy and my Go server, copy config files, add cron jobs for backups (150 lines of Ansible YAML).

It looks like you don't need most of this, or get it without additional configuration with Fly.io and Render.

It's kind of like the difference between Dropbox and "you can already build such a system yourself quite trivially by getting an FTP account, mounting it locally with curlftpfs, and then using SVN or CVS on the mounted filesystem. From Windows or Mac, this FTP account could be accessed through built-in software." (https://news.ycombinator.com/item?id=28153080)

Post reply on HN