0

I have a scenario where I deploy to different environments (development, testing and production), and each of these databases use different table name prefixes, along with different schema names.

Currently, in my CD pipeline I generate an idempotent script, and do string replacement on my 'variables' in my migration, however I'd like to utilize the dotnet ef database update command which makes it tricky as all my past migrations have been generated with a variable name within their table names, etc.

Is it possible that I can read from an environment file what that variable is while creating my DbContext and map/replace/infer what the actual table prefixes are when running the command line?

Edit 1:

  • To make matters a little more complicated, we're using an Oracle database
  • When generating our migrations, we have assistive functions that grant access to certain database users to view the newly added tables. These users too have different names per environment.
  • Reworking the users, table names, etc isn't in scope of this - I'm aware of the inefficiencies
jarodsmk
  • 1,876
  • 2
  • 21
  • 40
  • 1
    I assume you already set the schema by something like `builder.HasDefaultSchema`? – Gert Arnold Jan 04 '23 at 07:54
  • @GertArnold that's correct :) for running the regular solution of our API, our TypeConfigurations substitute an environment prefix for our tables and connect to that relevant database. The trouble is with our migrations, we have certain scripts that give access to different roles. I'll update the question with more information shortly – jarodsmk Jan 04 '23 at 08:29

2 Answers2

0

Without modifying too much your current flow you can do your string replacement before creating the migration and then, for each environment, use a different folder:

dotnet ef migrations add InitialCreate --context DbContext --output-dir Migrations/DevMigrations
dotnet ef migrations add InitialCreate --context DbContext --output-dir Migrations/ProdMigrations

But a cleaner approach would be: as you already know the differences between the various environments you could add that specific information in different db context and manage different DB schema/table names in the code itself of the dedicated DbContext. So in each DBContext you can put default schema name, link the entities to the correct table using a dedicated OnModelCreating and all the info that is different among the environments and then you can evolve each of these migrations:

dotnet ef migrations add InitialCreate --context DevContext --output-dir Migrations/DevMigrations
dotnet ef migrations add InitialCreate --context ProdContext --output-dir Migrations/ProdMigrations

To add the common relations/definitions that are the same for every environment you can use a dedicated IEntityTypeConfiguration to group your "business related" EF core configurations in a common place (the relations for example). This will avoid code duplication and let you have the "infrastructure related" EF core configurations defined in each dedicated DbContext (schema and table names), as they are actually different.

update To address the requirement to assign permissions you can add an empty migration where you can put sql scripts that perform whatever you need

So at the very end, in your pipeline, you can infer from the name of the stage/environment the name of DbContext and the folder where to save/fetch these migrations.

If you follow any of these approaches probably you will need to "sync" the already existing DB, see here and here,in the "Add your baseline migration" part

david-ao
  • 3,000
  • 5
  • 12
  • For the most part of your message, I've solved these issues - however, the roadblock is more migration-specific code where I need to apply environment related naming conventions. For example, granting read/write permissions for tables to various DB-level users which also have environment-specific name prefixes – jarodsmk Jan 10 '23 at 12:09
  • I updated my answer, you can do that inside the migrations, each environment can have its dedicated "permissions migration" – david-ao Jan 11 '23 at 22:34
0

Why don't you simply replicate the entire code? When you scaffold/migrate you get partial classes, isn't it? So, here the steps:

  1. set the environment for Production, with a specific BUILD variable
  2. prepare the migration to the production database. You will get at least the migration partial auto-class.
  3. surround the entire body of the migration (having practically an empty file) within #if PRODUCTION ... #endif
  4. rename the file with some _PRODUCTION suffix in the name. (it's partial class, it will be seen anyway)
  5. prepare for DEVELOPMENT as in step 1.
  6. migrate again, you will get again part of the partial class (the one that disappeared because the build variable changed.
  7. surround everything with another #if DEVELOPMENT ... #endif and change the file name suffixing it with _DEVELOPMENT.cs
  8. loop unil you finish all your enviroments
  9. do the same compiler selection for the variables holding the names of the users. You can be smart here, there should not be anything auto-generated in the class holding the users, so you can define that only class with parts of the names changing again with insertion/removals with #if and #endif.

I never happened to fall in such a weird case, but this would be the first solution I would try. The codebase is not growing as in any instant there is only one enviroment selected, the users get managed and EF can generate its automatic parts independently from your code.

Some side notes:

  • a sandbox or a clone of the database would be more suitable for testing. After you tested the development enviroment, will you test again the production one from scratch?
  • Changing the names of the users with just a prefix to differentiate the environment (without having all the details, I may not catch the full meaning of this) seems to bring small value to the effort of separation.

EDIT: I don't change the previous text because it may drive you on theright path, but it is likely that you should not rename the migration file, otherwise the next migration will create anothe file with the base name. Just surround the entire migration class with the compiler directives, and let EF create another class in the same file at steps 2-3 and 5-6.

cheers Alex

AlessandroParma
  • 141
  • 7