Comparing Two Database Schemas

What are schemas in database

A database schema is the formal structural blueprint that defines how data is organized, stored, and related within a database management system. It establishes the catalog of tables, columns, data types, default values, primary keys, foreign keys, unique constraints, check constraints, indexes, views, and stored routines. Database engines enforce these definitions at runtime to guarantee structural integrity and prevent incompatible data writes.

Different database management systems implement schemas with distinct architectural boundaries. In PostgreSQL, a database cluster contains one or more named databases, and each database contains one or more named schemas that act as namespaces for tables, data types, functions, and operators[1]. Objects created without a schema qualification land in the default public schema, and administrators list all schemas in the psql terminal using the \dn command. In Oracle Database, a schema is inextricably linked to a database user account. Creating a user account automatically creates a matching schema of the same name, which contains all database objects owned by that user.

Understanding these architectural differences is critical before initiating any schema comparison. When you compare two schemas across environments, you are evaluating differences in metadata definitions, constraint hierarchies, and indexing strategies across these engine-specific boundaries.

The situation this solves

Database environments naturally drift apart during software development lifecycles. Developers add columns, create temporary staging tables, modify constraints, and adjust indexes in local environments to support new application features. When multiple engineers deploy changes across development, testing, staging, and production environments, uncoordinated modifications lead to schema drift.

Schema drift introduces severe operational risks for database administrators. Undocumented structural differences cause runtime application failures, broken query plans, invalid foreign key references, and deployment rollbacks. Storing the DbSchema design model as a plain XML .dbs file in Git allows teams to version control the intended schema baseline alongside application source code.

• Hotfixes applied directly to production: Emergency structural adjustments made during outages often bypass staging pipelines, creating immediate schema discrepancies.
• Concurrent feature branch changes: Multiple engineering teams modifying the same tables in Git branches introduce conflicting column definitions and constraint names.
• Incomplete test deployments: Test environments frequently retain orphaned mock tables, obsolete indexes, and modified column nullability constraints that do not match production.
• Multi-tenant environment drift: SaaS systems hosting independent databases per tenant experience configuration drift when sequential migration scripts fail on specific tenant instances.

DbSchema bridges this gap. It compares its offline design model against the target database, or one saved model file against another. It isolates every discrepancy in a Synchronization Dialog and generates the migration script that brings the target into parity. Schema synchronization is a Pro Edition feature.

How to do schema compare

Automated schema comparison follows a three-stage mechanical process: metadata introspection, object abstract syntax tree (AST) comparison, and dependency-ordered DDL generation. Schema comparison tools execute this process without reading or transferring underlying table data.

1. Introspect metadata catalogs: The comparison engine queries database system catalogs or JDBC metadata over a secure connection to extract object definitions, including column types, nullability, default expressions, constraints, indexes, triggers, and views.
2. Normalize and compare object graphs: The engine builds structural representations of both database definitions and matches objects by identifier, applying options that ignore cosmetic differences such as whitespace, column order, or partition schemes, then reports the result as a set of actions that would make the target match the source[3].
3. Generate ordered migration DDL: The tool evaluates object dependencies (such as foreign key hierarchies, table inheritance, and view dependencies) to generate an ordered SQL migration script that applies changes without violating foreign key or constraint checks.

Visual schema comparison interfaces present these results in an interactive diff matrix. In DbSchema the results land in a Synchronization Dialog. It is a per-object tree with the model's value on the left and the database's on the right, Merge and Commit buttons on each row, and Apply Model Actions, Generate Script and Commit In Database along the bottom.

A worked before-and-after diff

The cleanest way to compare two schemas in DbSchema is to reverse-engineer each one into its own .dbs design model, then run Schema > Compare Model with Other Model From File. Comparing two models of the same PostgreSQL task-app schema, the Synchronization Dialog put the open model in a DbSchema Model column and the file in a Project From File column, and listed exactly two differences.

The third row is the point of a model-to-model compare. Layout, colours and virtual foreign keys live in the same file as the schema, and the comparison steps over them instead of reporting them as drift. Each difference row carries its own direction, so a widened column can be merged back into the model while a column you never wanted is dropped from the file.

