SQL Server Database Projects support

[jmezach]

.NET Aspire issue link

https://github.com/dotnet/aspire/discussions/1696

Overview

As discussed in #110 we would like to contribute a new integration that provides support for deploying SQL Server Database Projects as part of an Aspire AppHost project. This will allow developers to quickly iterate on their SQL Server Database schema during their inner loop for people that prefer schema-first design.

Usage example

We are currently proposing something like this:

var builder = DistributedApplication.CreateBuilder(args);

var database = builder.AddSqlServer("sql")
                    .AddDatabase("TargetDatabase");

builder.AddSqlProject<Projects.MySqlServerDatabaseProject>("my-sqlserver-database-project")
       .WithReference(database);

builder.Build().Run();

Upon running the AppHost project this will automatically publish the data-tier application package (.dacpac) created from the referenced project MySqlServerDatabaseProject once the SQL Server is ready (ie. Healthy). Said project could be either one using [MSBuild.Sdk.SqlProj(https://github.com/rr-wfm/MSBuild.Sdk.SqlProj) or Microsoft.Build.Sql.

Alternatively, if only a data-tier application package is available, this could also be deployed directly be leveraging the WithDacpac() method like so:

var builder = DistributedApplication.CreateBuilder(args);

var database = builder.AddSqlServer("sql")
                    .AddDatabase("TargetDatabase");

builder.AddSqlProject("my-sqlserver-database-project")
       .WithDacpac("path/to/somedatabase.dacpac")
       .WithReference(database);

builder.Build().Run();

Additional context

No response

Help us help you

Yes, I'd like to be assigned to work on this item

[ErikEJ]

@jmezach did you see this discussion about the API shape?

https://github.com/CommunityToolkit/Aspire/discussions/110#discussioncomment-11028852

[jmezach]

I did, but I'm not entirely convinced that renaming PublishTo to WithReference is a good idea. I think most developers mental model here is that you either deploy or publish a SQL database project to a server. WithReference doesn't quite make clear the implications of what you are actually doing I think.

[ErikEJ]

@jmezach Agree, it will be hard to discover "WithReference" and grasp what it does.

[aaronpowell]

One of the main things that we want to ensure we do in the Community Toolkit is to stay true to the API design that is established with .NET Aspire, so that there isn't the ".NET Aspire way" and the "Community Toolkit way" when it comes to the API surface area that we expose.

If we look from a high level at the problem space that we have here, you are going to have a SqlProjectResource which is going to need to have a related DatabaseResource in which it will use when the SqlProjectResource starts to run against (or thus is my understanding).

With this in mind, I find that WithReference makes a lot of sense, especially as we're going to be looking at this in the context of a .NET Aspire app host, the database is a reference required by the sql project.

When I look at an API like PublishTo it reads to me more like a once-off deployment job, and while yes, you are deploying a schema/data to a database, it's more an intent to be part of running an application that you run a database provisioner using the available reference.

I hope that makes sense as to why I'm pushing back against the original PoC API design you had.

[jmezach]

Okay, yeah that makes sense I think. I've updated the proposal above and also added a note about deploying a .dacpac sources from anywhere using WithDacpac.

[ErikEJ]

@jmezach I think the sample above should be:

var database = builder.AddSqlServer("sql")
                    .AddDatabase("TargetDatabase");

builder.AddSqlProject<Projects.MySqlServerDatabaseProject>("my-sqlserver-database-project")
       .WithReference(database);

as the AddDatabase method returns a IRescourceBuilder<SqlServerDatabaseResource>

[jmezach]

Good catch, although it is just the variable name really but it does make it a bit more clear.

[ErikEJ]

@jmezach Yeah, just for clarity / understanding

[aaronpowell]

How does this play in the deployment story, whether it's to Azure or elsewhere?

[ErikEJ]

@aaronpowell Not sure what you mean by "this"- the recommended way to deploy a .dacpac is to use the microsoft.sqlpackage .NET tool from a pipeline script / task to deploy the .dacpac as part of app deployment.

[jmezach]

Haven't really thought about the deployment story just yet to be honest. I don't think anything we have currently is blocking deployment, but we might want to do something in that area at some point.

[aaronpowell]

By default, anything that inherits from Resource in Aspire will be generated in the manifest to be used by the deployment tool (such as azd for Azure) unless you explicitly exclude it.

Would we want to have this excluded by default, or is there some particular way you would use that in a IaC deployment pipeline?

[jmezach]

I don't currently know of any IaC tool that is capable of deploying a .dacpac so I think it would be best to exclude it from the manifest by default for now

[jmezach]

Just checking in here. Would it be appropriate to start a PR for this?

[aaronpowell]

Just checking in here. Would it be appropriate to start a PR for this?

Totally @jmezach