How to handle field renaming?

[jingsam]

Suppose the remote state is:

create table students (
  id primary key,
  age integer
)

the local state is:

create table students (
  id primary key,
  score integer
)

There are two strategies to migrate the remote state to the desired local state:
1、drop/add (destrcutive)

alter table students drop column age;
alter table students add column integer;

2、rename (non-destrcutive)

alter table students rename column age to score;

In production database, the age and score may be two distinct attributes or the same attributes with different names.

So, my question is how renovate tell these cases and choose the appropriate migrating strategy?

[tyrchen]

Thanks for your feedback. That was missing in the previous implementation see PR #4 for a resolution. So previously it will generate a drop column / add column for the migration plan. With PR #4, it will generate rename for the migration plan. However, it only works only if one field is renamed at a time.

See this test: https://github.com/tyrchen/renovate/blob/master/src/parser/table/column/mod.rs#L257.

And below is my manual test:

➜ renovate schema apply --remote
table public.todos is changed:

1   1    | CREATE TABLE public.todos (
2   2    |     title text NOT NULL,
3        |-    completed boolean,
    3    |+    completed1 boolean,
4   4    |     id bigint DEFAULT nextval('public.todos_id_seq' :: regclass) NOT NULL,
5        |-    CONSTRAINT todos_title_check CHECK (length(title) > 5)
    5    |+    CONSTRAINT todos_title_check1 CHECK (length(title) > 5)
6   6    | )

The following SQLs will be applied:

ALTER TABLE
    ONLY public.todos RENAME COLUMN completed TO completed1;
ALTER TABLE
    ONLY public.todos RENAME CONSTRAINT todos_title_check TO todos_title_check1;
✔ Do you want to perform this update? · yes
Successfully applied migration to postgres://xxxx:[email protected]/todo.
Your repo is updated with the latest schema. See `git diff HEAD~1` for details.

[tyrchen]

Released it with v0.2.23.

[jingsam]

Thanks. In my opinion, database migration is hard in production environment, just like changing engines on a plane while in flight. In prodution, we need to migrate not only schema, but also data. Differing the local state and the remote state is fessible for schema migration, but not for data migration.

For example:

1   1    | CREATE TABLE public.todos (
2   2    |     title text NOT NULL,
3        |-    completed boolean,
    3    |+    completed1 boolean,
4   4    |     id bigint DEFAULT nextval('public.todos_id_seq' :: regclass) NOT NULL,
5        |-    CONSTRAINT todos_title_check CHECK (length(title) > 5)
    5    |+    CONSTRAINT todos_title_check1 CHECK (length(title) > 5)
6   6    | )

In production data migration, sometimes we just simply renaming completed to completed1. In other situations, we need drop completed and create completed1 as they are distinct attributes. Renovate can not tell the difference, only dev or DBA know the right migrating way.