Introduce an Explicit Workflow for Renaming Schema Objects

[natty-pluz]

This proposal introduces a safe, explicit, and idempotent workflow for handling RENAME operations. It centers around a version-controlled manifest file, refactor.yaml, which explicitly declares the developer's intent. To ensure operations run only once, a new internal log table, pgschema.refactor_log, will track completed refactors. Finally, a new command, pgschema refactors, will provide a way to audit this log.

The Problem: Renaming is Destructive

For a user of the current version of pgschema, any attempt to rename an object is interpreted as a destructive DROP and CREATE operation, leading to data loss. This proposal solves that by allowing developers to explicitly declare their intent.

Core Component 1: The pgschema.refactor_log Table

The cornerstone of this solution is an internal log table whose sole purpose is to serve as a permanent, historical record of completed refactoring operations. This is the key to idempotency.

• Location: Resides in a dedicated pgschema schema (CREATE SCHEMA IF NOT EXISTS pgschema;).
• Proposed Structure (pgschema.refactor_log):

-- This table stores a permanent, append-only log of completed refactoring operations.
-- Its primary purpose is to ensure that a given refactor is only ever applied once.
CREATE TABLE IF NOT EXISTS pgschema.refactor_log (
    id BIGSERIAL PRIMARY KEY,

    -- A unique, deterministic SHA-256 hash of the operation's parameters.
    -- This is the key to ensuring idempotency.
    -- e.g., '3a4f6534571e1f76f7f6c3823f6d7240c21b3a6a9b5f0a1c2d3e4f5a6b7c8d9e'
    operation_hash TEXT NOT NULL UNIQUE,

    -- The type of refactoring operation that was performed.
    -- e.g., 'RENAME'
    operation_type VARCHAR(64) NOT NULL,

    -- The type of database object that was affected.
    -- e.g., 'TABLE', 'COLUMN'
    object_type VARCHAR(64) NOT NULL,

    -- The schema of the object that was refactored.
    -- e.g., 'public'
    schema_name TEXT NOT NULL,

    -- For nested objects (like columns or indexes), this is the name of the parent object.
    -- For top-level objects (like tables), this is NULL.
    -- e.g., 'users' for a column rename, NULL for a table rename
    parent_object_name TEXT,

    -- The name of the object before the refactor.
    -- e.g., 'email', 'orders_archive'
    old_name TEXT,

    -- The name of the object after the refactor.
    -- e.g., 'email_address', 'archived_orders'
    new_name TEXT,

    -- The timestamp when the operation was successfully applied.
    -- e.g., '2025-09-28 14:30:00Z'
    applied_at TIMESTPTZ NOT NULL DEFAULT NOW()
);

Core Component 2: The refactor.yaml Manifest File

This file is the developer's explicit, version-controlled intent for the next migration.

• Syntax (refactor.yaml):

  renames:
    - type: column
      table: public.users
      from: email
      to: email_address
  
    # For rare edge cases, an optional `nonce` can force a unique hash.
    - type: table
      from: public.archived_orders
      to: public.orders_archive_2025
      # A "nonce" (number used once) makes this operation's hash unique.
      nonce: "archive-orders-for-end-of-year-2025"

---

Part 1: Declaring Intent (Two Alternative Workflows)

A developer can use either method to populate their local refactor.yaml file.

Workflow A: Convenient CLI (pgschema rename)

• New Command: pgschema rename
• Behavior: A pure file-writing utility that appends an entry to the local refactor.yaml file. It does not interact with the database.
• Syntax: pgschema rename column --table public.users --from email --to email_address

Workflow B: Manual File Creation

The developer creates or edits the refactor.yaml file by hand, giving them full control, including the ability to add a nonce if needed.

---

Part 2: Applying and Tracking Changes

The plan and apply commands are enhanced to use the manifest and the log. The key is the deterministic generation of an operation_hash for each directive.

• Hash Generation: The operation_hash is a SHA-256 hash of a canonical JSON string representing the operation. If the optional nonce field is present in refactor.yaml, its value is included in the string that gets hashed. This ensures that even if all other parameters are identical to a past operation, the presence of a unique nonce will generate a new, un-logged hash.

• pgschema plan: Reads refactor.yaml, checks the pgschema.refactor_log for each entry's hash, and generates a safe RENAME plan for any un-logged operations.

