3 ms·

and if you need to migrate your database schema in any non-trivial way.....well then you're on your own. for anything beyond adding a column to a table, you'l

by zzzeek 4y ago

and if you need to migrate your database schema in any non-trivial way.....well then you're on your own. for anything beyond adding a column to a table, you'll have to copy the whole table to a new one with the structure you want, drop the old table, then rename your new table, carrying along all the foreign key constraints and other constraints while you do so. Or use a tool which does this (I write one such tool and it's not fun to maintain). if SQLite allowed for custom commands, at least there could be ALTER commands that run this process behind the scenes, which the SQLite developers wouldn't have to maintain.

srcreigh 4y ago
SQLite has commands to rename columns (this is somewhat new). Which other migration is not supported without new table/copy/drop old table process? Also, MySQL can't run a migration on a FK-constrant table without downtime. To do this you need an online schema migration tool which generally requires the absence of foreign keys.
- zzzeek 4y ago
  > 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.
  - srcreigh 4y ago
    Fair enough re migrations. That’s annoying 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.

by zzzeek 4y ago

srcreigh 4y ago

SQLite has commands to rename columns (this is somewhat new). Which other migration is not supported without new table/copy/drop old table process? Also, MySQL can't run a migration on a FK-constrant table without downtime. To do this you need an online schema migration tool which generally requires the absence of foreign keys.

zzzeek 4y ago
> 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.
- srcreigh 4y ago
  Fair enough re migrations. That’s annoying 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.