Enable linter for SQL migrations

Open Nutomic opened this issue 3 months ago • 8 comments

This could help us to catch common issues such as https://github.com/LemmyNet/lemmy/pull/6022.

Here are the available tools that I found:

https://github.com/sbdchd/squawk
https://github.com/bolajiwahab/pgrubic
https://github.com/sqlfluff/sqlfluff

cc @dullbananas @dessalines

Sep 29 '25 09:09 Nutomic

We already have a linter, called pg_format, that's in use, but its not perfect, and can't check a few weird cases like the one above.

iirc we tried a few different ones, but none was better than pg_format. You're free to try any of these out. A few of them have docker images, so that might be useful.

Oct 01 '25 15:10 dessalines

pgformat is only a formatter (like cargo fm) but it doesnt have any lint rules.

Oct 02 '25 08:10 Nutomic

Both sqlfluff and squawk look promising. Its worth a run of sqlfluff at least to see how it views our existing migrations.

Oct 02 '25 19:10 dessalines

Here is the output from squawk on a single migration

$ npx squawk-cli migrations/2025-09-19-090047_notify-mod-action/up.sql
warning[require-timeout-settings]: Missing `set lock_timeout` before potentially slow operations
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:2:1
│
2 │ ┏ ALTER TABLE notification
3 │ ┃     ADD COLUMN admin_add_id int REFERENCES admin_add ON UPDATE CASCADE ON DELETE CASCADE,
4 │ ┃     ADD COLUMN mod_add_to_community_id int REFERENCES mod_add_to_community ON UPDATE CASCADE ON DELETE CASCADE,
5 │ ┃     ADD COLUMN admin_ban_id int REFERENCES admin_ban ON UPDATE CASCADE ON DELETE CASCADE,
‡ ┃
11 │ ┃     ADD COLUMN mod_lock_comment_id int REFERENCES mod_lock_comment ON UPDATE CASCADE ON DELETE CASCADE,
12 │ ┃     ADD COLUMN mod_transfer_community_id int REFERENCES mod_transfer_community ON UPDATE CASCADE ON DELETE CASCADE;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Configure a `lock_timeout` before this statement.
╭╴
2 + set lock_timeout = '1s';
╰╴
warning[require-timeout-settings]: Missing `set statement_timeout` before potentially slow operations
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:2:1
│
2 │ ┏ ALTER TABLE notification
3 │ ┃     ADD COLUMN admin_add_id int REFERENCES admin_add ON UPDATE CASCADE ON DELETE CASCADE,
4 │ ┃     ADD COLUMN mod_add_to_community_id int REFERENCES mod_add_to_community ON UPDATE CASCADE ON DELETE CASCADE,
5 │ ┃     ADD COLUMN admin_ban_id int REFERENCES admin_ban ON UPDATE CASCADE ON DELETE CASCADE,
‡ ┃
11 │ ┃     ADD COLUMN mod_lock_comment_id int REFERENCES mod_lock_comment ON UPDATE CASCADE ON DELETE CASCADE,
12 │ ┃     ADD COLUMN mod_transfer_community_id int REFERENCES mod_transfer_community ON UPDATE CASCADE ON DELETE CASCADE;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Configure a `statement_timeout` before this statement
╭╴
2 + set statement_timeout = '5s';
╰╴
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:3:5
│
3 │     ADD COLUMN admin_add_id int REFERENCES admin_add ON UPDATE CASCADE ON DELETE CASCADE,
│     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
╭╴
3 │     ADD COLUMN if not exists admin_add_id int REFERENCES admin_add ON UPDATE CASCADE ON DELETE CASCADE,
╰╴               +++++++++++++
warning[prefer-bigint-over-int]: Using 32-bit integer fields can result in hitting the max `int` limit.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:3:29
│
3 │     ADD COLUMN admin_add_id int REFERENCES admin_add ON UPDATE CASCADE ON DELETE CASCADE,
│                             ━━━
│
├ help: Use 64-bit integer values instead to prevent hitting this limit.
╭╴
3 │     ADD COLUMN admin_add_id bigint REFERENCES admin_add ON UPDATE CASCADE ON DELETE CASCADE,
╰╴                            +++
warning[adding-foreign-key-constraint]: Adding a foreign key constraint requires a table scan and a `SHARE ROW EXCLUSIVE` lock on both tables, which blocks writes to each table.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:3:33
│
3 │     ADD COLUMN admin_add_id int REFERENCES admin_add ON UPDATE CASCADE ON DELETE CASCADE,
│                                 ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
│
╰ help: Add `NOT VALID` to the constraint in one transaction and then VALIDATE the constraint in a separate transaction.
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:4:5
│
4 │     ADD COLUMN mod_add_to_community_id int REFERENCES mod_add_to_community ON UPDATE CASCADE ON DELETE CASCADE,
│     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
╭╴
4 │     ADD COLUMN if not exists mod_add_to_community_id int REFERENCES mod_add_to_community ON UPDATE CASCADE ON DELETE CASCADE,
╰╴               +++++++++++++
warning[prefer-bigint-over-int]: Using 32-bit integer fields can result in hitting the max `int` limit.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:4:40
│
4 │     ADD COLUMN mod_add_to_community_id int REFERENCES mod_add_to_community ON UPDATE CASCADE ON DELETE CASCADE,
│                                        ━━━
│
├ help: Use 64-bit integer values instead to prevent hitting this limit.
╭╴
4 │     ADD COLUMN mod_add_to_community_id bigint REFERENCES mod_add_to_community ON UPDATE CASCADE ON DELETE CASCADE,
╰╴                                       +++
warning[adding-foreign-key-constraint]: Adding a foreign key constraint requires a table scan and a `SHARE ROW EXCLUSIVE` lock on both tables, which blocks writes to each table.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:4:44
│
4 │     ADD COLUMN mod_add_to_community_id int REFERENCES mod_add_to_community ON UPDATE CASCADE ON DELETE CASCADE,
│                                            ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
│
╰ help: Add `NOT VALID` to the constraint in one transaction and then VALIDATE the constraint in a separate transaction.
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:5:5
│
5 │     ADD COLUMN admin_ban_id int REFERENCES admin_ban ON UPDATE CASCADE ON DELETE CASCADE,
│     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
╭╴
5 │     ADD COLUMN if not exists admin_ban_id int REFERENCES admin_ban ON UPDATE CASCADE ON DELETE CASCADE,
╰╴               +++++++++++++
warning[prefer-bigint-over-int]: Using 32-bit integer fields can result in hitting the max `int` limit.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:5:29
│
5 │     ADD COLUMN admin_ban_id int REFERENCES admin_ban ON UPDATE CASCADE ON DELETE CASCADE,
│                             ━━━
│
├ help: Use 64-bit integer values instead to prevent hitting this limit.
╭╴
5 │     ADD COLUMN admin_ban_id bigint REFERENCES admin_ban ON UPDATE CASCADE ON DELETE CASCADE,
╰╴                            +++
warning[adding-foreign-key-constraint]: Adding a foreign key constraint requires a table scan and a `SHARE ROW EXCLUSIVE` lock on both tables, which blocks writes to each table.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:5:33
│
5 │     ADD COLUMN admin_ban_id int REFERENCES admin_ban ON UPDATE CASCADE ON DELETE CASCADE,
│                                 ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
│
╰ help: Add `NOT VALID` to the constraint in one transaction and then VALIDATE the constraint in a separate transaction.
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:6:5
│
6 │     ADD COLUMN mod_ban_from_community_id int REFERENCES mod_ban_from_community ON UPDATE CASCADE ON DELETE CASCADE,
│     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
╭╴
6 │     ADD COLUMN if not exists mod_ban_from_community_id int REFERENCES mod_ban_from_community ON UPDATE CASCADE ON DELETE CASCADE,
╰╴               +++++++++++++
warning[prefer-bigint-over-int]: Using 32-bit integer fields can result in hitting the max `int` limit.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:6:42
│
6 │     ADD COLUMN mod_ban_from_community_id int REFERENCES mod_ban_from_community ON UPDATE CASCADE ON DELETE CASCADE,
│                                          ━━━
│
├ help: Use 64-bit integer values instead to prevent hitting this limit.
╭╴
6 │     ADD COLUMN mod_ban_from_community_id bigint REFERENCES mod_ban_from_community ON UPDATE CASCADE ON DELETE CASCADE,
╰╴                                         +++
warning[adding-foreign-key-constraint]: Adding a foreign key constraint requires a table scan and a `SHARE ROW EXCLUSIVE` lock on both tables, which blocks writes to each table.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:6:46
│
6 │     ADD COLUMN mod_ban_from_community_id int REFERENCES mod_ban_from_community ON UPDATE CASCADE ON DELETE CASCADE,
│                                              ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
│
╰ help: Add `NOT VALID` to the constraint in one transaction and then VALIDATE the constraint in a separate transaction.
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:7:5
│
7 │     ADD COLUMN mod_lock_post_id int REFERENCES mod_lock_post ON UPDATE CASCADE ON DELETE CASCADE,
│     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
╭╴
7 │     ADD COLUMN if not exists mod_lock_post_id int REFERENCES mod_lock_post ON UPDATE CASCADE ON DELETE CASCADE,
╰╴               +++++++++++++
warning[prefer-bigint-over-int]: Using 32-bit integer fields can result in hitting the max `int` limit.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:7:33
│
7 │     ADD COLUMN mod_lock_post_id int REFERENCES mod_lock_post ON UPDATE CASCADE ON DELETE CASCADE,
│                                 ━━━
│
├ help: Use 64-bit integer values instead to prevent hitting this limit.
╭╴
7 │     ADD COLUMN mod_lock_post_id bigint REFERENCES mod_lock_post ON UPDATE CASCADE ON DELETE CASCADE,
╰╴                                +++
warning[adding-foreign-key-constraint]: Adding a foreign key constraint requires a table scan and a `SHARE ROW EXCLUSIVE` lock on both tables, which blocks writes to each table.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:7:37
│
7 │     ADD COLUMN mod_lock_post_id int REFERENCES mod_lock_post ON UPDATE CASCADE ON DELETE CASCADE,
│                                     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
│
╰ help: Add `NOT VALID` to the constraint in one transaction and then VALIDATE the constraint in a separate transaction.
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:8:5
│
8 │     ADD COLUMN mod_remove_comment_id int REFERENCES mod_remove_comment ON UPDATE CASCADE ON DELETE CASCADE,
│     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
╭╴
8 │     ADD COLUMN if not exists mod_remove_comment_id int REFERENCES mod_remove_comment ON UPDATE CASCADE ON DELETE CASCADE,
╰╴               +++++++++++++
warning[prefer-bigint-over-int]: Using 32-bit integer fields can result in hitting the max `int` limit.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:8:38
│
8 │     ADD COLUMN mod_remove_comment_id int REFERENCES mod_remove_comment ON UPDATE CASCADE ON DELETE CASCADE,
│                                      ━━━
│
├ help: Use 64-bit integer values instead to prevent hitting this limit.
╭╴
8 │     ADD COLUMN mod_remove_comment_id bigint REFERENCES mod_remove_comment ON UPDATE CASCADE ON DELETE CASCADE,
╰╴                                     +++
warning[adding-foreign-key-constraint]: Adding a foreign key constraint requires a table scan and a `SHARE ROW EXCLUSIVE` lock on both tables, which blocks writes to each table.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:8:42
│
8 │     ADD COLUMN mod_remove_comment_id int REFERENCES mod_remove_comment ON UPDATE CASCADE ON DELETE CASCADE,
│                                          ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
│
╰ help: Add `NOT VALID` to the constraint in one transaction and then VALIDATE the constraint in a separate transaction.
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:9:5
│
9 │     ADD COLUMN admin_remove_community_id int REFERENCES admin_remove_community ON UPDATE CASCADE ON DELETE CASCADE,
│     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
╭╴
9 │     ADD COLUMN if not exists admin_remove_community_id int REFERENCES admin_remove_community ON UPDATE CASCADE ON DELETE CASCADE,
╰╴               +++++++++++++
warning[prefer-bigint-over-int]: Using 32-bit integer fields can result in hitting the max `int` limit.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:9:42
│
9 │     ADD COLUMN admin_remove_community_id int REFERENCES admin_remove_community ON UPDATE CASCADE ON DELETE CASCADE,
│                                          ━━━
│
├ help: Use 64-bit integer values instead to prevent hitting this limit.
╭╴
9 │     ADD COLUMN admin_remove_community_id bigint REFERENCES admin_remove_community ON UPDATE CASCADE ON DELETE CASCADE,
╰╴                                         +++
warning[adding-foreign-key-constraint]: Adding a foreign key constraint requires a table scan and a `SHARE ROW EXCLUSIVE` lock on both tables, which blocks writes to each table.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:9:46
│
9 │     ADD COLUMN admin_remove_community_id int REFERENCES admin_remove_community ON UPDATE CASCADE ON DELETE CASCADE,
│                                              ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
│
╰ help: Add `NOT VALID` to the constraint in one transaction and then VALIDATE the constraint in a separate transaction.
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:10:5
│
10 │     ADD COLUMN mod_remove_post_id int REFERENCES mod_remove_post ON UPDATE CASCADE ON DELETE CASCADE,
│     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
╭╴
10 │     ADD COLUMN if not exists mod_remove_post_id int REFERENCES mod_remove_post ON UPDATE CASCADE ON DELETE CASCADE,
╰╴               +++++++++++++
warning[prefer-bigint-over-int]: Using 32-bit integer fields can result in hitting the max `int` limit.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:10:35
│
10 │     ADD COLUMN mod_remove_post_id int REFERENCES mod_remove_post ON UPDATE CASCADE ON DELETE CASCADE,
│                                   ━━━
│
├ help: Use 64-bit integer values instead to prevent hitting this limit.
╭╴
10 │     ADD COLUMN mod_remove_post_id bigint REFERENCES mod_remove_post ON UPDATE CASCADE ON DELETE CASCADE,
╰╴                                  +++
warning[adding-foreign-key-constraint]: Adding a foreign key constraint requires a table scan and a `SHARE ROW EXCLUSIVE` lock on both tables, which blocks writes to each table.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:10:39
│
10 │     ADD COLUMN mod_remove_post_id int REFERENCES mod_remove_post ON UPDATE CASCADE ON DELETE CASCADE,
│                                       ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
│
╰ help: Add `NOT VALID` to the constraint in one transaction and then VALIDATE the constraint in a separate transaction.
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:11:5
│
11 │     ADD COLUMN mod_lock_comment_id int REFERENCES mod_lock_comment ON UPDATE CASCADE ON DELETE CASCADE,
│     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
╭╴
11 │     ADD COLUMN if not exists mod_lock_comment_id int REFERENCES mod_lock_comment ON UPDATE CASCADE ON DELETE CASCADE,
╰╴               +++++++++++++
warning[prefer-bigint-over-int]: Using 32-bit integer fields can result in hitting the max `int` limit.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:11:36
│
11 │     ADD COLUMN mod_lock_comment_id int REFERENCES mod_lock_comment ON UPDATE CASCADE ON DELETE CASCADE,
│                                    ━━━
│
├ help: Use 64-bit integer values instead to prevent hitting this limit.
╭╴
11 │     ADD COLUMN mod_lock_comment_id bigint REFERENCES mod_lock_comment ON UPDATE CASCADE ON DELETE CASCADE,
╰╴                                   +++
warning[adding-foreign-key-constraint]: Adding a foreign key constraint requires a table scan and a `SHARE ROW EXCLUSIVE` lock on both tables, which blocks writes to each table.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:11:40
│
11 │     ADD COLUMN mod_lock_comment_id int REFERENCES mod_lock_comment ON UPDATE CASCADE ON DELETE CASCADE,
│                                        ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
│
╰ help: Add `NOT VALID` to the constraint in one transaction and then VALIDATE the constraint in a separate transaction.
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:12:5
│
12 │     ADD COLUMN mod_transfer_community_id int REFERENCES mod_transfer_community ON UPDATE CASCADE ON DELETE CASCADE;
│     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
╭╴
12 │     ADD COLUMN if not exists mod_transfer_community_id int REFERENCES mod_transfer_community ON UPDATE CASCADE ON DELETE CASCADE;
╰╴               +++++++++++++
warning[prefer-bigint-over-int]: Using 32-bit integer fields can result in hitting the max `int` limit.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:12:42
│
12 │     ADD COLUMN mod_transfer_community_id int REFERENCES mod_transfer_community ON UPDATE CASCADE ON DELETE CASCADE;
│                                          ━━━
│
├ help: Use 64-bit integer values instead to prevent hitting this limit.
╭╴
12 │     ADD COLUMN mod_transfer_community_id bigint REFERENCES mod_transfer_community ON UPDATE CASCADE ON DELETE CASCADE;
╰╴                                         +++
warning[adding-foreign-key-constraint]: Adding a foreign key constraint requires a table scan and a `SHARE ROW EXCLUSIVE` lock on both tables, which blocks writes to each table.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:12:46
│
12 │     ADD COLUMN mod_transfer_community_id int REFERENCES mod_transfer_community ON UPDATE CASCADE ON DELETE CASCADE;
│                                              ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
│
╰ help: Add `NOT VALID` to the constraint in one transaction and then VALIDATE the constraint in a separate transaction.
warning[constraint-missing-not-valid]: By default new constraints require a table scan and block writes to the table while that scan occurs.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:23:5
│
23 │     ADD CONSTRAINT notification_check CHECK …move_community_id, mod_transfer_community_id) = 1);
│     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━…━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
│
╰ help: Use `NOT VALID` with a later `VALIDATE CONSTRAINT` call.
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:26:1
│
26 │ CREATE INDEX idx_notification_unread ON notification (read);
│ ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
│
├ help: Use an explicit name for a concurrently created index
╭╴
26 │ CREATE INDEX if not exists idx_notification_unread ON notification (read);
╰╴             +++++++++++++
warning[require-concurrent-index-creation]: During normal index creation, table updates are blocked, but reads are still allowed.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:26:1
│
26 │ CREATE INDEX idx_notification_unread ON notification (read);
│ ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
│
├ help: Use `concurrently` to avoid blocking writes.
╭╴
26 │ CREATE INDEX concurrently idx_notification_unread ON notification (read);
╰╴             ++++++++++++
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:28:1
│
28 │ ┏ CREATE INDEX idx_notification_admin_add_id ON notification (admin_add_id)
29 │ ┃ WHERE
30 │ ┃     admin_add_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use an explicit name for a concurrently created index
╭╴
28 │ CREATE INDEX if not exists idx_notification_admin_add_id ON notification (admin_add_id)
╰╴             +++++++++++++
warning[require-concurrent-index-creation]: During normal index creation, table updates are blocked, but reads are still allowed.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:28:1
│
28 │ ┏ CREATE INDEX idx_notification_admin_add_id ON notification (admin_add_id)
29 │ ┃ WHERE
30 │ ┃     admin_add_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use `concurrently` to avoid blocking writes.
╭╴
28 │ CREATE INDEX concurrently idx_notification_admin_add_id ON notification (admin_add_id)
╰╴             ++++++++++++
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:32:1
│
32 │ ┏ CREATE INDEX idx_notification_mod_add_to_community_id ON notification (mod_add_to_community_id)
33 │ ┃ WHERE
34 │ ┃     mod_add_to_community_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use an explicit name for a concurrently created index
╭╴
32 │ CREATE INDEX if not exists idx_notification_mod_add_to_community_id ON notification (mod_add_to_community_id)
╰╴             +++++++++++++
warning[require-concurrent-index-creation]: During normal index creation, table updates are blocked, but reads are still allowed.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:32:1
│
32 │ ┏ CREATE INDEX idx_notification_mod_add_to_community_id ON notification (mod_add_to_community_id)
33 │ ┃ WHERE
34 │ ┃     mod_add_to_community_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use `concurrently` to avoid blocking writes.
╭╴
32 │ CREATE INDEX concurrently idx_notification_mod_add_to_community_id ON notification (mod_add_to_community_id)
╰╴             ++++++++++++
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:36:1
│
36 │ ┏ CREATE INDEX idx_notification_admin_ban_id ON notification (admin_ban_id)
37 │ ┃ WHERE
38 │ ┃     admin_ban_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use an explicit name for a concurrently created index
╭╴
36 │ CREATE INDEX if not exists idx_notification_admin_ban_id ON notification (admin_ban_id)
╰╴             +++++++++++++
warning[require-concurrent-index-creation]: During normal index creation, table updates are blocked, but reads are still allowed.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:36:1
│
36 │ ┏ CREATE INDEX idx_notification_admin_ban_id ON notification (admin_ban_id)
37 │ ┃ WHERE
38 │ ┃     admin_ban_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use `concurrently` to avoid blocking writes.
╭╴
36 │ CREATE INDEX concurrently idx_notification_admin_ban_id ON notification (admin_ban_id)
╰╴             ++++++++++++
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:40:1
│
40 │ ┏ CREATE INDEX idx_notification_mod_ban_from_community_id ON notification (mod_ban_from_community_id)
41 │ ┃ WHERE
42 │ ┃     mod_ban_from_community_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use an explicit name for a concurrently created index
╭╴
40 │ CREATE INDEX if not exists idx_notification_mod_ban_from_community_id ON notification (mod_ban_from_community_id)
╰╴             +++++++++++++
warning[require-concurrent-index-creation]: During normal index creation, table updates are blocked, but reads are still allowed.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:40:1
│
40 │ ┏ CREATE INDEX idx_notification_mod_ban_from_community_id ON notification (mod_ban_from_community_id)
41 │ ┃ WHERE
42 │ ┃     mod_ban_from_community_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use `concurrently` to avoid blocking writes.
╭╴
40 │ CREATE INDEX concurrently idx_notification_mod_ban_from_community_id ON notification (mod_ban_from_community_id)
╰╴             ++++++++++++
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:44:1
│
44 │ ┏ CREATE INDEX idx_notification_mod_lock_post_id ON notification (mod_lock_post_id)
45 │ ┃ WHERE
46 │ ┃     mod_lock_post_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use an explicit name for a concurrently created index
╭╴
44 │ CREATE INDEX if not exists idx_notification_mod_lock_post_id ON notification (mod_lock_post_id)
╰╴             +++++++++++++
warning[require-concurrent-index-creation]: During normal index creation, table updates are blocked, but reads are still allowed.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:44:1
│
44 │ ┏ CREATE INDEX idx_notification_mod_lock_post_id ON notification (mod_lock_post_id)
45 │ ┃ WHERE
46 │ ┃     mod_lock_post_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use `concurrently` to avoid blocking writes.
╭╴
44 │ CREATE INDEX concurrently idx_notification_mod_lock_post_id ON notification (mod_lock_post_id)
╰╴             ++++++++++++
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:48:1
│
48 │ ┏ CREATE INDEX idx_notification_mod_remove_comment_id ON notification (mod_remove_comment_id)
49 │ ┃ WHERE
50 │ ┃     mod_remove_comment_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use an explicit name for a concurrently created index
╭╴
48 │ CREATE INDEX if not exists idx_notification_mod_remove_comment_id ON notification (mod_remove_comment_id)
╰╴             +++++++++++++
warning[require-concurrent-index-creation]: During normal index creation, table updates are blocked, but reads are still allowed.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:48:1
│
48 │ ┏ CREATE INDEX idx_notification_mod_remove_comment_id ON notification (mod_remove_comment_id)
49 │ ┃ WHERE
50 │ ┃     mod_remove_comment_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use `concurrently` to avoid blocking writes.
╭╴
48 │ CREATE INDEX concurrently idx_notification_mod_remove_comment_id ON notification (mod_remove_comment_id)
╰╴             ++++++++++++
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:52:1
│
52 │ ┏ CREATE INDEX idx_notification_admin_remove_community_id ON notification (admin_remove_community_id)
53 │ ┃ WHERE
54 │ ┃     admin_remove_community_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use an explicit name for a concurrently created index
╭╴
52 │ CREATE INDEX if not exists idx_notification_admin_remove_community_id ON notification (admin_remove_community_id)
╰╴             +++++++++++++
warning[require-concurrent-index-creation]: During normal index creation, table updates are blocked, but reads are still allowed.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:52:1
│
52 │ ┏ CREATE INDEX idx_notification_admin_remove_community_id ON notification (admin_remove_community_id)
53 │ ┃ WHERE
54 │ ┃     admin_remove_community_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use `concurrently` to avoid blocking writes.
╭╴
52 │ CREATE INDEX concurrently idx_notification_admin_remove_community_id ON notification (admin_remove_community_id)
╰╴             ++++++++++++
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:56:1
│
56 │ ┏ CREATE INDEX idx_notification_mod_remove_post_id ON notification (mod_remove_post_id)
57 │ ┃ WHERE
58 │ ┃     mod_remove_post_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use an explicit name for a concurrently created index
╭╴
56 │ CREATE INDEX if not exists idx_notification_mod_remove_post_id ON notification (mod_remove_post_id)
╰╴             +++++++++++++
warning[require-concurrent-index-creation]: During normal index creation, table updates are blocked, but reads are still allowed.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:56:1
│
56 │ ┏ CREATE INDEX idx_notification_mod_remove_post_id ON notification (mod_remove_post_id)
57 │ ┃ WHERE
58 │ ┃     mod_remove_post_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use `concurrently` to avoid blocking writes.
╭╴
56 │ CREATE INDEX concurrently idx_notification_mod_remove_post_id ON notification (mod_remove_post_id)
╰╴             ++++++++++++
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:60:1
│
60 │ ┏ CREATE INDEX idx_notification_mod_lock_comment_id ON notification (mod_lock_comment_id)
61 │ ┃ WHERE
62 │ ┃     mod_lock_comment_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use an explicit name for a concurrently created index
╭╴
60 │ CREATE INDEX if not exists idx_notification_mod_lock_comment_id ON notification (mod_lock_comment_id)
╰╴             +++++++++++++
warning[require-concurrent-index-creation]: During normal index creation, table updates are blocked, but reads are still allowed.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:60:1
│
60 │ ┏ CREATE INDEX idx_notification_mod_lock_comment_id ON notification (mod_lock_comment_id)
61 │ ┃ WHERE
62 │ ┃     mod_lock_comment_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use `concurrently` to avoid blocking writes.
╭╴
60 │ CREATE INDEX concurrently idx_notification_mod_lock_comment_id ON notification (mod_lock_comment_id)
╰╴             ++++++++++++
warning[prefer-robust-stmts]: Missing `IF NOT EXISTS`, the migration can't be rerun if it fails part way through.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:64:1
│
64 │ ┏ CREATE INDEX idx_notification_mod_transfer_community_id ON notification (mod_transfer_community_id)
65 │ ┃ WHERE
66 │ ┃     mod_transfer_community_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use an explicit name for a concurrently created index
╭╴
64 │ CREATE INDEX if not exists idx_notification_mod_transfer_community_id ON notification (mod_transfer_community_id)
╰╴             +++++++++++++
warning[require-concurrent-index-creation]: During normal index creation, table updates are blocked, but reads are still allowed.
╭▸ migrations/2025-09-19-090047_notify-mod-action/up.sql:64:1
│
64 │ ┏ CREATE INDEX idx_notification_mod_transfer_community_id ON notification (mod_transfer_community_id)
65 │ ┃ WHERE
66 │ ┃     mod_transfer_community_id IS NOT NULL;
│ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
│
├ help: Use `concurrently` to avoid blocking writes.
╭╴
64 │ CREATE INDEX concurrently idx_notification_mod_transfer_community_id ON notification (mod_transfer_community_id)
╰╴             ++++++++++++

