Reviewing a Schema Change in a Pull Request

What a schema change looks like in a pull request

For the database architect who reviews schema pull requests and needs to approve changes before they deploy to production. A pull request review on a database schema must verify the exact migration SQL and table lock modes generated between model states, not just the visible text diff. A pull request that changes a database schema usually arrives as one of three artifacts: a handwritten migration file, a framework-generated DDL script, or a committed database design model file. These three formats do not carry the same information, and confusing them leads to approving changes that run different operations on production than what appeared in the review.

A handwritten migration file contains raw SQL statements such as ALTER TABLE or CREATE INDEX. It tells you what statements the author intends to execute, but it gives no context on whether those statements match the current state of production or staging. A framework-generated migration, produced by an object-relational mapping tool, translates entity class changes into DDL. These scripts often omit index tuning parameters, lock timeouts, and non-blocking keywords. A visual design model file records the intended state of tables, columns, data types, foreign keys, and layout structures in structured XML.

GitHub pull request reviews let you inspect file diffs, leave comments on specific lines, and request changes before merging code[1]. Whichever of the three formats lands in the diff, each one leaves a different gap between what the reviewer can read and what the deployment will run.

A comprehensive review requires inspecting both the declarative model change and the exact DDL statements that will execute against the target database engine.

Reading the design model file in a review

A visual design tool can save its database project metadata into a .dbs model file, which is a plain XML document. Because the design model is stored as structured text rather than a binary blob, Git tracks every modification across branches and commits. When a developer modifies a table, renames a column, or adds an index, the pull request displays those modifications line by line in the standard Git diff.

You can review changes in a .dbs file directly in your Git web interface or terminal without opening the desktop tool, because git diff shows changes between the working tree and the index or a tree, between the index and a tree, or between two trees, rendered as line-by-line text[2]. The XML structure groups definitions by schema, table, column, and constraint. Here is an example of a git diff on a .dbs model file where a developer renamed customer_mail to email, converted the data type from varchar(100) to varchar(255), and added a unique index:

 <table name="customers" >
- <column name="customer_mail" type="varchar" length="100" decimal="0" jt="12" mandatory="y" />
+ <column name="email" type="varchar" length="255" decimal="0" jt="12" mandatory="y" />
  <column name="created_at" type="timestamp" decimal="0" jt="93" mandatory="y" />
+ <index name="idx_customers_email" unique="UNIQUE" >
+   <column name="email" />
+ </index>
 </table>

The text diff clearly identifies the intent: one column was renamed and widened, and one index was introduced. The XML diff does not show the migration script needed to apply this change safely to a live table containing millions of rows. In relational databases, renaming a column breaks running queries that reference the old name, and widening a column or building an index can acquire exclusive locks.

None of this is reviewable from a single pull request in isolation: it depends on the whole model being put under version control in the first place, so that every branch's .dbs file has a common ancestor a diff can be taken against.

Generating the migration script between two versions

In a robust schema review workflow, developers do not write deployment DDL by hand from scratch. Instead, migration scripts are generated by comparing two model states, such as the current feature branch model against the main branch model, or comparing the design model directly against the live database. Generating migration scripts between model versions is a Pro Edition capability of the design tool: it calculates the structural difference and produces the corresponding SQL DDL.

When you generate a migration script between two design versions, you inspect the exact statements that will run during deployment. Using a dedicated schema comparison tool generates the deployment script and automatically orders table creation, index builds, and constraint enforcement so that dependent objects deploy in the correct sequence.

1. Check out the feature branch containing the updated .dbs model file.
2. Open the model in the desktop design tool and select Schema Synchronization from the Model menu.
3. Select the comparison source as the target live database connection or a previous baseline model file.
4. Review the side-by-side diff of tables, columns, foreign keys, and indexes.
5. Click Generate SQL to produce the deployment DDL script and copy it into the pull request review.

Inspecting the generated SQL script bridges the gap between what the developer designed in the visual model and what the database engine will execute during release. This review stops at inspecting that script; writing one safely, backfills and all, is its own procedure — see turning a schema diff into a safe migration script.

Lock levels a text diff hides: PostgreSQL

A clean text diff in Git hides the transactional locking mechanics of the database engine. In the PostgreSQL documentation (current release 18), ACCESS EXCLUSIVE is the strongest table-level lock mode, and many forms of ALTER INDEX and ALTER TABLE acquire a lock at that level[3]. ACCESS EXCLUSIVE conflicts with locks of all modes, including ACCESS SHARE, the mode the SELECT command takes on referenced tables, so it blocks concurrent read queries as well as the ROW EXCLUSIVE locks that INSERT, UPDATE and DELETE acquire[3].

When reviewing PostgreSQL schema changes, pay attention to column defaults and table rewrites. The PostgreSQL ALTER TABLE reference explains that when a column is added with a non-volatile default, the default value is evaluated once at the time of the statement and stored in the table's metadata, so existing rows do not have to be rewritten[4]. If the default expression is volatile, such as clock_timestamp() or random(), the value has to be stored in every existing row instead, which means the whole table is rewritten while the ALTER TABLE holds its lock[4].

-- Unsafe in high-traffic production: rewrites the table under ACCESS EXCLUSIVE lock
ALTER TABLE orders ADD COLUMN generated_token text DEFAULT md5(random()::text);

-- Safe multi-step pattern: adds column instantly, then updates in batches
ALTER TABLE orders ADD COLUMN generated_token text;
-- Backfill rows in controlled application batches before adding NOT NULL constraints

