sql-schema

cwoodruff's avatarfrom cwoodruff

Database Architect skill. Use this when you need to modify the database schema, add tables, or seed data. This project uses RAW SQL SCRIPTS orchestrated by .NET Aspire, NOT Entity Framework Migrations.

⭐5stars🔀3forks📁View on GitHub🕐Updated Jan 7, 2026
[SQL and Databases]

When & Why to Use This Skill

This Database Architect skill provides a specialized framework for managing SQL Server schemas and database orchestration using raw SQL scripts. Specifically designed for .NET Aspire environments, it enables developers to modify schemas, add tables, and manage seed data without the overhead of Entity Framework Migrations. By leveraging a 'Script-Load' pattern, it ensures precise control over database versioning and idempotent deployments, making it an essential tool for high-performance database architecture.

Use Cases

  • Schema Evolution: Adding new tables, columns, or constraints to an existing SQL Server database using raw T-SQL scripts while maintaining a single source of truth.
  • Environment Orchestration: Automating database initialization and script execution within .NET Aspire AppHost to ensure consistent environments across development and production.
  • Idempotent Data Seeding: Implementing safe, repeatable scripts for seeding initial data and creating database views using 'IF NOT EXISTS' patterns to prevent deployment errors.
  • Legacy or Custom Migrations: Managing database changes in projects where Entity Framework Migrations are prohibited or where direct SQL control is required for performance and clarity.
namesql-schema
descriptionDatabase Architect skill. Use this when you need to modify the database schema, add tables, or seed data. This project uses RAW SQL SCRIPTS orchestrated by .NET Aspire, NOT Entity Framework Migrations.

SQL Schema Management Skill

Overview

MoreSpeakers.com uses a "Script-Load" pattern via .NET Aspire (MoreSpeakers.AppHost) to initialize the SQL Server container. We DO NOT use dotnet ef migrations.

Source of Truth

The database schema is defined in scripts/database/.

Workflow

1. Identify the Change

Determine if you need a new table, a modified column, or new seed data.

2. Locate the Loading Logic

Check src/MoreSpeakers.AppHost/AppHost.cs. Look for the sqlText concatenation logic:

var sqlText = string.Concat(
    File.ReadAllText(Path.Combine(path, @"../../scripts/database/create-database.sql")),
    " ",
    File.ReadAllText(Path.Combine(path, @"../../scripts/database/create-tables.sql")),
    // ... other files
);

3. Apply the Change

Option A: Modify Existing Files (Preferred for clean slate)

  • If adding a core table, add T-SQL to scripts/database/create-tables.sql.
  • If adding a view, use scripts/database/create-views.sql.

Option B: Create New Script (For specific updates)

  1. Create a new file, e.g., scripts/database/update-schema-features.sql.
  2. CRITICAL: You MUST update AppHost.cs to include this new file in the string.Concat list, or it will be ignored.

4. Writing SQL

  • Idempotency: Always write idempotent SQL. The script may run on container startup.
    IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'MyTable')
BEGIN
    CREATE TABLE MyTable (...)
END
  • Foreign Keys: Ensure referenced tables are created before the table defining the key (check file order in AppHost.cs).

5. Verification

  • Run dotnet run --project src/MoreSpeakers.AppHost.
  • The container will spin up and execute the concatenated SQL script.

PROHIBITED ACTIONS

  • ❌ dotnet ef migrations add
  • ❌ dotnet ef database update
  • ❌ Modifying the DbContext OnModelCreating without adding corresponding SQL scripts.