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

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)

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

Bases: squabble.rules.BaseRule

Prevent changing the type of an existing column.

Configuration:

{ "DisallowChangeColumnType": {} }

class ChangeTypeNotAllowed(**kwargs)

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

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)

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

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)

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.

DisallowNotIn

class squabble.rules.disallow_not_in.DisallowNotIn

Bases: squabble.rules.BaseRule

Prevent NOT IN as part of queries, due to the unexpected behavior around NULL values.

Configuration:

{ "DisallowNotIn": {} }

class NotInNotAllowed(**kwargs)

Bases: squabble.message.Message

NOT IN (along with any expression containing NOT ... IN) should generally not be used as it behaves in unexpected ways if there is a null present.

-- Always returns 0 rows
SELECT * FROM foo WHERE col NOT IN (1, null);

-- Returns 0 rows if any value of bar.x is null
SELECT * FROM foo WHERE col NOT IN (SELECT x FROM bar);

col IN (1, null) returns TRUE if col=1, and NULL otherwise (i.e. it can never return FALSE).

Since NOT (TRUE) = FALSE, but NOT (NULL) = NULL, it is not possible for this expression to return TRUE.

If you can guarantee that there will never be a null in the list of values, NOT IN is safe to use, but will not be optimized nicely.

DisallowPaddedCharType

class squabble.rules.disallow_padded_char_type.DisallowPaddedCharType

Bases: squabble.rules.BaseRule

Prevent using CHAR(n) data type.

Postgres recommends never using CHAR(n), as any value stored in this type will be padded with spaces to the declared width. This padding wastes space, but doesn’t make operations on any faster; in fact the reverse, thanks to the need to strip spaces in many contexts.

In most cases, the variable length types TEXT or VARCHAR will be more appropriate.

Configuration

{ "DisallowPaddedCharType": {} }

class WastefulCharType(**kwargs)

Bases: squabble.message.Message

Any value stored in this type will be padded with spaces to the declared width. This padding wastes space, but doesn’t make operations on any faster; in fact the reverse, thanks to the need to strip spaces in many contexts.

From a storage point of view, CHAR(n) is not a fixed-width type. The actual number of bytes varies since characters (e.g. unicode) may take more than one byte, and the stored values are therefore treated as variable-length anyway (even though the space padding is included in the storage).

If a maximum length must be enforced in the database, use VARCHAR(n), otherwise, consider using TEXT as a replacement.

DisallowRenameEnumValue

class squabble.rules.disallow_rename_enum_value.DisallowRenameEnumValue

Bases: squabble.rules.BaseRule

Prevent renaming existing enum value.

Configuration:

{ "DisallowChangeEnumValue": {} }

class RenameNotAllowed(**kwargs)

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.

DisallowTimestampPrecision

class squabble.rules.disallow_timestamp_precision.DisallowTimestampPrecision

Bases: squabble.rules.BaseRule

Prevent using TIMESTAMP(p) due to rounding behavior.

For both TIMESTAMP(p) and TIMESTAMP WITH TIME ZONE(p), (as well as the corresponding TIME types) the optional precision parameter p rounds the value instead of truncating.

This means that it is possible to store values that are half a second in the future for p == 0.

To only enforce this rule for certain values of p, set the configuration option allow_precision_greater_than.

Configuration

{ "DisallowTimestampPrecision": {
    "allow_precision_greater_than": 0
  }
}

class NoTimestampPrecision(**kwargs)

Bases: squabble.message.Message

Specifying a fixed precision for TIMESTAMP and TIME types will cause the database to round inserted values (instead of truncating, as one would expect).

This rounding behavior means that some values that get inserted may be in the future, up to half a second with a precision of 0.

Instead, explicitly using date_trunc('granularity', time) may be a better option.

DisallowTimetzType

class squabble.rules.disallow_timetz_type.DisallowTimetzType

Bases: squabble.rules.BaseRule

Prevent using time with time zone, along with CURRENT_TIME.

Postgres recommends never using this type, citing that it’s only implemented for ANSI SQL compliance, and that timestamptz / timestamp with time zone is almost always a better solution.

Configuration

{ "DisallowTimetzType": {} }

class NoTimetzType(**kwargs)

Bases: squabble.message.Message

The type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness.

In most cases, a combination of date, time, timestamp without time zone, and timestamp with time zone should provide a complete range of date/time functionality required by any application.

class NoCurrentTime(**kwargs)

Bases: squabble.message.Message

CURRENT_TIME returns a time with time zone type, which is likely not what you want.

In most cases, CURRENT_TIMESTAMP is the correct replacement.

Some other options:

• CURRENT_TIMESTAMP, now() - timestamp with time zone
• LOCALTIMESTAMP - timestamp without time zone
• CURRENT_DATE - date
• LOCALTIME - time

RequireColumns

class squabble.rules.require_columns.RequireColumns

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)

Bases: squabble.message.Message

class ColumnWrongType(**kwargs)

Bases: squabble.message.Message

RequireConcurrentIndex

class squabble.rules.require_concurrent_index.RequireConcurrentIndex

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 file as the 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)

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

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)

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

Bases: squabble.rules.BaseRule

Require that all new tables specify a PRIMARY KEY constraint.

Configuration:

{ "RequirePrimaryKey": {} }

class MissingPrimaryKey(**kwargs)

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)
);