Docs: Schema Change Workflow

This document describes the recommended, idiomatic way to use Skeema manually / interactively, for a pull-request based schema change flow. However, there are many other ways to leverage the tool, especially in automation pipelines.

Initial one-time setup

  1. Use skeema init production to import schemas from your master database instance. If you have multiple distinct database clusters, repeat this step for each one.

  2. In the host directory created in step 1, use skeema add-environment development to configure the connection information for your dev environment(s). This can either be a central shared dev database (e.g. -h some.host.name, along with -P 1234 if using non-3306 port), or perhaps a separate database running locally on each engineer’s dev server reached via UNIX domain socket (-h localhost -S /path/to/mysql.sock).

  3. If you have additional environments such as “staging”, use skeema add-environment to configure connection information for them as well. Environment naming is completely arbitrary; no need to strictly use “production”, “development”, and “staging”. Just be aware that “production” is the default for all Skeema commands if no environment name is supplied as the first positional arg on the command-line.

  4. Add all of the generated directories and files to a git repo. This can either be a new repo that you create just for schema storage, or it can be placed inside of a corresponding application repo.

  5. Optional: if you have large tables, configure Skeema to use an external online schema change tool. For example, configure the alter-wrapper setting in a .skeema file in the top dir of your schema repo.

Updating dev environment with changes from other engineers

If each engineer has their own localhost dev database, they can use Skeema to pull in changes made by other team members.

  1. git checkout <branchname> to check out the shared trunk branch (typically main, master, or develop)

  2. git pull to obtain changes made by other team members

  3. skeema diff development to preview DDL corresponding to those changes

  4. skeema push development to apply those schema changes to localhost dev

Schema change process

Steps 1-3 are performed by a developer. Steps 4-6 can be performed by a developer or by a DBA / SRE / devops engineer, depending on your company’s preferred policy.

  1. Check out a new branch in your schema repo.

  2. Test the schema change in dev, and update the corresponding files in the repo. There are a few equivalent ways of doing this:

    a) If using a migration tool from an MVC framework (Rails, Django, etc): Run the migration tool on dev as usual. Then use skeema pull development to update the table files in the repo.

    b) If you prefer running DDL manually: Run the statement(s) in dev. Then use skeema pull development to update the table files.

    c) If you prefer to just change the CREATE statements directly: Modify the *.sql files as desired. Use skeema diff development to confirm the auto-generated DDL looks correct, and then use skeema push development to update the dev database.

  3. Commit the change to the repo, push to origin, and open a pull request. Follow whatever review process your team uses for code changes. If using Skeema Cloud Linter, your pull request will receive automated comments with errors and warnings, as well as the generated DDL. This saves your reviewers time.

  4. Once merged, git checkout master and git pull to ensure your working copy of the schema repo is up-to-date.

  5. skeema diff production to review the list of DDL that will need to be applied to production.

  6. skeema push production to execute the schema change. Be sure to run this from a centralized location which prevents two engineers from pushing at the same time.