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
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.
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
-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.
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.
.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
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 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.
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-tablebut 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
dockeris 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
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.
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.