Because the .dbs file is plain, indented, human-readable XML, those same two changes show up as a text diff before anyone opens the application. A reviewer reading the pull request sees the widened column in the XML. A reviewer opening DbSchema sees it in the Synchronization Dialog. Model > Git Collaboration is a full Git client inside DbSchema, with Clone, Pull, Push, Branch, Stash and a commit graph, so the model file is committed from the same window that edits it.

Generate Script turns the difference list into the migration script between the two versions. Execute Script In Database then steps through the generated DDL one statement at a time, with an execution message per statement and Execute, Execute All or Skip on each. That viewer is read-only. When a statement fails you can Execute, Skip or Close, but you cannot edit it in place, so a script that needs changing has to be regenerated or fixed outside the dialog.

The constraint on comparing two live databases

Comparing two live databases directly is the case DbSchema handles worst, and it is worth knowing before you plan around it. Synchronization identifies a schema by its catalog as well as its schema name. Building a model from one database and repointing the connection at a second database on the same server does not produce a short drift report. In a hands-on run against two PostgreSQL databases holding the same 14-table schema with twelve deliberate differences, it produced 75 statements that recreate the entire schema from scratch, because the first catalog's public schema has no counterpart in the second catalog. The Synchronization Dialog says so outright, listing each side's public schema as existing on one side and missing on the other.

Connections > Schema Mapping does not rescue this. It remaps a model schema onto a differently-named schema, and it has no concept of remapping the catalog, so where both sides are already called public there is nothing for it to map. Two reliable routes remain. Compare databases that carry the same catalog and schema names on different servers, or reverse-engineer each side into its own .dbs file and compare the two models, as above. Where the source of truth should live is a separate decision, covered in visual schema diff or a code-only migration library.

How to compare schemas in SQL Server

Comparing SQL Server database schemas involves extracting metadata from system catalog views over TCP port 1433. Database engines reconstruct the complete data definition by querying catalog tables like sys.tables, sys.columns, sys.indexes, sys.foreign_keys, and sys.check_constraints.

In the Microsoft ecosystem, the schema comparison tooling is available in Visual Studio, Visual Studio Code (via the MSSQL extension and SQL database projects), and from the command line[3]. It compares any combination of connected databases, SQL database projects, and compiled.dacpac package files.

To run a comparison in Visual Studio, open the Tools menu, select SQL Server, then New Schema Comparison. Choose your source definition (a connected SQL Server database, SQL database project, or.dacpac file) and your target definition, set Options to control which object types are compared and which differences are ignored, then select Compare. The results grid groups differing objects by action, such as Add, Change, or Delete. From there you can selectively exclude individual differences before updating the target, apply the changes directly, or generate an update script to run later[3].

What is the best SQL Server schema compare tool

Selecting the best SQL Server schema comparison tool depends on operating system requirements, CI/CD automation pipelines, and team collaboration workflows. The right tool must handle complex schema hierarchies, generate clean migration DDL, and support offline model versioning.

For multi-platform development teams, DbSchema is usually the better fit. It runs natively on Windows, macOS and Linux, and connects to SQL Server over standard JDBC. It stores the database model locally as a plain XML .dbs file, so developers design offline, commit schema revisions directly to Git, and run a visual side-by-side synchronization against a live database. It generates migration scripts, supports multi-table relational data browsing over virtual foreign keys, and exports interactive HTML5 vector documentation. Schema synchronization, offline design and the documentation export are Pro Edition features. The free Community Edition covers connecting, reverse-engineering, interactive diagrams and the SQL editor.

Microsoft SQL Server Data Tools (SSDT) provides native schema comparison for teams fully invested in Windows and Visual Studio workflows. It integrates with MSBuild and compiles schemas into.dacpac packages for deployment via DacFx command-line utilities. For headless CI/CD automation pipelines, Liquibase compares a reference database against a target database from the command line and reports missing, unexpected, and changed objects, which you can then capture as declarative ChangeLogs. Choosing between Liquibase, Flyway or a visual schema compare is really a choice about where the source of truth lives[4].

Is SQL Compare free

Redgate SQL Compare is commercial software and is not free[5]. Redgate provides a time-limited free trial for evaluation, but ongoing production usage requires purchasing a commercial subscription license. Teams working on Linux or macOS often review Redgate SQL Compare alternatives for that reason.

