January 18, 2019
Skeema uses a declarative approach to schema management: the repository reflects a desired end-state of table and object definitions, and the tool figures out how to convert any live database into this state.
This contrasts with most preexisting schema management systems, which instead are based on a concept of migrations: an incremental series of files, each defining a schema change operation and its corresponding rollback. In this post, we’ll discuss the differences in these approaches.
Language
Popular MVC frameworks, such as Rails and Django, include built-in migration tools. These work very well for initial rapid development, and each migration file is written in the same language (e.g. Ruby or Python) as the application itself. Over time, however, this can become more troublesome: what happens once your company has backend systems/tools written in multiple languages? You either have to shoehorn all migrations into one language (requiring engineers to be fluent in multiple programming languages), or have a separate migration tool for each language. Either way, this workflow gets extremely messy.
In contrast, declarative systems can rely purely on SQL, since SQL is already a declarative language. The tool – and corresponding schema repo – can be completely agnostic about what language(s) the backend systems use. SQL is already designed specifically for relational databases, and CREATE TABLE statements are very easy for developers to write.
Rollbacks
In migration systems, each migration file defines not just an action to take, but also a reverse action that rolls back the change. Some migration tools auto-generate this, but others require it to be written by hand, which introduces a potential source of human error: in theory the rollback could contain a mistake, or even refer to the wrong table in the case of bad copypasta.
Meanwhile, in a declarative system, rollbacks are implicit. Engineers don’t ever need to define them. To return to a previous state, you can simply use your chosen SCM tool and workflow (e.g. git checkout or git revert) to make the repo reflect a historical commit, and then skeema push again.
In either approach, care must be taken around rollbacks in general, to avoid accidentally destroying data. Skeema’s built-in detection of destructive changes comes in handy here, requiring a user to include --allow-unsafe to confirm that a destructive action is intentional.
Readability
In a declarative repo of purely CREATE statements, it is immediately obvious what the final desired state of the schema is: each file declares exactly what you want each table to look like. Engineers can use SCM to examine history of any table, and diffs are easy to understand visually. The entire repo serves as an easy-to-grok reference of what tables exist, and how they’re defined; engineers can keep this open in their preferred IDE.
The file layout in migration-based systems tends to be much harder to read. Each incremental migration may just affect some piece of a table that was defined in a previous file; the full change set of a single table may span many separate files. In SCM, each migration just consists of adding more incremental files, making history harder to piece together.
New environment performance
Once a migration system has been used for a long period of time, a huge number of incremental migration files can accumulate. This means that bringing up a new dev environment can be slow, as the migration tool may have to iterate through hundreds of tiny ALTER TABLE statements.
Some migration tools allow you to “squash” or “flatten” the migrations to remove this clutter. However, this makes the SCM history even harder to follow, since it typically involves removing all of the incremental migration files from before the current point in time.
These issues simply don’t exist in a declarative workflow. A declarative schema repo is always inherently flattened, by definition.
Tracking state
Declarative tools know how to introspect the state of live databases, and “diff” this against the desired state declared in the filesystem. This means they work regardless of what state a live database is in. If some engineer made a manual, direct change to a database – either by mistake, or in an emergency – this doesn’t present a problem for a declarative tool.
Migration tools typically need to keep track of what incremental “version” a schema is currently on, often using an extra table to track this in each schema. If a manual change is introduced outside of the tool, most migration tools won’t be aware of it, and could break due to the state being unknown.
Infrastructure as code
Modern dev/ops tools use an “infrastructure as code” approach, meaning the desired infrastructure is expressed in a repo. Systems like Kubernetes use a concept of a reconciliation loop, where the desired state is repeatedly compared to the live state, and the tool understands how to diff and apply any changes. This is inherently a declarative approach.
The same reasoning is directly applicable to relational database schemas. This is especially true in large sharded environments, where schema changes on some shards can fail, just due to routine master failure or scheduled maintenance. A declarative system can simply loop until the states match, trying again after the failed cluster is restored.
One drawback: Renames
This is one area where migration tools have the upper hand: they can express table renames and column renames just as any other incremental migration. Declarative tools cannot natively express renames, which are inherently imperative actions.
Practically speaking, renames tend to be rare in production, since they introduce substantial deploy-order complexity (in terms of the application code referring to the table or column being renamed). This means having a separate “out-of-band” deploy process for renames may be necessary anyway, even if you’re using a migration tool that supports them anyway.
Although Skeema currently has no built-in support for renames, you can use skeema pull to update the repo to reflect any such out-of-band changes. And to aid in rapid development, you can use settings like safe-below-size=1 to allow destructive changes only on empty tables. After all, in an empty table, a rename is always equivalent to a drop followed by an add/create.