Declarative Schema Migrations

I would love to be able to declare my database schema declaratively in a schema.sql or schema.json file and pass that up to CockroachDB where it can reconcile the changes to tables/columns/indexes etc for me.

Could this be built into CockroachDB?

That would be nice but not really necessary IMHO. At the moment, we are using jobs to migrate the schemas in an idempotent way. There are some other ways of doing this (e.g https://www.liquibase.org/), but I prefer doing this with a single schema file which is idempotent. The job (k8s job) should be run every time you release new versions of the code.

We decided to go with K8S job instead of initContainers because in initContainers case you can end up running the schema file in multiple containers, depending on the replicas count.

You can achieve idempotency in the schema evolution by using <cmd if not exists …>.

Thanks for the reply!

We actually do something similar with our migrations. We use migrate which is run as part of our CI pipeline. My suggestion above doesn’t stop the need for migrations to be run.

What I am suggesting is the use of a single schema file which describes the database schema declaratively, without having to use ALTER and IF NOT EXISTS statements whenever a change is made. You just update the schema you already have and allow cockroachdb to work out the delta and ADD/DROP columns etc accordingly.

This allows developers to use their schema.sql as a source of truth, without having to scan through multiple ALTER lines to get an understanding of the model.

It also allows for better tooling to be built around schema files. For example, parsers can be built to code generate models from a schema, without having to understand ALTER statements. You could also generate schema files from code first models, perhaps even run as part of application code, which opens up some interesting possibilities.

1 Like