Usando INSERT ... ON CONFLICT (UPSERT) no PostgreSQL com exemplos em Prisma

O que é e por que importa

O padrão UPSERT — inserir ou atualizar quando há conflito — resolve um problema muito comum em aplicações: você quer gravar uma linha, mas se ela já existir você deseja atualizar alguns campos em vez de falhar. No PostgreSQL isso é feito com INSERT ... ON CONFLICT, conhecido informalmente como UPSERT. Para quem está começando, pensar no UPSERT como uma caixa postal ajuda: você entrega uma carta (INSERT); se já existe envelope com o mesmo endereço (conflito), você atualiza o conteúdo ao invés de criar um segundo envelope.

Por que isso importa? Porque evita lógica cliente complexa como 'SELECT antes de INSERT' que vence com condições de corrida (race conditions) e múltiplas consultas que degradam performance e aumentam a chance de erros. Um UPSERT bem usado é atômico, reduz round-trips e delega a reconciliação ao banco.

Este artigo explora o comando SQL no PostgreSQL e mostra como aplicá-lo na prática com Prisma — tanto usando o método nativo do Prisma quanto executando SQL raw quando necessário. O foco é prático: entender quando usar, armadilhas e como escrever consultas robustas já no nível iniciante.

Quando optar por UPSERT

UPSERT é indicado quando a operação deve ser atômica e você tem uma restrição única para detectar duplicidade (ex.: email, cpf, combinação de colunas). Não é solução universal: para mudanças complexas de estado, lógica de negócio pode exigir transações com checagens adicionais.

Vantagens do UPSERT:

• Atomicidade: uma única operação no banco.
• Performance: menos consultas/round-trips.
• Simplicidade no cliente: menos branches de código.

Quando evitar

Evite UPSERT quando precisa de lógica condicional complexa que envolve várias linhas/tabelas ou quando a regra de atualização depende de dados externos. Nesses casos, prefira transações explícitas com SELECT ... FOR UPDATE.

Conceitos fundamentais com analogias

UPSERT explicado com analogia

Pense em uma prateleira de livros (a tabela). Cada posição da prateleira tem uma etiqueta única (a constraint UNIQUE). Inserir um livro numa posição ocupada causa um problema. O UPSERT é a ação de levar um novo livro e, se a posição estiver ocupada, trocar algumas páginas do livro existente sem mover toda a prateleira.

No SQL essa troca é feita com EXCLUDED (o registro que você tentou inserir) e o nome da tabela existente (p.ex. users). A cláusula ON CONFLICT identifica qual índice/constraint aciona a lógica.

Chaves, constraints e conflitos

Para o ON CONFLICT funcionar você precisa de uma restrição única ou índice que defina o conflito. Pode ser uma single-column UNIQUE, PRIMARY KEY, ou um índice composto. Sem isso, o banco não sabe quando considerar um registro como duplicado.

Exemplo prático: se você quer detectar duplicidade por email, garanta que exista um índice UNIQUE(email). Sem esse índice, o INSERT sempre insere e ON CONFLICT não será acionado.

Passo a passo com código comentado

1) Criando a tabela no PostgreSQL

Contexto: criar uma tabela simples de usuários com um índice UNIQUE em email. Código abaixo cria a tabela; depois explico o que acontece.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  views INT DEFAULT 0,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

O que faz: cria a tabela users com uma constraint UNIQUE em email e valores padrão para views e updated_at. O banco agora pode usar ON CONFLICT(email) para detectar duplicatas.

Após executar, o PostgreSQL terá um índice implícito para email que captura conflitos.

2) Exemplo básico de INSERT ... ON CONFLICT

Contexto: inserir um usuário; se já existir (mesmo email), incrementar visualizações e atualizar name e updated_at.

INSERT INTO users (email, name, views)
VALUES ('alice@example.com', 'Alice', 1)
ON CONFLICT (email)
DO UPDATE SET
  views = users.views + EXCLUDED.views,
  name = EXCLUDED.name,
  updated_at = now()
RETURNING *;

O que faz: tenta inserir; se houver conflito em email, atualiza a linha existente usando valores de EXCLUDED (o que você tentou inserir). RETURNING * retorna a linha final resultante.

Depois de rodar isso, se Alice não existia, a linha é criada. Se existia, seus views são somados e name/updated_at atualizados.

3) Inserindo via Prisma (schema e comando)

Contexto: vamos mapear a tabela no Prisma e mostrar duas abordagens: usar prisma.upsert e executar SQL raw para forçar comportamento específico do PostgreSQL.

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  views     Int      @default(0)
  updatedAt DateTime @default(now()) @map("updated_at")
}

O que faz: define o modelo User no schema.prisma. @map é usado para alinhar nome da coluna updated_at no banco. Ao rodar prisma migrate, a tabela equivalente será criada.

Após gerar client, você pode usar prisma.user.upsert ou executar raw SQL para aproveitar opções específicas do PostgreSQL.

Exemplo prático completo

Caso: contador de visualizações de artigo

Problema: cada vez que alguém visualiza um artigo associado a um email (usuário), queremos incrementar um contador. Precisamos evitar duplo registro por email e garantir que a operação seja atômica sob alta concorrência.

Abordagem: usar ON CONFLICT(email) DO UPDATE para somar views. Implementaremos com Prisma em Node.js, mostrando duas formas: prisma.upsert e prisma.$executeRaw pra deixar explícito o SQL.

