Skip to content

README.md

Latest commit

 

History

History
326 lines (237 loc) · 13.6 KB

README.md

File metadata and controls

326 lines (237 loc) · 13.6 KB

Database Integration

Introduction

In this module, we will integrate a PostgreSQL database with our application. We will use Entity Framework Core (EF Core) to interact with the database. Additionally, we will set up PgAdmin to manage our PostgreSQL database.

Setting Up PostgreSQL

Aspire provides built-in support for PostgreSQL through the Aspire.Hosting.PostgreSQL package. To set up PostgreSQL:

  1. Install the required NuGet package in your AppHost project. The easiest way is using the Aspire CLI:
aspire add postgresql

Alternatively, add the package reference manually:

<PackageReference Include="Aspire.Hosting.PostgreSQL" Version="13.1.0" />
  1. Update the AppHost's Program.cs to add PostgreSQL:
var postgres = builder.AddPostgres("postgres")
    .WithDataVolume(isReadOnly: false);

var weatherDb = postgres.AddDatabase("weatherdb");

The WithDataVolume(isReadOnly: false) configuration ensures that your data persists between container restarts. The data is stored in a Docker volume that exists outside the container, making it survive container restarts. This is optional for the workshop—if you omit it, the sample still runs; you just won't keep data between runs.

Enhanced Database Initialization

Aspire provides the method WithInitFiles() for all database providers, replacing the more complex WithInitBindMount() method:

var postgres = builder.AddPostgres("postgres")
    .WithDataVolume(isReadOnly: false)
    .WithInitFiles("./database-init");  // Simplified initialization from files

This method works consistently across all database providers (PostgreSQL, MySQL, MongoDB, Oracle) and provides better error handling and simplified configuration. Using WithInitFiles is optional for this workshop; the database integration works without it.

To ensure proper application startup, we'll configure the web application to wait for the database:

var web = builder.AddProject<Projects.MyWeatherHub>("myweatherhub")
    .WithReference(weatherDb)
    .WaitFor(postgres)  // Ensures database is ready before app starts
    .WithExternalHttpEndpoints();

Adding Container Persistence to Your Database

For development scenarios where you want your database container and data to persist across multiple application runs, you can configure the container lifetime:

var postgres = builder.AddPostgres("postgres")
    .WithDataVolume(isReadOnly: false)
    .WithLifetime(ContainerLifetime.Persistent);  // Container persists across app restarts

var weatherDb = postgres.AddDatabase("weatherdb");

With ContainerLifetime.Persistent, the PostgreSQL container will continue running even when you stop your Aspire application. This is optional and not required to complete the module. If enabled, it means:

  • Faster startup times: No need to wait for PostgreSQL to initialize on subsequent runs
  • Data persistence: Your database data remains intact between application sessions
  • Consistent development: The database stays in the same state you left it

Note

Persistent containers are mainly for local development convenience. In production you'll typically rely on a managed database service (Azure Database for PostgreSQL, Azure Cosmos DB for PostgreSQL, etc.) or external infrastructure that already guarantees durability.

Need finer control? Aspire also supports:

  • WithExplicitStart() — manually coordinate start order
  • WithContainerFiles() — inject init scripts and assets
  • WithInitFiles() — simplified cross-database initialization

Learn more: Persist data using volumes · Container resource lifecycle

Integrating EF Core with PostgreSQL

  1. Install the required NuGet packages in your web application:
<PackageReference Include="Aspire.Npgsql.EntityFrameworkCore.PostgreSQL" Version="13.1.0" />
  1. Create your DbContext class:
public class MyWeatherContext : DbContext
{
    public MyWeatherContext(DbContextOptions<MyWeatherContext> options)
        : base(options)
    {
    }

    public DbSet<Zone> FavoriteZones => Set<Zone>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Zone>()
            .HasKey(z => z.Key);
    }
}
  1. Register the DbContext in your application's Program.cs:
builder.AddNpgsqlDbContext<MyWeatherContext>(connectionName: "weatherdb");

Note that Aspire handles the connection string configuration automatically. The connection name "weatherdb" matches the database name we created in the AppHost project.

  1. Set up database initialization:
if (app.Environment.IsDevelopment())
{
    using (var scope = app.Services.CreateScope())
    {
        var context = scope.ServiceProvider.GetRequiredService<MyWeatherContext>();
        await context.Database.EnsureCreatedAsync();
    }
}

For development environments, we use EnsureCreatedAsync() to automatically create the database schema. In a production environment, you should use proper database migrations instead.

Updating the Web App

Now we'll update the web application to support favoriting weather zones and filtering them. Let's make these changes step by step:

  1. Make sure to add these Entity Framework using statements at the top of Home.razor if they're not already present:
@using Microsoft.EntityFrameworkCore
@inject MyWeatherContext DbContext
  1. Add these new properties to the @code block to support the favorites functionality:
bool ShowOnlyFavorites { get; set; }
List<Zone> FavoriteZones { get; set; } = new List<Zone>();
  1. Update the OnInitializedAsync method to load favorites from the database. Find the existing method and replace it with:
protected override async Task OnInitializedAsync()
{
    AllZones = (await NwsManager.GetZonesAsync()).ToArray();
    FavoriteZones = await DbContext.FavoriteZones.ToListAsync();
}
  1. Finally, add the ToggleFavorite method to handle saving favorites to the database. Add this method to the @code block:
