TableSchema and database structure upgrades: compare, plan, execute

This post explains how a .NET ERP framework applies TableSchema definitions to the physical database without migration scripts. Each upgrade compares the live structure with the definition, then separates compare, plan and execute so the mode (ALTER or rebuild) can be checked before running. It is useful for developers who maintain schemas that already hold production data.

Key points

• Each upgrade compares the live database with the TableSchema definition instead of running versioned scripts, so reruns are safe.
• The plan step reads the database without writing and reports ALTER, rebuild or no change before anything runs.
• A rebuild copies data into a temporary table and renames it; on large tables it can take tens of minutes to hours.
• Lengthening a string or moving between numeric types stays an ALTER; crossing type families forces a rebuild.
• Columns missing from the definition are kept, so column drops are never automatic.

The author describes how a TableSchema definition in a .NET ERP framework controls the physical database structure. Instead of versioned migration scripts, each upgrade compares the live database with the definition, computes the difference and then executes it. The difference is treated as a plan first, which states whether the change is an ALTER or a full table rebuild. A rebuild can mean a maintenance window on large tables.

The post also covers why native column types are kept out of the definition, why staged ALTER steps run in separate transactions, and why rerunning after a failure is preferred over a rollback that cannot be trusted. Column removal is intentionally not automated. The original article has the full walkthrough and the sample definitions.

Real-world use

• Checking whether widening a column on a table with tens of millions of rows will force a rebuild before scheduling downtime.
• Maintaining one schema definition that deploys to SQL Server, PostgreSQL and SQLite in a multi-database ERP product.
• Realigning a production database that someone edited by hand, using a rerunnable upgrade instead of hand-written scripts.

Why it matters

You can read whether a schema change needs downtime from the plan output before deploying, rather than discovering it mid-run. Closing the gap between definition and database with a rerunnable comparison, instead of an ordered script sequence, makes production deployments more predictable.

Related stories

• Idempotent NebulaGraph bulk ingestion from .NET using deterministic VIDsDVDEV Community: #csharp3 d ago
• Read your own writes with Marten: ProjectLatest and Wolverine's UpdatedAggregateJMJeremy D. Miller (The Shade Tree Developer)6 d ago
• Collection Return Types in C#: Interfaces, Views, and SnapshotsDLNick Cosentino (Dev Leader)2 d ago