Script completo em TypeScript usando prisma.upsert

Contexto: este script usa o método upsert do Prisma. É seguro e expressivo; Prisma escolherá a melhor estratégia por trás dos panos.

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

async function incrementViews(email: string, name?: string) {
  const result = await prisma.user.upsert({
    where: { email },
    create: { email, name, views: 1 },
    update: { views: { increment: 1 }, name: name ?? undefined, updatedAt: new Date() },
  })
  return result
}

// Uso
incrementViews('alice@example.com', 'Alice')
  .then(r => console.log('upsert result', r))
  .finally(() => prisma.$disconnect())

O que faz: tenta localizar usuário por email; cria se não existir; atualiza incrementando views se existir. Prisma traduz isso em operações eficientes ou em SQL apropriado.

Depois de rodar, você terá a linha com a contagem atualizada e updatedAt ajustado.

Script usando SQL raw com Prisma

Contexto: às vezes você precisa de controle absoluto sobre o SQL — por exemplo, usar RETURNING, cláusulas WHERE na parte DO UPDATE, ou manipular EXCLUDED. Abaixo usamos prisma.$executeRaw (com template tag) e parâmetros para segurança.

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

async function incViewsRaw(email: string, name: string | null) {
  await prisma.$executeRaw`
    INSERT INTO users (email, name, views)
    VALUES (${email}, ${name}, 1)
    ON CONFLICT (email)
    DO UPDATE SET
      views = users.views + EXCLUDED.views,
      name = EXCLUDED.name,
      updated_at = now();
  `
}

incViewsRaw('alice@example.com', 'Alice')
  .then(() => console.log('raw executed'))
  .finally(() => prisma.$disconnect())

O que faz: executa uma única query SQL no PostgreSQL. É atômico e permite usar recursos avançados do banco (EXCLUDED, RETURNING, WHERE em DO UPDATE).

Resultado: comportamento idêntico ao exemplo SQL direto. Útil quando Prisma abstração não cobre um caso específico ou quando você precisa otimizar uma query.

Erros comuns, armadilhas e dicas práticas

Contexto: detalhes que quebram código na prática. Aqui estão os problemas e como evitá-los.

1) Falta de índice/constraint: ON CONFLICT precisa de UNIQUE ou PRIMARY KEY. Sem isso nada acontece.

2) Condições de corrida: embora ON CONFLICT seja atômico, implementar lógica adicional entre SELECT e INSERT no cliente causa problemas. Prefira sempre um comando único ou transação com LOCK quando necessário.

3) DO NOTHING vs DO UPDATE: se quiser ignorar duplicatas, use DO NOTHING; se quiser reconciliar, use DO UPDATE. Lembre-se que DO UPDATE sempre realiza um write (mesmo que escreva os mesmos valores) — se isso for relevante para triggers ou timestamps, adicione uma cláusula WHERE para evitar writes desnecessários.

Dica: para evitar sobrescrever dados mais recentes, adicione uma condição na cláusula DO UPDATE. Exemplo: ON CONFLICT (...) DO UPDATE SET ... WHERE users.updated_at < EXCLUDED.updated_at

4) Prisma upsert limitações: prisma.upsert é conveniente, mas nem sempre expõe cláusulas WHERE internas do DO UPDATE. Use raw SQL se precisar de condições condicionais complexas no DO UPDATE.

5) Parâmetros e segurança: use prisma.$executeRaw com template tags para evitar SQL injection. Evite prisma.$executeRawUnsafe.

Próximos passos para aprofundar

Performance e índices

Estude como índices influenciam o custo do ON CONFLICT. Índices compostos, partial indexes e INCLUDE columns podem melhorar performance em queries de leitura e reduzir custo na detecção de conflito.

Considere também a seletividade do índice: um índice em campo com muitos valores duplicados pode gerar mais contenção sob alta concorrência.

Transações complexas e isolamento

Quando múltiplas tabelas ou validações são necessárias, combine UPSERT com transações explícitas e SELECT ... FOR UPDATE. Entenda níveis de isolamento e suas implicações em concorrência e consistência.

Explore técnicas como advisory locks para sincronização fina quando o UPSERT por si só não resolve contenção lógica.

Conclusão prática

UPSERT via INSERT ... ON CONFLICT é uma ferramenta essencial no PostgreSQL para reduzir complexidade e aumentar atomicidade em operações de escrita. No contexto do Prisma, você tem duas vias: o método upsert do cliente, mais alto nível, e o SQL raw, que dá controle total. Aprenda a identificar quando cada abordagem é apropriada.

Resumo dos pontos chave:

1. Garanta índices/constraints corretos antes de usar ON CONFLICT.
2. Prefira UPSERT para operações atômicas e simples de reconciliação por chave única.
3. Use prisma.upsert para conveniência; use SQL raw quando precisar de EXCLUDED, RETURNING ou WHERE no DO UPDATE.
4. Adote cláusulas WHERE no DO UPDATE para evitar writes desnecessários e preservar dados mais recentes.

Próximo passo prático: crie uma tabela de teste, rode os exemplos SQL e os scripts Prisma mostrados aqui, e observe comportamento com concorrência simulada (vários processos tentando incrementar a mesma linha). Essa experimentação é a forma mais rápida de fixar os conceitos.

Se precisar, posso fornecer um checklist e scripts para testar concorrência localmente com Docker e um pequeno benchmark em Node.js usando Promise.all.