قائمة تحقق لتنسيق SQL قبل نشر الترحيل

A practical checklist to run before a schema or data migration goes to review and production. Each item is short, explains why it matters, and most can be checked in a minute. A worked example at the end shows a migration before and after formatting.

The checklist at a glance

1. The file is formatted with the team's documented options.
2. The file contains one logical change.
3. Every INSERT lists its columns.
4. Constraints and indexes have explicit names.
5. Comments explain why, not what.
6. Every UPDATE and DELETE has a reviewed WHERE clause.
7. Transaction boundaries are deliberate.
8. The migration can be re-run safely, or it has a rollback.
9. The reviewer has read the formatted diff, not just the description.

Migrations deserve more care than ordinary queries because they run once, against production data, often under time pressure, and many of their effects cannot be undone with a simple revert. Readability is part of safety: a reviewer can only catch what they can read.

1. Consistent formatting with documented options

Format every migration with the same settings, and write those settings down where the team will find them, for example in the repository's contributing guide. In SQL Format that means five values: indent spaces, maximum columns, comma placement, keyword case and parentheses on new line. When everyone uses the same values, a diff shows only what changed in the logic.

Format before the file is committed, not after review starts. Reformatting a file that is already under review buries the real comments under layout noise. The guide on formatting, code review and version control covers this workflow in more detail.

2. One logical change per migration

A migration should do one thing that can be described in a sentence: "add last_reminded_at to invoices and backfill it". A column, the index that supports it and the data backfill belong together. An unrelated constraint on another table does not. Small files are easier to review, easier to roll back, and when something fails halfway, the error message points at a change that is easy to understand.

3. Explicit column lists in INSERT

An INSERT without a column list depends on the current order of the table's columns. When a later migration adds a column, the statement fails; when columns of the same type are reordered, it can succeed and put values in the wrong place.

-- Breaks, or silently swaps values, when a column is added or reordered
INSERT INTO job_settings VALUES ('dunning', 'min_days_between_reminders', '7');

-- Survives schema changes and documents itself
INSERT INTO job_settings (job_name, setting_key, setting_value)
VALUES ('dunning', 'min_days_between_reminders', '7');

After formatting, each column and each value sits on its own line, so the reviewer can check them in pairs.

4. Explicit constraint and index names

If you do not name a constraint, the database generates a name, and the generated name depends on the database and sometimes on the environment. Later migrations that need to drop or alter it then have to look the name up, and error messages such as "violates check constraint" become less helpful.

-- Unnamed: the database invents a name such as invoices_total_check
-- or SYS_C0012345, and it differs between environments
ALTER TABLE invoices ADD CHECK (total >= 0);

-- Named: predictable in error messages, monitoring and later migrations
ALTER TABLE invoices
    ADD CONSTRAINT ck_invoices_total_non_negative CHECK (total >= 0);

ALTER TABLE invoice_reminders
    ADD CONSTRAINT fk_invoice_reminders_invoice
    FOREIGN KEY (invoice_id) REFERENCES invoices (id);

Choose a pattern and stick to it, for example pk_, fk_, uq_, ck_ and ix_ prefixes followed by the table and the columns or rule. Check your database's maximum identifier length, which is shorter than you may expect on some systems.

5. Comments that explain why

The SQL already says what the migration does. A comment at the top should say why: the problem it solves, the ticket, and how to undo it. Inside the file, comment anything a reviewer would otherwise have to ask about, such as why a backfill skips some rows or why a default value was chosen. SQL Format keeps -- and /* */ comments in place, so they survive formatting.

6. A reviewed WHERE clause on every UPDATE and DELETE

A data fix without a WHERE clause changes every row, and a WHERE clause with a misplaced OR can do almost the same. Before deploying, run a SELECT COUNT(*) with the exact same condition and compare the number with what the ticket expects:

-- 1. Count what the WHERE clause matches, and compare with the ticket
SELECT COUNT(*) FROM sessions WHERE expires_at < DATE '2026-01-01';

-- 2. Run the change with the same WHERE clause, copied, not retyped
DELETE FROM sessions WHERE expires_at < DATE '2026-01-01';

Formatting helps here: with one condition per line and parenthesised groups indented, mixed AND/OR logic is much easier to verify. The same trap is described in common SQL readability mistakes. For large tables, also consider deleting or updating in batches to keep locks and transaction logs small.