Redgate offers SQL Compare under an annual subscription licensing model that requires recurring annual renewals per user. For organizations managing multiple databases across development and operations teams, recurring per-seat subscriptions represent a significant operational expense.

• Commercial annual subscriptions: Redgate SQL Compare is sold as a per-user annual subscription, priced in bands that reduce the cost per seat as the licence count rises, from $327 per user per year for 1 to 4 licences down to $294 per user per year for 10 to 19 licences[5].
• Perpetual licensing with offline support: DbSchema sells perpetual licences alongside monthly tiers, so teams retain permanent ownership of their design environment with optional maintenance renewals.
• Free community and CLI utilities: The free DbSchema Community Edition covers reverse-engineering, interactive ER diagrams and the SQL editor, though not schema synchronization. Open-source command-line tools offer scriptable diff capabilities.

What to check afterwards

Executing a schema comparison and generating migration DDL is only the first part of a deployment. Database administrators must systematically review the output to prevent accidental data loss, constraint violations, and production downtime during database migration deployments.

1. Audit destructive DDL statements: Scan the generated script for DROP TABLE, DROP COLUMN, or ALTER COLUMN statements that reduce data type precision. Ensure any dropped column has been formally deprecated and that existing data is archived.
2. Validate foreign key constraint order: Verify that tables are created before foreign keys reference them, and confirm that foreign key cascade rules (ON DELETE CASCADE or ON UPDATE SET NULL) match application requirements.
3. Check default values and nullability: Ensure new columns added to tables containing existing rows define a valid DEFAULT expression or allow NULL values. Adding a NOT NULL column without a default value causes immediate DDL execution failure on populated tables.
4. Rehearse against staging: Execute the migration script against a sanitized staging database that mirrors production data volume to measure lock acquisition times and index build durations.
5. Commit the updated model to Git: After completing synchronization, save the .dbs model and commit it from DbSchema's Git Collaboration window, so the new schema baseline is the one the whole team pulls.

Compare and synchronize your database schemas visually across SQL Server, PostgreSQL, Oracle and the more than 100 SQL and NoSQL databases DbSchema supports. Visit DbSchema Download to install DbSchema, open the model against your own database, and generate the migration script between two versions. Schema synchronization and saving the model to a file are Pro Edition features. The free Community Edition covers connecting, reverse-engineering, interactive diagrams and the SQL editor.

Frequently asked questions

What is a database schema?

A database schema is the logical blueprint that defines how data is structured and organized. It includes tables, views, primary keys, foreign keys, and indexes. In engines like PostgreSQL, schemas act as namespaces within a database, while in Oracle, a database user account owns a single schema.

How do I list all schemas in PostgreSQL?

To list schemas in PostgreSQL, open the psql command-line tool and execute the \dn command. This returns all user-defined schemas. Alternatively, database administrators can query the pg_catalog.pg_namespace system table to retrieve a comprehensive list of schemas alongside their ownership metadata.

How do you sync two database schemas without data loss?

Schema comparison is performed by extracting the metadata from a source database and comparing it line-by-line against a target environment. DbSchema inspects tables, indexes and routines, highlights the differences side by side in its Synchronization Dialog, and generates the DDL migration script that synchronizes the two. Review that script for destructive statements before running it.

Can you compare SQL Server schemas using Visual Studio?

Yes, database administrators use Microsoft SSDT within Visual Studio to compare schemas in SQL Server. The tool evaluates structural differences between a connected database, a SQL database project, or a .dacpac file, allowing users to apply changes directly to the target or generate a reusable update script.

Does Redgate SQL Compare offer a free community edition?

No, Redgate SQL Compare is commercial proprietary software and does not offer a free community edition. It is licensed as a per-user annual subscription with volume discounts at higher license counts. Teams seeking cost-effective alternatives often evaluate DbSchema, which sells perpetual licences and ships a free Community Edition, or headless CLI utilities like Liquibase.

Sources

1. postgresql.org
2. learn.microsoft.com
3. learn.microsoft.com
4. docs.liquibase.com
5. red-gate.com