Acesso a dados e migrações

Ligar a API ao banco com segurança: consultas parametrizadas, repositórios, ORM, transações e migrações versionadas.

Cerca de 16 min · Revisado em

Ao final, você vai conseguir

  • Descrever o caminho de uma requisição da API até o banco e de volta
  • Demonstrar um SQL injection e evitá-lo com consultas parametrizadas
  • Identificar o problema N+1 e resolvê-lo com uma única consulta com JOIN
  • Usar uma transação e desfazê-la com ROLLBACK
  • Aplicar e desfazer migrações versionadas num banco vazio

Uma API sem banco de dados esquece tudo quando é reiniciada: suas despesas sumiriam toda vez que o servidor fosse atualizado. Ligar a API ao banco parece só "rodar um SQL", mas é aí que moram alguns dos problemas mais caros da área: dados vazados por SQL injection, saldos pela metade e um banco de produção que ninguém sabe como chegou no estado atual. Nesta aula você aprende as práticas que evitam cada um deles.

A API conversa com o banco

Nas aulas de SQL você escreveu consultas à mão. Numa API, quem escreve e envia as consultas é o próprio código, a cada requisição. Para isso ele usa um driver: uma biblioteca que sabe falar com aquele banco, como psycopg para PostgreSQL em Python, pg em Node.js ou JDBC em Java.

O caminho de um GET /despesas até o banco e de volta

  1. Cliente → API: GET /despesas. O pedido chega na rota. A API ainda não tem os dados: eles estão no banco.
  2. API → API: Pega conexão do pool. Abrir uma conexão nova custa caro. A API mantém um pool: algumas conexões já abertas, emprestadas a cada requisição e devolvidas no fim.
  3. API → Banco: SELECT ... LIMIT 10. O código envia a consulta, já com o filtro da pessoa e a paginação.
  4. Banco → API: 10 linhas. O banco devolve linhas e colunas, não JSON.
  5. API → API: Linhas viram JSON. A API transforma cada linha num objeto do contrato e devolve a conexão ao pool.
  6. API → Cliente: 200 + JSON. O cliente recebe a resposta no formato combinado. Ele nunca fala direto com o banco.

Conectar e consultar em Python com psycopg (PostgreSQL)

import os
import psycopg

conexao = psycopg.connect(os.environ["DATABASE_URL"])

with conexao.cursor() as cursor:
    cursor.execute(
        "SELECT id, descricao, valor FROM despesas WHERE usuario_id = %s",
        (usuario_id,),
    )
    linhas = cursor.fetchall()

Repare em os.environ["DATABASE_URL"]. O endereço do banco, com usuário e senha, vem de uma variável de ambiente: um valor configurado na máquina onde a API roda, fora do código.

Onde deve ficar a senha do banco de dados de produção?

  • Escrita no código, num arquivo config.py versionado no Git
  • Numa variável de ambiente ou num cofre de segredos, configurada no servidor
  • No frontend, para o navegador conectar direto no banco

Quando o dado vira comando: SQL injection

É exatamente isso que acontece quando o código junta o texto digitado pela pessoa dentro da consulta SQL. Se esse texto tiver aspas e comandos, o banco não sabe onde termina o dado e onde começa a ordem. Esse ataque se chama SQL injection, e há décadas está entre os mais explorados da web.

Nunca faça isso: o texto da busca entra direto na consulta

texto = requisicao["busca"]   # a pessoa digitou:  ' OR '1'='1
sql = f"SELECT * FROM despesas WHERE usuario_id = 1 AND descricao = '{texto}'"

Veja o ataque acontecer e neutralize-o

