Options for integrating SQL that work on both local and cloud

[CarlGraff]

I have been struggling to find a way to integrate SQL server that works both while running locally and publishing to the cloud.
I have watched many Youtube videos and various other documentation that describe different methods of doing this but none of them seem really that good. Perhaps I am missing something that exists that is better than these approaches.

Here is host code that allows connection to an existing local SQL development server complete with migrations working as expected and also allows publishing to provision (if needed) and perform migrations as expected. Note that is is also trivial to connect to either using a tool like SSMS.

=======================================
using Microsoft.AspNetCore.Hosting;
using System;

var builder = DistributedApplication.CreateBuilder(args);

var cache = builder.AddRedis("cache");

// ASPIRE TOGGLE AI PROMPT:
// Toggle Aspire mode in Program.cs to the opposite of its current setting by commenting/uncommenting lines marked ASPIRE_MODE:LOCAL and ASPIRE_MODE:PUBLISH as appropriate.

// ASPIRE_MODE:LOCAL
//var azureSql =
// builder.AddConnectionString("aspiretest-db");

//var migrations =
// builder.AddProject<Projects.AspireApp2_MigrationService>("migrationservice")
// .WithReference(azureSql);

// ASPIRE_MODE:PUBLISH
var azureSql =
builder.AddSqlServer("aspiretest-sql").AddDatabase("aspiretest-db");

var migrations =
builder.AddProject<Projects.AspireApp2_MigrationService>("migrationservice")
.WithReference(azureSql)
.WaitFor(azureSql);

var apiService = builder.AddProject<Projects.AspireApp2_ApiService>("apiservice")
.WithReference(azureSql) // API service needs the database connection
.WithReference(migrations) // API service depends on the migration service
.WaitFor(migrations) // Crucial: API service waits for migrations to complete before starting
.WithReference(cache)
.WaitFor(cache);

builder.AddProject<Projects.AspireApp2_Web>("webfrontend")
.WithExternalHttpEndpoints()
.WithReference(cache)
.WaitFor(cache)
.WithReference(apiService)
.WaitFor(apiService);

builder.Build().Run();

Issue with this is of course I have to Uses the appropriate code for running locally and uses the appropriate code for publishing - a hack I know but I had to weigh this against the other options that i found. Real issue is that the code analysis is very aggressive won't allow me to do things like this:

#if DEBUG
do local stuff
#else
do publish stuff
#endif

or

if (env.IsDevelopment())
do local stuff
else
do publish stuff

I am looking at this:
AzureSqlExtensions.PublishAsAzureSqlDatabase Method
and this
var data = builder.AddAzureSqlServer("data")
.RunAsContainer();

But I am not sure if these will help Any suggestions are welcome.

[engineering87]

Hello @CarlGraff,
you can try to avoid #if DEBUG and aggressive env.IsDevelopment() checks by making the switch fully configuration-driven.
Put local and cloud SQL definitions in config (appsettings.Development.json / appsettings.Production.json or environment variables) and resolve them at runtime with builder.Environment.

var azureSql = builder.Environment.IsDevelopment()
    ? builder.AddConnectionString("LocalSqlConnection")
    : builder.AddSqlServer("aspiretest-sql").AddDatabase("aspiretest-db");

This way you keep one code path, just different config per environment.
For local dev you could also .RunAsContainer() with SQL Server, and for cloud use PublishAsAzureSqlDatabase() in your deployment process.
Keep migrations and dependencies the same, only the database provider changes based on config.

[CarlGraff]

Thanks for the reply,

I did try this:
var azureSql = builder.Environment.IsDevelopment()
? builder.AddConnectionString("LocalSqlConnection")
: builder.AddSqlServer("aspiretest-sql").AddDatabase("aspiretest-db");

And it works fine when ran locally on SQL Develoment addition assuming the database is already created, but, there is an issue when publishing to azure as it thinks the connectionstring is missing and prompts you for it.

This is what I am working with now and it works fine in version 9.4.0:

IResourceBuilder sqlDb = default;
if (builder.ExecutionContext.IsPublishMode)
{
sqlDb = builder.AddAzureSqlServer("aspiretest-sql").AddDatabase("aspiretest-db");
}
else
{
sqlDb = builder.AddConnectionString("aspiretest-db");
}

The key was finding this:
if (builder.ExecutionContext.IsPublishMode)
I don't know why I had so much trouble finding this is various sample code and tutorials I viewed but it sure makes sense.

So this does everything I wanted

Publish mode:

1. Creates Azure SQL and Database (defaults to free tier).
2. Uses EntraId (managed id) and creates secure wiring to other apps.
3. Does migrations just fine using when linked to a standard migration project (I can provide details if anyone is interested).
4. Can connect to db with SSMS.
5. DbContext injection is simple and straight forward.

Local

1. Connect to local sql server.
2. Uses SQL username / password auth.
3. Migrations same as 3. above
4. Can connect to db with SSMS
5. DbCortext injection is the same code as 5. above.

Next I will try local as SQL in container.

Best,
Carl