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

91–100 of 378 posts

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

#91

Post by irskep »

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'm not convinced this is true. Django supports SQLite out of the box, and otherwise database management is an exercise for the reader. The migration tools, for example, work fine with SQLite.

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

#92

Post by Arathorn »

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 Synapse with Sqlite, so there’s a risk of perf regressions.

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

#93

Post by acdha »

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.

Why? It’s been supported since the 2000s and is popular for test suites so it gets more exercise than you might think.

https://docs.djangoproject.com/en/4.1/ref/databases/

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

#94

Post by cldellow »

irskep wrote: post #88
cldellow wrote: post #75

Earlier quoted context omitted.

You can run migrations from another process. Migrations are just writes, and SQLite supports writes from multiple processes. A single transaction that does a very large write will likely impact the reader -- the reader will be blocked while the write finishes. I use SQLite in a web scraper on my laptop. The scraper runs as 16 processes hammering the database, doing about 5,000 write transactions/sec. Occasionally, si…

> You can run migrations from another process. Migrations are just writes, and SQLite supports writes from multiple processes. Thank you for explicitly saying this! I'll see if I can update my repo to allow for online migrations. I was trying to be as conservative as possible based on what I knew at the time. It'll be nice to update the migration steps to be simpler.

BTW, for schema migrations, I've found the approach described here [1] to be pretty good!

[1]: https://david.rothlis.net/declarative-schema-migration-for-s...

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

#95

Post by lelandbatey »

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.

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 handle two different processes accessing a JPG? They both open and read the JPG. Same for SQLite. For other concerns, you're trying to do things that don't map well to files, so once again more research needed.

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

#96

Post by guggle »

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.

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.

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

#97

Post by giobox »

xenator wrote: post #84
deathclassic wrote: post #65

Earlier quoted context omitted.

SQLite has other advantages over larger db systems. - By far easiest db to install. - Really go to learn database fundamentals with. If you have no experience with databases and are just starting out programming, PG is going to steepen the learning curve substantially.

> By far easiest db to install. The difference between installation for sqlite and postgres is the difference between typing `apt install sqlite3` and `apt install postgres`. The actual learning curve for sqlite is more complex because picking random sql queries on the internet will end in misunderstanding in underlying types in sqlite and broken data. Postgres tools ecosystem is much more expressive, because more pe…

I'd go further and just point out the obvious - all DBs have incredibly well supported container run options in 2023, that mean you don't have to install the DB at all. I know of no DB that cannot be easily learned or deployed this way - even Microsoft SQLServer has a Linux image these days.

Since the advent of containers for running software, deploying any DB software is largely as easy as any other with a one line run statement, and if container image is cached you are getting a new DB in seconds.

If just starting out, running PG from a container is exactly how I would tell someone to learn today, and the process is largely identical whether they learn on windows, linux or a mac.

I haven't natively installed a DB since probably 2015 for any kind of local development use, its just a waste of your time more often than not now.

> https://hub.docker.com/_/postgres

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

#98

Post by cutler »

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

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.

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

#100

Post by chillfox »

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…

I guess it depends upon how SQLite is configured and what storage it's deployed on. If it's setup with WAL mode and on SSD storage then it should handle thousands of writes a second fairly easily.
Post reply on HN