plan-forge database.instructions.md

.NET Database Patterns

ORM Strategy

Option A: Dapper (Micro-ORM)

// Always use parameterized queries
const string sql = "SELECT * FROM users WHERE email = @Email";
var user = await connection.QuerySingleOrDefaultAsync<User>(sql, new { Email = email });

Option B: Entity Framework Core

var user = await _context.Users
    .Where(u => u.Email == email)
    .FirstOrDefaultAsync(cancellationToken);

Non-Negotiable Rules

Parameterized Queries (SQL Injection Prevention)

// ❌ NEVER: String interpolation in SQL
var sql = $"SELECT * FROM users WHERE id = '{userId}'";

// ✅ ALWAYS: Parameters
const string sql = "SELECT * FROM users WHERE id = @UserId";
await connection.QueryAsync<User>(sql, new { UserId = userId });

Connection Management

// ❌ NEVER: Manual connection creation
using var conn = new NpgsqlConnection(connectionString);

// ✅ ALWAYS: Use DI / connection factory
using var conn = await _connectionFactory.CreateConnectionAsync(cancellationToken);

Async with CancellationToken

// ❌ NEVER: Sync database calls
var result = connection.Query<User>(sql);

// ✅ ALWAYS: Async with cancellation
var result = await connection.QueryAsync<User>(sql, cancellationToken: cancellationToken);

Migration Strategy

Non-Negotiable Migration Rules

• NEVER deploy a destructive migration (drop column/table) in the same release that removes the code using it
• ALWAYS review generated SQL before applying to staging or production — dotnet ef migrations script --idempotent
• ALWAYS make migrations backward-compatible — the old version of the app must still work after the migration runs
• ALWAYS test migrations against a copy of production data before applying to production
• NEVER use EnsureCreated() in production — it bypasses the migration pipeline
• ALWAYS run migrations as a separate pipeline step before deploying the new app version