Built-in Rules

Squabble ships with several rules that are focused mostly on preventing unsafe schema migrations. To enable these rules, reference them in your .squabblerc configuration file.

For example:

{
  "rules": {
    "AddColumnDisallowConstraints": {
      "disallowed": ["DEFAULT"]
    },
    "RequirePrimaryKey": {}
  }
}

AddColumnDisallowConstraints

class squabble.rules.add_column_disallow_constraints.AddColumnDisallowConstraints[source]

Bases: squabble.rules.BaseRule

Prevent adding a column with certain constraints to an existing table.

Configuration:

{
    "AddColumnDisallowConstraints": {
        "disallowed": ["DEFAULT", "FOREIGN"]
    }
}
Valid constraint types:
  • DEFAULT
  • NULL
  • NOT NULL
  • FOREIGN
  • UNIQUE
class ConstraintNotAllowed(**kwargs)[source]

Bases: squabble.message.Message

When adding a column to an existing table, certain constraints can have unintentional side effects, like locking the table or introducing performance issues.

For example, adding a DEFAULT constraint may hold a lock on the table while all existing rows are modified to fill in the default value.

A UNIQUE constraint will require scanning the table to confirm there are no duplicates.

On a particularly hot table, a FOREIGN constraint will introduce possibly dangerous overhead to confirm the referential integrity of each row.

DisallowChangeColumnType

class squabble.rules.disallow_change_column_type.DisallowChangeColumnType[source]

Bases: squabble.rules.BaseRule

Prevent changing the type of an existing column.

Configuration:

{ "DisallowChangeColumnType": {} }
class ChangeTypeNotAllowed(**kwargs)[source]

Bases: squabble.message.Message

Trying to change the type of an existing column may hold a full table lock while all of the rows are modified.

Additionally, changing the type of a column may not be backwards compatible with code that has already been deployed.

Instead, try adding a new column with the updated type, and then migrate over.

For example, to migrate a column from type_a to type_b.

ALTER TABLE foo ADD COLUMN bar_new type_b;
UPDATE foo SET bar_new = cast(bar_old, type_b);
-- Deploy server code to point to new column
ALTER TABLE foo DROP COLUMN bar_old;

DisallowForeignKey

class squabble.rules.disallow_foreign_key.DisallowForeignKey[source]

Bases: squabble.rules.BaseRule

Prevent creation of new FOREIGN KEY constraints.

Optionally, can be configured with a list of table names that ARE allowed to create foreign key references.

This rule will check CREATE TABLE and ALTER TABLE statements for foreign keys.

Configuration

{
    "DisallowForeignKey": {
        "excluded": ["table1", "table2"]
    }
}
class DisallowedForeignKeyConstraint(**kwargs)[source]

Bases: squabble.message.Message

Sometimes, foreign keys are not possible, or may cause more overhead than acceptable.

If you’re working with multiple services, each of which with their own database, it’s not possible to create a foreign key reference to a table that exists on another database. In this case, you’ll likely need to rely on your business logic being correct to guarantee referential integrity.

A foreign key constraint requires the database to query the referenced table to ensure that the value exists. On high-traffic, write heavy production instances, this may cause unacceptable overhead on writes.

DisallowFloatTypes

class squabble.rules.disallow_float_types.DisallowFloatTypes[source]

Bases: squabble.rules.BaseRule

Prevent using approximate float point number data types.

In SQL, the types FLOAT, REAL, and DOUBLE PRECISION are implemented as IEEE 754 floating point numbers, which will not be able to perfectly represent all numbers within their ranges.

Often, they’ll be “good enough”, but when doing aggregates over a large table, or trying to store very large (or very small) numbers, errors can be exaggerated.

Most of the time, you’ll probably want to used a fixed-point number, such as NUMERIC(3, 4).

Configuration

{ "DisallowFloatTypes": {} }
class LossyFloatType(**kwargs)[source]

Bases: squabble.message.Message

The types FLOAT, REAL, and DOUBLE PRECISION are implemented as IEEE 754 floating point numbers, which by definition will not be able to perfectly represent all numbers within their ranges.

