Simplest method for declaratively applying a SQL schema to a table?

Question

Goal: I'm looking for a declarative and repeatable way of "applying" SQL table schemas to a database

What I've tried

Below is a SQL script that does what I want (as best as I can tell from my superficial knowledge of MySQL and with some manual testing):

1. If the table doesn't exist, it creates it
2. If the table does exist, it ensures it has the desired schema

-- create table

create table if not exists `EntryType` (
  `EntryCode` varchar(2) primary key,
  `Name` varchar(256) not null
) engine = InnoDB default charset = utf8mb4;

-- modify table

alter table `EntryType`
  engine = InnoDB,
  character set utf8mb4;

alter table `EntryType`
  modify `EntryCode` varchar(2),
  drop primary key,
  add primary key (`EntryCode`);

alter table `EntryType`
  modify `Name` varchar(256) not null;

Problems with this approach

• duplication: repeating column definitions, engine, and character set twice
• laborious: it takes a lot of time to craft these queries and manually test them each time a table is added or modified
• imperative: this is fundamentally an imperative approach to something that should be declarative

Question: Is there some safe means of declaratively "applying" a table schema?

• idempotent: can be applied any number of times with the exact same end state
• works whether or not the table already exists
• works whether or not the table already contains data
• works whether or not there are constraints and foreign key references to other tables

Preferences:

1. A pure SQL solution would be ideal
2. A reliable and easy-to-use CLI solution would work
3. Would also accept a relatively simple hand-coded SQL or Node.js solution

Answer 1

As suggested by Bill Karwin, the Skeema.io CLI ended up working out pretty well for a few simple tests.

The docs don't do a great job of guiding you through the setup process, so I captured what I figured out here.

First-time schema fetch

Download a ZIP file from the github repo releases page and unzip it.

Put the skeema binary somewhere in your project. Inside my project root, I put it in a bin folder, so from the project root the path is ./bin/skeema.

Then, make sure your MySQL database is running and you have the connection info. Mine was running locally on 127.0.0.1, port 3306 (the MySQL default, so I didn't have to enter it).

From the project root, I entered the following command to initialize things by fetching the current state of the database schema.

./bin/skeema init -h 127.0.0.1 -u root -p --schema=MyDB -d schemas

A few specifics:

• I used the --schema=MyDB option to track just one database. If you leave this out, it will track all databases it finds.
• The -d schemas option says to store the config and schema files in the schemas directory.
• Using -p option, it will prompt you for a password after running the command.

It then connects to and inspects your database, creates the schemas directory, and write some files there that represent the current schema state. You'll notice a .skeema config file and a .sql file for each table in the database.

Config tweaks

I then went into the schemas/.skeema config file and renamed the [production] section to [development] because this is actually a dev instance. By doing this, I'll have to specify development in every command because skeema assumes production as the default.

[Optional, Node.js projects only] The skeema command is intended to be run from within the folder that contains the .skeema config file, but I don't want to worry about this, so I created a script in my Node.js package.json file that basically does this for me every time.

{
  "scripts": {
    "skeema": "cd schemas && ../bin/skeema"
  },
}

Now I can type the following from the project root (or anywhere else within the project):

npm run skeema

A first schema change

While in the schemas/.skeema config file, I made a few changes to correct a character encoding mistake when setting up these tables:

• changed default-character-set=utf8 to default-character-set=utf8mb4
• changed default-collation=utf8_general_ci to default--collation=utf8mb4_unicode_ci

I also added a new column to one of the .sql files:

CREATE TABLE `EntryType` (
  `EntryCode` varchar(2) NOT NULL,
  `Name` varchar(256) NOT NULL,
  `Foo` varchar(3), -- new column
  PRIMARY KEY (`EntryCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Pushing a schema change

The first step is to see if there are schema differences:

npm run skeema diff development

The output should the schema changes you made, for example:

2020-07-02 19:32:14 [INFO]  Generating diff of 127.0.0.1:3306 MyDB vs
                            /Users/chriscalo/Projects/my-project/schemas/*.sql
-- instance: 127.0.0.1:3306
USE `MyDB`;
ALTER TABLE `EntryType` ADD COLUMN `Foo` varchar(3) DEFAULT NULL;
2020-07-02 19:32:15 [INFO]  127.0.0.1:3306 MyDB: diff complete

And then push the schema change:

yarn run skeema push development

That's it!