squabble

Catch unsafe SQL migrations.

-- sql/migration.sql:4:46 ERROR: column "uh_oh" has a disallowed constraint
ALTER TABLE big_table ADD COLUMN uh_oh integer DEFAULT 0;
                                               ^

Squabble works to help automate the process of reviewing SQL migrations by catching simple mistakes, such as:

• Changing the type of an existing column
• Adding a new column with a default value or NOT NULL constraint
• Building an index without using CONCURRENTLY

For more information on why these particular cases can be dangerous:

• https://www.braintreepayments.com/blog/safe-operations-for-high-volume-postgresql/
• https://blog.codeship.com/rails-migrations-zero-downtime/
• https://stripe.com/blog/online-migrations

Currently, most of the rules have been focused on Postgres and its quirks. However, squabble can parse any ANSI SQL and new rules that are specific to other databases are appreciated!

Installation

$ pip3 install squabble
$ squabble --help

Note: Squabble is only supported on Python 3.5+

If you’d like to install from source:

$ git clone /~https://github.com/erik/squabble.git && cd squabble
$ python3 -m venv ve && source ve/bin/activate
$ python setup.py install
$ squabble --help

Configuration

To see a list of rules, try

$ squabble --list-rules

Then, to show more verbose information about a rule (such as rationale and configuration options)

$ squabble --show-rule AddColumnsDisallowConstraints

Once a configuration file is in place, it can be passed explicitly on the command line, or automatically looked up.

$ squabble -c path/to/config ...

If not explicitly given on the command line, squabble will look for a file named .squabblerc in the following places (in order):

• ./.squabblerc
• (git_repo_root)/.squabblerc
• ~/.squabblerc

Example Configuration

{
  "reporter": "color",

  "plugins": [
    "/some/directory/with/custom/rules"
  ],

  "rules": {
    "AddColumnsDisallowConstraints": {
      "disallowed": ["DEFAULT", "FOREIGN", "NOT NULL"]
    }
  }
}

nitty gritty

• Parsing is done using libpg_query, a Postgres query parser.
  • theoretically it will work with other SQL dialects
• Rules are implemented by registering callbacks while traversing the Abstract Syntax Tree of the query.
  • e.g. entering a CREATE TABLE node registers a callback for a column definition node, which checks that the column type is correct.

Writing your own lint rules

As a somewhat unfortunate consequence of our reliance on libpg_query, the abstract syntax tree is very, very specific to Postgres. While developing new rules, it will be necessary to reference the Postgres AST Node source listing, or, more readably, the Python bindings.

import squabble.rule
from squabble.rules import BaseRule

class AllTablesMustBeLoud(BaseRule):
    """
    A custom rule which makes sure that all table names are expressed
    in CAPSLOCK NOTATION.
    """

    # Define the different message types that this rule can return
    MESSAGES = {
        'table_not_loud_enough': 'table "{name}" not LOUD ENOUGH'
    }

    def enable(self, root_ctx):
        """
        Called before the root AST node is traversed. Here's where most
        callbacks should be registered for different AST nodes.

        Each linter is initialized once per file that it is being run
        against. `self._options` will contain the merged base configuration
        with the file-specific configuration options for this linter.
        """

        # Register that any time we see a `CreateStmt` (`CREATE TABLE`), call
        # self._check()
        root_ctx.register('CreateStmt', self._check_create())

        # When we exit the root `ctx`, call `self._on_finish()`
        root_ctx.register_exit(lambda ctx: self._on_finish(ctx))

    # node_visitor will pass in `ctx, node` for you so there's no need to
    # use a lambda
    @squabble.rule.node_visitor
    def _check(self, ctx, node):
        """
        Called when we enter a 'CreateStmt' node. Here we can register more
        callbacks if we need to, or do some checking based on the `node` which
        will be the AST representation of a `CREATE TABLE`.
        """

        table_name = node.relation.relname.value
        if table_name != table_name.upper():
            # Report an error if this table was not SCREAMING_CASE
            ctx.report(
                self,
                'table_name_not_loud_enough',
                params={'name': table_name},
                node=node.relation)

Acknowledgments

This project would not be possible without:

• libpg_query - Postgres query parser
• pglast - Python bindings to libpg_query
• Postgres - …obviously