This is an issue when performing aggregates over large numbers of rows, as errors can accumulate.

Instead, using the fixed-precision numeric data types (NUMERIC, DECIMAL) are likely the right choice for most cases.

DisallowRenameEnumValue

class squabble.rules.disallow_rename_enum_value.DisallowRenameEnumValue[source]

Bases: squabble.rules.BaseRule

Prevent renaming existing enum value.

Configuration:

{ "DisallowChangeEnumValue": {} }
class RenameNotAllowed(**kwargs)[source]

Bases: squabble.message.Message

Renaming an existing enum value may not be backwards compatible with code that is live in production, and may cause errors (either from the database or application) if the old enum value is read or written.

RequireColumns

class squabble.rules.require_columns.RequireColumns[source]

Bases: squabble.rules.BaseRule

Require that newly created tables have specified columns.

Configuration

{
    "RequireColumns": {
        "required": ["column_foo,column_type", "column_bar"]
    }
}

If a column type is specified (like column_foo in the example configuration), the linter will make sure that the types match.

Otherwise, only the presence of the column will be checked.

class MissingRequiredColumn(**kwargs)[source]

Bases: squabble.message.Message

class ColumnWrongType(**kwargs)[source]

Bases: squabble.message.Message

RequireConcurrentIndex

class squabble.rules.require_concurrent_index.RequireConcurrentIndex[source]

Bases: squabble.rules.BaseRule

Require all new indexes to be created with CONCURRENTLY so they won’t block.

By default, tables created in the same index are exempted, since they are known to be empty. This can be changed with the option "include_new_tables": true.

Configuration:

{
    "RequireConcurrentIndex": {
        "include_new_tables": false
    }
}
class IndexNotConcurrent(**kwargs)[source]

Bases: squabble.message.Message

Adding a new index to an existing table may hold a full table lock while the index is being built. On large tables, this may take a long time, so the preferred approach is to create the index concurrently instead.

-- Don't do this
CREATE INDEX users_by_name ON users(name);

-- Try this instead
CREATE INDEX CONCURRENTLY users_by_name ON users(name);

RequireForeignKey

class squabble.rules.require_foreign_key.RequireForeignKey[source]

Bases: squabble.rules.BaseRule

New columns that look like references must have a foreign key constraint.

By default, “looks like” means that the name of the column matches the regex .*_id$, but this is configurable.

CREATE TABLE comments (
  post_id  INT,  -- warning here, this looks like a foreign key,
                 -- but no constraint was given

  -- No warning here
  user_id INT REFERENCES users(id)
)

ALTER TABLE books
  ADD COLUMN author_id INT;  -- warning here

Configuration

{
    "RequireForeignKey": {
        "column_regex": ".*_id$"
    }
}
class MissingForeignKeyConstraint(**kwargs)[source]

Bases: squabble.message.Message

Foreign keys are a good way to guarantee that your database retains referential integrity.

When adding a new column that points to another table, make sure to add a constraint so that the database can check that it points to a valid record.

Foreign keys can either be added when creating a table, or after the fact in the case of adding a new column.

CREATE TABLE admins (user_id INTEGER REFERENCES users(id));

CREATE TABLE admins (
  user_id INTEGER,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

ALTER TABLE admins ADD COLUMN user_id INTEGER REFERENCES users(id);

ALTER TABLE admins ADD FOREIGN KEY user_id REFERENCES users(id);

RequirePrimaryKey

class squabble.rules.require_primary_key.RequirePrimaryKey[source]

Bases: squabble.rules.BaseRule

Require that all new tables specify a PRIMARY KEY constraint.

Configuration:

{ "RequirePrimaryKey": {} }
class MissingPrimaryKey(**kwargs)[source]

Bases: squabble.message.Message

When creating a new table, it’s usually a good idea to define a primary key, as it can guarantee a unique, fast lookup into the table.

If no single column will uniquely identify a row, creating a composite primary key is also possible.

CREATE TABLE users (email VARCHAR(128) PRIMARY KEY);

-- Also valid
CREATE TABLE users (
  email VARCHAR(128),
  -- ...
  PRIMARY KEY(email)
);