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
- Cliente → API: GET /despesas. O pedido chega na rota. A API ainda não tem os dados: eles estão no banco.
- 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.
- API → Banco: SELECT ... LIMIT 10. O código envia a consulta, já com o filtro da pessoa e a paginação.
- Banco → API: 10 linhas. O banco devolve linhas e colunas, não JSON.
- API → API: Linhas viram JSON. A API transforma cada linha num objeto do contrato e devolve a conexão ao pool.
- 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.pyversionado 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)sA 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ãofalseevita 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.
Veja em que versão o banco está agora.
alembic currentINFO [alembic.runtime.migration] Context impl PostgresqlImpl. INFO [alembic.runtime.migration] Will assume transactional DDL.Liste o histórico de migrações do projeto.
alembic historya1b2c3 -> d4e5f6 (head), adiciona coluna pago <base> -> a1b2c3, cria tabela despesasAplique todas as migrações até a mais recente.
alembic upgrade headINFO [alembic.runtime.migration] Running upgrade -> a1b2c3, cria tabela despesas INFO [alembic.runtime.migration] Running upgrade a1b2c3 -> d4e5f6, adiciona coluna pagoConfirme a versão atual do banco.
alembic currentd4e5f6 (head)Desfaça só a última migração, voltando um passo.
alembic downgrade -1INFO [alembic.runtime.migration] Running downgrade d4e5f6 -> a1b2c3, adiciona coluna pagoAplique de novo até a mais recente, provando que ida e volta funcionam.
alembic upgrade headINFO [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:
COMMITconfirma,ROLLBACKdesfaz. - 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.
- Crie as migrações da tabela de despesas e aplique num banco vazio, do zero até a mais recente
- Rode o downgrade da última migração e aplique de novo, conferindo o esquema nas duas pontas
- Escreva o repositório de despesas usando apenas consultas parametrizadas, e tente o texto
' OR '1'='1na busca para confirmar que nada vaza - 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
- Ligue o log de SQL do driver ou do ORM e verifique se alguma rota sofre de N+1