Keeping a database schema in sync with a specification — across development, staging, and production environments — is one of the more error-prone parts of managing a database-backed application. A column gets added in development but the migration script never runs on staging. A table gets renamed in production without updating the documentation. Schema Sync in BlueprintDB addresses this problem directly: it compares the live structure of any supported database against the Blueprint and generates exactly the changes needed to bring them in line, without touching data and without requiring manual DDL.

What Schema Sync does

Schema Sync performs a structural diff between two things:

• The Blueprint — the metadata stored in BlueprintDB (tables, columns, types, constraints)
• The live database — the actual structure of a connected database at the moment of analysis

The result is a list of differences, each mapped to a DDL operation:

Important: Schema Sync never drops tables or columns automatically, even if they exist in the live database but not in the Blueprint. Destructive operations require explicit manual action. This is a deliberate safety constraint.

Supported backends

Use cases

Initial schema creation

When starting with an empty target database, Schema Sync creates the entire schema from the Blueprint in a single operation. This is the standard last step after Schema Import in a migration workflow — import from source, then sync to target.

Propagating schema changes across environments

After modifying the Blueprint (adding a column, changing a type, adding a relation), run Schema Sync against each environment — dev, staging, production — to apply the same changes everywhere consistently. No migration scripts to write, no risk of environment drift.

Auditing live databases

Run Schema Sync in analyse-only mode (without applying) to see whether a live database matches the Blueprint. This is useful for auditing databases that may have been modified outside of the normal workflow.

Step-by-step: running a Schema Sync

1. Ensure your Blueprint is up to date

Schema Sync uses the Blueprint as the source of truth. Before syncing, confirm that the Blueprint accurately reflects the schema you want in the target database.

If you are starting from an existing database, use Schema Import first to populate the Blueprint from the live source. If you are adding a new table or column, do it in the Tables and Columns windows before proceeding.

[SCREENSHOT: Columns window showing a recently added column, not yet synced to the live database]

2. Open Schema Sync

Click Schema Sync in the main menu.

[SCREENSHOT: Schema Sync window, empty, backend dropdown and connection fields visible]

3. Select the backend and connect

Select the target database engine from the dropdown and fill in the connection details:

• File-based backends (SQLite, Access, dBASE): browse to the file.
• Server backends: enter host, port, database name, username, and password.

[SCREENSHOT: Schema Sync, MySQL selected as backend, connection fields filled, Analyse button highlighted]

4. Analyse

Click Analyse. BlueprintDB connects to the target database, reads its current structure, and compares it against the Blueprint. The diff list populates with all detected differences.

[SCREENSHOT: Diff list after Analyse — showing a mix of “Table to be created”, “Column to be added”, “Column type changed” entries]

Each entry in the list shows:

• The operation type (Create table, Add column, Alter column, Add constraint)
• The table and column affected
• The current state in the live database vs. the Blueprint definition

If the diff list is empty, the live database already matches the Blueprint — no changes are needed.

5. Review the diff

Read through the diff list before applying. Pay particular attention to Alter column entries — changing a column type on a table with existing data can fail if the existing values are not compatible with the new type (e.g. changing VARCHAR to INTEGER on a column that contains text).

Tables or columns that exist in the live database but not in the Blueprint are shown as extra. These are reported for information only — Schema Sync will not drop them.

[SCREENSHOT: Diff list detail — one “ALTER COLUMN” entry expanded, showing old type vs new type]

6. Apply

Click Apply to execute the DDL statements. BlueprintDB processes the diff list in dependency order:

1. Tables with no foreign key dependencies are created first.
2. Tables that reference other tables are created after their dependencies.
3. New columns are added to existing tables.
4. Column alterations are applied.
5. Foreign key constraints are added last, once all referenced tables exist.

[SCREENSHOT: Sync in progress, log panel showing DDL statements being executed one by one]

When all statements have been executed, the result is shown per entry — green for success, red for failure with an error message.

[SCREENSHOT: Sync complete, all entries green with checkmarks]

Practical example: adding a column to three environments

Suppose you add a last_login column of type DateTime to the users table in your Blueprint. You have three environments: development (SQLite), staging (PostgreSQL), and production (PostgreSQL).

1. Open Columns, select the users table, click New, add last_login as DateTime, nullable, and save.
2. Open Schema Sync → select SQLite → connect to the dev database → Analyse → Apply.
   BlueprintDB executes: ALTER TABLE users ADD COLUMN last_login TEXT;
3. Open Schema Sync → select PostgreSQL → connect to staging → Analyse → Apply.
   BlueprintDB executes: ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
4. Open Schema Sync → select PostgreSQL → connect to production → Analyse → Apply.
   Same statement, same result.

The column definition is written once in the Blueprint. The backend-specific DDL is generated automatically for each target engine.

[SCREENSHOT: Diff list for the staging environment, showing only “Add column: users.last_login TIMESTAMP”]

Batch Schema Sync

If you maintain a large number of databases with the same schema — for example, one database per client in a SaaS application — BlueprintDB’s Batch Schema Sync can sync the Blueprint against multiple databases in a single operation.

1. Open Batch Schema Sync from the main menu.
2. Add each target database connection to the list.
3. Click Run All. BlueprintDB analyses and applies changes to each database in sequence.
4. The result log shows a summary per database — changes applied, errors, and skipped entries.

[SCREENSHOT: Batch Schema Sync window, list of multiple database connections, Run All button]

[SCREENSHOT: Batch sync results log, per-database summary with row counts and status]

Troubleshooting

Analyse shows no differences, but the live database is clearly out of date

Confirm that you are connected to the correct database. Check the database name in the connection form — it is easy to accidentally connect to a different schema or catalog that already matches the Blueprint.

Apply fails with “Cannot alter column — data incompatibility”

The target column contains data that is incompatible with the new type defined in the Blueprint (e.g. text values in a column being changed to Integer). Options:

• Clean up the data in the live database first (cast or delete incompatible rows), then re-run Apply.
• Revert the type change in the Blueprint if the original type was correct.
• On SQL Server and PostgreSQL, you may need to add an explicit USING clause or CAST — run the ALTER manually with the appropriate cast expression.

Foreign key constraint fails during Apply

The referenced parent table or column does not yet exist in the live database, or it exists with a different type than the foreign key column. Ensure the parent table has been created (check earlier in the diff list for any failed CREATE TABLE entries) and that primary key types match foreign key column types in the Blueprint.

SQLite — “Cannot alter column type”

SQLite has limited ALTER TABLE support and does not allow changing column types directly. BlueprintDB handles this by recreating the table: it creates a new table with the updated schema, copies data from the old table, drops the old table, and renames the new one. This operation preserves data but requires that no other database connections are open on the SQLite file during the sync.

Extra tables or columns are not being removed

This is expected behaviour. Schema Sync never drops tables or columns automatically. To remove a table from the live database that is not in the Blueprint, execute the DROP TABLE statement manually in your database client after verifying that the data can be safely deleted.

Summary

Schema Sync gives you a single, authoritative definition of your database schema — the Blueprint — and applies it to any connected database on demand. Key points:

• It only adds and modifies, never drops — safe to run on databases with live data.
• DDL is generated in dependency order — no manual sorting of migration scripts.
• The same Blueprint can be synced to different backend engines — canonical types are translated automatically.
• Batch Sync scales the same workflow to many databases at once.

Used together, Schema Import, Schema Sync, and Transfer Data cover the full database migration and maintenance lifecycle — from reading an existing schema, to deploying it to new environments, to keeping multiple databases in sync over time.

Download BlueprintDB