First Contact with SQLite

There are things that different databases are best for.

This seems very blunty anti-sqlite, and things postgreSQL is the best, so I'd be interested to see a guide for (two things I've used sqlite for in the last week):

* Using postgreSQL to store data in an iPhone app

* Making a small python script which uses PostgreSQL, and then seeing how much work it is to send that to someone else, so they can use your work and extend it (send the database, and also instructions for installing postreSQL, and getting everything set up. Make sure it works on linux, mac and windows).

I think what the author misses is that SQLite's choices make a lot of sense in a world where working with the DB is super easy and you can move a lot of the complexity to the code around it. With traditional databases it used to be the case that you manage everything in the DB, because it's expensive to call it, and because you don't always know who's going to call it, but that's clearly not a typical scenario for SQLite.

How do you typically deal with "missing features" from SQLite (e.g. stored procedures)? Do you use extensions like https://github.com/nalgeon/sqlean in production?

The author is both ignorant of SQLite history, and philosophy as also ignorant of the real use-cases SQLite solves for embedding a lightweight SQL-ish database into applications.

Looking into his "about" section he worked mostly with web APIs (Stripe, Heroku) and is a self-proclaimed fan of Postgres. Maybe when he acquires some experience working with embedded applications without boatloads of resources available nor a stable network stack he might appreciate SQLite for what it is since the comparison to Postgres is non-sensical when approaching from that viewpoint.

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 you (also available as a Python library method): https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...