Building an index with a standard CREATE INDEX statement (without CONCURRENTLY) acquires a SHARE lock on the table, and SHARE conflicts with the ROW EXCLUSIVE mode that INSERT, UPDATE and DELETE take, so concurrent writes wait until the index build finishes[3]. In production reviews, require CREATE INDEX CONCURRENTLY, which takes only a SHARE UPDATE EXCLUSIVE lock and therefore lets reads and writes continue[3]. A single pull request is one snapshot of that lock behavior; how the same PostgreSQL schema is versioned across environments over many such reviews is a separate concern.

Lock levels a text diff hides: SQL Server

In Microsoft SQL Server, a data definition language operation such as ALTER TABLE acquires a schema modification (Sch-M) lock on the table for the duration of execution. Microsoft's transaction locking and row versioning guide describes Sch-M locks as blocking all outside operations on the object until the lock is released[5]. That includes read queries, which take schema stability (Sch-S) locks while a statement is compiled and executed, because Sch-S and Sch-M are incompatible. If an ALTER TABLE statement encounters an open user transaction, it waits in the lock queue and blocks all subsequent incoming read and write requests behind it.

Index creation in SQL Server without explicit online options acquires shared or exclusive table locks that stall production traffic on large datasets. The reviewer must check that index creation commands on enterprise workloads specify the ONLINE = ON option.

-- Standard index creation: blocks concurrent modifications
CREATE NONCLUSTERED INDEX idx_orders_customer ON orders (customer_id);

-- Online index creation: permits concurrent user transactions during build
CREATE NONCLUSTERED INDEX idx_orders_customer ON orders (customer_id)
WITH (ONLINE = ON);

Column type changes need the same scrutiny. Changing a column from INT to BIGINT means running ALTER TABLE ALTER COLUMN, and the T-SQL reference shows that this form of the statement accepts a WITH (ONLINE = ON) clause precisely so that concurrent access can continue while the alteration is applied[6]. Without it, the Sch-M lock the statement takes is held until the engine finishes applying the change to the stored data. In high-throughput tables, review this as an expand-contract deployment (add the wider column, backfill in batches, switch reads and writes over, then drop the old column) rather than a single transaction.

What to check before approving a schema change

Before approving a schema pull request, verify each change against a strict architectural checklist. A checklist prevents common deployment incidents by validating lock impact, backward compatibility, and artifact synchronization.

• Reversibility: Verify that a rollback script exists for every DDL operation, or that the pull request description explicitly states why the operation is irreversible.
• Expand-contract sequence: Confirm that no existing column is dropped or renamed in the same deployment that stops writing to it. The application code must stop referencing the column in a prior release before the column is dropped.
• Non-blocking index builds: Verify that PostgreSQL index creations specify CONCURRENTLY and SQL Server index creations specify ONLINE = ON. On MySQL 8.4, InnoDB adds a secondary index in place and keeps the table available for reads and writes during the build, but adding a FULLTEXT or SPATIAL index does not permit concurrent DML[7].
• Artifact parity: Ensure the changes in the visual .dbs design model match the generated SQL migration script exactly.
• Lock timeouts: Check that DDL scripts define explicit session timeouts (such as SET lock_timeout = '3s' in PostgreSQL) so a blocked migration fails fast instead of creating a connection cascade.

If any item in the checklist fails, request changes on the pull request and require the author to update the migration script with safe execution parameters.

A checklist only works if the reviewer is not the only person who ever opens the model file. See team collaboration in schema design for how a team shares one .dbs model through Git without two people silently overwriting each other's tables.

When the diff is clean but the deploy is not

A common failure mode in database releases happens when a pull request diff looks completely clean in code review, passes automated unit tests in CI, and still causes an outage in production. This occurs because unit tests and staging environments run against small, idle databases where an ACCESS EXCLUSIVE or Sch-M lock completes in milliseconds. On a production database processing thousands of transactions per second, that same DDL statement queues behind a long-running transaction, blocks all incoming queries, and exhausts the connection pool within seconds.

Preventing deployment failures requires reviewing the operational mechanics of the generated DDL alongside the design model. Download DbSchema, open the project model against your target database, and generate the exact migration script for the schema change currently sitting in review.

Frequently asked questions

What does a schema change look like in a pull request?

A schema change typically arrives in a pull request as a hand-written migration file, a generated DDL script, or a changed design model file. A visual design model saved as XML means column and table changes can be read directly as a standard text diff.

Why is reading a text diff not enough to approve a database change?

A text diff shows how the schema's definition changed, but it hides the exact SQL statements the deployment will run and the locking behavior of the database engine. A clean diff can still take the site down if the engine rewrites the table during the deployment.

Does adding a column lock the table in PostgreSQL?

Yes. In PostgreSQL 18, many forms of ALTER TABLE take an ACCESS EXCLUSIVE lock, which blocks read and write operations against the table while the change is applied. Adding a column with a volatile default value will also rewrite the entire table.

What is a Sch-M lock in SQL Server?

A schema modification (Sch-M) lock is acquired by SQL Server during any ALTER TABLE data definition language operation. This lock blocks all concurrent workloads from accessing the object until the modification finishes, halting application operations.

How do you check a schema migration script before approving it?

Verify that the migration is reversible, that no column is dropped while the application still writes to it, and that index builds use a concurrent form. The exact change must be present in both the design model file and the generated migration script.

Can you review schema changes directly in GitHub?

You can review the XML model file diff and submit decisions directly in the pull request interface using comment, approve, or request changes statuses. However, you must generate the SQL migration script locally to verify the database engine locking behavior.

Sources

1. docs.github.com
2. git-scm.com
3. postgresql.org
4. postgresql.org
5. learn.microsoft.com
6. learn.microsoft.com
7. dev.mysql.com