SQLFluff is a popular SQL linter and formatter for SQL code. This repository provides a set of custom rules designed to enforce safe SQL practices and prevent dangerous or breaking changes in your codebase.
We use SQLFluff exclusively through pre-commit to automatically lint SQL files before every commit. You do not need to use SQLFluff from the CLI or enable core rules.
Add the following to your .pre-commit-config.yaml:
repos:
- repo: https://github.com/sqlfluff/sqlfluff
rev: 3.4.0 # Use the desired version
hooks:
- id: sqlfluff-lint
additional_dependencies:
- git+ssh://git@versioning.advans-group.com/DevOps/database/sqlfluff-custom-rules.git@main#egg=safe-sqlfluff
args:
- --ignore
- parsingTo ensure only the custom rules are used, add a .sqlfluff file to your repository root:
[sqlfluff]
dialect = postgres
large_file_skip_byte_limit = 0
output_line_length = 100
max_line_length = 100
rules = custom_rulesIf you want to push changes that triggers one or more custom rules, you can tell SQLFluff to ignore this in two ways.
1. Using the comment --noqa
to use this method, write a comment like this: --noqa: disable=LL01 just above the line that raised the linting error.
This is the best way because it is at the querry level and allows you to see what rule was causing issues.
2. Using a .sqlfluffignore
This file can be added in the project and works just like any ignore file like .gitignore for example.
This can be used to skip checks on a given file, patterns and folders.
If you need assistance with a rule that is firing on your project and you are unsure about how to fix it, reach out to the DevOps service.
What is this rule about?
Flags UPDATE statements that do not include a WHERE clause.
Why did it fire?
It fires when an UPDATE statement is missing a WHERE clause, which would update all rows in the table.
Why is the rule important?
Running an UPDATE without a WHERE clause can unintentionally modify every row in a table, leading to data loss or corruption.
How to fix / What is the good practice?
Always use a WHERE clause to target only the intended rows.
- ❌ Anti-pattern:
UPDATE foo SET bar = 'some value';
- ✅ Best practice:
UPDATE foo SET bar = 'some value' WHERE id = 13;
What is this rule about?
Flags DELETE statements that do not include a WHERE clause.
Why did it fire?
It fires when a DELETE statement is missing a WHERE clause, which would delete all rows in the table.
Why is the rule important?
Running a DELETE without a WHERE clause can unintentionally remove all data from a table.
How to fix / What is the good practice?
Always use a WHERE clause to target only the intended rows.
- ❌ Anti-pattern:
DELETE FROM foo;
- ✅ Best practice:
DELETE FROM foo WHERE id = 13;
What is this rule about?
Flags any use of TRUNCATE TABLE.
Why did it fire?
It fires when a TRUNCATE statement is used on a table.
Why is the rule important?
TRUNCATE irreversibly removes all data from a table and cannot be rolled back in some databases. It is a destructive operation.
How to fix / What is the good practice?
Avoid using TRUNCATE TABLE. If you need to remove data, use DELETE with a WHERE clause or truncate only in controlled environments.
- ❌ Anti-pattern:
TRUNCATE TABLE foo;
What is this rule about?
Flags direct renaming of tables using ALTER TABLE ... RENAME TO.
Why did it fire?
It fires when a table is renamed directly.
Why is the rule important?
Renaming tables directly can break dependencies and references in code, views, or other database objects.
How to fix / What is the good practice?
Use a migration pattern: create a new table, migrate data, drop the old table, and rename the new one if needed.
- ❌ Anti-pattern:
ALTER TABLE old_table RENAME TO new_table;
- ✅ Best practice:
-- Step 1: Create new table CREATE TABLE new_table (...); -- Step 2: Migrate data INSERT INTO new_table SELECT * FROM old_table; -- Step 3: Drop old table DROP TABLE old_table; -- Step 4: Optionally rename ALTER TABLE new_table RENAME TO old_table;
What is this rule about?
Flags direct renaming of columns using ALTER TABLE ... RENAME COLUMN.
Why did it fire?
It fires when a column is renamed directly.
Why is the rule important?
Renaming columns directly can break code, views, or other dependencies.
How to fix / What is the good practice?
Use a migration pattern: add a new column, migrate data, drop the old column.
- ❌ Anti-pattern:
ALTER TABLE tbl RENAME COLUMN old_col TO new_col;
- ✅ Best practice:
ALTER TABLE tbl ADD COLUMN new_col ...; UPDATE tbl SET new_col = old_col; ALTER TABLE tbl DROP COLUMN old_col;
What is this rule about?
Flags dropping columns using ALTER TABLE ... DROP COLUMN.
Why did it fire?
It fires when a column is dropped from a table.
Why is the rule important?
Dropping columns is a breaking change and can result in data loss and broken dependencies.
How to fix / What is the good practice?
Avoid dropping columns unless absolutely necessary. Consider deprecating columns first.
- ❌ Anti-pattern:
ALTER TABLE tbl DROP COLUMN col;
What is this rule about?
Flags adding a new column with NOT NULL constraint and no default value.
Why did it fire?
It fires when a new column is added as NOT NULL without a DEFAULT value.
Why is the rule important?
Adding a NOT NULL column without a default will fail if the table already contains data, or will require all existing rows to be updated.
How to fix / What is the good practice?
Always provide a DEFAULT value when adding a NOT NULL column to an existing table.
- ❌ Anti-pattern:
ALTER TABLE tbl ADD COLUMN col INT NOT NULL;
- ✅ Best practice:
ALTER TABLE tbl ADD COLUMN col INT NOT NULL DEFAULT 0;
What is this rule about?
Flags making an existing column NOT NULL using ALTER TABLE ... ALTER COLUMN ... SET NOT NULL.
Why did it fire?
It fires when an existing column is altered to add a NOT NULL constraint.
Why is the rule important?
Making a column NOT NULL is a breaking change if any existing data is null.
How to fix / What is the good practice?
Ensure all existing data is non-null before applying the constraint, or avoid making the change if not required. If needed, update all NULL fields of the column to a selected default value and add the default value to the constraint.
- ❌ Anti-pattern:
ALTER TABLE tbl ALTER COLUMN col SET NOT NULL;
- ✅ Best practice:
ALTER TABLE tbl ALTER COLUMN col SET DEFAULT 0; UPDATE tbl SET col = 0 WHERE col IS NULL; ALTER TABLE tbl ALTER COLUMN col SET NOT NULL;
What is this rule about?
Flags dropping tables using DROP TABLE.
Why did it fire?
It fires when a table is dropped.
Why is the rule important?
Dropping tables is a destructive, breaking change that removes all data and dependencies.
How to fix / What is the good practice?
Avoid dropping tables unless absolutely necessary. Consider archiving data or renaming tables instead.
- ❌ Anti-pattern:
DROP TABLE tbl;
What is this rule about?
Flags changing a column's data type using ALTER TABLE ... ALTER COLUMN ... TYPE.
Why did it fire?
It fires when a column's type is changed.
Why is the rule important?
Changing a column's type can cause data loss, conversion errors, or break application logic.
How to fix / What is the good practice?
Avoid changing column types unless you are certain it is safe and all data is compatible.
- ❌ Anti-pattern:
ALTER TABLE tbl ALTER COLUMN col TYPE varchar(10);
What is this rule about?
Flags dropping constraints using ALTER TABLE ... DROP CONSTRAINT.
Why did it fire?
It fires when a constraint is dropped from a table.
Why is the rule important?
Dropping constraints can break data integrity and allow invalid data.
How to fix / What is the good practice?
Avoid dropping constraints unless absolutely necessary. Consider disabling or modifying constraints instead.
- ❌ Anti-pattern:
ALTER TABLE tbl DROP CONSTRAINT pk_tbl;
What is this rule about?
Flags dropping views using DROP VIEW.
Why did it fire?
It fires when a view is dropped.
Why is the rule important?
Dropping views can break dependencies in code or other database objects.
How to fix / What is the good practice?
Avoid dropping views unless absolutely necessary. Consider renaming or archiving views instead.
- ❌ Anti-pattern:
DROP VIEW myview;
What is this rule about?
Flags dropping functions using DROP FUNCTION.
Why did it fire?
It fires when a function is dropped.
Why is the rule important?
Dropping functions can break application logic or dependencies.
How to fix / What is the good practice?
Avoid dropping functions unless absolutely necessary. Consider deprecating or renaming functions instead.
- ❌ Anti-pattern:
DROP FUNCTION myfunc();
What is this rule about?
Flags dropping triggers using DROP TRIGGER.
Why did it fire?
It fires when a trigger is dropped.
Why is the rule important?
Dropping triggers can break automated processes or data integrity.
How to fix / What is the good practice?
Avoid dropping triggers unless absolutely necessary. Consider disabling triggers instead.
- ❌ Anti-pattern:
DROP TRIGGER mytrigger ON tbl;
What is this rule about?
Flags dropping schemas using DROP SCHEMA.
Why did it fire?
It fires when a schema is dropped.
Why is the rule important?
Dropping schemas is a destructive, breaking change that removes all contained objects and data.
How to fix / What is the good practice?
Avoid dropping schemas unless absolutely necessary. Consider archiving or renaming schemas instead.
- ❌ Anti-pattern:
DROP SCHEMA myschema;