Find detailed examples and solutions for each rule at https://squawkhq.com/docs/rules
Found 55 issues in 1 file (checked 1 source file)

So it wants to add set lock_timeout = '1s';, use if not exists for adding columns, use bigint instead of int, use `create index concurrently. It is clearly meant for live migrations, not for offline migrations like we are using. It would probably require a lot of config changes to make it work for us. Also there are a lot of warnings and errors for old migrations so we would have to fix those (or ignore them). There are few rules available.

Output from sqlfluff on the same file

sudo docker run -it --rm -v $PWD:/sql sqlfluff/sqlfluff lint migrations/2025-09-19-090047_notify-mod-action/up.sql --dialect postgres
== [migrations/2025-09-19-090047_notify-mod-action/up.sql] FAIL
L:   3 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:   3 | P:   5 | LT05 | Line is too long (89 > 80). [layout.long_lines]
L:   4 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:   4 | P:   5 | LT05 | Line is too long (111 > 80).
| [layout.long_lines]
L:   5 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:   5 | P:   5 | LT05 | Line is too long (89 > 80). [layout.long_lines]
L:   6 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:   6 | P:   5 | LT05 | Line is too long (115 > 80).
| [layout.long_lines]
L:   7 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:   7 | P:   5 | LT05 | Line is too long (97 > 80). [layout.long_lines]
L:   8 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:   8 | P:   5 | LT05 | Line is too long (107 > 80).
| [layout.long_lines]
L:   9 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:   9 | P:   5 | LT05 | Line is too long (115 > 80).
| [layout.long_lines]
L:  10 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  10 | P:   5 | LT05 | Line is too long (101 > 80).
| [layout.long_lines]
L:  11 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  11 | P:   5 | LT05 | Line is too long (103 > 80).
| [layout.long_lines]
L:  12 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  12 | P:   5 | LT05 | Line is too long (115 > 80).
| [layout.long_lines]
L:  16 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  16 | P:   9 | CP01 | Keywords must be consistently upper case.
| [capitalisation.keywords]
L:  20 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  23 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  23 | P:   5 | LT05 | Line is too long (321 > 80).
| [layout.long_lines]
L:  23 | P:  58 | LT01 | Unexpected whitespace before start bracket '('.
| [layout.spacing]
L:  23 | P:  58 | LT06 | Function name not immediately followed by parenthesis.
| [layout.functions]
L:  30 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  32 | P:   1 | LT05 | Line is too long (95 > 80). [layout.long_lines]
L:  34 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  38 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  40 | P:   1 | LT05 | Line is too long (99 > 80). [layout.long_lines]
L:  42 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  44 | P:   1 | LT05 | Line is too long (81 > 80). [layout.long_lines]
L:  46 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  48 | P:   1 | LT05 | Line is too long (91 > 80). [layout.long_lines]
L:  50 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  52 | P:   1 | LT05 | Line is too long (99 > 80). [layout.long_lines]
L:  54 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  56 | P:   1 | LT05 | Line is too long (85 > 80). [layout.long_lines]
L:  58 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  60 | P:   1 | LT05 | Line is too long (87 > 80). [layout.long_lines]
L:  62 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  64 | P:   1 | LT05 | Line is too long (99 > 80). [layout.long_lines]
L:  66 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  67 | P:   1 | LT12 | Files must end with a single trailing newline.
| [layout.end_of_file]
All Finished 📜 🎉!

So there are only formatting problems shown, and it seems there are few other rules available.

And here pgrubic

$ pgrubic lint migrations/2025-09-19-090047_notify-mod-action/up.sql
/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:6:16: TP009: Prefer bigint over integer
6 |     ADD COLUMN mod_ban_from_community_id int REFERENCES mod_ban_from_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:12:46: CT002: Cascade delete in foreign key constraint
12 |     ADD COLUMN mod_transfer_community_id int REFERENCES mod_transfer_community ON UPDATE CASCADE ON DELETE CASCADE;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:7:37: US012: Validating foreign key constraint on existing rows
7 |     ADD COLUMN mod_lock_post_id int REFERENCES mod_lock_post ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:58:36: US016: Non concurrent index creation
55 |
56 | CREATE INDEX idx_notification_mod_remove_post_id ON notification (mod_remove_post_id)
57 | WHERE
58 |     mod_remove_post_id IS NOT NULL;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:10:50: SM001: Database object `mod_remove_post` should be schema qualified
10 |     ADD COLUMN mod_remove_post_id int REFERENCES mod_remove_post ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:38:30: US016: Non concurrent index creation
35 |
36 | CREATE INDEX idx_notification_admin_ban_id ON notification (admin_ban_id)
37 | WHERE
38 |     admin_ban_id IS NOT NULL;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:8:42: CT002: Cascade delete in foreign key constraint
8 |     ADD COLUMN mod_remove_comment_id int REFERENCES mod_remove_comment ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:9:46: US012: Validating foreign key constraint on existing rows
9 |     ADD COLUMN admin_remove_community_id int REFERENCES admin_remove_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:4:44: CT002: Cascade delete in foreign key constraint
4 |     ADD COLUMN mod_add_to_community_id int REFERENCES mod_add_to_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:9:46: CT001: Cascade update in foreign key constraint
9 |     ADD COLUMN admin_remove_community_id int REFERENCES admin_remove_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:5:33: CT001: Cascade update in foreign key constraint
5 |     ADD COLUMN admin_ban_id int REFERENCES admin_ban ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:5:16: TP009: Prefer bigint over integer
5 |     ADD COLUMN admin_ban_id int REFERENCES admin_ban ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:7:37: CT002: Cascade delete in foreign key constraint
7 |     ADD COLUMN mod_lock_post_id int REFERENCES mod_lock_post ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:9:46: NM008: Prefer named constraint
9 |     ADD COLUMN admin_remove_community_id int REFERENCES admin_remove_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:26:41: SM001: Database object `notification` should be schema qualified
26 | CREATE INDEX idx_notification_unread ON notification (read);
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:42:43: US016: Non concurrent index creation
39 |
40 | CREATE INDEX idx_notification_mod_ban_from_community_id ON notification (mod_ban_from_community_id)
41 | WHERE
42 |     mod_ban_from_community_id IS NOT NULL;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:36:47: SM001: Database object `notification` should be schema qualified
36 | CREATE INDEX idx_notification_admin_ban_id ON notification (admin_ban_id)
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:8:42: US012: Validating foreign key constraint on existing rows
8 |     ADD COLUMN mod_remove_comment_id int REFERENCES mod_remove_comment ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:23:9: US013: Validating check constraint on existing rows
23 |     ADD CONSTRAINT notification_check CHECK (num_nonnulls (post_id, comment_id, private_message_id, admin_add_id, mod_add_to_community_id, admin_ban_id, mod_ban_from_community_id, mod_lock_post_id, mod_remove_post_id, mod_lock_comment_id, mod_remove_comment_id, admin_remove_community_id, mod_transfer_community_id) = 1);
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:5:33: US012: Validating foreign key constraint on existing rows
5 |     ADD COLUMN admin_ban_id int REFERENCES admin_ban ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:34:41: US016: Non concurrent index creation
31 |
32 | CREATE INDEX idx_notification_mod_add_to_community_id ON notification (mod_add_to_community_id)
33 | WHERE
34 |     mod_add_to_community_id IS NOT NULL;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:12:57: SM001: Database object `mod_transfer_community` should be schema qualified
12 |     ADD COLUMN mod_transfer_community_id int REFERENCES mod_transfer_community ON UPDATE CASCADE ON DELETE CASCADE;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:48:56: SM001: Database object `notification` should be schema qualified
48 | CREATE INDEX idx_notification_mod_remove_comment_id ON notification (mod_remove_comment_id)
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:16:27: SM001: Database object `notification_type_enum` should be schema qualified
13 |
14 | -- new types for mod actions
15 | ALTER TYPE notification_type_enum
16 |     ADD value 'ModAction';
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:7:37: NM008: Prefer named constraint
7 |     ADD COLUMN mod_lock_post_id int REFERENCES mod_lock_post ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:62:37: US016: Non concurrent index creation
59 |
60 | CREATE INDEX idx_notification_mod_lock_comment_id ON notification (mod_lock_comment_id)
61 | WHERE
62 |     mod_lock_comment_id IS NOT NULL;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:9:16: TP009: Prefer bigint over integer
9 |     ADD COLUMN admin_remove_community_id int REFERENCES admin_remove_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:22:13: SM001: Database object `notification` should be schema qualified
22 | ALTER TABLE notification
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:11:16: TP009: Prefer bigint over integer
11 |     ADD COLUMN mod_lock_comment_id int REFERENCES mod_lock_comment ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:4:44: CT001: Cascade update in foreign key constraint
4 |     ADD COLUMN mod_add_to_community_id int REFERENCES mod_add_to_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:8:42: CT001: Cascade update in foreign key constraint
8 |     ADD COLUMN mod_remove_comment_id int REFERENCES mod_remove_comment ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:4:44: NM008: Prefer named constraint
4 |     ADD COLUMN mod_add_to_community_id int REFERENCES mod_add_to_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:11:51: SM001: Database object `mod_lock_comment` should be schema qualified
11 |     ADD COLUMN mod_lock_comment_id int REFERENCES mod_lock_comment ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:11:40: CT001: Cascade update in foreign key constraint
11 |     ADD COLUMN mod_lock_comment_id int REFERENCES mod_lock_comment ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:10:39: CT002: Cascade delete in foreign key constraint
10 |     ADD COLUMN mod_remove_post_id int REFERENCES mod_remove_post ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:32:58: SM001: Database object `notification` should be schema qualified
32 | CREATE INDEX idx_notification_mod_add_to_community_id ON notification (mod_add_to_community_id)
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:11:40: NM008: Prefer named constraint
11 |     ADD COLUMN mod_lock_comment_id int REFERENCES mod_lock_comment ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:3:44: SM001: Database object `admin_add` should be schema qualified
3 |     ADD COLUMN admin_add_id int REFERENCES admin_add ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:8:16: TP009: Prefer bigint over integer
8 |     ADD COLUMN mod_remove_comment_id int REFERENCES mod_remove_comment ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:66:43: US016: Non concurrent index creation
63 |
64 | CREATE INDEX idx_notification_mod_transfer_community_id ON notification (mod_transfer_community_id)
65 | WHERE
66 |     mod_transfer_community_id IS NOT NULL;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:3:33: NM008: Prefer named constraint
3 |     ADD COLUMN admin_add_id int REFERENCES admin_add ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:60:54: SM001: Database object `notification` should be schema qualified
60 | CREATE INDEX idx_notification_mod_lock_comment_id ON notification (mod_lock_comment_id)
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:7:16: TP009: Prefer bigint over integer
7 |     ADD COLUMN mod_lock_post_id int REFERENCES mod_lock_post ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:28:47: SM001: Database object `notification` should be schema qualified
28 | CREATE INDEX idx_notification_admin_add_id ON notification (admin_add_id)
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:56:53: SM001: Database object `notification` should be schema qualified
56 | CREATE INDEX idx_notification_mod_remove_post_id ON notification (mod_remove_post_id)
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:9:57: SM001: Database object `admin_remove_community` should be schema qualified
9 |     ADD COLUMN admin_remove_community_id int REFERENCES admin_remove_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:6:57: SM001: Database object `mod_ban_from_community` should be schema qualified
6 |     ADD COLUMN mod_ban_from_community_id int REFERENCES mod_ban_from_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:3:16: TP009: Prefer bigint over integer
3 |     ADD COLUMN admin_add_id int REFERENCES admin_add ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:10:39: US012: Validating foreign key constraint on existing rows
10 |     ADD COLUMN mod_remove_post_id int REFERENCES mod_remove_post ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:9:46: CT002: Cascade delete in foreign key constraint
9 |     ADD COLUMN admin_remove_community_id int REFERENCES admin_remove_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:5:44: SM001: Database object `admin_ban` should be schema qualified
5 |     ADD COLUMN admin_ban_id int REFERENCES admin_ban ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:6:46: CT002: Cascade delete in foreign key constraint
6 |     ADD COLUMN mod_ban_from_community_id int REFERENCES mod_ban_from_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:50:39: US016: Non concurrent index creation
47 |
48 | CREATE INDEX idx_notification_mod_remove_comment_id ON notification (mod_remove_comment_id)
49 | WHERE
50 |     mod_remove_comment_id IS NOT NULL;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:5:33: CT002: Cascade delete in foreign key constraint
5 |     ADD COLUMN admin_ban_id int REFERENCES admin_ban ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:44:51: SM001: Database object `notification` should be schema qualified
44 | CREATE INDEX idx_notification_mod_lock_post_id ON notification (mod_lock_post_id)
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:4:44: US012: Validating foreign key constraint on existing rows
4 |     ADD COLUMN mod_add_to_community_id int REFERENCES mod_add_to_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:3:33: US012: Validating foreign key constraint on existing rows
3 |     ADD COLUMN admin_add_id int REFERENCES admin_add ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:10:16: TP009: Prefer bigint over integer
10 |     ADD COLUMN mod_remove_post_id int REFERENCES mod_remove_post ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:2:13: SM001: Database object `notification` should be schema qualified
2 | ALTER TABLE notification
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:11:40: CT002: Cascade delete in foreign key constraint
11 |     ADD COLUMN mod_lock_comment_id int REFERENCES mod_lock_comment ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:8:53: SM001: Database object `mod_remove_comment` should be schema qualified
8 |     ADD COLUMN mod_remove_comment_id int REFERENCES mod_remove_comment ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:64:60: SM001: Database object `notification` should be schema qualified
64 | CREATE INDEX idx_notification_mod_transfer_community_id ON notification (mod_transfer_community_id)
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:19:13: SM001: Database object `notification` should be schema qualified
19 | ALTER TABLE notification
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:11:40: US012: Validating foreign key constraint on existing rows
11 |     ADD COLUMN mod_lock_comment_id int REFERENCES mod_lock_comment ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:52:60: SM001: Database object `notification` should be schema qualified
52 | CREATE INDEX idx_notification_admin_remove_community_id ON notification (admin_remove_community_id)
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:40:60: SM001: Database object `notification` should be schema qualified
40 | CREATE INDEX idx_notification_mod_ban_from_community_id ON notification (mod_ban_from_community_id)
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:7:37: CT001: Cascade update in foreign key constraint
7 |     ADD COLUMN mod_lock_post_id int REFERENCES mod_lock_post ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:6:46: CT001: Cascade update in foreign key constraint
6 |     ADD COLUMN mod_ban_from_community_id int REFERENCES mod_ban_from_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:30:30: US016: Non concurrent index creation
27 |
28 | CREATE INDEX idx_notification_admin_add_id ON notification (admin_add_id)
29 | WHERE
30 |     admin_add_id IS NOT NULL;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:46:34: US016: Non concurrent index creation
43 |
44 | CREATE INDEX idx_notification_mod_lock_post_id ON notification (mod_lock_post_id)
45 | WHERE
46 |     mod_lock_post_id IS NOT NULL;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:12:16: TP009: Prefer bigint over integer
12 |     ADD COLUMN mod_transfer_community_id int REFERENCES mod_transfer_community ON UPDATE CASCADE ON DELETE CASCADE;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:54:43: US016: Non concurrent index creation
51 |
52 | CREATE INDEX idx_notification_admin_remove_community_id ON notification (admin_remove_community_id)
53 | WHERE
54 |     admin_remove_community_id IS NOT NULL;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:26:61: US016: Non concurrent index creation
24 |
25 | -- add indexes
26 | CREATE INDEX idx_notification_unread ON notification (read);
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:7:48: SM001: Database object `mod_lock_post` should be schema qualified
7 |     ADD COLUMN mod_lock_post_id int REFERENCES mod_lock_post ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:5:33: NM008: Prefer named constraint
5 |     ADD COLUMN admin_ban_id int REFERENCES admin_ban ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:20:50: CT004: Constraint `notification_check` removal detected
17 |
18 | -- update constraint with new columns
19 | ALTER TABLE notification
20 |     DROP CONSTRAINT IF EXISTS notification_check;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:3:33: CT001: Cascade update in foreign key constraint
3 |     ADD COLUMN admin_add_id int REFERENCES admin_add ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:10:39: NM008: Prefer named constraint
10 |     ADD COLUMN mod_remove_post_id int REFERENCES mod_remove_post ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:12:46: CT001: Cascade update in foreign key constraint
12 |     ADD COLUMN mod_transfer_community_id int REFERENCES mod_transfer_community ON UPDATE CASCADE ON DELETE CASCADE;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:6:46: US012: Validating foreign key constraint on existing rows
6 |     ADD COLUMN mod_ban_from_community_id int REFERENCES mod_ban_from_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:4:55: SM001: Database object `mod_add_to_community` should be schemaqualified
4 |     ADD COLUMN mod_add_to_community_id int REFERENCES mod_add_to_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:10:39: CT001: Cascade update in foreign key constraint
10 |     ADD COLUMN mod_remove_post_id int REFERENCES mod_remove_post ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:8:42: NM008: Prefer named constraint
8 |     ADD COLUMN mod_remove_comment_id int REFERENCES mod_remove_comment ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:12:46: US012: Validating foreign key constraint on existing rows
12 |     ADD COLUMN mod_transfer_community_id int REFERENCES mod_transfer_community ON UPDATE CASCADE ON DELETE CASCADE;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:12:46: NM008: Prefer named constraint
12 |     ADD COLUMN mod_transfer_community_id int REFERENCES mod_transfer_community ON UPDATE CASCADE ON DELETE CASCADE;
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:3:33: CT002: Cascade delete in foreign key constraint
3 |     ADD COLUMN admin_add_id int REFERENCES admin_add ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:4:16: TP009: Prefer bigint over integer
4 |     ADD COLUMN mod_add_to_community_id int REFERENCES mod_add_to_community ON UPDATE CASCADE ON DELETE CASCADE,
^

/home/felix/workspace/lemmy/lemmy/migrations/2025-09-19-090047_notify-mod-action/up.sql:6:46: NM008: Prefer named constraint
6 |     ADD COLUMN mod_ban_from_community_id int REFERENCES mod_ban_from_community ON UPDATE CASCADE ON DELETE CASCADE,
^

Found 88 violation(s)
52 fix(es) available, 52 fix(es) enabled
0 error(s) found
Use with '--fix' to auto fix the violations

So again a lot of irrelevant warnings which are meant for live migrations. There are a lot more rules available like null-comparison (https://github.com/LemmyNet/lemmy/pull/6022) or use of timestamp without timezone.

Overall its probably not worth adding a linter as they find few problems which are relevant for us. Most of these problems will also be discovered during normal testing and code review.

Oct 13 '25 11:10 Nutomic

sqlfluff looks like a less strict version of our already in use pg_format, and is only doing formats, so no use in using that.
pg_rubric has a few useful things, but the low number of stars / devs on that repo scares me a little.
I really like squawk, in that is has a lot of tunable lints. Some of those lints look really useful.

I think it'd be worth it to create a squawk.toml, tune it to remove some of the useless things like concurrently and lock_timeout, and run it in CI (there's a docker container at ghcr.io/sbdchd/squawk:latest), only for migrations added after 2025-08-01, which from then on are all our non-deployed 1.0 migrations.

A few lints that look really useful, are

Detect syntax errors
required fields
Identity columns.

Even if we left these as warnings, and not blocking the PR, it still might be useful.

Oct 13 '25 18:10 dessalines

Also --assume-in-transaction might help ignore some of these.

EDIT: yep it did, went from 55 errors down to 34.

Oct 13 '25 18:10 dessalines

Tried to write a .squawk.toml by disabling rules we dont need, but it really looks like every single warning is irrelevant for us. Here is the file:

assume_in_transaction = true
excluded_rules = [
    "require-concurrent-index-creation",
    "require-concurrent-index-deletion",
    "adding-foreign-key-constraint",
    "constraint-missing-not-valid",
    "prefer-bigint-over-int",
    "require-timeout-settings",
    "adding-not-nullable-field",
    "ban-drop-not-null",
    "ban-drop-column",
    "renaming-column",
    "changing-column-type",
    "prefer-text-field"
]

Run with npx squawk-cli "migrations/*/*.sql". It still warns about renaming or dropping tables, or adding constraints. I could disable those as well, but then there is nothing left to check. There is not even a rule to prevent using where name = null instead of where name is null. Only general syntax errors, and those are caught by our existing test cases anyway.

Out of these three only pg_rubric seems to make any sense. Maybe there is another linter we could try, there seem to be a lot.

Oct 14 '25 08:10 Nutomic

I wouldn't run this for all our migrations, only maybe new ones, or ones past that date above. You could probably add a lot of these checks back in if that were the case.

There is not even a rule to prevent using where name = null instead of where name is null

We could open up an issue for them to add that rule.

pg_rubric scares me because its just one dev, and its in python.

Oct 14 '25 17:10 dessalines