Skip to content

Latest commit

 

History

History
159 lines (103 loc) · 6.04 KB

hosting-sql-database-projects.md

File metadata and controls

159 lines (103 loc) · 6.04 KB
title author description ms.date
SQL Database Projects hosting
erikej
A .NET Aspire hosting integration for publishing SQL Database Projects from your AppHost.
11/12/2024

.NET Aspire SQL Database Projects hosting integration

[!INCLUDE includes-hosting]

[!INCLUDE banner]

In this article, you learn how to use the .NET Aspire SQL Database Projects hosting integration to publish your database schema to your SQL Server database.

Prerequisites

This integration requires a SQL Database Project based on either MSBuild.Sdk.SqlProj or Microsoft.Build.Sql.

Hosting integration

To get started with the .NET Aspire SQL Database Projects hosting integration, install the 📦 CommunityToolkit.Aspire.Hosting.SqlDatabaseProjects NuGet package in the app host project.

.NET CLI

dotnet add package CommunityToolkit.Aspire.Hosting.SqlDatabaseProjects

PackageReference

<PackageReference Include="CommunityToolkit.Aspire.Hosting.SqlDatabaseProjects"
                  Version="*" />

For more information, see dotnet add package or Manage package dependencies in .NET applications.

Example usage

Add a reference to the 📦 MSBuild.Sdk.SqlProj or 📦 Microsoft.Build.Sql project you want to publish in your .NET Aspire app host project:

dotnet add reference ../MySqlProj/MySqlProj.csproj

Note

Adding this reference will currently result in warning ASPIRE004 on the project due to how references are parsed. The .NET Aspire team is aware of this and we're working on a cleaner solution.

Add the project as a resource to your .NET Aspire AppHost:

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql")
                 .AddDatabase("test");

builder.AddSqlProject<Projects.MySqlProj>("mysqlproj")
       .WithReference(sql);

Now when you run your .NET Aspire app host project you see the SQL Database Project being published to the specified SQL Server.

NuGet Package support

Starting with version 9.2.0, you can deploy databases from referenced NuGet packages, such as those produced by 📦 MSBuild.Sdk.SqlProj or 📦 Microsoft.Build.Sql. To deploy, add the NuGet package to your Aspire app host project, for example:

dotnet add package ErikEJ.Dacpac.Chinook

Next, edit your project file to set the IsAspirePackageResource flag to True for the corresponding PackageReference, as shown in the following example:

<PackageReference Include="ErikEJ.Dacpac.Chinook" Version="1.0.0"
                  IsAspirePackageResource="True" />

Finally, add the package as a resource to your app model:

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql")
                 .AddDatabase("test");

builder.AddSqlPackage<Packages.ErikEJ_Dacpac_Chinook>("chinook")
       .WithReference(sql);

Note

By default, the .dacpac is expected to be located under tools/<package-id>.dacpac. In the preceding example, the tools/ErikEJ.Dacpac.Chinook.dacpac path is expected. If for whatever reason the .dacpac is under a different path within the package you can use WithDacpac("relative/path/to/some.dacpac") API to specify a path relative to the root of app host project directory.

Local .dacpac file support

If you are sourcing your .dacpac file from somewhere other than a project reference, you can also specify the path to the .dacpac file directly:

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql")
                 .AddDatabase("test");

builder.AddSqlProject("mysqlproj")
       .WithDacpac("path/to/mysqlproj.dacpac")
       .WithReference(sql);

Support for existing SQL Server instances

Starting with version 9.2.0, you can publish the SQL Database project to an existing SQL Server instance by using a connection string:

var builder = DistributedApplication.CreateBuilder(args);

// Get an existing SQL Server connection string from the configuration
var connection = builder.AddConnectionString("Aspire");

builder.AddSqlProject<Projects.SdkProject>("mysqlproj")
       .WithReference(connection);

builder.Build().Run();

Deployment options support

To define options that affect the behavior of package deployment, call the WithConfigureDacDeployOptions API:

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql")
                 .AddDatabase("test");

builder.AddSqlProject("mysqlproj")
       .WithConfigureDacDeployOptions(options => options.IncludeCompositeObjects = true)
       .WithReference(sql);

builder.Build().Run();

The preceding code:

  • Adds a SQL server resource named sql and adds a test database resource to it.
  • Adds a SQL project resource named mysqlproj and then configures the xref:Microsoft.SqlServer.Dac.DacDeployOptions.
  • The SQL project resource depends on the database resource.

Redeploy support

If you make changes to your SQL Database project while the app host is running, you can use the Redeploy custom action on the .NET Aspire dashboard to redeploy your updates without having to restart the app host.

See also