private async Task ToggleFavorite(Zone zone)
{
    if (FavoriteZones.Contains(zone))
    {
        FavoriteZones.Remove(zone);
        DbContext.FavoriteZones.Remove(zone);
    }
    else
    {
        FavoriteZones.Add(zone);
        DbContext.FavoriteZones.Add(zone);
    }
    await DbContext.SaveChangesAsync();
}
  1. In the @code block of Home.razor, locate the zones property and replace it with this updated version that includes the favorites filter:
IQueryable<Zone> zones
{
    get
    {
        var results = AllZones.AsQueryable();

        if (ShowOnlyFavorites)
        {
            results = results.Where(z => FavoriteZones.Contains(z));
        }

        results = string.IsNullOrEmpty(StateFilter) ? results
                : results.Where(z => z.State == StateFilter.ToUpper());

        results = string.IsNullOrEmpty(NameFilter) ? results
                : results.Where(z => z.Name.Contains(NameFilter, StringComparison.InvariantCultureIgnoreCase));

        return results.OrderBy(z => z.Name);
    }
}
  1. First, add a checkbox to filter the zones list. In Home.razor, add this code just before the <QuickGrid> element:
<div class="form-check mb-3">
    <input class="form-check-input" type="checkbox" @bind="ShowOnlyFavorites" id="showFavorites">
    <label class="form-check-label" for="showFavorites">
        Show only favorites
    </label>
</div>
  1. Next, add a new column to show the favorite status. Add this column definition inside the <QuickGrid> element, after the existing State column:
<TemplateColumn Title="Favorite">
    <ChildContent>
        <button @onclick="@(() => ToggleFavorite(context))">
            @if (FavoriteZones.Contains(context))
            {
                <span>&#9733;</span> <!-- Starred -->
            }
            else
            {
                <span>&#9734;</span> <!-- Unstarred -->
            }
        </button>
    </ChildContent>
</TemplateColumn>

Testing Your Changes

Now let's verify that your changes are working correctly by testing the favorites functionality and database persistence:

  1. Start the application:

    • In Visual Studio: Right-click the AppHost project and select "Set as Startup Project", then press F5
    • In VS Code: Open the Run and Debug panel (Ctrl+Shift+D), select "Run AppHost" from the dropdown, and click Run

  2. Open your browser to the My Weather Hub application:

    • Navigate to https://localhost:7274
    • Verify you see the new "Show only favorites" checkbox above the grid
    • Check that each row in the grid now has a star icon (☆) in the Favorite column

  3. Test the favorites functionality:

    • Use the Name filter to find "Philadelphia"
    • Click the empty star (☆) next to Philadelphia - it should fill in (★)
    • Find and favorite a few more cities (try "Manhattan" and "Los Angeles County")
    • Check the "Show only favorites" checkbox
    • Verify that the grid now only shows your favorited cities
    • Uncheck "Show only favorites" to see all cities again
    • Try unfavoriting a city by clicking its filled star (★)

  4. Verify the persistence:

    • Close your browser window
    • Stop the application in your IDE (click the stop button or press Shift+F5)
    • Restart the AppHost project
    • Navigate back to https://localhost:7274
    • Verify that:
      • Your favorited cities still show filled stars (★)
      • Checking "Show only favorites" still filters to just your saved cities
      • The star toggles still work for adding/removing favorites

If you want to reset and start fresh:

  1. Stop the application completely
  2. Open Docker Desktop
  3. Navigate to the Volumes section
  4. Find and delete the PostgreSQL volume
  5. Restart the application - it will create a fresh database automatically

Note: The Zone type is a record, so equality is by value. When the UI checks FavoriteZones.Contains(context), it's comparing by the record's values (like Key/Name/State), which is the intended behavior for favorites.

Other Data Options

In addition to PostgreSQL, Aspire provides first-class support for several other database systems:

Azure SQL/SQL Server

SQL Server integration in Aspire includes automatic container provisioning for development, connection string management, and health checks. It supports both local SQL Server containers and Azure SQL Database in production. The integration handles connection resiliency automatically and includes telemetry for monitoring database operations.

MySQL

The MySQL integration for Aspire provides similar capabilities to PostgreSQL, including containerized development environments and production-ready configurations. It includes built-in connection retries and health monitoring, making it suitable for both development and production scenarios.

MongoDB

For NoSQL scenarios, Aspire's MongoDB integration offers connection management, health checks, and telemetry. It supports both standalone MongoDB instances and replica sets, with automatic container provisioning for local development. The integration handles connection string management and includes retry policies specifically tuned for MongoDB operations.

SQLite

While SQLite doesn't require containerization, Aspire provides consistent configuration patterns and health checks. It's particularly useful for development and testing scenarios, offering the same familiar development experience as other database providers while being completely self-contained.

Community Toolkit Database Features

The Aspire Community Toolkit extends database capabilities with additional tooling:

SQL Database Projects

The SQL Database Projects integration enables you to include your database schema as part of your source code. It automatically builds and deploys your database schema during development, ensuring your database structure is version controlled and consistently deployed. This is particularly useful for teams that want to maintain their database schema alongside their application code.

Data API Builder

Data API Builder (DAB) automatically generates REST and GraphQL endpoints from your database schema. This integration allows you to quickly expose your data through modern APIs without writing additional code. It includes features like:

  • Automatic REST and GraphQL endpoint generation
  • Built-in authentication and authorization
  • Custom policy support
  • Real-time updates via GraphQL subscriptions
  • Database schema-driven API design

Conclusion

In this module, we added PostgreSQL database support to our application using Aspire's database integration features. We used Entity Framework Core for data access and configured our application to work with both local development and cloud-hosted databases.

The natural next step would be to add tests to verify the database integration works correctly.

Head over to Module #8: Integration Testing to learn how to write integration tests for your Aspire application.

Next: Module #8: Integration Testing