SQL Is No Excuse to Avoid DevOps
Ignoring the rather bonkers anecdote at the start of the article, I&#x27;d also argue against one of the other recommendations here, even though it is quite common in practice:<p><pre><code>  &gt; The command in array index n upgrades the schema from version n-1 to n.
  &gt; Thus, no matter which version is found, the software can bring the
  &gt; database to the required schema version. In fact, if an uninitialized
  &gt; database is found (for example, in a testing environment),
  &gt; it might loop through dozens of schema changes until it gets to the
  &gt; newest version.
</code></pre>
There are 3 major problems with traditional incremental migration strategies:<p>1. Once you&#x27;ve accumulated years of migrations, it&#x27;s unnecessarily slow to bring up a new dev&#x2F;test db. The tool is potentially running many ALTERs against each new table, vs a declarative approach which just runs a single CREATE with the desired final state.<p>2. The migration tool does not have a way of handling databases that aren&#x27;t in one of the well-defined versioned states. This can happen easily on dev databases, where a developer just wants to experiment before landing on the final desired migration.<p>3. Most of these tools require a developer to code (in a specific programming language, or a proprietary DSL) the migration logic, as well as its rollback. There&#x27;s plenty of room for human error there, especially since most of these tools don&#x27;t automatically validate that the rollback actually correctly reverts the migration.<p>In contrast, a declarative approach simply tracks the final desired state (a repo containing a bunch of CREATE statements), and the <i>tool</i> figures out how to properly transition from any arbitrary state to the new desired state. This permits the state to be expressed in pure SQL, with no need for a DSL or custom programming, nor any need to define rollback logic.<p>A declarative tool can also be used in an automated reconciliation loop, which can handle situations like a master failing mid-schema-change in a sharded environment. This is analogous to other declarative tools, such as k8s.<p>There are several recent open source projects using the declarative approach for schema management. I&#x27;m the author of one, Skeema [1], for MySQL&#x2F;MariaDB. Others include migra [2] for Postgres, and sqldef [3] for MySQL or Postgres.<p>Although declarative schema management is only recently picking up adoption, it isn&#x27;t a new approach. Facebook has been handling their schema changes using a declarative approach for over 8 years now.<p>[1] <a href="https:&#x2F;&#x2F;github.com&#x2F;skeema&#x2F;skeema" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;skeema&#x2F;skeema</a><p>[2] <a href="https:&#x2F;&#x2F;github.com&#x2F;djrobstep&#x2F;migra" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;djrobstep&#x2F;migra</a><p>[3] <a href="https:&#x2F;&#x2F;github.com&#x2F;k0kubun&#x2F;sqldef" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;k0kubun&#x2F;sqldef</a>