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": {}
}
}
Available Rules
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
totype_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;
-
class
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
andALTER 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.
-
class
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
, andDOUBLE 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
, andDOUBLE 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.
-
class
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.
-
class
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
-
class
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);
-
class
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);
-
class
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) );
-
class