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.

rename_with_compat_view.sql 
CREATE TABLE migration_choice AS WITH params(email_pattern) AS (VALUES ()) 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 ()) 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;
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.