I’m working on a new style of database migration system that is coming along nicely. There is one last aspect of it that I want to finish, which is to enable MySQL linting. For background, this is a little different than standard migration systems that come with e.g. laravel, django, etc… Rather than specifying actions to migrate the database from one state to the next, the system is driven by .sql files which contain a CREATE TABLE statement that gives the desired structure for each table. My migration system then reads these files to determine what the database should look like, reads the database to see what it actually looks like, and generates ALTER TABLE commands to make the one match the other.

One big feature I want to accomplish with this is some linting on the database structure itself. In this case it means setting rules for column types, application specific rules, etc… I want to give the “user” (in this case, me and my team), full control of what these rules are. Just to give you an idea of what I am aiming for, I have linting rules in mind like this:

1. Any column that represents a foreign key (identified by having a column name ending in _id) should have an actual foreign key constraint on it.
2. Any column with a foreign key should have a column name ending in _id)
3. Certain column types are disallowed (enum, boolean, some others)

I’ve seen other linting systems that give you configurable options (how many spaces between open paren and identifiers, etc). However, I’m looking to build a fully configurable linting system, as these are the kinds of rules that could easily change from application-to-application, or team-to-team. My system currently reads the structure of the database files into some python objects with a clear API. As a result, I’m thinking about making the linting system work like this:

1. The migration linter is run from the command line.
2. migration system parses *.sql files and determines the database structure, storing each table in a convenient object
3. The user specifies the name of a python module that will receive these objects.
4. migration system loads up that module and, via a standardized API, passes tables in one-at-a-time (or more-at-a-time if necessary), so that the user’s module can process the tables and return user-defined linting errors
5. The migration system returns any linting errors to the command line

Obviously such a system can also be integrated into a build pipeline. The potential problem I forsee with such a system is that the user’s linting module will have to be installed on all machines where it needs to run. This might force the team to manage deployments and upgrades of their linting module if the rules ever change, which can certainly be a pain. Another option might be to let the user specify a folder where the migration system finds the linting module and imports it from. In that case the linting module might simply sit in the main code repository and be managed/distributed with everything else.

To summarize, the essence of the problem is that I want a part of my application to be written and under full-control of the end user. I want to make it as easy as possible for them to bolt it onto my application. It seems likely to me that a good way to accomplish this is by letting them specify the location of a python module on their system, specifying a clear API for that module to follow, and then manually adding that module location to the python path at run-time and calling it via the standardized API. Does anyone have experience with something along these lines? Any suggestions on methods that might work better for the end-user?