Search code examples
databasesqlitemigrationsequelize.jssequelize-cli

How to drop a non-key column without deleting related records with Sequelize Migrations?

I am currently about to learn how to use Sequelize migrations. Therefore I created some example models and migrated them as well. No I tried to delete a column - the migration works fine for that as long as I had no data inserted.

So here are my example models:

Model1 {id:int, name:string, description:string, fk_model2:int {references Model2:id} } 
Model2 {id:int, name:string, description:string, test:string}

Model1toModel2 {id_model1:int, id_model2:int} //not a real model but a table

There is a 1:n relation between Model1 and Model2, and an n:m relation, too. The n:m relation is represented in the database with a table and is created by it's own migration.

Now there is this problem: I defined a column named test in table Model2 (as shown). After I inserted some example data, I tried to run a migration, which deletes this column. On table Model2 this works just fine.

First try: I used onUpdate: 'cascade', onDelete: 'cascade' in fk_model2-column in table Model1 when creating the tables in a migration file. So the delete-column migration emptied the Model1 table and the Model1toModel2-table which was created for the n:m relation. That is not what I want.

Second try: I used onUpdate: 'cascade', onDelete: 'restrict'. As expected this gives me ERROR: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed as I now cannot delete Model2-data that is used in Model1. Also this is not what I want.

Question: If I migrate a table, does it delete the whole record and recreates it according to the new conditions defined in the migration file? - so the ON DELETE-Condition fires?

How could I achieve that it just deletes the column test which is not a key value, without touching the tables Model1 and Model1toModel2? (Of course, if the key (& therefore the whole record) in Model2 is deleted, I want the records in Model1toModel2 to be deleted, too, and the 1:n in Model1 to be set NULL or DEFAULT.)

Solution

  • I found the problem/solution here: How to delete or add column in SQLITE?

    Sequelize only pretends to drop a column, instead of this it deletes the data and recreates it afterwards as expected. So this causes the related records to drop or throw SQL-Errors.