Writing Database Queries
Developmentwriting-database-queriesWriting Database Queries is an Agent Skill for development published in the bitwarden/server repository on GitHub: a SKILL.md instruction file an AI agent can load to do this task.
Bitwarden database architecture, migrations, and dual-ORM strategy. Use when working with `.sql` files, stored procedures, EF migrations, or database schema changes. Also use when deciding whether a change needs both Dapper and EF Core implementations, or whether a breaking stored-procedure change requires `_V2` versioning.
How to use this skill
Bring this guide into your coding agent with a prompt tailored to the tool you use.
- Open your project in Codex.
- Copy and paste the prompt below.
- Review files and risks before approving any action.
Inspect the Agent Skill at https://github.com/bitwarden/server/blob/HEAD/.claude/skills/writing-database-queries/SKILL.md and all supporting files. Report provenance, permissions, scripts, dependencies, and security risks. Propose an exact installation into .agents/skills/writing-database-queries, but wait for my approval before writing files or running scripts.
Copying this prompt does not install or run the skill. Review third-party files before use.
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/andutil/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_V2is needed. - Breaking (
_V2versioning): 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 creatingProcedureName_V2while 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 truthutil/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
dboschema — 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