SQL Review Guide
An online tool for DBA to generate the SQL review / DDL guideline.
PostgreSQL SQL Review Guide
Advanced Template
Comprehensive policy where you have total protection and best practices for your databases.
Basic Template
Basic coverage to defend your databases from common misusage.
Database Review Guide
Enforce the required columns in each table.
-
Required column names:
id created_ts updated_ts creator_id updater_id
Auto-increment column must be the UNSIGNED type.
Configure whether the column requires comments and the maximum comment length.
- Require comment: true
- Max length: 64
Force to change CHAR to VARCHAR if maximum length is reached.
- Maximum length: 20
Limit the count of NOW(), CURRENT_TIME() and CURRENT_TIMESTAMP() columns.
MySQL and TiDB support checking whether the schema change is backward compatible.
Only tables named with specific patterns can be deleted. The requires users to do a rename before dropping the table. The table name must have "_del" suffix by default.
- Table name format (regex): _del$
Configure whether the table requires comments and the maximum comment length.
- Require comment: true
- Max length: 64
Disallow leading '%' in LIKE, e.g. LIKE foo = '%x' is not allowed.
Must specify the column to insert. For example, "INSERT INTO t (id,name) VALUES(...)".
The PostgreSQL will lock the table and rewrite the whole table when you adding column with default value. You can separate the adding column, setting default value and backfilling all existing rows.
Adding CHECK constraints without NOT VALID can cause downtime because it blocks reads and writes. You can manually verify all rows and validate the constraint after creating.
It can cause downtime because it blocks reads and writes. You can add CHECK constraints with NOT VALID option to avoid this.
Enforce the table name format and length limit. Default snake_lower_case with 63 characters.
- Table name format (regex): ^[a-z]+(_[a-z]+)*$
- Length limit: 63
Enforce the column name format and length limit. Default snake_lower_case with 63 characters.
- Column name format (regex): ^[a-z]+(_[a-z]+)*$
- Length limit: 63
Enforce the unique key name format and length limit. Default uk_<table_name>_<column_list> or empty within 63 characters.
- Unique key name format: ^$|^uk_{{table}}_{{column_list}}$
- Length limit: 63
Enforce the primary key name format and length limit. Default pk_<table_name>_<column_list> or empty within 63 characters.
- Primary key name format: ^$|^pk_{{table}}_{{column_list}}$
- Length limit: 63
Enforce the index name format and length limit. Default idx_<table_name>_<column_list> or empty within 63 characters.
- Index name format: ^$|^idx_{{table}}_{{column_list}}$
- Length limit: 63
Enforce the foreign key name format and length limit. Default fk_<referencing_table>_<referencing_column>_<referenced_table>_<referenced_column> or empty within 63 characters.
- Foreign key name format: ^$|^fk_{{referencing_table}}_{{referencing_column}}_{{referenced_table}}_{{referenced_column}}$
- Length limit: 63
Enforce the auto-increment column name format and length limit. Default id within 63 characters.
- Auto-increment column name format (regex): ^id$
- Length limit: 63
Creating indexes blocks writes (but not reads) on the table until it's done. Use CONCURRENTLY when creates indexes can allow writes to continue.
at 2023-03-20