Round-Tripping Bitwarden and Smartstore Through a Database Diagram: What SQL Server Lost and What We Fixed

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: We put two real SQL Server schemas, Bitwarden (a SQL Server database project) and Smartstore (Entity Framework Core), through Schemity and rebuilt a database from the SQL it wrote. Before this round of fixes Bitwarden's script failed, varchar came back as nvarchar, the live path turned tinyint, money and datetime into wider types, and filtered indexes lost their WHERE, so a copy refused rows the original accepts. After the fixes Smartstore comes back with no difference, Bitwarden differs only in two foreign keys created WITH NOCHECK, and 21 behaviour probes match.

A database diagram that reads a SQL Server schema and writes it back should give you the same database, and before this round of fixes Schemity did not. We took two real open-source schemas, Bitwarden server v2026.10.0 (a SQL Server database project, 64 tables) and Smartstore 6.4.0 (Entity Framework Core, 118 tables), put each through Schemity from a script and from the live database, and rebuilt a database from the SQL Schemity wrote. Bitwarden's script failed, every varchar came back as nvarchar, and the copies accepted and refused different rows from the originals. After the fixes, Smartstore comes back with no catalog difference at all on either path, Bitwarden differs only in two foreign keys it created WITH NOCHECK, and both behave exactly like their originals in 21 behaviour probes.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. This is the third post in a series that tests the round trip on each database it supports, after Mastodon and Saleor on PostgreSQL and Invoice Ninja and Sylius on MySQL. Reading a schema you did not write is how you understand your schema before changing it, and SQL Server tested the parts of the diagram the other two never reached: its own type names, and indexes that carry more than their key columns.

Which two schemas, and which two paths?

Two schemas built two different ways. Bitwarden's comes from a SQL Server database project (Sql.sqlproj) applied by its own migrator, so it is hand-written T-SQL with procedures and views. Smartstore's comes from its installer, which runs on Entity Framework Core with FluentMigrator.

	Bitwarden server v2026.10.0	Smartstore 6.4.0
What it is	Password manager	E-commerce platform
Built by	SQL Server database project, Bitwarden's migrator	Entity Framework Core and FluentMigrator, the web installer
Tables	64	118
Columns	705	1,225
Foreign keys	88: 43 ON DELETE CASCADE, 2 ON DELETE SET NULL, 2 WITH NOCHECK	122: 86 ON DELETE CASCADE, 20 ON DELETE SET NULL
Indexes besides primary keys	117	260
Filtered (WHERE)	22, 3 of them unique	5, all unique
Covering (INCLUDE)	19	4
With a DESC key	6	0
Keys	mostly uniqueidentifier, 4 IDENTITY	104 IDENTITY
varchar / tinyint / money / datetime	75 / 47 / 3 / 3	0 / 0 / 0 / 1
CHECK / defaults / unique constraints	1 / 40 / 5	none
Also has	61 views, 574 procedures, 3 functions, 5 table types	nothing

SQL Server has no pg_dump or mysqldump. The script for the Import SQL path came from sqlpackage, Microsoft's command-line tool, with /a:Extract /p:ExtractTarget=File, which writes the whole schema as one .sql file. Each schema then went through Schemity on two paths:

• Import SQL: that script, read by Schemity's SQL parser, with no connection.
• Live connection: Schemity's SQL Server reader connected to the original database and read its catalog views.

Both paths end with the diagram's SQL applied to an empty database on the same SQL Server 2022 server. The runs used Schemity's own code from a development build, called from scripts rather than through the app's window. The fixes described below are in that build and ship in the next release; until then, the released app has the same type and index losses as the "before" column.

How do you prove a rebuilt SQL Server schema is the same?

The same two checks as the MySQL post.

The catalog diff. A query over SQL Server's catalog views (sys.columns, sys.indexes, sys.foreign_keys and the rest) lists one line per fact for the original and for the copy: every column with its type, length or precision, nullability, default, identity and collation; every primary key, unique constraint and index with its keys in order, sort direction, INCLUDE columns, filter and clustering; every foreign key with its actions and whether SQL Server trusts it; every CHECK. The two lists are diffed line by line.

