usefulweb.siteDependable little tools for people and AI agents. Opsdeck Schema Diff & Migration Generator (PostgreSQL, MySQL, SQLite)
Theme: Auto

Theme

Premium apps Part of Opsdeck

Opsdeck Schema Diff & Migration Generator (PostgreSQL, MySQL, SQLite)

Compare two DDL versions: ordered migration, rollback script and data-loss warnings.

CREATE TABLE, CREATE INDEX and ALTER TABLE statements of the version that is deployed now.

More options

The version you want to migrate to.

A diff cannot tell a rename from drop + add. One hint per line keeps the data: table.old -> new for a column, old -> new for a table.

☁ Sign in to save to your Google Drive

Try an example: Shop schema v1 → v2 (PostgreSQL) with a column rename · The same change for SQLite (table rebuilds where ALTER TABLE cannot) · MySQL: widen a column, add an index and a foreign key

More about this tool

How it works

Paste the current and the new CREATE TABLE / CREATE INDEX / ALTER TABLE statements: the tool reads both into a schema model, lists every change per table and writes an ordered migration script for PostgreSQL, MySQL/MariaDB or SQLite plus the reverse (rollback) script. Dropped tables and columns, narrowed types, new NOT NULL, unique, foreign-key and check constraints are flagged. Both scripts are verified by applying them to the model. It is the schema diff of the Opsdeck Database desk.

  • Both DDL texts are tokenised (strings, quoted names, comments, dollar quotes) and read statement by statement into one schema model: tables, columns (normalised type, NULL/NOT NULL, default, identity/auto-increment, generated expression), primary key, unique keys, foreign keys with ON DELETE/UPDATE, checks and indexes. Later ALTER TABLE statements are applied to the model in order.
  • Tables and columns are matched by name (case-insensitive). Rename hints are applied first; without them a renamed column shows up as drop + add, and the tool points that out.
  • The script runs in a safe order: drop changed foreign keys, indexes and constraints; renames; new tables in dependency order; add, change and drop columns; drop tables (children first); then new keys, checks, indexes and foreign keys. MySQL uses MODIFY COLUMN with its position; SQLite rebuilds a table (create new, copy rows, drop, rename) when ALTER TABLE cannot express the change.
  • Type changes are classified: narrower integers, shorter text, fewer decimal digits, float → integer, timestamp → date are destructive; changes between kinds of data are risky. New NOT NULL, unique, primary-key, foreign-key and check constraints are risky because existing rows may break them.
  • Verification: the forward script is applied to the parsed source with the same parser and must give the parsed target; the reverse script must give back the source. PostgreSQL and SQLite add columns at the end, so their column order is compared as a set.

The script is generated from the DDL text only: it cannot see your data, views, triggers, functions, grants or partitions. Review it, run it on a copy first and keep a backup.

Sources

Part of Opsdeck

  • Opsdeck Developer & ops workbench

    One deck for CI failures, cron, traces, k8s events, API diffs, query plans and Terraform.

    Open Opsdeck

Related tools

How to use the Opsdeck Schema Diff & Migration Generator

Compare two versions of your DDL and get an ordered migration, a rollback script and warnings for every step that can lose data or fail.

What it is for

You changed a few CREATE TABLE statements and now need the ALTER TABLE script that takes the live database from the old version to the new one, plus a way back. This tool reads both DDL texts into a schema model, lists every change per table and writes the migration and the rollback for PostgreSQL, MySQL/MariaDB or SQLite.

It works on the DDL text only and never connects to a database, so it cannot see your rows, views, triggers or grants. It flags steps that can fail or destroy data; you still test the script on a copy.

Worked example

The first example compares two versions of a small shop schema in PostgreSQL. Version 2 renames customers.name to full_name, shortens email from varchar(255) to varchar(120), adds a country column, makes orders.customer_id and orders.status NOT NULL, narrows orders.total from numeric(12,2) to numeric(10,2), drops legacy_code and the audit_log table and adds an order_items table. The rename hint customers.name -> full_name is filled in.

The tool answers with 18 migration statements: 1 table added, 1 dropped, 3 changed; 4 destructive steps. The four destructive steps are the dropped table, the dropped column and the two narrowed types. Four more steps are marked risky, for example "Making “orders.customer_id” NOT NULL fails while any row holds NULL there" and the new foreign key, which fails if an order points to a missing customer.

The script starts with ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey, then RENAME COLUMN name TO full_name, creates order_items, changes the columns and only at the end adds the new check, index and foreign key. The rollback script reverses every step, and both are verified.

Switch the dialect to SQLite and the same change becomes 21 statements: SQLite cannot change a column type in place, so the tool rebuilds customers and orders (create a new table, copy the rows, drop the old one, rename) with foreign keys switched off.

How to read the result

  • Metrics: statement count, tables and columns added, dropped and changed, the number of destructive steps and whether both scripts were verified.
  • Warnings: destructive means data is lost or truncated; risky means the statement fails if existing rows break the new rule.
  • Changes per table: a summary for your pull request.
  • Forward and reverse migration: ready to download as .sql. PostgreSQL scripts run inside BEGIN … COMMIT, and so do SQLite scripts that rebuild or drop tables.
  • Notes: unnamed constraints are dropped under the name the database gives them by default, such as orders_customer_id_fkey. Check the real name first.

Tips and common mistakes

  • A diff cannot tell a rename from drop + add. Without a hint you lose the column's data; the tool spots same-type pairs and suggests the hint line.
  • Give constraints explicit names in your DDL. Then drops never depend on guessed default names.
  • Before tightening a column to NOT NULL, run the suggested UPDATE … WHERE … IS NULL first.
  • PostgreSQL and SQLite add new columns at the end of a table; MySQL scripts use AFTER to keep your order.
  • Types are not translated between dialects: compare PostgreSQL DDL with PostgreSQL DDL.

FAQ

What does "verified on the model" mean?

The tool applies the generated script to the parsed old schema with its own DDL parser and checks that the result equals the parsed new schema; the rollback must give back the old one. It proves the script is complete, not that your data fits.

Which statements does it understand?

CREATE TABLE, CREATE INDEX, ALTER TABLE (add, drop, alter, modify, change and rename columns and constraints), DROP and RENAME TABLE. Views, functions, triggers and partitions are skipped with a note.

Does it handle foreign keys in the right order?

Yes. Changed foreign keys are dropped first and added last, new tables are created in dependency order and dropped tables go children first.

Is my schema uploaded anywhere?

The tool page sends the text to the usefulweb API to compute the result. In the Opsdeck Database desk the same engine runs in your browser.

Guide last updated:

For scripts and AI agents

  • JSON API: POST https://opsdeckschemadiff.usefulweb.site/api/v1/calculate (same calculation as this page)
  • Interface description: OpenAPI 3.1 · AI guide: llms.txt
  • MCP server (Streamable HTTP): https://opsdeckschemadiff.usefulweb.site/mcp · tool name opsdeck_schema_diff
Example request
curl -s -X POST https://opsdeckschemadiff.usefulweb.site/api/v1/calculate \
  -H 'content-type: application/json' \
  -d '{…see OpenAPI examples…}'
Feedback · ideas & bug reports welcome