`SqlConnection.OpenWithCreate()`?

[mitchdenny]

Context

One of the friction points that we have around databases in .NET Aspire is that for local development we don't automatically create the databases for the user. In deployment scenarios we often do create the database because we emit (in the case of AZD) Bicep that creates the database resource for the various databases that we support in the tree.

I've been looking at various options to decorate the app model with some code to support creating the database but we need to be careful to not suddenly make the app host require client side dependencies just to support this scenario (which could lead to dependency management issues since the AppHost would logically have a dependency thing on everything).

We could create a special package for each database resource type which includes this creation logic but that seems like a sledge hammer.

Proposal

Stepping back a bit the main issue we have is that the database doesn't exist when you first got to connect to it. Tools like EFCore have some extensive plumbing to work around this where you can ensure that the database exists - but we don't have that if say you just want to use SqlConnection directly. The SqlConnection.Open(...) method doesn't allow us to specify that we want the database to be created if it doesn't exist.

But what if it did?

What if we had an API like this:

SqlConnection.OpenWithCreate()

We could potentially provide an extension method in the Aspire application libraries to plaster over this issue. It would mirror what is done in EF today where if the database does not exist it connects to the same server using the default database (it parses the connection string using the builder) and then calls CREATE DATABASE.

[mitchdenny]

@davidfowl @DamianEdwards @eerhardt

[eerhardt]

I don't think this is the right level to provide this API. If this is generally valuable, it should be in SqlConnection itself. Aspire components generally don't try to wrap or augment the underlying library's APIs, but rather provide the glue necessary to augment an existing client library with the missing pieces to satisfy the "Aspire requirements" (telemetry, config, health checks, etc).

We should be asking ourselves "What about Aspire is making this a problem? Why don't others hit it?" I think the answer is:

By default, .NET Aspire starts the database server from scratch on every F5. Typical application development experiences against databases don't do this. Outside of the "app" someone creates the database server, the database, and the tables. This happens once and doesn't need to happen again on every F5.

We could create a special package for each database resource type

We did this already in #2986.

[davidfowl]

@roji Thoughts?

[mitchdenny]

If this is generally valuable, it should be in SqlConnection itself

I actually agree. And part of filing this issue and putting up the PR is to facilitate the conversation. What we'd basically be asking here is for the various ADO.NET providers to implement an overload for the Open() and OpenAsync() methods.

It would take some time for that to filter through the ecosystem, so the question is are we happy with folks having to implement this themselves in the meantime?

For example if https://github.com/dotnet/sqlclient decided to take this on, would we need to wait until .NET 9.0 for it?

[eerhardt]

so the question is are we happy with folks having to implement this themselves in the meantime?

I don't think they need to implement this themselves. I think there are other things that can be done in the AppHost that would make this experience better:

1. builder.AddSqlServer("server").AddDatabase("database"); actually creates the database.
2. Databases use persisted data by default. So the DB isn't wiped away between F5s
3. Optionally, database servers stay running and are re-connected between F5s, so I can look at and modify the data in the database without the whole app running.

[eerhardt]

Note also that "database creation" is only the tip of the issue. The app also needs the tables created, and optionally seeded with data.

[davidfowl]

The reason database creation comes up often is because it looks like the apphost does create the database (AddDatabase). The user didn't call AddTable so there shouldn't be an expectation that the tables would be created.

[mitchdenny]

• builder.AddSqlServer("server").AddDatabase("database"); actually creates the database.

I think this is probably the way customers expect it to work for local development. There are really two broad approaches to make this work:

1. Add a dependency on Microsoft.Data.SqlClient to Aspire.Hosting.SqlServer and add a hook that creates the database once the server is online.
2. Inject some script/configure an initialization container to create the database.

I've tried option 2 above but its so clunky that it makes me want to just do option 1 ... but I don't like that because it'll end up pulling all the client libraries into the AppHost as a dependency which could create a bit of a socialisation issue. Perhaps we can do it this way but provided we have enough abstraction we can change the implementation down the track.

• Databases use persisted data by default. So the DB isn't wiped away between F5s

There is always a first run. I think if we don't provide people the ability to git clone then F5 then we've missed a big part of the promise of Aspire.

• Optionally, database servers stay running and are re-connected between F5s, so I can look at and modify the data in the database without the whole app running.

