SQL Schema Deploy
SQL

Migrações de Schema

Uma migração de schema é uma alteração controlada e versionada à estrutura da base de dados. O problema central é que o schema e o código da aplicação evoluem juntos — um ALTER TABLE executado manualmente num servidor de produção não pode ser reproduzido noutro ambiente, não fica registado em Git e não tem rollback definido. Ferramentas como Flyway e Liquibase resolvem isto tratando o schema como código: cada alteração é um ficheiro versionado, aplicado exactamente uma vez, em ordem, em todos os ambientes. O desafio mais difícil não é a ferramenta — é escrever migrações que funcionam em produção sem downtime, enquanto a aplicação antiga ainda está a correr.

O Problema: Schema como Estado Partilhado

-- Sem migrações versionadas, o estado do schema diverge entre ambientes:
--
-- dev (máquina local)          staging                  produção
-- ─────────────────────        ─────────────────────    ─────────────────────
-- utilizador                   utilizador               utilizador
--   id BIGSERIAL                 id BIGSERIAL             id BIGSERIAL
--   email VARCHAR(255)           email VARCHAR(255)       email VARCHAR(255)
--   nome VARCHAR(100)            nome VARCHAR(100)        nome VARCHAR(100)
--   telefone VARCHAR(30)  ←      telefone VARCHAR(30)     ← falta em produção!
--   ultimo_login TIMESTAMPTZ ←   ← falta em staging!
--
-- Resultado:
--   - A aplicação funciona em dev mas crasha em produção
--   - Ninguém sabe exactamente o que está em produção
--   - "ALTER TABLE manual" em produção esquecido de documentar
--
-- Solução: tratar o schema como código
--   - Cada alteração = um ficheiro SQL numerado e imutável
--   - A ferramenta de migrações garante que cada ficheiro é executado
--     exactamente uma vez, na ordem correcta, em todos os ambientes
--   - O estado actual do schema é sempre derivável a partir do Git

Estrutura de Ficheiros de Migração

-- Convenção de nomes Flyway (a mais comum):
-- V{versão}__{descrição}.sql
-- R{descrição}.sql   ← repeatable migrations (views, stored procedures)
--
-- db/migration/
-- ├── V1__create_utilizador.sql
-- ├── V2__create_produto_categoria.sql
-- ├── V3__create_encomenda.sql
-- ├── V4__add_telefone_utilizador.sql
-- ├── V5__add_indices_performance.sql
-- ├── V6__create_auditoria_log.sql
-- └── R__create_views.sql          ← re-executada quando o checksum muda

-- Regra fundamental: ficheiros de migração são IMUTÁVEIS após serem aplicados.
-- Flyway/Liquibase guardam o checksum (MD5) de cada ficheiro.
-- Se o ficheiro for alterado após ser aplicado, a ferramenta lança erro:
-- "ERROR: Found non-empty schema with no schema history table"
-- ou
-- "ERROR: Migration checksum mismatch for migration version 4"
--
-- Para corrigir uma migração já aplicada → criar uma nova migração (V7, V8, ...)
-- Nunca editar um ficheiro já commitado e aplicado em qualquer ambiente


-- ── Tabela de controlo (criada automaticamente pelo Flyway) ──────────────────
-- flyway_schema_history
-- ┌─────────────┬──────────────────────────────┬─────────┬────────────┬─────────┐
-- │ installed_rank│ version │ description        │ checksum│ success    │         │
-- ├─────────────┼──────────────────────────────┼─────────┼────────────┼─────────┤
-- │     1       │  1       │ create utilizador  │ 8f3a... │    true    │         │
-- │     2       │  2       │ create produto...  │ 2c1d... │    true    │         │
-- │     3       │  3       │ create encomenda   │ 9b7e... │    true    │         │
-- │     4       │  4       │ add telefone...    │ 4f2a... │    true    │         │
-- └─────────────┴──────────────────────────────┴─────────┴────────────┴─────────┘


-- ── V1__create_utilizador.sql ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS utilizador (
    id            BIGSERIAL       PRIMARY KEY,
    email         VARCHAR(255)    NOT NULL,
    nome          VARCHAR(100)    NOT NULL,
    password_hash CHAR(60)        NOT NULL,
    activo        BOOLEAN         NOT NULL DEFAULT TRUE,
    criado_em     TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_utilizador_email UNIQUE (email)
);

