Transactions com PostgreSQL e Node.js
As transações são fundamentais para garantir a integridade dos dados em operações críticas. No PostgreSQL com Node.js (usando a biblioteca pg), uma transação agrupa múltiplas queries em uma unidade atômica: ou todas executam com sucesso ou nenhuma é confirmada. Isso é especialmente importante em cenários como transferências bancárias ou atualizações de inventário.
const { Pool } = require('pg');
const pool = new Pool({
connectionString: 'postgresql://user:password@localhost:5432/mydb'
});
async function transferirFundos(deContaId, paraContaId, valor) {
const client = await pool.connect();
try {
await client.query('BEGIN');
// Debita da primeira conta
await client.query(
'UPDATE contas SET saldo = saldo - $1 WHERE id = $2',
[valor, deContaId]
);
// Credita na segunda conta
await client.query(
'UPDATE contas SET saldo = saldo + $1 WHERE id = $2',
[valor, paraContaId]
);
await client.query('COMMIT');
console.log('Transferência realizada com sucesso');
} catch (erro) {
await client.query('ROLLBACK');
console.error('Transação revertida:', erro);
throw erro;
} finally {
client.release();
}
}
O padrão BEGIN, operações, COMMIT ou ROLLBACK é essencial. Se qualquer query falhar, o ROLLBACK desfaz tudo, mantendo consistência. Use níveis de isolamento apropriados (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) conforme sua necessidade de segurança versus performance.
CTEs (Common Table Expressions): Queries Complexas Simplificadas
As CTEs, também chamadas de "WITH clauses", permitem criar queries reutilizáveis e legíveis. São especialmente úteis para quebrar lógica complexa em partes menores e hierárquicas. No Node.js, você escreve a CTE em SQL puro e reutiliza facilmente.
async function obterVendedoresTopComDetalhes(mesano) {
const query = `
WITH vendedor_totais AS (
SELECT
vendedor_id,
SUM(valor) as total_vendas,
COUNT(*) as qtd_vendas
FROM pedidos
WHERE DATE_TRUNC('month', data_pedido) = $1::date
GROUP BY vendedor_id
),
top_vendedores AS (
SELECT *
FROM vendedor_totais
ORDER BY total_vendas DESC
LIMIT 5
)
SELECT
v.id,
v.nome,
tv.total_vendas,
tv.qtd_vendas,
ROUND(tv.total_vendas / tv.qtd_vendas::numeric, 2) as ticket_medio
FROM top_vendedores tv
JOIN vendedores v ON v.id = tv.vendedor_id
ORDER BY tv.total_vendas DESC
`;
const result = await pool.query(query, [`${mesano}-01`]);
return result.rows;
}
// Uso
obterVendedoresTopComDetalhes('2024-01').then(console.log);
CTEs recursivas também são poderosas para estruturas hierárquicas (árvores organizacionais, categorias aninhadas). A vantagem é clareza: você lê a query de cima para baixo, entendendo cada etapa do processamento antes de aplicar a lógica final.
Window Functions: Análise de Dados Sem Agregação
Window functions realizam cálculos sobre um conjunto de linhas relacionadas, mantendo cada linha original no resultado. Diferem de agregações comuns porque não colapsam grupos. São ideais para rankings, totais acumulados, mudanças percentuais e comparações ano-a-ano.
async function obterVendasComRanking() {
const query = `
SELECT
data_venda,
vendedor_id,
valor,
RANK() OVER (PARTITION BY DATE(data_venda) ORDER BY valor DESC) as ranking_diario,
SUM(valor) OVER (
PARTITION BY vendedor_id
ORDER BY data_venda
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as total_acumulado,
LAG(valor) OVER (PARTITION BY vendedor_id ORDER BY data_venda) as venda_anterior,
ROUND(
((valor - LAG(valor) OVER (PARTITION BY vendedor_id ORDER BY data_venda))
/ LAG(valor) OVER (PARTITION BY vendedor_id ORDER BY data_venda) * 100)::numeric,
2
) as variacao_percentual,
PERCENT_RANK() OVER (ORDER BY valor) as percentil_valor
FROM vendas
WHERE data_venda >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY data_venda DESC, ranking_diario ASC;
`;
const result = await pool.query(query);
return result.rows;
}
// Retorna rankings, totais acumulados, variações — tudo em uma passada
obterVendasComRanking().then(dados => {
dados.forEach(row => {
console.log(`${row.data_venda}: Vendedor ${row.vendedor_id} - Valor: ${row.valor}, Ranking: ${row.ranking_diario}, Acumulado: ${row.total_acumulado}`);
});
});
As cláusulas principais são: PARTITION BY (agrupa logicamente), ORDER BY (ordena dentro da partição), ROWS/RANGE (define o frame). RANK() gera ranking com empates, ROW_NUMBER() enumera, LAG()/LEAD() acessa valores de linhas anteriores/posteriores, e funções como SUM() acumulam dentro do frame definido.
Combinando Tudo: Um Exemplo Real
async function relatorioVendasCompleto() {
const query = `
WITH mes_atual AS (
SELECT
vendedor_id,
SUM(valor) as total,
COUNT(*) as qtd,
DATE_TRUNC('month', data_venda)::date as mes
FROM vendas
WHERE DATE_TRUNC('month', data_venda) = DATE_TRUNC('month', CURRENT_DATE)
GROUP BY vendedor_id, DATE_TRUNC('month', data_venda)
),
mes_anterior AS (
SELECT
vendedor_id,
SUM(valor) as total_ant
FROM vendas
WHERE DATE_TRUNC('month', data_venda) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
GROUP BY vendedor_id
)
SELECT
ma.vendedor_id,
v.nome,
ma.total,
ma.qtd,
COALESCE(mp.total_ant, 0) as total_mes_anterior,
ROUND(((ma.total - COALESCE(mp.total_ant, 0)) / COALESCE(mp.total_ant, 1) * 100)::numeric, 2) as crescimento_percentual,
RANK() OVER (ORDER BY ma.total DESC) as ranking_geral,
ROUND(ma.total / SUM(ma.total) OVER () * 100, 2) as percentual_total
FROM mes_atual ma
JOIN mes_anterior mp ON ma.vendedor_id = mp.vendedor_id
JOIN vendedores v ON v.id = ma.vendedor_id
ORDER BY ranking_geral;
`;
const client = await pool.connect();
try {
await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
const result = await client.query(query);
await client.query('COMMIT');
return result.rows;
} catch (erro) {
await client.query('ROLLBACK');
throw erro;
} finally {
client.release();
}
}
Este exemplo combina CTEs para encapsular lógica de períodos, window functions para rankings e percentuais, e transações para garantir consistência.
Conclusão
Dominando transações, você garante integridade em operações críticas. As CTEs tornam suas queries legíveis e reutilizáveis, facilitando manutenção e debug. As window functions permitem análises sofisticadas mantendo contexto de linhas individuais. Estes três pilares formam a base do PostgreSQL avançado; pratique-os em cenários reais como relatórios financeiros, dashboards e processamentos em lote. Lembre-se: código claro é código que escala.