A consulta abaixo é o que o código acima montou para a Ana (usuario_id = 1). Rode e veja que aparecem despesas de outras pessoas. Depois corrija tratando o texto digitado como dado: dentro de um texto SQL, cada aspa simples precisa ser escrita duas vezes. A busca deve ficar descricao = ''' OR ''1''=''1'. Uma descrição com esse texto estranho não existe, então a resposta certa é não mostrar nada.

-- A Ana digitou na busca:  ' OR '1'='1
-- O código colou o texto dela na consulta e gerou isto:
SELECT id, usuario_id, descricao, valor
FROM despesas
WHERE usuario_id = 1 AND descricao = '' OR '1'='1';
  • Nenhuma despesa de outra pessoa aparece
  • O texto digitado continua na busca, como dado
  • A consulta ainda filtra pela pessoa 1

Dobrar aspas à mão funciona neste exemplo, mas ninguém deve fazer isso no código: é fácil esquecer um caso. A solução de verdade é a consulta parametrizada: você escreve o SQL com marcadores no lugar dos valores e entrega os valores separados. O driver envia os dois por canais diferentes, e o banco nunca confunde dado com comando.

A mesma consulta parametrizada em três linguagens. Só muda o marcador.

Python

cursor.execute(
    "SELECT * FROM despesas WHERE usuario_id = %s AND descricao = %s",
    (usuario_id, texto),
)

JavaScript

await pool.query(
  'SELECT * FROM despesas WHERE usuario_id = $1 AND descricao = $2',
  [usuarioId, texto],
);

Java

PreparedStatement stmt = conexao.prepareStatement(
    "SELECT * FROM despesas WHERE usuario_id = ? AND descricao = ?");
stmt.setLong(1, usuarioId);
stmt.setString(2, texto);

Troque a concatenação por parâmetros

A função montar_busca devolve o SQL e os parâmetros que seriam entregues ao driver (aqui, um driver que usa ? como marcador, como o do SQLite). Reescreva para que o SQL tenha um ? no lugar de cada valor e a tupla parametros leve usuario_id e texto, nessa ordem.

def montar_busca(usuario_id, texto):
    sql = f"SELECT id, descricao, valor FROM despesas WHERE usuario_id = {usuario_id} AND descricao = '{texto}'"
    parametros = ()
    return sql, parametros


sql, parametros = montar_busca(1, "' OR '1'='1")
print(sql)
print(parametros)
  • O SQL tem um marcador ? para cada valor
  • O texto malicioso não aparece dentro do SQL
  • Os valores vão separados, na ordem certa

Repositório, ORM e o problema N+1

Se cada rota escreve SQL do seu jeito, a mesma consulta aparece copiada em dez lugares. Um repositório resolve isso: um módulo com funções como buscar_por_id e listar_do_usuario, que concentra todo o acesso a uma tabela. As rotas pedem dados ao repositório e não sabem que SQL existe.

Um ORM (mapeamento objeto-relacional) vai além: você descreve a tabela como uma classe e ele escreve o SQL por você. Exemplos: SQLAlchemy e Django ORM em Python, Prisma e TypeORM em Node, Hibernate em Java. Ajuda muito, mas o banco continua recebendo SQL, e você precisa saber qual.

A mesma busca escrita com SQL num repositório e com um ORM

Repositório com SQL

def listar_do_usuario(cursor, usuario_id, limite):
    cursor.execute(
        "SELECT id, descricao, valor FROM despesas"
        " WHERE usuario_id = %s ORDER BY id LIMIT %s",
        (usuario_id, limite),
    )
    return cursor.fetchall()

ORM (SQLAlchemy)

def listar_do_usuario(sessao, usuario_id, limite):
    consulta = (
        select(Despesa)
        .where(Despesa.usuario_id == usuario_id)
        .order_by(Despesa.id)
        .limit(limite)
    )
    return sessao.scalars(consulta).all()

SQL que o ORM gera

SELECT despesas.id, despesas.descricao, despesas.valor, despesas.usuario_id
FROM despesas
WHERE despesas.usuario_id = %(usuario_id_1)s
ORDER BY despesas.id
LIMIT %(param_1)s

A armadilha mais famosa dos ORMs é o problema N+1. O código busca 50 despesas numa consulta e, ao mostrar cada uma, acessa despesa.categoria.nome. Sem você perceber, o ORM faz uma consulta extra por despesa para buscar a categoria. A tela fica lenta, e o código parece inocente.

Uma rota lista 50 despesas e, para cada uma, o ORM busca a categoria separadamente. Quantas consultas chegam ao banco?

  • 1
  • 2
  • 51

Troque N+1 consultas por uma só

Hoje o código busca as despesas e depois faz uma consulta por categoria. Escreva uma única consulta com JOIN que traga, para cada despesa, a descrição, o valor e o nome da categoria.

-- Consulta 1: a lista
SELECT id, descricao, valor, categoria_id FROM despesas
-- Depois, uma consulta para CADA despesa, como:
-- SELECT nome FROM categorias WHERE id = 1
-- SELECT nome FROM categorias WHERE id = 2
  • Todas as 6 despesas aparecem
  • Cada linha traz o nome da categoria
  • É uma única consulta, com JOIN

Tudo ou nada: transações

Uma transação agrupa vários comandos para que valham juntos. Você começa com BEGIN, executa os comandos e termina com COMMIT (confirmar tudo) ou ROLLBACK (desfazer tudo). Se o servidor cair antes do COMMIT, o banco descarta o que estava pela metade.

Uma transferência protegida por transação

BEGIN;
UPDATE contas SET saldo = saldo - 50 WHERE nome = 'Ana';
UPDATE contas SET saldo = saldo + 50 WHERE nome = 'Bruno';
COMMIT;

Teste um rollback

Comece uma transação com BEGIN, faça as duas atualizações da transferência de R$ 50 da Ana para o Bruno e mostre os saldos com um SELECT. Agora imagine que o servidor detectou um problema antes de confirmar: desfaça tudo com ROLLBACK e termine com outro SELECT nome, saldo FROM contas; para provar que nada mudou.

UPDATE contas SET saldo = saldo - 50 WHERE nome = 'Ana';
UPDATE contas SET saldo = saldo + 50 WHERE nome = 'Bruno';
SELECT nome, saldo FROM contas;
  • A transferência foi feita dentro de uma transação
  • A transação foi desfeita com ROLLBACK
  • Os saldos voltaram a ser 100 e 20
  • O último SELECT mostra as duas contas

A API faz as duas atualizações da transferência sem transação, e o servidor reinicia logo depois da primeira. Como fica o banco?

  • Ana perdeu R$ 50 e Bruno não recebeu nada
  • O banco desfaz o primeiro comando sozinho
  • O segundo comando roda quando o servidor voltar

Migrações: o histórico do banco

O esquema do banco (tabelas, colunas, índices) muda junto com o código. Uma migração é um arquivo versionado que descreve uma mudança: o upgrade (ou up) aplica, o downgrade (ou down) desfaz. As migrações ficam no Git, numeradas, e uma ferramenta anota no próprio banco qual foi a última aplicada.

Uma migração do Alembic, a ferramenta mais usada com SQLAlchemy

revision = "d4e5f6"
down_revision = "a1b2c3"


def upgrade():
    op.add_column(
        "despesas",
        sa.Column("pago", sa.Boolean(), nullable=False, server_default=sa.false()),
    )


def downgrade():
    op.drop_column("despesas", "pago")
  • Posição no histórico: Cada migração tem um identificador e aponta para a anterior. É isso que garante que elas rodem sempre na mesma ordem, em qualquer máquina.
  • Upgrade: O que esta versão muda: acrescenta a coluna pago. O valor padrão false evita erro nas linhas que já existem, que não teriam valor para uma coluna obrigatória.
  • Downgrade: Como desfazer. Precisa deixar o banco exatamente como estava antes do upgrade. Atenção: aqui, desfazer apaga a informação de quais despesas foram pagas.

A mesma migração em outras ferramentas populares

Knex (Node.js)

exports.up = (knex) =>
  knex.schema.alterTable('despesas', (t) => {
    t.boolean('pago').notNullable().defaultTo(false);
  });

exports.down = (knex) =>
  knex.schema.alterTable('despesas', (t) => {
    t.dropColumn('pago');
  });

Flyway (Java, SQL puro)

-- arquivo V2__adiciona_coluna_pago.sql
ALTER TABLE despesas ADD COLUMN pago BOOLEAN NOT NULL DEFAULT false;

Aplicando migrações num banco vazio

O projeto da API já tem duas migrações: uma cria a tabela despesas, a outra acrescenta a coluna pago. O banco local acabou de ser criado e está vazio. Use o Alembic para inspecionar, aplicar e desfazer.

  1. Veja em que versão o banco está agora.

    alembic current
    INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
    INFO  [alembic.runtime.migration] Will assume transactional DDL.
  2. Liste o histórico de migrações do projeto.

    alembic history
    a1b2c3 -> d4e5f6 (head), adiciona coluna pago
    <base> -> a1b2c3, cria tabela despesas
  3. Aplique todas as migrações até a mais recente.

    alembic upgrade head
    INFO  [alembic.runtime.migration] Running upgrade  -> a1b2c3, cria tabela despesas
    INFO  [alembic.runtime.migration] Running upgrade a1b2c3 -> d4e5f6, adiciona coluna pago
  4. Confirme a versão atual do banco.

    alembic current
    d4e5f6 (head)
  5. Desfaça só a última migração, voltando um passo.

    alembic downgrade -1
    INFO  [alembic.runtime.migration] Running downgrade d4e5f6 -> a1b2c3, adiciona coluna pago
  6. Aplique de novo até a mais recente, provando que ida e volta funcionam.

    alembic upgrade head
    INFO  [alembic.runtime.migration] Running upgrade a1b2c3 -> d4e5f6, adiciona coluna pago

Você precisa acrescentar uma coluna. Coloque em ordem o caminho seguro.

  • Gerar um arquivo de migração novo
  • Escrever o upgrade e o downgrade
  • Aplicar no banco local e rodar os testes
  • Testar o downgrade e aplicar de novo
  • Fazer commit da migração junto com o código que usa a coluna
  • Deixar o processo de deploy aplicar a migração em produção

Fechamento

  • A API fala com o banco por um driver, reaproveitando conexões de um pool, e lê a senha de uma variável de ambiente.
  • Juntar texto digitado no SQL abre espaço para SQL injection; consultas parametrizadas enviam dado e comando separados.
  • Um repositório concentra o acesso aos dados, e um ORM escreve o SQL por você, mas cuidado com o problema N+1.
  • Transações fazem vários comandos valerem juntos: COMMIT confirma, ROLLBACK desfaz.
  • Migrações versionadas, com upgrade e downgrade, contam a história do esquema e reconstroem o banco em qualquer ambiente.

Ligue a API de despesas a um banco de verdade

Use PostgreSQL (ou SQLite, se for mais fácil no seu computador) com a ferramenta de migração da sua stack: Alembic, Knex, Prisma, Flyway ou outra. O objetivo é provar, com comandos, que o banco é reproduzível e que os dados estão protegidos.

  1. Crie as migrações da tabela de despesas e aplique num banco vazio, do zero até a mais recente
  2. Rode o downgrade da última migração e aplique de novo, conferindo o esquema nas duas pontas
  3. Escreva o repositório de despesas usando apenas consultas parametrizadas, e tente o texto ' OR '1'='1 na busca para confirmar que nada vaza
  4. Faça uma operação com dois comandos dentro de uma transação, force um erro no meio e confirme que o rollback desfez tudo
  5. Ligue o log de SQL do driver ou do ORM e verifique se alguma rota sofre de N+1

Referências

Cursos relacionados