CREATE INDEX idx_utilizador_email ON utilizador (email);


-- ── V4__add_telefone_utilizador.sql ──────────────────────────────────────────
-- Cada migração deve ser idempotente quando possível (IF NOT EXISTS, IF EXISTS)
ALTER TABLE utilizador
    ADD COLUMN IF NOT EXISTS telefone VARCHAR(30);

COMMENT ON COLUMN utilizador.telefone
    IS 'Número de telefone opcional. Formato livre — validação feita na aplicação.';

Migrações Zero-Downtime

O problema mais difícil em migrações de produção: a aplicação antiga continua a correr enquanto a migração é aplicada. O schema tem de ser compatível com a versão antiga E a versão nova ao mesmo tempo durante o período de deployment. Isto requer o padrão expand-contract (também chamado parallel change). 

-- ═══════════════════════════════════════════════════════════════════════════
-- CENÁRIO: renomear a coluna "nome" para "nome_completo" em utilizador
-- ═══════════════════════════════════════════════════════════════════════════

-- ── ❌ ABORDAGEM PERIGOSA — migração directa ──────────────────────────────────
-- V10__rename_nome_to_nome_completo.sql
ALTER TABLE utilizador RENAME COLUMN nome TO nome_completo;
-- PROBLEMA: a aplicação antiga ainda usa "nome" → crasha imediatamente
-- O rename e o deploy da nova aplicação não são atómicos


-- ── ✅ PADRÃO EXPAND-CONTRACT — 3 fases ────────────────────────────────────────

-- FASE 1: EXPAND — adicionar a nova coluna, manter a antiga (V10)
-- Deploy desta migração enquanto a aplicação antiga ainda está a correr:
-- V10__add_nome_completo.sql
ALTER TABLE utilizador ADD COLUMN nome_completo VARCHAR(100);

-- Sincronizar a nova coluna com a antiga (trigger para manter em sync):
CREATE OR REPLACE FUNCTION sync_nome_completo()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.nome IS NOT NULL AND NEW.nome_completo IS NULL THEN
        NEW.nome_completo := NEW.nome;
    END IF;
    IF NEW.nome_completo IS NOT NULL AND NEW.nome IS NULL THEN
        NEW.nome := NEW.nome_completo;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_nome
BEFORE INSERT OR UPDATE ON utilizador
FOR EACH ROW EXECUTE FUNCTION sync_nome_completo();

-- Preencher linhas existentes:
UPDATE utilizador SET nome_completo = nome WHERE nome_completo IS NULL;

-- Após esta migração: ambas as colunas existem e estão em sync
-- A aplicação antiga usa "nome" → continua a funcionar
-- A nova aplicação pode começar a usar "nome_completo"


-- FASE 2: MIGRATE — deploy da nova versão da aplicação
-- A nova aplicação usa "nome_completo" em vez de "nome"
-- O trigger mantém as duas colunas em sync durante a transição
-- (possível ter múltiplas instâncias a correr em simultâneo durante o deploy)


-- FASE 3: CONTRACT — remover a coluna antiga (V11, numa sprint/deploy futura)
-- V11__remove_nome_coluna.sql
-- Só executar quando TODAS as instâncias da aplicação usam nome_completo:
DROP TRIGGER IF EXISTS trg_sync_nome ON utilizador;
DROP FUNCTION IF EXISTS sync_nome_completo();
ALTER TABLE utilizador DROP COLUMN nome;
ALTER TABLE utilizador ALTER COLUMN nome_completo SET NOT NULL;


-- ═══════════════════════════════════════════════════════════════════════════
-- CENÁRIO: adicionar coluna NOT NULL sem default dinâmico
-- ═══════════════════════════════════════════════════════════════════════════

-- ❌ Perigoso em tabelas grandes (reescreve a tabela inteira + bloqueia):
ALTER TABLE produto ADD COLUMN descricao TEXT NOT NULL DEFAULT '';

-- ✅ Padrão seguro em 3 passos:

