0

I want to add a new non-null column ("department" for example) to an existing entity ("employee" for example). The "employee" table already contains data, when run "$ nest start", I got the following error -

QueryFailedError: column "department" contains null values at QueryFailedError.TypeORMError [as constructor] (E:\repo\todo-js\src\error\TypeORMError.ts:7:9)

I tried to use typeORM migration to add a nullable "department" column and fill in data beforehand but turns out migration take place after typeORM data schema update when "$ nest start" is run. As such I got the above error even after I created the migration. Please note that I do not want to add a default value to the "department" column's definition as new employee insertion should specify the employee's department. How can I proceed with that? Thanks

(Part of the accepted answer is included in the comments below, you better read it all.)

Peter_101
  • 303
  • 4
  • 13

1 Answers1

1

I see at least three ways:

  1. Create nullable department column, fill it with existing data and then make it non-nullable;
  2. Create department column with empty DEFAULT value, fill it with data and then remove DEFAULT;
  3. Create new employee_new table with department column, fill it with data, remove old employee table and rename employee_new to employee.
D.Zotov
  • 2,044
  • 2
  • 14
  • 28
  • Thanks D.Zotov! The three ways all works in my local environment. Yet, in the case of our team, they all could cause the backend of other developers to break. With the first way for example, when another developer pull my updates, the employee entity's department field will be set to non-nullable, once they run "$ nest start", the system will first try to update the database schema, then run my migration script. But if there's data in the employee table, they will see the "column 'department' contains null values" error and the process will stop right there. – Peter_101 Sep 06 '22 at 23:23
  • I just stuck with the same situation when I try to change another field from nullable to non-nullable. Now in the nestjs + typeorm setup, there seems to be no way you can choose to execute some migration scripts before the database schema update happen. Thus, "column 'department' contains null values" could still happen and require manual fixes. I just wonder if there's a way to make the above entity updates to happen smoothly. – Peter_101 Sep 06 '22 at 23:34
  • 1
    @Peter_101, you can use Git Hooks to run your script after each pull. See [related question](https://stackoverflow.com/questions/25011021/how-to-run-a-customized-script-after-a-git-repo-is-pulled-and-actually-updated). – D.Zotov Sep 08 '22 at 08:37