Think before seeding data
Today, myself and a colleague both needed to re-build our local databases for different reasons. This was in a .Net MVC application so I fired up SQL Management Studio and deleted the database. Then I ran my migrations and they were failing. The error message was
Cannot insert the value NULL into column ‘COMMAND_ROLE_ID’, table ‘COMMAND’; column does not allow nulls. UPDATE fails.
The statement has been terminated.
What caused this was that previously we had created a migration that was used to seed the database with some test data and initial roles. I wanted to implement Identity V2 which has it’s own Roles table, so I created another migration that dropped my existing Role table. This left a null value where any Foreign Keys once existed. All was good until my next task where I wanted to make Roles mandatory for a table. Entity Framework did its thing and created a migration which ran after I manually updated the required columns on my local database. I put this down to incompatible data in my local environment and though nothing more of it, forgetting that the data was seeded in an earlier migration.
Now when we went to run the migrations again to re-build the database. We both faced the same data issue again and it needed to be fixed otherwise we would keep facing it every time the database was re-built for an environment. Luckily the application is still in early development and we are waiting on a Continuous Integration server to be provided so the only place it existed was on our 2 development environments. This allowed me to just delete the seed migration and re-run the migrations.
The root of the problem was that by seeding the test data in the database it tied the data to the early data model. When we needed to change the model, the data caused us issues because there were null values in columns that we no longer wanted null values.
In the future a better approach would be to keep a SQL script with test data, that can be manually run by the development team. This can be included in the source control of the project and version controlled, but it will need to be manually run to get the data into the database.
Another thing I have seen is that in Rails there are gems that can populate your database for you when you enter a command in the terminal. The Gem is Faker and you can tell it what type of data to put in certain columns, e.g for a name it would insert a random name. I will need to look and see if anything like this exists for .Net.
This would have been a bigger issue if the application had of been deployed and then my fix wouldn’t have worked. I would have had to truncate the tables using migrations and get the new migration to run in the correct order or manually edit the data in the database.
Let me know if you have had any similar experiences and what you did in the comments below. It would be great to hear from others.