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

61–70 of 378 posts

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

#61

Post by bravura »

cldellow wrote: post #51
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 syst…

Thank you for the thoughtful response.

I was looking at https://github.com/irskep/cheapo_website from commenter irskep above, and they make a nice point that render.com has automatic daily backups, solving 4)

However, in another comment they mention "You can't(?) run migrations from another process" and that "people don't talk about the completely ordinary need to run migrations on a database".

I guess this is also the piece that I'm missing. How do I run migrations? Do I deploy a new version with the migration and temporarily take down the server? I'm glad to do that.

I guess I'm also walking through this because---as I said---I'd love just to switch to SQLite but I'm still not sure how many simple non-esoteric gotchas will pop up.

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

#62

Post by danielmarkbruce »

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…

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.

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

#63

Post by irskep »

samtho wrote: post #55
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.

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

I think it's more that PaaS vendors aren't interested in first-class SQLite support when they can sell overpriced managed Postgres instead. Sure, it doesn't scale the same way, so it's hard to move upmarket and sell to Enterprise, but it's a shame that there's no one-click solution like there is for a managed database.

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

#64

Post by plonq »

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.

Doesn't django use sqlite by default?

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

#65

Post by deathclassic »

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?

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.

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

#66

Post by gerdesj »

massysett wrote: post #19
deathclassic wrote: post #11

I like sqlite as much as the next guy but it's built-in datatypes are limited. Things like arrays, UUIDs, geometry stuff, JSON, etc. Sure you can store more advanced stuff as blobs or text but then you have to mess around with deserializing it in the host language and you lose the ability to query it directly in the db engine.

I agree. For my little applications I've looked at Postgres because it has much richer data types, but I can't justify the huge complexity increase of Postgres. So SQLite it is.

There are some right old noddies around here! You (masstsett) expressed a preference for something with some working shown and ended up in DV land.

That's not fair on many levels and reflects harsher on the casual readership hereabouts than yourself.

Your comment is probably rated stellar by the time I hit enter ...

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

#67

Post by ericHosick »

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…

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

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

#68

Post by acuozzo »

deathclassic wrote: post #42
briHass wrote: post #38

Earlier quoted context omitted.

The biggest one missing is date and/or time. The workarounds all suck: - Store the date as a huge, wasteful string in ISO8601 format - Store it as Unix epoch seconds - Store it as a fractional Julian day Besides the first one, you have to remember how the date is stored and ensure all client libraries handle the conversion. If you want to view or manipulate the latter 2 formats in SQL, you need to chain a bunch of co…

Also valid. I just use ISO8601 and bite the bullet because storage is cheap.

FWIW, storage is cheap, but caches are not.

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

#70

Post by bravura »

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.

Agreed perhaps, but I still don't want to devops Postgres and I'd rather having tiny hosting costs for tiny apps. So I think there's a definite need here for this tooling.
Post reply on HN