Seed data in a database using .NET Aspire

Article
05/01/2024

In this article, you learn how to configure .NET Aspire projects to seed data in a database during app startup. .NET Aspire enables you to seed data using database scripts or Entity Framework Core for common platforms such as SQL Server, PostgreSQL and MySQL.

When to seed data

Seeding data pre-populates database tables with rows of data so they're ready for testing via your app. You may want to seed data for the following scenarios:

Manually develop and test different features of your app against a meaningful set of data, such as a product catalog or list of customers.
Run test suites to verify that features behave a specific way with a given set of data.

Manually seeding data is tedious and time consuming, so you should automate the process when possible. Use volumes to run database scripts for .NET Aspire projects during startup. You can also seed your database using tools like Entity Framework Core, which handles many underlying concerns for you.

Understand containerized databases

By default, .NET Aspire database components rely on containerized databases, which create the following challenges when trying to seed data:

.NET Aspire destroys and recreates containers every time the app restarts, which means by default you have to re-seed your database every time the app restarts.
Depending on your selected database technology, the new container instance may or may not create a default database, which means you might also have to create the database itself.
Even if a default database exists, it most likely will not have the desired name or schema for your specific app.

.NET Aspire enables you to resolve these challenges using volumes and a few configurations to seed data effectively.

Seed data using volumes and SQL scripts

Volumes are the recommended way to automatically seed containerized databases when using SQL scripts. Volumes can store data for multiple containers at a time, offer high performance and are easy to back up or migrate. With .NET Aspire, you configure a volume for each resource container using the ContainerResourceBuilderExtensions.WithBindMount method, which accepts three parameters:

Source: The source path of the volume mount, which is the physical location on your host.
Target: The target path in the container of the data you want to persist.

Consider the following volume configuration code from a Program.cs file in a sample AppHost project:

var todosDbName = "Todos";
var todosDb = builder.AddPostgres("postgres")
    .WithEnvironment("POSTGRES_DB", todosDbName)
    .WithBindMount(
        "../DatabaseContainers.ApiService/data/postgres",
        "/docker-entrypoint-initdb.d")
    .AddDatabase(todosDbName);

In this example, the .WithBindMount method parameters configure the following:

../DatabaseContainers.ApiService/data/postgres sets a path to the SQL script in your local project that you want to run in the container to seed data.
/docker-entrypoint-initdb.d sets the path to an entry point in the container so your script will be run during container startup.

The referenced SQL script located at ../DatabaseContainers.ApiService/data/postgres creates and seeds a Todos table:

-- Postgres init script

-- Create the Todos table
CREATE TABLE IF NOT EXISTS Todos
(
    Id SERIAL PRIMARY KEY,
    Title text UNIQUE NOT NULL,
    IsComplete boolean NOT NULL DEFAULT false
);

-- Insert some sample data into the Todos table
INSERT INTO Todos (Title, IsComplete)
VALUES
    ('Give the dog a bath', false),
    ('Wash the dishes', false),
    ('Do the groceries', false)
ON CONFLICT DO NOTHING;

The script runs during startup every time a new container instance is created.

Database seeding examples

The following examples demonstrate how to seed data using SQL scripts and configurations applied using the .WithBindMount method for different database technologies:

Note

Visit the Database Container Sample App to view the full project and file structure for each database option.

The configuration code in the .AppHost Program.cs file mounts the required database files and folders and configures an entrypoint so that they run during startup.


// SQL Server container is configured with an auto-generated password by default
// but doesn't support any auto-creation of databases or running scripts on startup so we have to do it manually.
var addressBookDb = builder.AddSqlServer("sqlserver")
    // Mount the init scripts directory into the container.
    .WithBindMount("./sqlserverconfig", "/usr/config")
    // Mount the SQL scripts directory into the container so that the init scripts run.
    .WithBindMount("../DatabaseContainers.ApiService/data/sqlserver", "/docker-entrypoint-initdb.d")
    // Run the custom entrypoint script on startup.
    .WithEntrypoint("/usr/config/entrypoint.sh")
    // Add the database to the application model so that it can be referenced by other resources.
    .AddDatabase("AddressBook");

The entrypoint.sh script lives in the mounted ./sqlserverconfig project folder and runs when the container starts. The script launches SQL Server and checks that it's running.

#!/bin/bash

# Adapted from: https://github.com/microsoft/mssql-docker/blob/80e2a51d0eb1693f2de014fb26d4a414f5a5add5/linux/preview/examples/mssql-customize/entrypoint.sh

# Start the script to create the DB and user
/usr/config/configure-db.sh &

# Start SQL Server
/opt/mssql/bin/sqlservr

The init.sql SQL script that lives in the mounted ../DatabaseContainers.ApiService/data/sqlserver project folder creates the database and tables.

-- SQL Server init script

-- Create the AddressBook database
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'AddressBook')
BEGIN
  CREATE DATABASE AddressBook;
END;
GO

USE AddressBook;
GO

-- Create the Contacts table
IF OBJECT_ID(N'Contacts', N'U') IS NULL
BEGIN
    CREATE TABLE Contacts
    (
        Id        INT PRIMARY KEY IDENTITY(1,1) ,
        FirstName VARCHAR(255) NOT NULL,
        LastName  VARCHAR(255) NOT NULL,
        Email     VARCHAR(255) NULL,
        Phone     VARCHAR(255) NULL
    );
END;
GO

-- Ensure that either the Email or Phone column is populated
IF OBJECT_ID(N'chk_Contacts_Email_Phone', N'C') IS NULL
BEGIN
    ALTER TABLE Contacts
    ADD CONSTRAINT chk_Contacts_Email_Phone CHECK
    (
        Email IS NOT NULL OR Phone IS NOT NULL
    );
