Hi AspNetZero and fellow coders (AIs included),
Are there any instructions/documentation on how to support two different databases in parallel?
Some background:
- Our application is based on ASP.NET CORE & Angular, version 12.1.0. and is using Hangfire.
- The database is MSSQL 2019 and it includes views, functions and stored procedures, integrted in the migration as external scripts/seeds.
- The entities are defined in dedicated configurations (IEntityTypeConfiguration) and integrated in AppDbContext (i.e. modelBuilder.ApplyConfiguration(new UserInvitationConfiguration());)
The requirement is to support PostgreSQL 15 in parallel, using as much as possible the same base code. That doesn't mean to connect to two databases at the same time, but to be able to deploy the application using either MSSQL or PostgreSQL.
Following the tutorial, I managed to completely re-create the application and use Postgres, by removing the existing migrations and rewriting the views, functions and stored procedures
What I couldn't figure out is: how to structure the code in a way that we maintain compatibility with the two databases in the same base code. This would be the expected scenario:
- Maintain two migration sets, one for each database
- Update/Create a database, targeting one of the migration sets and relative seed scripts
- Start the application dynamically, considering:
- Database type in AppDbContextConfigurer (builder.UseSqlServer(connectionString) vs builder.UseNpgsql(connectionString);)
- Hangfire start in Program.cs ( config.UseSqlServerStorage(_appConfiguration.GetConnectionString("Default")); vs config.UsePostgreSqlStorage(_appConfiguration.GetConnectionString("Default"));)
Any suggestions are greatly appreciated.
Best regards
4 Answer(s)
-
1
Hi @swx
I think, making those changes only when you need to use a different database is easier than maintaining two databases at the same time. If you want to support both databases in your codebase, you may create two git branches (one for SQL Server and one for PostgreSQL) and keep them synced.
-
0
Thanks @ismcagdas
There are pros and cons for each scenario, but the two branches approach was for me the worst case. It's easier to separate the code, but it adds another layer in the deployment process and possible errors.
-
0
Hi @swx
Yes, I understand. Keeping migrations for two database providers in the same project and keeping them up to date is also a big problem I think. So, these are the options come to my mind at the moment.
-
0
@ismcagdas Thanks for following up I managed to achieve my goal and keep everything in the same branch, without too many conditions.
I used the connection string to distinguish between MSSQL (Server=) and PostgreSQL (Host=). There were only a few places where I had to use the condition: Startup.cs, AppDbContextConfigurer.cs and AppDbContext.cs To separate the migrations I created two class projects, one for each migration, and used the flag -Project to run the migration updates (e.g.: "add-migration Initial_Migration -Project Project.App.Migrator.DatabaseType")
