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:
- Primeiro, se você tem muitos
customers
ou muitosphones
faça essa migração em batches. Eu recomendo que você também crie uma columa para marcar oscustomers
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 - 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
eaddresses
- Talvez você tenha uma associação polimórfica