-- V12__add_descricao_nullable.sql
ALTER TABLE produto ADD COLUMN IF NOT EXISTS descricao TEXT;
-- Passo 1: adicionar como nullable — instantâneo, sem bloqueio

-- V13__backfill_descricao.sql
-- Preencher em batches para não bloquear a tabela (em produção com milhões de linhas):
DO $$
DECLARE
    batch_size INT := 1000;
    last_id    BIGINT := 0;
    max_id     BIGINT;
BEGIN
    SELECT MAX(id) INTO max_id FROM produto;
    WHILE last_id < max_id LOOP
        UPDATE produto
        SET descricao = ''
        WHERE id > last_id
          AND id <= last_id + batch_size
          AND descricao IS NULL;

        last_id := last_id + batch_size;
        PERFORM pg_sleep(0.01);  -- 10ms de pausa entre batches — reduz contention
    END LOOP;
END;
$$;

-- V14__set_descricao_not_null.sql
-- Passo 3: adicionar a constraint NOT VALID primeiro (não verifica linhas existentes):
ALTER TABLE produto
    ADD CONSTRAINT chk_descricao_not_null
    CHECK (descricao IS NOT NULL) NOT VALID;

-- Validar em background (sem bloquear escritas):
ALTER TABLE produto VALIDATE CONSTRAINT chk_descricao_not_null;

-- Finalmente: set NOT NULL no catálogo (usa a constraint validada — instantâneo):
ALTER TABLE produto ALTER COLUMN descricao SET NOT NULL;
ALTER TABLE produto DROP CONSTRAINT chk_descricao_not_null;

Operações de Alto Risco e Alternativas

-- ── Tabela de referência rápida ───────────────────────────────────────────────
--
-- Operação                          | Risco        | Alternativa Segura
-- ──────────────────────────────────┼──────────────┼──────────────────────────────
-- ADD COLUMN NOT NULL sem DEFAULT   | Bloqueia     | 3 passos: add nullable → backfill → set NOT NULL
-- ADD COLUMN com DEFAULT dinâmico   | Bloqueia     | Add nullable + UPDATE em batches
-- RENAME COLUMN                     | Quebra app   | Expand-contract (nova coluna + trigger + deploy + drop)
-- DROP COLUMN                       | Irreversível | Verificar que nenhuma app usa → DROP
-- ALTER COLUMN TYPE incompatível    | Bloqueia     | Nova coluna + backfill + swap
-- CREATE INDEX (sem CONCURRENTLY)   | Bloqueia     | CREATE INDEX CONCURRENTLY
-- ADD CONSTRAINT sem NOT VALID      | Bloqueia     | ADD CONSTRAINT NOT VALID + VALIDATE separado
-- DROP TABLE                        | Irreversível | Soft delete primeiro → confirmar → DROP
-- TRUNCATE                          | Irreversível | Só em staging/dev ou após backup confirmado


-- ── ADD COLUMN com DEFAULT constante (PostgreSQL 11+) ────────────────────────
-- Desde o PostgreSQL 11, adicionar NOT NULL com DEFAULT constante é instantâneo:
ALTER TABLE produto ADD COLUMN em_destaque BOOLEAN NOT NULL DEFAULT FALSE;
-- O default é guardado no catálogo do sistema — não reescreve as linhas existentes
-- As linhas antigas "vêem" o default virtualmente até serem actualizadas
-- ✅ Seguro em produção sem 3 passos — APENAS com defaults constantes
-- ❌ Não funciona para defaults dinâmicos como NOW() ou nextval()


-- ── RENAME TABLE com zero-downtime ───────────────────────────────────────────
-- Criar a nova tabela, mover dados, criar view com o nome antigo:
CREATE TABLE encomenda_v2 (LIKE encomenda INCLUDING ALL);
INSERT INTO encomenda_v2 SELECT * FROM encomenda;
ALTER TABLE encomenda RENAME TO encomenda_deprecated;
ALTER TABLE encomenda_v2 RENAME TO encomenda;
-- A app continua a funcionar sem alterações de código

-- Ou usar uma view transitória:
ALTER TABLE encomenda RENAME TO pedido;
CREATE VIEW encomenda AS SELECT * FROM pedido;  -- compatibilidade retroactiva

Rollback de Migrações

