PostgreSQL: Migre uma Relação has many para JSONB

NOTA: Eu digitei direto no psql, crie um arquivo sql se achar mais cômodo.

Eu tenho uma estrutura no meu banco de dados similar a uma relação has many do Rails.

No meu caso um Customer pode ter vários Phones e, claro, um Phone pertence a apenas um Customer.

Com o passar do tempo percebemos que não é necessário ter uma tabela para telefones. Existem vantagens para ambos os casos, não vou discutir isso aqui, mas sinta-se livre para deixar um comentário 😊.

Dados de teste

Para os meus testes eu usei esse SQL para criar a estrutura base e adicionar dados de teste.

create database mexample;
\c mexample;
create table customers (id integer, name text);
create table phones (id integer, number text, customer_id integer);
insert into customers (id, name) values (1, 'Alice');
insert into customers (id, name) values (2, 'Bob');
insert into customers (id, name) values (3, 'Carl');
insert into phones (id, number, customer_id) values (1, 'alice#1', 1);
insert into phones (id, number, customer_id) values (2, 'alice#2', 1);
insert into phones (id, number, customer_id) values (3, 'bob#1', 2);

• Alice tem dois telefones
• Bob tem somente um, e
• Carl não tem nenhum

Migrando

Hora de migrar os dados.

O primeiro passo é adicionar uma coluna jsonb em customers:

alter table customers add column phones jsonb default '[]'::jsonb;

O banco de dados fica assim:

mexample=# select * from customers;
 id | name  | phones
----+-------+--------
  1 | Alice | []
  2 | Bob   | []
  3 | Carl  | []
(3 rows)

Agora a parte que dá mais trabalho:

1. Primeiro, se você tem muitos customers ou muitos phones faça essa migração em batches. Eu recomendo que você também crie uma columa para marcar os customers que você migrou. Normalmente eu começo os batches com 200 e mudo de acordo com a app em produção ou de acordo com o banco de dados
2. Segundo, a própria app (Ruby) expera que phones seja um array vazio [] caso não existam telefones; a app literalmente quebra se não for um array.

O ponto (2) era muito important enquanto faziamos a migração. Se os dados fossem criados usando a própria app não tinha problema nenhum, pois a app garantia a integridade. (Não se preocupe, já corrigimos isso 😉).

Agora o SQL para migrar:

do
$$
declare r record;
begin
  for r in select customers.id from customers
  loop
    update customers set phones = (
select
  to_json(array_agg(p))
from
  customers c
inner join
  phones p on p.customer_id = c.id
where
  p.customer_id = r.id
group by
  p.customer_id
) where customers.id = r.id;
  end loop;
end
$$;

Depois desse SQL o banco de dados ficou assim:

mexample=# select * from customers;
 id | name  |                                                phones
----+-------+------------------------------------------------------------------------------------------------------
  1 | Alice | [{"id": 1, "number": "alice#1", "customer_id": 1}, {"id": 2, "number": "alice#2", "customer_id": 1}]
  2 | Bob   | [{"id": 3, "number": "bob#1", "customer_id": 2}]
  3 | Carl  |
(3 rows)

No meu caso eu tive que rodar isso:

update customers set phones = '[]'::json where phones is null;

Para que o banco de dados ficasse assim:

mexample=# select * from customers;
 id | name  |                                                phones
----+-------+------------------------------------------------------------------------------------------------------
  1 | Alice | [{"id": 1, "number": "alice#1", "customer_id": 1}, {"id": 2, "number": "alice#2", "customer_id": 1}]
  2 | Bob   | [{"id": 3, "number": "bob#1", "customer_id": 2}]
  3 | Carl  | []
(3 rows)

Mais uma vez no meu caso phones tem que ser um array vazio e não null.

Não esqueça de adaptar o SQL para o seu caso:

• Talvez você tenha milhares de linhas no banco de dados em ambas as tabelas
• Talvez seja companies e addresses
• Talvez você tenha uma associação polimórfica