When it comes to databases, things get tricky. Unlike code, databases hold persistent data that can’t just be wiped and redeployed. So how do we handle CI/CD for databases? Enter state-based and migration-based deployments.

I’ve worked with both approaches in real-world scenarios, and each has its strengths and weaknesses. Let’s break them down and figure out when to use what.

The arguments below are based on my experience with SQL Server databases, but the concepts apply to other database systems as well. It is also important to note that the arguments are not exhaustive and that there are exceptions to every rule.

State-Based Deployments: The “Desired State” Approach

State-based deployments treat your database like infrastructure as code. You decide how the database should look, including tables, indexes, and stored procedures.

Your tool, such as SQL Server Data Tools (SSDT), Redgate SQL Compare, or DbForge Schema Compare, spots gaps between the current and desired database states. Then, it creates the SQL scripts needed to sync them up.

Pros:

  • Simplicity - You define the end state, and the tool handles the transition. No need to write upgrade scripts manually.
  • Good for large teams - Since the tool manages schema changes, conflicts and merge issues are reduced.
  • Works well for stable databases - Ideal when you’re dealing with well-defined schemas that don’t change drastically.

Cons:

  • Lack of versioning for changes - Since you’re only storing the desired state, you lose visibility into how the database evolved.
  • Auto-generated scripts can be risky - Sometimes, the tool generates scripts that aren’t optimal (or even dangerous). Dropping and recreating objects is not always an option in production!
  • Harder to control complex data transformations - If a column rename is detected as a drop and add, you might lose data if you’re not careful.

When to Use State-Based Deployments

  • You have a well-defined and stable schema.
  • Your team prefers automation over manual scripting.
  • You don’t need to track every change at the script level.

Migration-Based Deployments: The “Versioned Changes” Approach

Migration-based deployments treat database changes as incremental scripts that are applied in order.

Each migration is a SQL script (or a code-based change) that modifies the database schema or data, just like a Git commit history. Tools like Flyway, Liquibase, DBUp, and Alembic (for Python-based projects) help manage this process.

Pros:

  • Full version control - Every change is explicitly written and stored in source control. You can roll back if needed.
  • Better for complex data transformations - If you need to rename a column or migrate data, you have full control over the SQL commands.
  • Predictable and transparent - Since all changes are manually defined, you know exactly what will be deployed.

Cons:

  • More manual effort - Developers have to write and maintain all migration scripts.
  • Can get messy - If multiple branches introduce migrations in parallel, conflicts can occur.
  • Order of execution matters - If migrations are applied in the wrong order, things can break.

When to Use Migration-Based Deployments

  • Your team needs strict version control and rollback capabilities.
  • You make frequent, complex schema changes.
  • You want full control over how data is transformed.

Which One Should You Use?

There’s no universal answer, your choice depends on your project’s needs.

  • If you have a stable, well-defined schema and want automation, go with state-based.
  • If you need full control over schema changes and versioning, migration-based is your best bet.

In many enterprise environments, a hybrid approach works best. Use state-based deployments to ensure a consistent database structure. Use migration-based scripts for controlled data transformations. This way, you get the best of both worlds.

Tools to Automate Database CI/CD

Here are some products that can help you streamline your database deployments:

State-Based Deployment Tools:

  • SQL Server Data Tools (SSDT) - Integrated with Visual Studio, great for managing SQL Server schema.
  • Redgate SQL Compare - Compares and synchronizes SQL Server databases.
  • DbForge Schema Compare - Helps with schema comparison and synchronization.

Migration-Based Deployment Tools:

  • Flyway - A lightweight, SQL-based migration tool supporting multiple databases.
  • Liquibase - Provides database version control with XML, YAML, JSON, or SQL-based migrations.
  • DBUp - A simple .NET-based tool for running SQL migrations.
  • dbops - A PowerShell module for managing database deployments based on DbUp

Final Thoughts

Database CI/CD isn’t just about picking the right tool, it’s about understanding how database changes affect your business. I’ve seen teams struggle with broken deployments because they didn’t consider data integrity or rollback strategies. The key is testing, automation, and choosing the right approach for your specific needs.

What has your experience been with database deployments? Have you found one method to work better than the other? Let’s discuss!