> SQLite has commands to rename columns (this is somewhat new). Which other migration is not supported without new table/copy/drop old table process?
adding or dropping any constraints, including nullability, foreign keys, check constraints, changing the structure of the primary key, etc. changing a type also, even given SQLite's squishy typing model.
pretty much anything is not allowed except adding and renaming columns.
> Also, MySQL can't run a migration on a FK-constrant table without downtime.
That's not an issue for the overwhelming vast majority of MySQL databases in production, which are to be clear not running or aspiring to run at Facebook / OLTP-level scales. A table with a few million rows can be migrated in seconds, the table gets locked for a few seconds, everything keeps running after a brief pause. This is not a problem for the "most cases" use case the article refers towards.
> To do this you need an online schema migration tool which generally requires the absence of foreign keys.
if you are running at Facebook / OLTP scales or aspiring to be, then yes. Otherwise, not usually. The article here is referring to SQLite being used for "most cases", not just "running at Facebook / OLTP -level scales". Running at Facebook / OLTP-level scales is still one of the places where you most certainly would *not* be using SQLite for your primary database.
Faire used ghost for MySQL migrations in 2018. We’d run some migrations in a blocking fashion if they were on a smaller table, but the smaller ones still took a few seconds. I suspect the delay would be very noticeable (15s+) for data sizes far less than at FB.
adding or dropping any constraints, including nullability, foreign keys, check constraints, changing the structure of the primary key, etc. changing a type also, even given SQLite's squishy typing model.
pretty much anything is not allowed except adding and renaming columns.
> Also, MySQL can't run a migration on a FK-constrant table without downtime.
That's not an issue for the overwhelming vast majority of MySQL databases in production, which are to be clear not running or aspiring to run at Facebook / OLTP-level scales. A table with a few million rows can be migrated in seconds, the table gets locked for a few seconds, everything keeps running after a brief pause. This is not a problem for the "most cases" use case the article refers towards.
> To do this you need an online schema migration tool which generally requires the absence of foreign keys.
if you are running at Facebook / OLTP scales or aspiring to be, then yes. Otherwise, not usually. The article here is referring to SQLite being used for "most cases", not just "running at Facebook / OLTP -level scales". Running at Facebook / OLTP-level scales is still one of the places where you most certainly would *not* be using SQLite for your primary database.