7. Deliberate transaction boundaries

Whether a failed migration leaves the database half changed depends on the database:

• PostgreSQL runs most DDL inside transactions, so a migration wrapped in a transaction either applies completely or not at all. A few commands, such as CREATE INDEX CONCURRENTLY, cannot run inside a transaction block and need a migration of their own.
• MySQL and MariaDB commit implicitly before and after most DDL statements. A migration that fails on its third statement leaves the first two applied, so plan the order and the recovery steps.
• Oracle also commits implicitly around DDL, with the same consequence.

Many migration tools already wrap each file in a transaction on databases that support transactional DDL. Check what yours does before adding BEGIN and COMMIT by hand, and never mix a long data backfill and DDL that takes strong locks in the same transaction without considering how long those locks will be held.

8. Idempotent or reversible

Deployments get retried. A migration should either be safe to run twice, or come with a tested way back. Guards such as IF NOT EXISTS (where your database supports them) and WHERE clauses that exclude rows already fixed make a second run harmless:

-- PostgreSQL: safe to re-run
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS last_reminded_at timestamptz;
CREATE INDEX IF NOT EXISTS ix_invoices_last_reminded_at ON invoices (last_reminded_at);

-- Data fix: the WHERE clause makes a second run a no-op
UPDATE invoices
SET status = 'void'
WHERE status = 'draft'
  AND created_at < DATE '2025-01-01';

For changes that cannot be re-run, write the rollback script at the same time as the migration and reference it in the header comment. Dropping a column or a table is only reversible if the data was copied somewhere first.

9. Review the formatted diff

Finally, read the diff the reviewer will see. Because the file was formatted with the agreed options, every changed line should correspond to a real change. Look for the items above: unnamed constraints, missing column lists, conditions without parentheses and comments that no longer match the code.

Worked example

A migration header that answers the reviewer's first questions before they ask them:

-- V42__invoices_last_reminded_at.sql
-- Why: the dunning job resends reminders because it cannot tell when
-- the last one went out. Ticket: BILL-318.
-- Rollback: V42_1__drop_last_reminded_at.sql (drops the index and column).
-- Run by hand with psql; a migration tool may add its own transaction.

BEGIN;

-- formatted statements go here, in the order they must run

COMMIT;

And the statements, as first typed and after formatting with the default options. Comments stay where they were, ALTER TABLE stays on one line, CREATE INDEX puts its ON clause on the next line, the backfill's derived table is indented inside its parentheses, and INSERT INTO job_settings ( stays on one line with the columns and values listed one per line below it. DDL words such as INDEX and type names such as TIMESTAMPTZ follow the keyword case setting like any other keyword.

-- V42: track when an invoice was last reminded, so the dunning job stops resending the same reminder
alter table invoices add column last_reminded_at timestamptz;
create index ix_invoices_last_reminded_at on invoices (last_reminded_at);
-- backfill from the reminder log; invoices never reminded stay null
update invoices set last_reminded_at = r.sent_at from (select invoice_id, max(sent_at) as sent_at from invoice_reminders group by invoice_id) r where r.invoice_id = invoices.id and invoices.status = 'open';
insert into job_settings (job_name, setting_key, setting_value) values ('dunning', 'min_days_between_reminders', '7');

-- V42: track when an invoice was last reminded, so the dunning job stops resending the same reminder
ALTER TABLE invoices ADD COLUMN last_reminded_at TIMESTAMPTZ;
CREATE INDEX ix_invoices_last_reminded_at
ON invoices(last_reminded_at);
-- backfill from the reminder log; invoices never reminded stay null
UPDATE invoices
SET last_reminded_at = r.sent_at
FROM (
    SELECT invoice_id,
        MAX(sent_at) AS sent_at
    FROM invoice_reminders
    GROUP BY invoice_id
) r
WHERE r.invoice_id = invoices.id
AND invoices.status = 'open';
INSERT INTO job_settings (
    job_name,
    setting_key,
    setting_value
)
VALUES (
    'dunning',
    'min_days_between_reminders',
    '7'
);

Note how the backfill's WHERE clause now shows its two conditions on separate lines, and how the INSERT makes it obvious which value goes into which column. Those are exactly the lines a reviewer should spend time on.

Frequently asked questions