-- ── Flyway: undo migrations (versão paga) ────────────────────────────────────
-- U{versão}__{descrição}.sql — executado pelo comando flyway undo
-- db/migration/
-- ├── V5__add_indices_performance.sql
-- └── U5__add_indices_performance.sql   ← undo da V5

-- U5__add_indices_performance.sql:
DROP INDEX CONCURRENTLY IF EXISTS idx_encomenda_estado;
DROP INDEX CONCURRENTLY IF EXISTS idx_produto_categoria;


-- ── Flyway community: rollback manual ────────────────────────────────────────
-- Na versão gratuita, não existe undo automático.
-- Padrão: escrever sempre a migração de rollback como uma nova migração (forward-only):

-- V5__add_coluna_risco.sql (migração normal)
ALTER TABLE produto ADD COLUMN margem NUMERIC(5,2);

-- Se correu mal e precisamos de reverter → nova migração:
-- V6__rollback_coluna_risco.sql
ALTER TABLE produto DROP COLUMN IF EXISTS margem;
-- Esta abordagem mantém o histórico completo e nunca altera ficheiros existentes


-- ── Transacções em migrações (PostgreSQL DDL transaccional) ──────────────────
-- No PostgreSQL, DDL é transaccional — uma migração que falha a meio pode ser revertida

-- V7__migration_complexa.sql
BEGIN;

ALTER TABLE utilizador ADD COLUMN preferencias JSONB;
ALTER TABLE utilizador ADD COLUMN notificacoes_email BOOLEAN NOT NULL DEFAULT TRUE;
CREATE INDEX idx_utilizador_preferencias ON utilizador USING GIN (preferencias);

-- Se algo falhar aqui, as alterações acima são revertidas automaticamente
-- A tabela flyway_schema_history não regista esta versão → pode corrigir e re-executar

COMMIT;

-- Flyway executa cada migration dentro de uma transacção por default no PostgreSQL
-- Excepções: CREATE INDEX CONCURRENTLY não pode correr dentro de uma transacção
-- Para esses casos, usar flywaydb.mixed=true ou separar em migrations distintas

Configuração Flyway (Java / Spring Boot)

-- ── application.properties ───────────────────────────────────────────────────
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.flyway.baseline-on-migrate=false
# baseline-on-migrate=true: para bases de dados existentes sem histórico Flyway
# Marca a versão actual como "baseline" e só aplica migrações a partir daí

spring.flyway.validate-on-migrate=true
# Verifica checksums antes de executar — detecta ficheiros alterados

spring.flyway.out-of-order=false
# false: rejeita migrações fora de ordem (seguro para produção)
# true: permite (útil quando múltiplos developers criam migrações em paralelo)

spring.flyway.table=flyway_schema_history
# Nome da tabela de controlo (default: flyway_schema_history)

-- ── Estrutura do projecto ─────────────────────────────────────────────────────
-- src/
-- └── main/
--     └── resources/
--         └── db/
--             └── migration/
--                 ├── V1__init_schema.sql
--                 ├── V2__seed_categorias.sql
--                 └── V3__add_auditoria.sql

-- ── Executar migrações via Maven ──────────────────────────────────────────────
-- mvn flyway:migrate          ← aplica migrações pendentes
-- mvn flyway:info             ← mostra estado de todas as migrações
-- mvn flyway:validate         ← verifica checksums sem aplicar
-- mvn flyway:repair           ← repara entradas falhadas na tabela de histórico

-- ── Liquibase (alternativa ao Flyway) ────────────────────────────────────────
-- Liquibase usa XML/YAML/JSON em vez de SQL puro:
-- db/changelog/db.changelog-master.yaml

-- databaseChangeLog:
--   - changeSet:
--       id: 4-add-telefone-utilizador
--       author: ruben
--       changes:
--         - addColumn:
--             tableName: utilizador
--             columns:
--               - column:
--                   name: telefone
--                   type: varchar(30)
--       rollback:
--         - dropColumn:
--             tableName: utilizador
--             columnName: telefone
--
-- Vantagem do Liquibase: rollback automático definido no mesmo ficheiro
-- Vantagem do Flyway: SQL puro — mais legível e portável entre ferramentas

Checklist Migrações de Schema

Índices & Performance