Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

First Contact with SQLite

brandur.org

Post reply on HN
Print view View original

41–50 of 99 posts

Re: First Contact with SQLite

#41

Post by mbforbes »

It’s interesting to read a lot of push back to the points here.

I recently built a product with the backend using SQLite as the data store and ran into all these issues and many more. It is frustrating. I use SQLAlchemy and Alembic. It seemed everywhere I turned, the docs said “it works this way in all databases, except SQLite where X isn’t supported or you have to do Y differently.”

I think with litestream and D1 and other web SQLite tech emerging, you see the sentiment: “if you don’t have Google-scale, you can easily serve using disk-backed SQLite, plus enjoy skipping RTT network latency to DB.” Then, when someone does that and has a bad time, the comments instead go: “SQLite is only for embedded data stores.”

Personally, if I had to do it again, I would stick to the most boring tech for the target stack: Postgres and Django’s ORM.

Re: First Contact with SQLite

#42

Post by simonw »

silvestrov wrote: post #26
simonw wrote: post #19

Advanced alter table operations do involve creating a new table, but the pattern for doing that is actually pretty robust: you start a new transaction, create the new table, copy the data across and then atomically swap the table names before you commit: https://www.sqlite.org/lang_altertable.html#otheralter My favourite feature of my sqlite-utils CLI tool is the "transform" command which implements this pattern for…

This does not work (easily) when other tables have "foreign key" constraints to the table. You need to manually recreate them.

Yeah, my solution doesn't automatically handle that (yet).

That's why it provides a --sql option - if it doesn't entirely handle your particular case you can instead get it to generate SQL for you without executing it, so you can make modifications you need before running it.

Re: First Contact with SQLite

#43

Post by simonw »

Worth noting that this is from Brandur's "atoms" series: https://brandur.org/atoms - "Multimedia particles in the style of a tweet"

So it's more a short set of notes (like a TIL) than a full-fleshed blog post. Brandur's long-form writing has a different tone: https://brandur.org/articles

Re: First Contact with SQLite

#44

Post by Gormo »

alberth wrote: post #39

I wish SQLite would officially fork itself and: - So make breaking changes as needed. (it was first released in 2000 and has fantastic backwards compatibility, but hardware & OS have radically changes over the last 24 years - as well as use cases). - put more focus on client/server use cases - make things more 'strict' (types, checks, etc) Note: I say this with tremendous love for SQLite. There's just so many attempt…

I guess I don't really understand the point of expecting distinct solutions to all converge toward the same general case. All of the criteria on your list are already fulfilled by Postgres, MySQL, etc. -- why dilute the optimality of SQLite for its specialized use cases just to target other use cases that are already served superbly with other tools?

This is like arguing how great bicycles would be if they had four wheels, enclosed cabins, and gasoline engines.

Re: First Contact with SQLite

#45

Post by natrys »

yread wrote: post #34

Funny! Next do a "First contact with c++ as a Python developer without reading a manual"

Do you think reading the manual is supposed to make those problems disappear or make them less frustrating for some reason? These kind of personal attacks just because their opinion differs from yours is low effort, specially given that they clearly mentioned "official recommendation" in multiple places implying they did go through the manual.

Re: First Contact with SQLite

#46

Post by andrewstuart »

mbforbes wrote: post #41

It’s interesting to read a lot of push back to the points here. I recently built a product with the backend using SQLite as the data store and ran into all these issues and many more. It is frustrating. I use SQLAlchemy and Alembic. It seemed everywhere I turned, the docs said “it works this way in all databases, except SQLite where X isn’t supported or you have to do Y differently.” I think with litestream and D1 an…

>> I would stick to the most boring stack

Just use sql. Very straightforward and easy to use program understand maintain.

Re: First Contact with SQLite

#47

Post by liveoneggs »

tazu wrote: post #8
eknkc wrote: post #6

I feel like the recent hype around SQLite made people use it for a lot of stuff that is not really suitable for SQLite. It has too many caveats especially around data types. I'm not saying it does not deserve the attention, it is a fantastic piece of software but if I had the option to use PostgreSQL for something I'd never ever get close to choosing SQLite over it. It shines when you don't need or want something mor…

> if I had the option to use PostgreSQL for something I'd never ever get close to choosing SQLite over it I had the option for a recent project. It's a niche forum-like application with around 2,000 users. Went with a monolithic design and vertical scaling (if needed), so SQLite was perfect. Every dynamic HTML page renders in under 1ms. Litestream for live-replication to a couple S3 buckets. Running PostgreSQL for so…

the real shock is how many websites, powered by wordpress, use mysql instead!

Re: First Contact with SQLite

#49

Post by ysw0145 »

SQLite has its own decisions, to keep itself embeddable and low-footprint. There are many choices on the server but little on embedded devices like mobile phones. I have faced many problems that arise when using SQLite in a cross-platform mobile app, such as corruption and deadlock. However, there are almost no proven alternatives to SQLite.

Re: First Contact with SQLite

#50

Post by troupo »

andrewstuart wrote: post #46
mbforbes wrote: post #41

It’s interesting to read a lot of push back to the points here. I recently built a product with the backend using SQLite as the data store and ran into all these issues and many more. It is frustrating. I use SQLAlchemy and Alembic. It seemed everywhere I turned, the docs said “it works this way in all databases, except SQLite where X isn’t supported or you have to do Y differently.” I think with litestream and D1 an…

>> I would stick to the most boring stack Just use sql. Very straightforward and easy to use program understand maintain.

> Just use sql. Very straightforward and easy to use program understand maintain.

In the end SQLAlchemy and Alembic generate and execute SQL. The weird behaviours are due to sqlite's idiosynchasies

Post reply on HN