END;
GO

-- Insert some sample data into the Contacts table
IF (SELECT COUNT(*) FROM Contacts) = 0
BEGIN
    INSERT INTO Contacts (FirstName, LastName, Email, Phone)
    VALUES
        ('John', 'Doe', 'john.doe@example.com', '555-123-4567'),
        ('Jane', 'Doe', 'jane.doe@example.com', '555-234-5678');
END;
GO

Configuration code in the .AppHost project:

var builder = DistributedApplication.CreateBuilder(args);

// PostgreSQL container is configured with an auto-generated password by default
// and supports setting the default database name via an environment variable & running *.sql/*.sh scripts in a bind mount.
var todosDbName = "Todos";
var todosDb = builder.AddPostgres("postgres")
    // Set the name of the default database to auto-create on container startup.
    .WithEnvironment("POSTGRES_DB", todosDbName)
    // Mount the SQL scripts directory into the container so that the init scripts run.
    .WithBindMount("../DatabaseContainers.ApiService/data/postgres", "/docker-entrypoint-initdb.d")
    // Add the default database to the application model so that it can be referenced by other resources.
    .AddDatabase(todosDbName);

Corresponding SQL script included in the app:

-- Postgres init script

-- Create the Todos table
CREATE TABLE IF NOT EXISTS Todos
(
    Id SERIAL PRIMARY KEY,
    Title text UNIQUE NOT NULL,
    IsComplete boolean NOT NULL DEFAULT false
);

-- Insert some sample data into the Todos table
INSERT INTO Todos (Title, IsComplete)
VALUES
    ('Give the dog a bath', false),
    ('Wash the dishes', false),
    ('Do the groceries', false)
ON CONFLICT DO NOTHING;

Configuration code in the .AppHost project:


// MySql container is configured with an auto-generated password by default
// and supports setting the default database name via an environment variable & running *.sql/*.sh scripts in a bind mount.
var catalogDbName = "catalog"; // MySql database & table names are case-sensitive on non-Windows.
var catalogDb = builder.AddMySql("mysql")
    // Set the name of the database to auto-create on container startup.
    .WithEnvironment("MYSQL_DATABASE", catalogDbName)
    // Mount the SQL scripts directory into the container so that the init scripts run.
    .WithBindMount("../DatabaseContainers.ApiService/data/mysql", "/docker-entrypoint-initdb.d")
    // Add the database to the application model so that it can be referenced by other resources.
    .AddDatabase(catalogDbName);

Corresponding SQL script included in the app:

-- MySql init script

-- NOTE: MySql database and table names are case-sensitive on non-Windows platforms!
--       Column names are always case-insensitive.

-- Create the Catalog table
CREATE TABLE IF NOT EXISTS `catalog`
(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `description` varchar(255) NOT NULL,
    `price` DECIMAL(18,2) NOT NULL,
    PRIMARY KEY (`id`)
);

-- Insert some sample data into the Catalog table only if the table is empty
INSERT INTO catalog (name, description, price)
SELECT *
FROM (
        SELECT '.NET Bot Black Hoodie', 'This hoodie will keep you warm while looking cool and representing .NET!', 19.5 UNION ALL
        SELECT '.NET Black & White Mug', 'The perfect place to keep your favorite beverage while you code.', 8.5 UNION ALL
        SELECT 'Prism White T-Shirt', "It's a t-shirt, it's white, and it can be yours.", 12
    ) data
-- This clause ensures the rows are only inserted if the table is empty
WHERE NOT EXISTS (SELECT NULL FROM catalog)

Seed data using Entity Framework Core

You can also seed data in .NET Aspire projects using Entity Framework Core by explicitly running migrations during startup. Entity Framework Core handles underlying database connections and schema creation for you, which eliminates the need to use volumes or run SQL scripts during container startup.

Important

These types of configurations should only be done during development, so make sure to add a conditional that checks your current environment context.

Add the following code to the Program.cs file of your .AppHost project.

// Register DbContext class
builder.AddSqlServerDbContext<TicketContext>("sqldata");

var app = builder.Build();

app.MapDefaultEndpoints();

if (app.Environment.IsDevelopment())
{
    // Retrieve an instance of the DbContext class and manually run migrations during startup
    using (var scope = app.Services.CreateScope())
    {
        var context = scope.ServiceProvider.GetRequiredService<TicketContext>();
        context.Database.Migrate();
    }
}

// Register DbContext class
builder.AddNpgsqlDbContext<TicketContext>("sqldata");

var app = builder.Build();

app.MapDefaultEndpoints();

if (app.Environment.IsDevelopment())
{
    // Retrieve an instance of the DbContext class and manually run migrations during startup
    using (var scope = app.Services.CreateScope())
    {
        var context = scope.ServiceProvider.GetRequiredService<TicketContext>();
        context.Database.Migrate();
    }
}

// Register DbContext class
builder.AddMySqlDataSource<TicketContext>("sqldata");

var app = builder.Build();

app.MapDefaultEndpoints();

if (app.Environment.IsDevelopment())
{
    // Retrieve an instance of the DbContext class and manually run migrations during startup
    using (var scope = app.Services.CreateScope())
    {
        var context = scope.ServiceProvider.GetRequiredService<TicketContext>();
        context.Database.Migrate();
    }
}

Next steps

Database seeding is useful in a variety of app development scenarios. Try combining these techniques with the resource implementations demonstrated in the following tutorials:

Share via