How to Automate MySQL Database Migrations With Skeema – CloudSavvy IT

Skeema is an open-source CLI tool that lets you synchronize MySQL database schemas across multiple hosts. It works with plain SQL files containing CREATE TABLE statements.

Database migrations are one of the most common challenges in the software development process. How do you version your schema and apply changes to all your environments?

Skeema helps alleviate this pain point by letting you manage your schema as regular SQL. There’s no special syntax or configuration file format to learn. When you push your schema up to a host, Skeema will pull the definitions of the tables on that host and diff them against your local files. It’ll then compute the CREATE, ALTER, and DROP statements to make the remote host match your SQL files.

You’ll need to be using InnoDB tables to get the most out of Skeema. It can work with MyISAM tables but not all features are supported and you may run into issues.

Getting Started

Skeema is available for Linux and Mac. DEB and RPM packages are offered, as well as pre-built standalone binaries. Grab the appropriate download for your system and either install the package or extract the binary to a directory that’s in your path.

The first stage in using Skeema is to get your database’s existing schema. If you’ve already got a collection of SQL files with CREATE TABLE statements, you can use them as-is. Otherwise, run the skeema init command to get the SQL representing a live database.

Skeema’s CLI accepts the same arguments as the regular mysql command. Use the -h, -u and -p flags to specify your MySQL host, username, and password. The MySQL user should have administrative access to your schemas; otherwise, Skeema might not be able to inspect them or apply all changes. Skeema will deposit your SQL files into a new directory within your working directory; add the -d flag to choose what this is called.

skeema init -h example.com -u root -p -d my-sql

Skeema defaults to exporting the SQL for all the schemas on the host. Each schema will get its own subdirectory within the my-sql folder. Use the --schema flag to indicate a specific schema to export. It will be placed directly into my-sql, without any subdirectory nesting.

Synchronizing Schemas

Once you’ve got your SQL, you’re ready to synchronize it to another host. Use the skeema push command to diff your local SQL against another MySQL server. Skeema will automatically apply any changes it detects.

cd my-sql
skeema push -h example.com -u root -p -d my-sql

Try editing one of the CREATE TABLE statements in your SQL. Add a new column or alter the type of an existing one. When you skeema push, Skeema will generate an ALTER TABLE statement that updates the remote table to match.

Potentially destructive operations like dropping a table or changing a column’s data type are disabled by default. This helps you avoid unintentional data loss if you run Skeema accidentally or specify an incorrect server. Destructive operations are activated by adding the --allow-unsafe flag to skeema push commands.

Using Multiple Environments

A common use of Skeema is keeping multiple environments in sync. If you’ve got local, dev, and production servers, Skeema lets you name them so you can seamlessly push and pull between them.

Edit the .skeema file in your schema directory to enable this functionality. This is an INI-like configuration structure where each section defines a new named environment. You’ll have a single production environment, added by skeema init, to begin with.

Specify each server’s connection details so you can push and pull to keep everything in sync. Keys at the top of the file, outside any section, are global settings applied to all environments.

default-character-set=utf8mb4
default-collation=utf8mb4_general_ci
generator=skeema:1.5.2-community
schema=example-db

[production]
flavor=mysql:8.0
host=example.com
port=3306
password=example
user=mysql

[local]
flavor=mysql:8.0
host=localhost
port=3306
password=example
user=mysql

Now you can easily synchronize changes between environments:

skeema pull local
skeema push production

This command sequence first updates your on-disk SQL files to match your local server’s schema. The changes are then applied to the live production environment. Alternatively, you could manually edit the on-disk SQL and then push to both local and production.

This approach makes database migrations simple, repeatable, and less error-prone. You could incorporate Skeema into a CI pipeline to roll out database changes to production as part of your regular deployment flow.

Dry Runs and Lints

Sometimes you might want to check the changes an environment needs without actually applying them. In this case, use skeema diff to compare a named environment to your on-disk SQL files. Skeema will show you the differences so you can anticipate the effects of a skeema push.

Skeema also has a built-in linting tool. Run skeema lint to analyze your SQL files and find possible issues. Several configurable rules are supported. These help you ensure your SQL is consistent, supported, and compatible with modern MySQL best practices. Lint results are also shown during skeema push and skeema pull commands.

More Options

Skeema commands have several shared flags that let you customize operations. Here are a few of the most significant ones:

• ignore-table – Specify a list of table names to exclude from synchronization. These tables will not be pushed to a remote host or pulled from it. The flag supports regex syntax.
• ignore-trigger – ignore-table but applied to triggers.
• temp-schema – The name of the temporary schema to create on the database host. This database is used by Skeema as a staging ground for intermediate changes during push/pull operations. It’s automatically deleted after the operation completes.
• workspace – Controls where the temporary schema is created. The default is to use the remote host but docker is supported as an alternative value. This will create a new MySQL Docker container on your local machine for each Skeema operation. It will only work if you have Docker installed.
• connect-options – A comma-separated list of MySQL options to set on the remote database connection, such as sql_mode="ALLOW_INVALID_DATES",innodb_lock_wait_timeout=1.

These options give you more control over Skeema’s operations. They let you setup the database connection to exactly match your application’s MySQL configuration.

Conclusion

Skeema takes the pain out of database migrations by providing automation and compatibility with CI/CD deployment pipelines. It incorporates a built-in SQL linter, diff tool, and synchronization mechanism.

A premium version adds extra functionality including the ability to work with views and triggers. Skeema Premium is a commercial offering built atop the open-source Skeema Community core. It retails at $99/month and also supports Windows systems.

Adopting Skeema makes it easier for you to share database schema changes with your team, keeping all your environments in sync. It’s a safer choice than manually sharing ALTER TABLE statements that could be misplaced and don’t create a canonical representation of your current schema.