undefined | Better HN

0 pointsdjbusby3y ago0 comments

I have this beef too. Tooling for dumping and restoring into a new schema are easy/simple/fast. So, these schema migrations can happen w/o issue. Some tricks with the PRAGMA directive in SQLite so you can roll out changes (eg: code supports old/new schema while migrating)

0 comments

JohnBooty3y ago

Interesting, that's super cool to read.

   Tooling for dumping and restoring into a new schema are easy/simple/fast.

Any resources you can point to that expand on this? Is this standard SQLite tooling? I'm curious how it would perform with large-ish databases - a few hundred GB or perhaps several TB.

(This is one of those things where I can "just Google it" but I was wondering if perhaps there was a particularly useful article that points out potential gotchas, etc)

djbusbyOP3y ago

I'm just using shell scripts and the SQLite CLI. I dump/restore files to the same FS so I can mv into place when ready.

Checkout the PRAGMA user_version, I just modify on schema change, so app knows where stuff is.

https://www.sqlite.org/pragma.html

tianzhou3y ago

We also used SQLite and leverage PRAGMA user_version to do the trick. But decided to move to PostgreSQL mainly due to the schema migration constraints.

Our experience is SQLite is OK for small to medium projects, but when the business logic/data model becomes more complex, SQLite is not sufficient.

More discussions are captured here https://news.ycombinator.com/item?id=31038614

j / k navigate · click thread line to collapse