The behaviour probes. A probe is a few statements in one transaction that is rolled back: insert 256 into a tinyint, insert two rows with no order into a filtered unique column, delete a group and count its members. Its outcome is "accepted" or "refused at step N" with SQL Server's message number, and where it ends with a SELECT, the value read back is part of the outcome. A foreign key a probe is not about is switched off with NOCHECK inside the transaction, which the rollback undoes.

Check	What it compares	What it catches
Catalog diff	Columns, keys, indexes, foreign keys, as SQL Server's catalog reports them	Anything missing, added or changed in the schema
Probes, accept or refuse	Which rows each database accepts	A type range, unique index or foreign key that behaves differently
Probes, read back	What each database stored	A value rounded or encoded differently

The 21 probes are 12 for Bitwarden and 9 for Smartstore. Smartstore's installer seeds rows in 35 tables and Bitwarden's migrator logs its scripts in one, which the copies do not have; every probe uses rows of its own.

What failed before the fixes?

Everything below is from Schemity's code just before these fixes (commit a599d7a5), on both paths this time.

Three of the four scripts failed under sqlcmd. Bitwarden's Import SQL script stopped at:

Msg 8166, Level 16, State 1, Line 983
Constraint name '#Cols_pkey' not permitted. Constraint names cannot begin with a number sign (#).

Both live-path scripts stopped at the first filtered index with Msg 1934, CREATE INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Smartstore's Import SQL script ran only because its filtered indexes had already lost their filters. With the phantom table removed by hand and the scripts run with QUOTED_IDENTIFIER ON, as the app's own connection has it, all four copies could be compared.

	Bitwarden, Import SQL	Bitwarden, live	Smartstore, Import SQL	Smartstore, live
Script ran	no, Msg 8166	no, Msg 1934	yes	no, Msg 1934
varchar written as nvarchar	75	75	0	0
tinyint / money / datetime widened	0	47 / 3 / 3	0	0 / 0 / 1
Filtered indexes changed or dropped	22 of 22	20 of 22	5 of 5	0 of 5
Covering indexes without INCLUDE	19 of 19	19 of 19	4 of 4	4 of 4
Indexes that lost a DESC key	6	6	0	0
Clustered index made nonclustered	0	0	1	1
Probes that match the original	10 of 12	7 of 12	8 of 9	8 of 9

The probes that failed are the ones that matter to the data:

Probe	Original	Before the fixes	After
Bitwarden: gw-Ā into a varchar(50)	stores gw-A	stores gw-Ā	stores gw-A
Bitwarden: 256 into a tinyint	refused, Msg 220	accepted (live)	refused
Bitwarden: an amount above money's range	refused, Msg 8115	accepted (live)	refused
Bitwarden and Smartstore: 10:00:00.001 into a datetime	stores .000	stores .0010000 (live)	stores .000
Bitwarden: two active tax rates for one postal code	refused, Msg 2601	accepted (Import SQL)	refused
Bitwarden: two inactive tax rates for one postal code	accepted	refused (live)	accepted
Smartstore: two reward point records with no order	accepted	refused (Import SQL)	accepted

Cascades on delete, the CHECK, defaults, IDENTITY, case-insensitive uniqueness and foreign keys all survived. Each failure had its own cause.

Why did Import SQL read a procedure as a table?

Because a script splits on more than GO. A sqlpackage or SSMS script separates batches with GO, and a procedure is one batch, but its body holds statements of its own, ended by semicolons. Bitwarden has 574 procedures, and one of them contains CREATE TABLE #Cols (...), a temporary table. Split at its semicolons, the body became separate statements, and that one was read as a table. Import SQL now keeps a CREATE PROCEDURE, FUNCTION, TRIGGER or VIEW batch whole and skips it, and names the 61 views it skipped. A diagram holds tables; a live connection reads the views as read-only tables.

Why did the script fail under sqlcmd?

Because a filtered index needs a session setting that sqlcmd leaves off. SQL Server refuses to create a filtered index unless QUOTED_IDENTIFIER is ON, and sqlcmd's own documentation says its default is OFF unless you pass -I. SSMS connects with it on and its Generate Scripts writes it before each table; the script sqlpackage extracted for this test carries no SET at all, so it fails under sqlcmd the same way. Schemity's SQL Server scripts now start with SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON, so View SQL's script runs the same in sqlcmd as in SSMS.

Why is varchar not nvarchar?

Because they store different things. A varchar holds one byte a character in the column's code page, here SQL_Latin1_General_CP1_CI_AS; an nvarchar holds Unicode at two bytes a character. The probe shows the difference: Ā is not in that code page, so the original's varchar(50) stored gw-Ā as gw-A, while the copy, with nvarchar(50), kept gw-Ā. The copy also doubled the bytes of 75 columns, and an index on one of them counts the doubled size against SQL Server's limit on an index key.

Schemity's SQL Server support used a reduced type list, and both paths folded a declared type into a wider one. The live reader read varchar and char as nvarchar and nchar, tinyint as smallint, money as decimal, and datetime and smalldatetime as datetime2. Each fold changes a value or a range:

• tinyint holds 0 to 255, so the original refuses 256 with Msg 220; smallint takes it.
• money stops at 922,337,203,685,477.5807; decimal(19,4) goes higher, so the copy accepted an amount the original refuses with Msg 8115.
• datetime rounds to increments of .000, .003 or .007 seconds, so 10:00:00.001 is stored as 10:00:00.000; datetime2(7) keeps it.

The diagram now keeps each type as SQL Server declares it on both paths, and the field editor offers SQL Server's own types: varchar, char, varchar(max), tinyint, money, smallmoney, datetime, smalldatetime, real, binary, varbinary and xml beside the ones it had. A diagram drawn for another database still converts its text columns to nvarchar.

What happens to a filtered unique index?

It depends on its filter, and before the fixes both readings were wrong in a different way. On SQL Server a unique index treats NULL as a value, so it allows one row without a value. A unique index filtered to WHERE col IS NOT NULL is how SQL Server writes the standard rule, many NULLs allowed, and Smartstore has five of them, among them IX_RewardPointsHistory_UsedWithOrder_Id. Import SQL dropped the filter, so the copy refused the second reward point record with no order. It now reads such an index as a unique rule that ignores NULL, as the live reader already did, and writes it back as the same filtered index.

Bitwarden's IX_TaxRate_Country_PostalCode_Active_Uniqueness is the other kind: unique WHERE ([Active]=(1)), one active rate per postal code. Import SQL left it out entirely, so the copy accepted two active rates; the live reader dropped its filter, so the copy refused two inactive ones. The diagram now keeps a SQL Server index's filter as its WHERE, as it keeps a PostgreSQL partial index's.

What about INCLUDE, DESC and clustered indexes?

They change speed, not rows, and they were all lost. A covering index's INCLUDE columns, a key sorted DESC and a CLUSTERED index that is not the primary key are now kept on both paths and written back; a table whose clustered index is another index gets its primary key written NONCLUSTERED, since SQL Server allows one clustered index a table. Neither schema needed that last one: Smartstore's clustered unique index is on a table with no primary key. INCLUDE turned up a PostgreSQL bug on the way: its live reader read a covering index's INCLUDE columns as key columns. PostgreSQL indexes keep INCLUDE now too.

Which bugs did only a refresh find?

Import a script, connect to the database it came from, and Refresh & merge should find nothing to change. Measured through the app's own merge code, called from a script, it planned 113 statements for Bitwarden and 459 for Smartstore, all spelling: INTEGER from the script against INT from the reader, a CHECK that SQL Server stores in its own lowercase form, INCLUDE columns in another order, and names in brackets against names without. Both refreshes now list nothing, and so do the four MySQL and PostgreSQL schemas through the same check.

The same holds in the app's window the other way round: Import SQL of Bitwarden's script onto the diagram read from its live database finds nothing to change.

Two more turned up while this post was being checked, in schemas neither app has. A script's INT primary key without IDENTITY, the shape of a lookup table with fixed ids, came back as IDENTITY(1,1), so the copy refused the ids the application inserts; the diagram now records whether SQL Server numbers a column, on both paths. And Import SQL wrote a deprecated text column as nvarchar(max), the same widening as above; it now reads text, ntext and image as varchar(max), nvarchar(max) and varbinary(max), as the live reader does.

One more turned up later, while taking the SQLite post's screenshots, which the scripts here could not see because they skip a step the app takes when it reads a table: the app lists a table's fields key first, foreign keys next and timestamps last, and wrote CREATE TABLE in that order, so a copy built from the diagram met a SELECT * or an INSERT without a column list with its columns in another order. The diagram now remembers each column's position as the table declares it and writes that order, while the canvas keeps its own; a column added in the diagram goes last, where ADD COLUMN puts it. Read and written back the way the app does it, 62 of Bitwarden's 64 tables and 96 of Smartstore's 118 came back in a different column order before this fix.

What does the round trip give you now?

The same database, on both paths, for both schemas.

	Import SQL	Live connection
Bitwarden: catalog differences	2 foreign keys WITH NOCHECK	the same
Bitwarden: probes that match the original	12 of 12	12 of 12
Smartstore: catalog differences	none	none
Smartstore: probes that match the original	9 of 9	9 of 9

Every one of the 75 varchar, 47 tinyint, 3 money and 4 datetime columns, the 27 filtered indexes, 23 covering indexes, 6 indexes with a DESC key and Smartstore's clustered unique index come back as they were, and the scripts run under sqlcmd without -I.

What still differs, and why?

Two foreign keys SQL Server does not trust. Bitwarden's FK_EmergencyAccess_GranteeId and FK_EmergencyAccess_GrantorId were created WITH NOCHECK, so SQL Server did not check the rows already in the table when they were added and marks them not trusted. Both copies create them as ordinary, trusted keys. Every new row is checked either way, and an empty copy has no old rows to skip, so the diagram does not record it.

Objects a diagram does not hold. Bitwarden's 574 procedures, 3 functions and 5 table types are skipped by Import SQL, and its 61 views are skipped by name; a live connection reads the views as read-only tables. None of them is written back. SQL Server's deprecated text, ntext and image types are written as varchar(max), nvarchar(max) and varbinary(max) on both paths; neither schema uses them. An IDENTITY column comes back with seed 1 and increment 1, which every identity in both schemas has.

What this benchmark does not show

• The app's window. These runs called Schemity's import, reader, export and comparison code from scripts on a development build. The screenshots are from the app's window on the same build.
• Data. The originals hold only their seed rows. A schema that matches is the precondition for moving data, not proof that a migration of a full database is safe.
• Triggers. Neither schema has one.
• Azure SQL and older servers. Everything ran on SQL Server 2022 CU26.
• Other databases. SQLite has its own post, with Campfire and Jellyfin, and so has Snowflake, with TPC-DS.
• Schemas this size are not a stress test. 64 and 118 tables are typical; a thousand-table schema is covered in a separate post.

How to rerun it

Create each schema with its own tooling against an empty SQL Server, take sqlpackage /a:Extract /SourceConnectionString:<connection> /TargetFile:<schema>.sql /p:ExtractTarget=File, put the script or the database through Schemity, export the SQL and apply it to an empty database. Then list both catalogs with a query like this one, a shorter form of the benchmark's, run with sqlcmd -d <database> -h -1 -y 8000, and diff the output:

SET NOCOUNT ON;
WITH cols AS (
  SELECT CONCAT('column | ', t.name, ' | ', c.name, ' | ', ty.name,
      CASE
        WHEN ty.name IN ('nvarchar', 'nchar') THEN IIF(c.max_length = -1, '(max)', CONCAT('(', c.max_length / 2, ')'))
        WHEN ty.name IN ('varchar', 'char', 'varbinary', 'binary') THEN IIF(c.max_length = -1, '(max)', CONCAT('(', c.max_length, ')'))
        WHEN ty.name IN ('decimal', 'numeric') THEN CONCAT('(', c.precision, ',', c.scale, ')')
        WHEN ty.name IN ('datetime2', 'datetimeoffset', 'time') THEN CONCAT('(', c.scale, ')')
        ELSE '' END,
      IIF(c.is_nullable = 1, ' | null', ' | not null'),
      ' | ', ISNULL('default ' + dc.definition, 'no default'),
      ' | ', IIF(ic.column_id IS NULL, '-', CONCAT('identity(', CONVERT(varchar(40), ic.seed_value), ',', CONVERT(varchar(40), ic.increment_value), ')')),
      ' | ', ISNULL(c.collation_name, '-')) AS line
  FROM sys.tables t
  JOIN sys.columns c ON c.object_id = t.object_id
  JOIN sys.types ty ON ty.user_type_id = c.user_type_id
  LEFT JOIN sys.default_constraints dc ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
  LEFT JOIN sys.identity_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
),
idx AS (
  SELECT CONCAT(IIF(i.is_primary_key = 1, 'pk', IIF(i.is_unique_constraint = 1, 'unique constraint', 'index')),
      ' | ', t.name, ' | ', i.name, ' | ', IIF(i.is_unique = 1, 'unique', 'non-unique'), ' | ', LOWER(i.type_desc), ' | ',
      (SELECT STRING_AGG(CONCAT(c.name, IIF(ic.is_descending_key = 1, ' desc', '')), ', ') WITHIN GROUP (ORDER BY ic.key_ordinal)
         FROM sys.index_columns ic JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
         WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0),
      ISNULL(' | include ' + (SELECT STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY c.name)
         FROM sys.index_columns ic JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
         WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1), ''),
      ISNULL(' | where ' + i.filter_definition, '')) AS line
  FROM sys.indexes i JOIN sys.tables t ON t.object_id = i.object_id
  WHERE i.type > 0
),
fks AS (
  SELECT CONCAT('fk | ', t.name, ' | ', fk.name, ' | ',
      (SELECT STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY fc.constraint_column_id)
         FROM sys.foreign_key_columns fc JOIN sys.columns c ON c.object_id = fc.parent_object_id AND c.column_id = fc.parent_column_id
         WHERE fc.constraint_object_id = fk.object_id),
      ' | ', OBJECT_NAME(fk.referenced_object_id),
      ' | on delete ', LOWER(fk.delete_referential_action_desc), ' | on update ', LOWER(fk.update_referential_action_desc),
      IIF(fk.is_not_trusted = 1, ' | not trusted', '')) AS line
  FROM sys.foreign_keys fk JOIN sys.tables t ON t.object_id = fk.parent_object_id
)
SELECT line FROM (
  SELECT line COLLATE Latin1_General_BIN2 AS line FROM cols
  UNION ALL SELECT line COLLATE Latin1_General_BIN2 FROM idx
  UNION ALL SELECT line COLLATE Latin1_General_BIN2 FROM fks
  UNION ALL SELECT CONCAT('check | ', t.name, ' | ', ck.name, ' | ', ck.definition) COLLATE Latin1_General_BIN2
    FROM sys.check_constraints ck JOIN sys.tables t ON t.object_id = ck.parent_object_id
) catalog ORDER BY line;

For behaviour, run a few statements per rule inside BEGIN TRANSACTION and ROLLBACK on the original and on the copy, end each with a SELECT of what was stored, and compare both the step SQL Server refuses at and the values it reads back. Connect with QUOTED_IDENTIFIER ON, or SQL Server refuses every write to a table with a filtered index before your probe gets to its point.