The system is created in a .NET Core with Entity Framework and SQL Server. It's quite big at the moment and it's still growing. In the continuous delivery pipeline, there are many environments e.g.
test1 -> test2 -> test3 -> test4 -> preprod -> prod
Each environment can have different configurations e.g. it can be hosted in Azure, on-premise or in an external private cloud.
There is a need to seed the database with sample data for each of those environments but each environment very often needs to have a different set of data.
I can see two approaches:
Write SQL scripts.
The advantage is that SQL scripts can be run very easily from SSMS or some other console window. The disadvantage I see is it can be horrendous to maintain when the system growths.
I'm thinking about finding the relations and management of Ids.
Create seeder classes in C# which can be run via EF Core.
This solution allows me to create classes which can be easily maintained and in theory I can create some feature toggle which tells which seeder class should be run for a given environment.
If the DB schema changes very often it would require to update all the SQL seeding scripts. The script can become invalid which cannot be automatically detected like in case of using the EF core seeding mechanism.
I've spoken to one developer who told me that it's a bit scary to run automatically such seeder class every time I make a release in the CD pipeline and the Seeder classes for test environments shouldn't be added to the Code Repository.
The question is what is the proper approach and can I use the Entity Framework for the described problem? Cheers
It all depends on your requirements and size of db. In my previous projects we have created SQL scripts but may not be well suited for a continuously changing dB. You can also use data generators such as: