Data infrastructure

Active contributors: dbops, platform, every product team that adds a column.

Purpose

Bitwarden's data layer supports five database engines (Microsoft SQL Server, MySQL, MariaDB, PostgreSQL, SQLite) without conditional code in the domain layer. It does this by defining each repository twice — once for SQL Server using Dapper + stored procedures, once for the EF-supported engines using EF Core. Both implementations satisfy the same domain interface and are registered against the appropriate provider at startup.

Directory layout

src/
├── Sql/                                       # SQL Server Database Project (.sqlproj)
│   ├── dbo/Tables/                            # Table definitions
│   ├── dbo/Views/
│   ├── dbo/Functions/
│   └── dbo/Stored Procedures/                 # The canonical query surface for Dapper
├── Infrastructure.Dapper/                     # Dapper-backed repositories (SQL Server)
│   ├── Repositories/Repository.cs             # Generic base
│   ├── Vault/Repositories/CipherRepository.cs # Concrete repos by domain
│   └── …
└── Infrastructure.EntityFramework/            # EF Core repositories (MySQL/Postgres/SQLite)
    ├── Repositories/Repository.cs
    ├── Vault/{Repositories,Models}/
    ├── Migrations/                            # Empty — migrations live in util/
    └── DatabaseContext.cs
util/
├── Migrator/                                  # SQL change-script runner
│   └── DbScripts/{date}_{slug}.sql            # 473 forward-only T-SQL migrations
├── MsSqlMigratorUtility/
├── MySqlMigrations/                           # EF migrations per provider
├── PostgresMigrations/
├── SqliteMigrations/
└── SqlServerEFScaffold/                       # EF model scaffolded from SQL Server (used to generate EF mappings consistently)

Selecting an engine

SharedWeb.Utilities.ServiceCollectionExtensions.AddDatabaseRepositories looks at globalSettings.DatabaseProvider and registers either the Dapper or EF concrete repos behind the shared IXxxRepository interfaces. The globalSettings.DatabaseProvider is itself derived from the connection-string section in appsettings.json — sqlServer, mySql, postgreSql, sqlite.

var provider = globalSettings.DatabaseProvider;
if (provider == "sqlServer") {
    services.AddDapperRepositories(globalSettings.SelfHosted);
} else {
    services.AddEntityFrameworkRepositories(provider, globalSettings);
}

(Pseudocode — the real method is in src/SharedWeb/Utilities/ServiceCollectionExtensions.cs and a per-domain Add*Repositories extension.)

Shape of a repository

Every domain entity has:

• src/Core/<Domain>/Repositories/IXxxRepository.cs — interface used by services / commands.
• src/Infrastructure.Dapper/<Domain>/Repositories/XxxRepository.cs — calls T-SQL sprocs.
• src/Infrastructure.EntityFramework/<Domain>/Repositories/XxxRepository.cs — uses EF Core.
• src/Infrastructure.EntityFramework/<Domain>/Models/XxxMapperProfile.cs — AutoMapper profile (or hand-written mapper) bridging the EF entity to the Core entity.

The Dapper layer extends Repository<TId, T> (src/Infrastructure.Dapper/Repositories/Repository.cs). Common operations (GetByIdAsync, CreateAsync, ReplaceAsync, UpsertAsync, DeleteAsync) are inherited; per-domain methods call into named stored procedures (e.g. Cipher_ReadByOrganizationId).

The EF layer mirrors the same surface area but uses LINQ-to-Entities. Both implementations are exercised by the same integration tests in test/Infrastructure.IntegrationTest/.

Migrations

Every schema change ships in four places:

Layer	Path
Canonical schema	src/Sql/dbo/... (modify the table definition)
MS SQL migration script	util/Migrator/DbScripts/YYYY-MM-DD_xx_<slug>.sql
EF migration (MySQL)	util/MySqlMigrations/Migrations/ (dotnet ef migrations add)
EF migration (Postgres)	util/PostgresMigrations/Migrations/
EF migration (SQLite)	util/SqliteMigrations/Migrations/

DbMigrator.cs (in util/Migrator/) is the runner. It applies any unapplied script (alphabetical order) and writes a row to dbo.Migration on success. dev/migrate.ps1 and dev/ef_migrate.ps1 drive the local cycle; dev/verify_migrations.ps1 makes sure every migration script has been applied to the local DB.

CI workflow .github/workflows/test-database.yml boots all four engines, applies migrations, and runs the integration tests against each one.

Some intentional asymmetries

• Stored procedures are SQL-Server-only. The EF repositories implement the same logic in LINQ and are kept in sync by hand. The integration-test suite catches drift.
• Specialised SQL features (XML columns, table-valued parameters, temporal tables in some places) are emulated in EF by repeating the operation per row. This is intentionally not optimised — the cloud runs SQL Server.
• Triggers in src/Sql/dbo/Triggers/ (where they exist) are translated to application-level fixups in EF.

Read models / non-table data

Some queries return shapes that aren't entities — e.g. Bit.Core.Vault.Models.Data.CipherDetails (a join of Cipher + OrganizationUser + Collection). These are projected directly out of stored procs / EF queries. The data classes live under src/Core/<Domain>/Models/Data/.

Connection management

• ASP.NET Core registers DbConnection / DbContext per scope; each request opens its own connection.
• Connection strings live in globalSettings.SqlServer, globalSettings.MySql, globalSettings.PostgreSql, globalSettings.Sqlite.
• For long-running background jobs, the host either uses transient scopes or explicit using var connection = new SqlConnection(...) in the Quartz job class.

Entry points for modification

• New table → add it under src/Sql/dbo/Tables/, write the CREATE TABLE migration script, add the EF model + migrations, add the IXxxRepository interface plus both impls, and write integration tests.
• Add a new method on an existing repo → update interface + both impls + at least one stored procedure (if SQL Server requires it).
• Change a stored procedure's signature → migration script that drops and recreates the sproc; update the corresponding Cipher_… literal in the Dapper repo and the LINQ in the EF repo.
• Drift-checking → dev/verify_migrations.ps1 and the test-database CI workflow.