Handling Destructive PostgreSQL Schema Changes Safely

Dropping a column, changing a type, or renaming a table are changes that can permanently destroy data if applied carelessly. supaschema blocks these operations by default and requires you to explicitly acknowledge them with a hint before it will render the corresponding SQL. This two-step pattern — declare intent, then generate — prevents accidental data loss from a typo in a schema file and creates a reviewable paper trail for every destructive decision.

What counts as destructive

supaschema classifies the following as destructive operations that require an explicit hint:

Column drops — removing a column from a CREATE TABLE statement
Column type changes — changing the data type of an existing column
Table drops — removing a table entirely from your schema
View drops — removing a view, or redefining a view in a way that changes its column list
Function signature changes — altering parameter types or return types of an existing function
Enum value removal or reorder — removing a label from an existing CREATE TYPE ... AS ENUM or changing the order of labels

Operations that are not destructive and require no hint include adding columns, adding tables, adding enum values, creating new indexes, and updating DEFAULT expressions.

Default behavior

When supaschema detects a destructive change without a corresponding hint, the diff command exits non-zero and prints one of these diagnostics:

Diagnostic code	Trigger
`SUPA_PLAN_DESTRUCTIVE_HINT_REQUIRED`	Column drop, table drop, view drop
`SUPA_PLAN_COLUMN_ALTER_HINT_REQUIRED`	Column type change
`SUPA_PLAN_RENAME_VERIFY_REQUIRED`	Table or column rename
`SUPA_CHECK_ALTER_COLUMN_TYPE_REWRITE`	Type change that requires a full table rewrite

No migration file is written until all required hints are present.

Dropping a column

Before

-- supabase/schemas/001_app.sql
CREATE TABLE app.accounts (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name        text NOT NULL,
  legacy_ref  text,           -- <-- you want to drop this
  status      text NOT NULL DEFAULT 'active'
);

After

-- supabase/schemas/001_app.sql
CREATE TABLE app.accounts (
  id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name    text NOT NULL,
  status  text NOT NULL DEFAULT 'active'
);

Remove the column from your schema file

Delete the legacy_ref text, line from supabase/schemas/001_app.sql and save.

Run diff with --write-hints to discover the hint key

npx supaschema diff \
  --from git:HEAD \
  --to dir:supabase/schemas \
  --write-hints

supaschema prints the SUPA_PLAN_DESTRUCTIVE_HINT_REQUIRED diagnostic and writes the required hint key to stdout. It also appends a suggested entry to supaschema.config.json under hints.destructive.

Confirm the hint in your config

Open supaschema.config.json and verify the entry was added:

{
  "hints": {
    "destructive": [
      "column:app.accounts.legacy_ref"
    ]
  }
}

Run diff again to generate the migration

npx supaschema diff \
  --from git:HEAD \
  --to dir:supabase/schemas

supaschema now renders the migration and writes it to supabase/migrations/:

-- supabase/migrations/20240601120000_drop_legacy_ref.sql
ALTER TABLE app.accounts
  DROP COLUMN IF EXISTS legacy_ref;

Verify the migration

npx supaschema verify \
  --from git:HEAD \
  --to dir:supabase/schemas \
  --migration supabase/migrations/20240601120000_drop_legacy_ref.sql \
  --database-url postgresql://postgres:postgres@localhost:54322/postgres

The hint key "column:app.accounts.legacy_ref" is a permanent acknowledgement that this column may be dropped. Remove the entry from hints.destructive after the migration has been applied to production so a future accidental deletion is caught again.

Changing a column type

Before → After

-- Before
created_at  bigint NOT NULL

-- After
created_at  timestamptz NOT NULL

Update the type in your schema file

Change bigint to timestamptz for the created_at column.

Get the hint key

npx supaschema diff --from git:HEAD --to dir:supabase/schemas --write-hints

The diagnostic SUPA_PLAN_COLUMN_ALTER_HINT_REQUIRED is raised. The suggested key is written to your config:

{
  "hints": {
    "destructive": [
      "column:app.accounts.created_at"
    ]
  }
}

Run diff to generate the migration

npx supaschema diff --from git:HEAD --to dir:supabase/schemas

The generated SQL uses a USING cast to convert existing values:

ALTER TABLE app.accounts
  ALTER COLUMN created_at TYPE timestamptz
  USING to_timestamp(created_at);

Review the USING expression

supaschema generates a best-effort USING clause, but you should verify it produces correct results for your data. Edit the migration file directly if the default conversion is not appropriate — for example, if your bigint stores milliseconds rather than seconds.

The SUPA_CHECK_ALTER_COLUMN_TYPE_REWRITE diagnostic is raised when the type change requires PostgreSQL to rewrite the entire table. This takes an ACCESS EXCLUSIVE lock and blocks reads and writes for the duration of the rewrite. For large tables, schedule the migration during a maintenance window or use a shadow-column approach.

Renaming a table or column

Supaschema cannot infer a rename from a schema diff alone — a renamed table looks identical to a drop followed by a create. You must supply a rename hint that explicitly maps the old name to the new name.

Renaming a table

{
  "hints": {
    "renames": [
      { "from": "table:app.users", "to": "table:app.accounts" }
    ]
  }
}

Update the table name in your schema file

Change every reference from app.users to app.accounts in your schema SQL files.

Add the rename hint to your config

Add the from/to entry shown above under hints.renames.

Run diff

npx supaschema diff --from git:HEAD --to dir:supabase/schemas

supaschema renders:

ALTER TABLE app.users RENAME TO accounts;

Run verify — strongly recommended