I think we'll need to make this work for sure.

[nycdotnet]

I spent some time looking at Aspire this past week and I think this issue and its friend "running db migrations" are the biggest blocker for us trying it with one of our real apps.

We've developed our own libraries internally which do a lot of what Aspire intends to do - effectively wrapping 3rd party NuGet packages and ensuring they have a consistent base config with the ability to override as needed - and making sure all of that works nicely with DI and observability, etc. We do lean into docker-compose fairly heavily on our end. We don't use Entity Framework - we prefer Dapper (the Micro-ORM) and use Flyway for DB migrations.

In our gRPC microservices-oriented environment, our workflow for getting started with an existing repo on a new machine is typically 1) clone, 2) use docker-compose run ... to protogen, 3) use docker-compose up ... to run the DB migrations. In our docker-compose files, the migration services have depends_on properties that represent the relevant database technologies (generally either or both of SQL Server and Postgres). 4) If we are troubleshooting locally we can use docker-compose up to run the Jaeger all-in-one container or Graphite.

With the above, we lean somewhat heavily on an up-to-date readme in each repo, and very heavily on docker expertise among our developers, but overall we're not typically more than 2 or 3 commands away from pressing F5 and getting something reasonable working locally, and we deploy to Kubernetes anyway so the need for comfort with containers is already baked-in.

In this talk at 1:14, @DamianEdwards says that .NET Aspire makes it easy to discover, acquire, and configure essential dependencies for cloud-native apps on day 1 as well as day 100 ... etc. Well making sure there is a DB to connect to (and that the relevant tables are there) sounds like "configuring essential dependencies" for a significant number of apps out there.

Since you're leaning into Docker anyway, I wonder if you'd be willing to support some sort of custom tasks feature on the Aspire developer portal which could plug into something that might run as part of a GitHub Actions, or Azure DevOps Workflow, or K8s job, etc. It would be really cool if for example there was a way to define in the Aspire app's Program.cs a task which would show as a button in the portal.

Let's say you could do something like this:

And then buttons would light up in the portal like this:

Letting developers implement their own tasks from a context that Aspire manages also lets those tasks be instrumented and use all the nice stuff that Aspire gives like instrumentation, and even be debuggable (put a breakpoint on line 10 above, for example). This could either be regular C# code or (ideally) allow integrating with running a docker container/compose or even a console or powershell command. This way, you as the Aspire team plug into existing stuff and don't need to reinvent the universe as Aspire.*, but this also solves your problem that any sufficiently complex app has some sort of setup step that will need to be supported. This also solves the problem of "when does this run?" (whenever the developer wants to) and does not depend on implementing persistent containers (https://github.com/dotnet/aspire/issues/923) . The output from the above would feed into the existing observability stuff implemented by Aspire - like if my DB migration task fails I might see that in the console tab or structured logs.

I could see a developer running performance tests through this (imagine integrating k6.io as a task) or integration tests, or a dozen other things. And if the developers implement their tasks in this way, the same code could also be deployed and run in the cloud (jobs, CI tasks, etc) becoming a virtuous cycle.

[atrauzzi]

Lots of angles here, yeah. I've just added a simple CLI application as an app to my Aspire host. It runs, does whatever chores and then exits.

The problem for me is centered around the fact that the DB doesn't exist prior, so I have to pass the database connection. And then yeah, I guess all the other known concerns around "not recreating it every time".

I would love the ability to register tasks that could be triggered from the Aspire dashboard, that would be useful. Would also be nice to see Aspire become EF Core Migration aware. I could create a migration bundle app I suppose and I could probably hack that into running using the connection info provided by Aspire...

[mitchdenny]

@nycdotnet what you proposed is not totally out of line with what we were thinking. We had been agonizing a fair amount about bringing client library dependencies into the AppHost. But we've made peace with that now having evaluated some of the other options. If client library dependencies present a problem for folks, then in those specific cases they would need to work around it.

We are doing some experimentation at the moment around an eventing mechanism which would allow firing off logic like creating a database.

[davidfowl]

The features we're building to implement stop/start/restart commands and to enable waiting for dependencies (the eventing model @mitchdenny mentions) will enable building something like the tasks above. Resources will be able to implement commands backed by code that runs in the app model.

This will not work when deployed and is a local development experience only.