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

> Compare two DDL versions: ordered migration, rollback script and data-loss warnings. 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.

Part of usefulweb.site (https://usefulweb.site/). Part of [Opsdeck](https://opsdeck.usefulweb.site/) (ownerApp: opsdeck). Results are deterministic calculations from the inputs and the listed assumptions; the API returns exactly what the web page shows.

## Use it
- Human interface: https://opsdeckschemadiff.usefulweb.site/ (interactive tool at https://opsdeckschemadiff.usefulweb.site/tool)
- JSON API: POST https://opsdeckschemadiff.usefulweb.site/api/v1/calculate with a JSON object of inputs (schema: https://opsdeckschemadiff.usefulweb.site/api/v1/tool)
- OpenAPI: https://opsdeckschemadiff.usefulweb.site/openapi.json
- MCP (Streamable HTTP): https://opsdeckschemadiff.usefulweb.site/mcp - tool `opsdeck_schema_diff`

## Inputs
- source (textarea): Current schema (DDL) - CREATE TABLE, CREATE INDEX and ALTER TABLE statements of the version that is deployed now.
- target (textarea): New schema (DDL) - The version you want to migrate to.
- dialect (select): SQL dialect
- renames (textarea): Rename hints (optional) - 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.

## Method
- 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.

## Limits
- 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.