The SUPA_PLAN_RENAME_VERIFY_REQUIRED diagnostic flags renames as operations that should always be verified before applying:

npx supaschema verify \
  --from git:HEAD \
  --to dir:supabase/schemas \
  --migration supabase/migrations/20240601130000_rename_users.sql \
  --database-url postgresql://postgres:postgres@localhost:54322/postgres \
  --ensure-roles

Renaming a column

{
  "hints": {
    "renames": [
      { "from": "column:app.accounts.user_name", "to": "column:app.accounts.display_name" }
    ]
  }
}

The generated SQL will be:

ALTER TABLE app.accounts RENAME COLUMN user_name TO display_name;

Changing a view’s column list

Adding, removing, or reordering columns in a view cannot be done with CREATE OR REPLACE VIEW alone when the column list changes. supaschema drops and recreates the view.

Update the view in your schema file

-- Before
CREATE VIEW app.account_summary AS
  SELECT id, name, legacy_ref, status FROM app.accounts;

-- After
CREATE VIEW app.account_summary AS
  SELECT id, name, status FROM app.accounts;

Run diff with --write-hints

npx supaschema diff --from git:HEAD --to dir:supabase/schemas --write-hints

The hint key "view:app.account_summary" is suggested.

Confirm the hint

{
  "hints": {
    "destructive": [
      "view:app.account_summary"
    ]
  }
}

Run diff to generate the migration

npx supaschema diff --from git:HEAD --to dir:supabase/schemas

Generated SQL:

DROP VIEW IF EXISTS app.account_summary;

CREATE VIEW app.account_summary AS
  SELECT id, name, status FROM app.accounts;

Dependent objects (materialized views, functions that reference the view) will also be dropped by the DROP VIEW cascade. Check your schema for dependents before applying.

Enum changes

Adding values — no hint needed

Adding labels to an existing enum is non-destructive. supaschema renders this automatically:

-- Before
CREATE TYPE app.account_status AS ENUM ('active', 'suspended');

-- After
CREATE TYPE app.account_status AS ENUM ('active', 'suspended', 'deleted');

Generated migration:

ALTER TYPE app.account_status ADD VALUE IF NOT EXISTS 'deleted';

Removing or reordering enum values — must hand-author

PostgreSQL does not support ALTER TYPE ... DROP VALUE or reordering of enum labels. supaschema will raise SUPA_PLAN_DESTRUCTIVE_HINT_REQUIRED and will not attempt to automate this change because there is no safe, reversible SQL expression for it. You must hand-author a migration using the temporary-type pattern:

-- Step 1: Create a new enum with the desired values
CREATE TYPE app.account_status_new AS ENUM ('active', 'deleted');

-- Step 2: Update all columns that use the old type
ALTER TABLE app.accounts
  ALTER COLUMN status TYPE app.account_status_new
  USING status::text::app.account_status_new;

-- Step 3: Drop the old type and rename the new one
DROP TYPE app.account_status;
ALTER TYPE app.account_status_new RENAME TO account_status;

The USING cast in step 2 will fail at runtime if any row contains a value that does not exist in the new enum. Run a data audit — SELECT DISTINCT status FROM app.accounts — before applying.

After hand-authoring the migration, update your schema file to match the new enum definition and run npx supaschema diff to confirm zero drift.

Complete before/after example

The following shows a real schema change that combines a column drop and a type change, the resulting hint config, and the generated migration.

Schema before

-- supabase/schemas/001_app.sql
CREATE TABLE app.orders (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id  uuid NOT NULL REFERENCES app.accounts(id),
  amount      bigint NOT NULL,        -- stored as cents, bigint
  note        text,                   -- to be dropped
  created_at  timestamptz NOT NULL DEFAULT now()
);

Schema after

-- supabase/schemas/001_app.sql
CREATE TABLE app.orders (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id  uuid NOT NULL REFERENCES app.accounts(id),
  amount      numeric(12, 2) NOT NULL, -- changed to decimal
  created_at  timestamptz NOT NULL DEFAULT now()
);

supaschema.config.json

{
  "$schema": "./node_modules/supaschema/config-schema.json",
  "hints": {
    "destructive": [
      "column:app.orders.note",
      "column:app.orders.amount"
    ]
  }
}

Generated migration

-- supabase/migrations/20240601140000_orders_cleanup.sql
ALTER TABLE app.orders
  DROP COLUMN IF EXISTS note;

ALTER TABLE app.orders
  ALTER COLUMN amount TYPE numeric(12, 2)
  USING (amount / 100.0);

Verify

npx supaschema verify \
  --from git:HEAD \
  --to dir:supabase/schemas \
  --migration supabase/migrations/20240601140000_orders_cleanup.sql \
  --database-url postgresql://postgres:postgres@localhost:54322/postgres

A passing verify confirms the migration applies cleanly, is idempotent, and leaves the database in a state that matches your declared schema.

​What counts as destructive

​Default behavior

​Dropping a column

​Before

​After

​Changing a column type

​Before → After

​Renaming a table or column

​Renaming a table

​Renaming a column

​Changing a view’s column list

​Enum changes

​Adding values — no hint needed

​Removing or reordering enum values — must hand-author

​Complete before/after example

​Schema before

​Schema after

​supaschema.config.json

​Generated migration

​Verify

What counts as destructive

Default behavior

Dropping a column

Before

After

Changing a column type

Before → After

Renaming a table or column

Renaming a table

Renaming a column

Changing a view’s column list

Enum changes

Adding values — no hint needed

Removing or reordering enum values — must hand-author

Complete before/after example

Schema before

Schema after

supaschema.config.json

Generated migration

Verify