Schema Evolution

Rename Columns

Keep a Compatibility View

When a column is renamed, a view can preserve the old response shape while application code catches up.

Program

Play the script to choose which email domain the compatibility view returns.

email_pattern
rename_with_compat_view.sql
Replay: real traced execution (multi-file project)
CREATE TABLE migration_choice AS WITH params(email_pattern) AS (VALUES ('%.com')) SELECT email_pattern FROM params;
CREATE TABLE customers (id INTEGER, fullname TEXT, email TEXT);
INSERT INTO customers VALUES (1, 'Ada Lovelace', 'ada@example.com'), (2, 'Lin Chen', 'lin@example.org'), (3, 'Mira Shah', 'mira@example.com');
ALTER TABLE customers RENAME COLUMN fullname TO display_name;
CREATE VIEW customer_api AS SELECT id, display_name AS name, email FROM customers;
SELECT id, name, email FROM customer_api WHERE email LIKE (SELECT email_pattern FROM migration_choice) ORDER BY id;

CREATE TABLE migration_choice AS WITH params(email_pattern) AS (VALUES ('%.org')) SELECT email_pattern FROM params;
CREATE TABLE customers (id INTEGER, fullname TEXT, email TEXT);
INSERT INTO customers VALUES (1, 'Ada Lovelace', 'ada@example.com'), (2, 'Lin Chen', 'lin@example.org'), (3, 'Mira Shah', 'mira@example.com');
ALTER TABLE customers RENAME COLUMN fullname TO display_name;
CREATE VIEW customer_api AS SELECT id, display_name AS name, email FROM customers;
SELECT id, name, email FROM customer_api WHERE email LIKE (SELECT email_pattern FROM migration_choice) ORDER BY id;

  1. choice ← 1 row

    1CREATE TABLE migration_choice AS WITH params(email_pattern) AS (VALUES ('%.com')) SELECT email_pattern FROM params;2CREATE TABLE customers (id INTEGER, fullname TEXT, email TEXT);
    values this step1 rowchoice

  2. customers ← 3 rows

    2CREATE TABLE customers (id INTEGER, fullname TEXT, email TEXT);3INSERT INTO customers VALUES (1, 'Ada Lovelace', 'ada@example.com'), (2, 'Lin Chen', 'lin@example.org'), (3, 'Mira Shah', 'mira@example.com');4ALTER TABLE customers RENAME COLUMN fullname TO display_name;
    values this step3 rowscustomers

  3. columns ← 3 rows

    3INSERT INTO customers VALUES (1, 'Ada Lovelace', 'ada@example.com'), (2, 'Lin Chen', 'lin@example.org'), (3, 'Mira Shah', 'mira@example.com');4ALTER TABLE customers RENAME COLUMN fullname TO display_name;5CREATE VIEW customer_api AS SELECT id, display_name AS name, email FROM customers;
    values this step3 rowscolumns

  4. views ← 1 row

    4ALTER TABLE customers RENAME COLUMN fullname TO display_name;5CREATE VIEW customer_api AS SELECT id, display_name AS name, email FROM customers;6SELECT id, name, email FROM customer_api WHERE email LIKE (SELECT email_pattern FROM migration_choice) ORDER BY id;
    values this step1 rowviews

  5. result ← 2 rows

    5CREATE VIEW customer_api AS SELECT id, display_name AS name, email FROM customers;6SELECT id, name, email FROM customer_api WHERE email LIKE (SELECT email_pattern FROM migration_choice) ORDER BY id;
    values this step2 rowsresult

  1. choice ← 1 row

    1CREATE TABLE migration_choice AS WITH params(email_pattern) AS (VALUES ('%.org')) SELECT email_pattern FROM params;2CREATE TABLE customers (id INTEGER, fullname TEXT, email TEXT);
    values this step1 rowchoice

  2. customers ← 3 rows

    2CREATE TABLE customers (id INTEGER, fullname TEXT, email TEXT);3INSERT INTO customers VALUES (1, 'Ada Lovelace', 'ada@example.com'), (2, 'Lin Chen', 'lin@example.org'), (3, 'Mira Shah', 'mira@example.com');4ALTER TABLE customers RENAME COLUMN fullname TO display_name;
    values this step3 rowscustomers

  3. columns ← 3 rows

    3INSERT INTO customers VALUES (1, 'Ada Lovelace', 'ada@example.com'), (2, 'Lin Chen', 'lin@example.org'), (3, 'Mira Shah', 'mira@example.com');4ALTER TABLE customers RENAME COLUMN fullname TO display_name;5CREATE VIEW customer_api AS SELECT id, display_name AS name, email FROM customers;
    values this step3 rowscolumns

  4. views ← 1 row

    4ALTER TABLE customers RENAME COLUMN fullname TO display_name;5CREATE VIEW customer_api AS SELECT id, display_name AS name, email FROM customers;6SELECT id, name, email FROM customer_api WHERE email LIKE (SELECT email_pattern FROM migration_choice) ORDER BY id;
    values this step1 rowviews

  5. result ← 1 row

    5CREATE VIEW customer_api AS SELECT id, display_name AS name, email FROM customers;6SELECT id, name, email FROM customer_api WHERE email LIKE (SELECT email_pattern FROM migration_choice) ORDER BY id;
    values this step1 rowresult
rename column `ALTER TABLE ... RENAME COLUMN` changes the stored column name.
compatibility view `customer_api` exposes `display_name` as `name` for readers that still expect the old shape.
filter The selector changes which compatibility-view rows are returned.