writing-database-queries by bitwarden/ai-plugins

Dual-ORM Architecture

Bitwarden maintains two data access implementations, split by database provider:

MSSQL: Dapper with stored procedures
PostgreSQL, MySQL, SQLite: Entity Framework Core

These implementations are mutually exclusive at runtime — SQL Server uses only Dapper, while the other providers use only EF Core. Both implementations conform to the same repository interfaces.

When adding new repository functionality, implement it in both Dapper and EF Core (unless the feature is explicitly EF-only).
When modifying an existing stored procedure in a backwards-compatible way (for example, adding a new parameter with a default), EF Core changes are not required.
Some commercial features (for example, Secrets Manager) are EF Core only.

Evolutionary Database Design (EDD)

Bitwarden Cloud uses a no-rollback approach to database deployments. The key implication: server deployments can be rolled back, but database migrations cannot, so migrations must be designed to avoid being a source of downtime.

All MSSQL migrations live in util/Migrator/DbScripts/ and execute in chronological order based on the migration filename (YYYY-MM-DD_##_Description.sql).

Note: You may see util/Migrator/DbScripts_transition/ and util/Migrator/DbScripts_finalization/ folders. These are not currently used; ignore them for now.

Simple additive changes (new nullable column, new table, new stored procedure) typically require only a single migration script in util/Migrator/DbScripts/.

Stored procedure compatibility

Stored procedure changes fall into two categories:

Non-breaking (DEFAULT parameters): Adding a parameter with a default value (e.g., @NewParam BIT = NULL) is backwards-compatible. Existing callers keep working; no _V2 is needed.
Breaking (_V2 versioning): Required when result-set structure changes, calling patterns change (e.g., single result → multiple result sets), required parameters are added without defaults, or query semantics differ. Implement this by creating ProcedureName_V2 while retaining the original procedure for backwards compatibility.

Table-level breaking changes (removing columns, changing types) typically cascade into stored procedure changes and often require the _V2 pattern.

Always defer to the developer on migration strategy. The approach is complex and context-dependent. When a database change is needed, write the migration script and ask the developer whether _V2 versioning or additional steps are required.

Key locations

src/Sql/dbo — Master schema source of truth
util/Migrator/DbScripts — All migrations (single folder, chronological)

ORM-Specific Implementation

When implementing Dapper repository methods, stored procedures, or MSSQL migration scripts, activate the implementing-dapper-queries skill.

When implementing EF Core repositories, generating EF migrations, or working with PostgreSQL/MySQL/SQLite, activate the implementing-ef-core skill.

Critical Rules

These are the most frequently violated conventions. Claude cannot fetch the linked docs at runtime, so these are inlined here:

Migration file naming: YYYY-MM-DD_##_Description.sql (e.g., 2025-06-15_00_AddVaultColumn.sql)
All schema objects use dbo schema — never create objects in other schemas
Constraint naming: PK_TableName (primary key), FK_Child_Parent (foreign key), IX_Table_Column (index), DF_Table_Column (default)
Idempotent scripts: Use IF NOT EXISTS / IF COL_LENGTH(...) guards before schema changes in migration scripts
New repository functionality requires both Dapper and EF Core implementations — unless the feature is explicitly EF-only or the change is a backwards-compatible stored procedure modification
Integration tests use [DatabaseData] attribute — this runs the test against all configured database providers