Keeping Database Schema in Sync With Production

One recurring problem in backend and data engineering workflows is this:

We design databases visually.
But we deploy them as SQL.

Somewhere between diagrams, migrations, CI/CD and Docker environments, things drift.

Over time:

• The ER diagram no longer reflects production.
• The production schema evolves through manual migrations.
• Docker setups are maintained separately.
• Version control contains SQL files, not the original intent of the model.

This disconnect is subtle at first. But in real systems, it becomes a source of confusion, onboarding friction, and architectural entropy.

Let’s unpack why this happens — and what alternative approaches might look like.

---

The Drift Problem

Most teams follow something like this workflow:

1. Initial schema modeled in a diagram tool.
2. SQL exported (or manually written).
3. Migrations handled via Flyway, Liquibase, Alembic, etc.
4. CI/CD pipelines apply migrations.
5. Docker environments configured separately.

After a few months:

• The diagram is outdated.
• Migrations reflect history, not structure.
• Reconstructing the full schema requires replaying migration history.
• Visual representation becomes documentation-only.

The visual model is no longer the source of truth.

The migration history is.

---

What Is the Real Source of Truth?

There are typically three candidates:

1. The Live Database

Reverse engineer from production.

Pros:

• Always accurate (for that environment).

Cons:

• Doesn’t represent intent.
• Environment-specific.
• Hard to reason about evolution.

---

2. Migration Files

Treat migrations as canonical.

Pros:

• Fully versioned.
• CI/CD friendly.
• Production-aligned.

Cons:

• Represents change history, not final structure.
• Hard to visualize architecture from them.
• Cross-dialect support becomes complex.

---

3. Declarative Schema Definition

Define structure once and generate artifacts.

Pros:

• Structure-first.
• Easier visualization.
• Potentially dialect-agnostic.

Cons:

• Requires strong validation.
• Needs deterministic SQL generation.
• Must integrate with migration workflows.

This third approach is less common in traditional relational tooling, but widely adopted in infrastructure-as-code philosophy.

---

Migration-First vs Structure-First

Most relational workflows are migration-first:

Change is primary. Structure is derived.

But what if we invert that?

Structure-first approach:

• Define schema declaratively (or visually).
• Generate deterministic SQL.
• Generate migration diffs.
• Generate container environment.
• Version the schema definition itself.

Now the model becomes the artifact.

Migrations become derivative, not primary.

---

Multi-Dialect Reality

In modern environments, teams may:

• Develop locally in PostgreSQL.
• Use MySQL in staging.
• Maintain compatibility layers.
• Spin up ephemeral Docker environments per branch.

This creates a problem:

SQL dialect differences introduce fragmentation.

If the schema is defined abstractly, dialect-specific SQL can be generated consistently.

This reduces duplication and potential inconsistencies.

---

Docker as a Derived Artifact

Another common disconnect:

The database schema and Docker setup live in separate places.

But from a reproducibility standpoint:

Schema definition → should produce → runnable environment.

If the model knows:

• Engine
• Version
• Extensions
• Ports
• Initialization SQL

Then generating Docker Compose becomes mechanical.

That reduces setup friction and onboarding time.

---

Treating Schema as a Versioned Artifact

What happens if we treat the schema definition file as:

• The source of truth
• Versioned in Git
• Used to generate SQL
• Used to generate Docker config
• Used to derive migrations

Now we have:

• Deterministic builds
• Reproducible environments
• Visual clarity
• Single canonical representation

This aligns more closely with infrastructure-as-code principles.

---

But What About Migrations?

This does not replace migrations.

Instead:

1. Schema definition changes.
2. Diff is computed.
3. Migration SQL is generated.
4. Migration is reviewed.
5. CI/CD applies it.

The difference is:

Migrations are output of structural change — not the primary design interface.

---

An Experiment

I’ve been experimenting with this structure-first approach in a side project.

The idea is simple:

• Model tables and relationships visually.
• Generate SQL for PostgreSQL, MySQL, SQL Server or Oracle.
• Structure the project ready for Git versioning.
• Export Docker Compose to spin up the database instantly.

The goal isn’t to replace mature tools like Flyway or Alembic.

It’s to explore whether making the schema definition the central artifact reduces drift between design and production.

---

Open Questions

I’m genuinely curious how others handle this in real systems:

• What do you consider the true source of truth?
• How do you prevent diagram drift?
• Do you rely entirely on migrations?
• Would you trust deterministic SQL generation?
• How do you handle multi-dialect environments?

If you’ve dealt with this in production, I’d love to understand your workflow.

The discussion itself is more valuable than any specific tool.

---

If you'd like to see the experiment I'm working on, it's called forgesql.com.
But more importantly, I’m interested in how teams solve this at scale.