• pgschema apply: Executes the RENAME and records its hash in the pgschema.refactor_log to mark it as completed.

---

Part 3: Auditing and History

To provide visibility into the history of completed refactors, a new, single-word command is introduced.

• New Command: pgschema refactors
• Behavior: This is a read-only command that connects to the database, queries the pgschema.refactor_log table, and displays a list of all completed refactoring operations for auditing purposes.
• Default Output (Human-Readable):

  $ pgschema refactors --db myapp
  
  TYPE    OBJECT    SCHEMA    PARENT    OLD_NAME        NEW_NAME         APPLIED_AT
  ------- --------- --------- --------- --------------- ---------------- --------------------
  RENAME  COLUMN    public    users     email           email_address    2025-09-28T14:30:00Z
  

• Optional YAML Output:

  pgschema refactors --db myapp --output-yaml > refactor_history.yaml

[tianzhou]

@nathnaeld Thanks for the detailed proposal.

However, one of the design goal of pgschema is not leveraging external state such as refactor_log table mentioned here

[natty-pluz]

@tianzhou, I just wanted to say a huge thank you for creating pgschema. I've always been looking for a declarative workflow for managing database schemas, so this tool is truly a dream come true. And thank you again for the quick reply and the clarification on the "no external state" design goal.

With that in mind, here's how the proposal would work in a completely stateless way, using the live diff as the context.

A Practical Example

Let's say we want to rename a column from email to email_address.

Scenario 1: Before the Rename is Applied

• The database has a column named email.
• Your .sql file has a column named email_address.
• refactor.yaml contains the instruction: rename from email to email_address.

When pgschema plan runs, it sees a destructive DROP/ADD diff but reconciles it with refactor.yaml and generates a safe plan:

ALTER TABLE users RENAME COLUMN email TO email_address;

Scenario 2: On All Future Runs (After the Rename is Applied)

After applying the change, the state is now:

• The database has the email_address column.
• The .sql file also has the email_address column.

This time, when pgschema plan runs, the planner sees no difference between the database and the file. Since there's no destructive DROP/ADD diff for the instruction in refactor.yaml to resolve, it is safely ignored. The plan will be empty ("No changes to apply.").

This approach achieves the same goal of safe, idempotent renames while fully respecting the core design.

Thanks again for your work on this fantastic tool.

[tianzhou]

Thanks for the adjusted proposal. I understand renaming is a desired feature, but it's not easy to get it right.

I need more time to think it through and also allow others to provide feedback.

[marcintustin]

Why not have explicit renames in the sql file?

[PadenZach]

+1 really do not want to introduce (more) yaml to our workflows, if we started doing something with a log file + table there isn't a reason to use something like pgschema over other declarative (but non sql!) db management systems.

perhaps we could use some sort of comment hint? These wouldn't be 'forced' but if they match could help prod pgschema into making a destructive change into a non-destructive change. This would have several limitations such as:

