sqlserver-frameworks

SQL Server Framework Patterns

EF Core (Code First)

DbContext

• One DbContext per bounded context
• Use IEntityTypeConfiguration<T> for entity configuration (separate files)
• Apply configurations via ApplyConfigurationsFromAssembly
• Use HasDefaultSchema("dbo") for explicit schema

Entity Configuration

• Always specify table name, schema, key, and max lengths
• Use UseIdentityColumn() for auto-increment
• Configure indexes with HasIndex() and HasDatabaseName()
• Configure relationships explicitly — don't rely on conventions for FKs

Migrations

• Name migrations descriptively: AddCustomerEmailColumn, CreateOrdersTable
• Generate idempotent scripts for deployment: dotnet ef migrations script --idempotent
• Never edit a migration after it's been applied to any environment
• Test migration rollback: dotnet ef database update <PreviousMigration>

Dapper

Query Best Practices

• Always use parameterized queries with anonymous objects
• Use QuerySingleOrDefaultAsync for single-row lookups (returns null if not found)
• Use QueryAsync for multi-row queries
• Use ExecuteAsync for INSERT/UPDATE/DELETE
• Use multi-mapping for JOINs with splitOn

Connection Management

• Use using for connections — let them be disposed
• Don't cache or reuse connections — connection pooling handles this
• Use IDbConnectionFactory for testability

DbUp

Script Organization

• V001__Create_Orders_Table.sql — versioned, runs once
• Use embedded resources or filesystem scripts
• Journal table SchemaVersions tracks applied scripts
• Scripts must be idempotent where possible

Flyway

Migration Naming

• Versioned: V1__Create_orders_table.sql (runs once, in order)
• Repeatable: R__Create_views.sql (re-runs when checksum changes)
• Undo: U1__Create_orders_table.sql (rollback)

Best Practices

• One logical change per migration file
• Don't modify already-applied migrations
• Use flyway info to check status before flyway migrate

SSDT Projects

Structure

• One .sqlproj per database
• Organize by schema: dbo/Tables/, dbo/StoredProcedures/, auth/Tables/
• Use schema compare for drift detection
• Generate DACPAC for deployment

Build

• Run dotnet build on the .sqlproj after any schema change — require zero errors before tests
• For a specific project: dotnet build <path-to>.sqlproj
• For a solution containing the SSDT project: dotnet build <path-to>.sln
• Build produces a .dacpac in bin/Debug/ (or bin/Release/) — use it for SqlPackage deployments
• Fix all build errors (unresolved references, syntax errors, missing schema objects) before proceeding