• Not being arrive from any previous schema to the current schema
• Not being able to convert multiple schemas in one go out of the box (unless we added some sort of 'order' hint, which I think starts getting too complicated.

eg, in the schema.sql have something like:

-- myschema.sql v1
CREATE TABLE my_table (
 id int
 email text
);

->

-- myschema.sql v2
CREATE TABLE my_table (
 id int
 email_addr text /* PGSCHEMA: RENAME COLUMN email TO email_addr */
);

Another good alternative (IMO) would be just to improve the escape hatch for adding a raw sql migrations to a PGSchema migration. Maybe some sort of lifecycle hooks for additional DDL? eg: a "--before-plan my-manual-migration.sql" option of some sort?

[tianzhou]

The generated plan.json is in a human editable format. The current escape hatch is to edit the migration SQL.

{
  "version": "1.0.0",
  "pgschema_version": "1.0.0",
  "created_at": "1970-01-01T00:00:00Z",
  "source_fingerprint": {
    "hash": "e989216806a1e82a6a01d6d5898b00b5d94d3a5888c6ef481fa5f931f649aab5"
  },
  "groups": [
    {
      "steps": [
        // We can manually change the sql below
        {
          "sql": "ALTER TABLE department\nADD CONSTRAINT department_dept_name_key UNIQUE (dept_name);",
          "type": "table.constraint",
          "operation": "create",
          "path": "public.department.department_dept_name_key"
        },
...
}

[natty-pluz]

The generated plan.json is in a human editable format. The current escape hatch is to edit the migration SQL.

Good:

• It keeps the solution minimal and consistent with pgschema’s design goals (no server-side state).
• It leverages an existing, documented escape hatch (editing the generated plan.json) rather than adding new YAML.

Risks:

• Manually editing generated SQL is error-prone and easy to lose when plans are regenerated.
• Not discoverable or auditable without docs/tests.
• It doesn’t give a first-class, user-friendly way to declare renames.
• For developers that want a declarative intent, this forces into imperative edits of generated SQL.

Options

1. Structured rename steps in plan.json

In addition to SQL blobs, emit optional typed steps. This keeps renames machine- and human-editable and avoids forcing to hand-edit SQL.

{
  "version": "1.0.0",
  "pgschema_version": "1.0.0",
  "created_at": "1970-01-01T00:00:00Z",
  "source_fingerprint": {
    "hash": "e989216806a1e82a6a01d6d5898b00b5d94d3a5888c6ef481fa5f931f649aab5"
  },
  "groups": [
    {
      "steps": [
        {
          "sql": "ALTER TABLE department\nADD CONSTRAINT department_dept_name_key UNIQUE (dept_name);",
          "type": "table.constraint",
          "operation": "create",
          "path": "public.department.department_dept_name_key"
        },

        // Alternative way
        {
          "type": "table.column",
          "operation": "rename",
          "table": "public.users",
          "from": "email",
          "to": "email_address",
        },
      ]
    }
  ]
}

2. Project-wide pgschema.yaml (stateless, extendable)

Place project-level renames/refactors intent in pgschema.yaml at repo root. pgschema plan reads it and rewrites destructive diffs into safe ALTER … RENAME steps at plan time.
Tiny illustrative example:

ignore: 
  schemas: [test] 
  tables: ["tmp_*"] 
  extensions: [postgis]

renames:
  - type: column
    table: public.users
    from: email
    to: email_address

  - type: table
    from: public.archived_orders
    to: public.orders_archive_2025

Benefits: single source of truth, discoverable, extensible (hooks, order, policies). pgschema.yaml may Include .pgschemaignore to avoid multiple configuration files.

[PadenZach]

I'd argue that the existence of a pgschema.yaml inherently introduces state (even if it's outside of a DB), and perhaps more importantly introduces state across two or more different files, I could see this as being much harder to teach and manage for many of my users.

[tianzhou]

There is no plan to introduce pgschema.yaml at this point.

The human-readable plan.json design is intentional (as opposed to Terraform's binary format plan):

1. Provide escape hatch for operations like RENAME or other statements that pgschema doesn't support
2. Readable when combining with GitOps workflow.

[PadenZach]

Yeah, requiring a plan is a sufficient escape hatch for me in prod releases even if it requires slightly more work to configure CICD correctly to support that.

That being said, I'd still like a way to "force include" sql file(s) as plan steps as a native part of the tool, but this sounds like a different issue

[natty-pluz]

What about a small helper command that safely patches plan.json instead of manual edits?

pgschema rename column \
  --table public.users \
  --from email \
  --to email_address \
  --plan ./out/plan.json

[elmeunick9]

Personally I think comments (annotations) in raw SQL files like @PadenZach suggested first is the best idea:

CREATE TABLE my_table (
 id int
 email_addr text /* PGSCHEMA: RENAME OF my_table.email */
);

These are easy to track if you're using SQL files as source of truth. Once in the new version we can remove all such comments automatically via find/replace all with regex from source code. Personally in my lifecycle I have:

db/20251201/schema.sql
db/20251206/schema.sql
db/20251219/schema.sql

And so on, so if the 20251206 version contains a rename comment, I could simply delete it automatically when I create the folder for the 20251219 version. (It would still be in the 20251206 folder though, so if we want to rollback we can).

In the alternative workflow where dump is used (I don't really like it since I want versioning), we could add the comment as a postgre sql comment, e.g:

COMMENT ON COLUMN my_table.email_addr IS 'RENAME OF my_table.email';

This is probably way easier than the syntax comment to implement, will probably get dumped (it is stored in the DB), and actually, doesn't even look that bad if I had to write it manually in my source schema.sql files (so we may as well only use this version). I'm pretty sure it's quite easy too to remove all of this automatically on apply (so next version dump doesn't have it).

Additionally I'd suggest if not already there to add optional warnings when there is any DROP command generated during a plan.