Como corrigir exercícios de PostgreSQL automaticamente, com pytest e um banco real

Also in EnglishTambién en español

Como funciona a correção no PostgreSQL?

Uma questão de SQL tem uma resposta que dá para conferir: as linhas que a consulta devolve. Para corrigir no dialeto do próprio PostgreSQL, você precisa de um servidor PostgreSQL de verdade, com os mesmos dados para todos os alunos. Aqui fica assim:

1. Um seed seu, db/01-shop.sql, cria as tabelas, preenche e cria um papel que só pode ler.
2. Cada aluno escreve um arquivo por questão: answers/q1.sql, answers/q2.sql e assim por diante.
3. Você envia o seed, os seus testes e as respostas do aluno para POST /api/run-tests/ no pacote python-3.14-tests com "services": ["postgresql"]. Um servidor novo sobe, o seed roda, e então o pytest roda os seus testes.
4. A resposta diz se cada teste passou ou falhou, com a mensagem do pytest, uma nota e um recibo assinado.

Por que não SQLite? Se o seu curso ensina PostgreSQL, as respostas certas usam PostgreSQL: o DISTINCT ON e o date_trunc abaixo não existem no SQLite, então um corretor em SQLite daria essas respostas como erradas. Para questões que o SQLite responde, o guia do SQLite (em inglês) funciona em todos os planos, com problemas armazenados e testes ocultos.

Como escrever o seed?

O exercício usa uma loja pequena: quatro clientes e seis pedidos feitos por três deles. Cada arquivo .sql em db/ roda antes dos testes, em ordem de nome, como o superusuário postgres.

CREATE TABLE customers (
    id serial PRIMARY KEY,
    name text NOT NULL,
    city text NOT NULL
);
CREATE TABLE orders (
    id serial PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers,
    placed_on date NOT NULL,
    amount integer NOT NULL
);
INSERT INTO customers (name, city) VALUES
    ('Asha', 'Pune'), ('Ben', 'Delhi'), ('Chen', 'Pune'), ('Dev', 'Mumbai');
INSERT INTO orders (customer_id, placed_on, amount) VALUES
    (1, '2026-01-05', 120), (2, '2026-01-20', 80), (1, '2026-02-03', 200),
    (3, '2026-02-14', 50), (2, '2026-03-01', 150), (1, '2026-03-09', 30);

-- O papel com que as consultas dos alunos rodam: lê todas as tabelas e não altera nada.
CREATE ROLE student LOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO student;

As duas últimas linhas são as que importam. Dentro do sandbox, postgres é um superusuário sem senha, então uma consulta rodada como postgres poderia mudar qualquer coisa. O papel student lê todas as tabelas e não muda nenhuma, e os testes rodam as consultas dos alunos com esse papel.

O que os alunos escrevem?

Três questões, um arquivo cada. Estas são as respostas certas:

1. Clientes por cidade: cada cidade e quantos clientes moram nela, em qualquer ordem.
   answers/q1.sql Copy

   SELECT city, count(*) FROM customers GROUP BY city;

2. O último pedido de cada cliente: nome, data e valor, para os clientes que fizeram pedidos, em qualquer ordem. O DISTINCT ON fica com a primeira linha de cada cliente segundo o ORDER BY.
   answers/q2.sql Copy

   SELECT DISTINCT ON (c.id) c.name, o.placed_on, o.amount
   FROM customers c
   JOIN orders o ON o.customer_id = c.id
   ORDER BY c.id, o.placed_on DESC;

3. Faturamento mensal com total acumulado: cada mês como YYYY-MM, o faturamento dele e o total até ali, com os meses em ordem. Usa uma função de janela sobre uma agregação.
   answers/q3.sql Copy

   SELECT to_char(date_trunc('month', placed_on), 'YYYY-MM') AS month,
          sum(amount) AS revenue,
          sum(sum(amount)) OVER (ORDER BY date_trunc('month', placed_on)) AS running_total
   FROM orders
   GROUP BY date_trunc('month', placed_on)
   ORDER BY date_trunc('month', placed_on);

Como os testes conferem uma consulta?

Um teste pytest por questão, mais um que pega respostas escritas à mão. O pacote python-3.14-tests inclui o psycopg, e DATABASE_URL aponta para o servidor.

import os
from datetime import date
from pathlib import Path

import psycopg

DB = os.environ["DATABASE_URL"]


def run(question):
    """Roda answers/<question>.sql do aluno como o papel somente leitura student e devolve as linhas
    do último comando."""
    sql = Path(f"answers/{question}.sql").read_text()
    with psycopg.connect(DB, user="student", options="-c statement_timeout=5s") as conn:
        cur = conn.execute(sql)
        while cur.nextset():
            pass
        return cur.fetchall()


def test_q1_customers_per_city():
    # Qualquer ordem das linhas está certa, então compare as linhas ordenadas.
    assert sorted(run("q1")) == [("Delhi", 1), ("Mumbai", 1), ("Pune", 2)]


def test_q2_latest_order_per_customer():
    assert sorted(run("q2")) == [
        ("Asha", date(2026, 3, 9), 30),
        ("Ben", date(2026, 3, 1), 150),
        ("Chen", date(2026, 2, 14), 50),
    ]


def test_q3_monthly_revenue_with_running_total():
    # A questão pede os meses em ordem, então aqui a ordem conta.
    assert run("q3") == [("2026-01", 200, 200), ("2026-02", 250, 450), ("2026-03", 180, 630)]


def test_q3_counts_a_new_order():
    # Pega uma resposta que imprime as linhas esperadas sem ler a tabela.
    with psycopg.connect(DB, autocommit=True) as admin:
        admin.execute("INSERT INTO orders (customer_id, placed_on, amount) VALUES (4, '2026-04-02', 100)")
        try:
            assert run("q3")[-1] == ("2026-04", 100, 730)
        finally:
            admin.execute("DELETE FROM orders WHERE placed_on = '2026-04-02'")

• run() lê o arquivo do aluno, conecta como student com statement_timeout de 5 segundos e devolve as linhas do último comando do arquivo, então uma resposta que monta uma tabela temporária antes ainda funciona.
• Ordem das linhas: as questões 1 e 2 comparam as linhas com sorted(), então qualquer ordem passa. A questão 3 pede os meses em ordem, então as linhas dela são comparadas como vêm.
• Tipos: o psycopg devolve os tipos do PostgreSQL como tipos do Python, então uma coluna date se compara com date(2026, 3, 9) e um count ou sum com um número comum.
• O quarto teste acrescenta um pedido de abril como superusuário, roda a questão 3 de novo e espera a linha de abril, e depois remove o pedido. Uma resposta que só imprime as linhas esperadas não passa nele.

Como corrigir um aluno?

Envie os cinco arquivos com o cliente Python (pip install onlinecompiler), ou como corpo JSON para https://api.onlinecompiler.io/api/run-tests/ com "compiler": "python-3.14-tests", "suite": {"preset": "pytest", "services": ["postgresql"]}, "memory_limit": 1024 e os arquivos. Este aluno, o Ben, esqueceu o DESC na questão 2, então o DISTINCT ON ficou com o primeiro pedido de cada cliente em vez do último:

SELECT DISTINCT ON (c.id) c.name, o.placed_on, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.placed_on;

from pathlib import Path
from onlinecompiler import OnlineCompiler

client = OnlineCompiler()  # lê ONLINECOMPILER_API_KEY
names = ["db/01-shop.sql", "tests/test_answers.py", "answers/q1.sql", "answers/q2.sql", "answers/q3.sql"]
result = client.run_suite(
    "python-3.14-tests",
    [{"name": name, "content": Path(name).read_text()} for name in names],
    preset="pytest",
    services=["postgresql"],
    memory_limit=1024,  # a memória do banco conta na da execução
)
print(result["summary"])  # 3 of 4 tests passed.

A resposta, resumida:

{
  "verdict": "failed",
  "passed": 3, "failed": 1, "total": 4,
  "score": 75.0,
  "tests": [
    {"name": "test_q1_customers_per_city", "status": "passed", "time": 0.017, "message": null},
    {"name": "test_q2_latest_order_per_customer", "status": "failed", "time": 0.015,
     "message": "AssertionError: assert [('Asha', dat..., 2, 14), 50)] == [('Asha', dat..., 2, 14), 50)]\n  \n  At index 0 diff: ('Asha', datetime.date(2026, 1, 5), 120) != ('Asha', datetime.date(2026, 3, 9), 30)\n  Use -v to get more diff"},
    {"name": "test_q3_monthly_revenue_with_running_total", "status": "passed", "time": 0.013, "message": null},
    {"name": "test_q3_counts_a_new_order", "status": "passed", "time": 0.023, "message": null}
  ],
  "run": {"exit_code": 1, "time": 1.1516, "output": "", "error": ".F..    [100%]\n=== FAILURES ===\n..."},
  "pack": {"version": "1.1"},
  "summary": "3 of 4 tests passed."
}

A mensagem do pytest mostra a primeira linha do Ben, o pedido de janeiro da Asha, onde o teste esperava o de março. A execução inteira levou cerca de 1,2 segundos com a subida do servidor e o seed, e as respostas com todas as questões certas voltaram accepted, 4 de 4.

O que outras respostas recebem?

Cada linha é uma execução dos mesmos testes com uma resposta trocada; as outras duas respostas estavam certas.

A resposta da questão 1 com tabela temporária:

CREATE TEMP TABLE c AS SELECT city FROM customers;
SELECT city, count(*) FROM c GROUP BY city;

Como corrigir uma turma inteira?

Deixe o seed e os testes onde estão e as respostas de cada aluno em submissions/<nome>/, como q1.sql, q2.sql e q3.sql. Este script corrige todos os alunos e escreve grades.csv:

import csv
from pathlib import Path
from onlinecompiler import OnlineCompiler

client = OnlineCompiler()  # lê ONLINECOMPILER_API_KEY
ours = [{"name": name, "content": Path(name).read_text()} for name in ["db/01-shop.sql", "tests/test_answers.py"]]

with open("grades.csv", "w", newline="") as f:
    out = csv.writer(f)
    out.writerow(["aluno", "veredito", "passaram", "total", "nota", "testes que falharam"])
    for folder in sorted(Path("submissions").iterdir()):
        answers = [{"name": f"answers/{p.name}", "content": p.read_text()} for p in sorted(folder.glob("*.sql"))]
        r = client.run_suite("python-3.14-tests", ours + answers, preset="pytest", services=["postgresql"], memory_limit=1024)
        failed = [t["name"] for t in r["tests"] if t["status"] != "passed"]
        out.writerow([folder.name, r["verdict"], r["passed"], r["total"], r["score"], "; ".join(failed)])
        print(folder.name, r["verdict"], f"{r['passed']}/{r['total']}")

aluno,veredito,passaram,total,nota,testes que falharam
asha,accepted,4,4,100.0,
ben,failed,3,4,75.0,test_q2_latest_order_per_customer
chen,failed,3,4,75.0,test_q3_counts_a_new_order

Cada aluno é uma execução, com o seu próprio servidor. Para dar pesos diferentes às questões, procure os nomes dos testes numa tabela sua, como mostra a página do corretor de Java (em inglês).

Quais são os limites?

• Plano: Pro e acima, e os planos de provas, com memory_limit de pelo menos 1.024 MB (preços).
• Servidor: PostgreSQL 18.6, 8 conexões ao mesmo tempo, dados em memória que somem quando a execução termina.
• Dados: o seed vai na requisição, até 20 arquivos e 1 MB no total; os dados do servidor dividem os 256 MB do sandbox com os arquivos.
• Tempo: a suíte inteira tem 20 segundos por padrão e até 30, com a subida do servidor e o seed.
• Um seed com erro para a execução antes de qualquer teste, com a mensagem do PostgreSQL e o veredito no_report.

Cada configuração e cada erro estão na referência do run-tests (em inglês).

Dá para escrever os testes em JavaScript?

Sim. O pacote nodejs-24-tests tem os mesmos services, com o pg para a conexão e o Jest, o Mocha ou o node:test para os testes; o seed funciona como está. A página de pytest e Jest (em inglês) corrige assim um app Express com PostgreSQL.

Onde isso entra num curso?

As suítes rodam pelo run-tests (em inglês), então o seu seed e os seus testes ficam no seu sistema e vão junto com cada requisição. Para questões de SQLite em qualquer plano, com problemas armazenados, testes ocultos e um editor no navegador para os alunos, veja o guia do SQLite (em inglês). Para corrigir trabalhos em todas as outras linguagens, veja a API de correção automática. Crie uma chave de API para começar; escolas podem pedir o Pro grátis pelo programa Founding Institutions (em inglês) ou pelo Pro grátis.

Perguntas frequentes

Dá para corrigir consultas PostgreSQL automaticamente, e não só SQLite?

Sim, no Pro e acima. Acrescente "services": ["postgresql"] a uma suíte de testes no pacote python-3.14-tests e um servidor PostgreSQL 18.6 novo sobe no sandbox, com os dados dos seus arquivos .sql em db/. Os seus testes pytest rodam a consulta de cada aluno nele e comparam as linhas, então respostas que usam DISTINCT ON, date_trunc, funções de janela ou qualquer outra parte do dialeto do PostgreSQL são corrigidas como o PostgreSQL as roda. As respostas de um aluno são uma requisição e uma execução.

A consulta de um aluno pode alterar ou apagar os dados?

Não, quando os testes a rodam com um papel que só pode ler. O seed cria o papel student com SELECT em todas as tabelas, e os testes se conectam com esse papel, então um DELETE para com "permission denied for table orders" e um CREATE TABLE com "permission denied for schema public". Uma tabela temporária ainda funciona, o que ajuda respostas feitas em dois passos. Cada aluno também ganha um servidor novo, então nada do que um aluno roda chega a outro.

A ordem das linhas importa?

O seu teste decide. Compare sorted(rows) quando qualquer ordem está certa, e compare as linhas como vêm quando a questão pede ORDER BY. Neste guia, as questões 1 e 2 aceitam qualquer ordem, e a questão 3 pede os meses em ordem.

Como evitar que os alunos escrevam as linhas esperadas direto na resposta?

Acrescente um teste que muda os dados e confere se a resposta muda junto. Aqui o quarto teste insere um pedido de abril como superusuário, roda de novo a consulta do aluno e espera uma linha de abril com o novo total acumulado; uma resposta que só imprime as três linhas esperadas com VALUES passa na questão 3 e falha nesse teste.

O que acontece quando uma consulta nunca termina?

Os testes se conectam com statement_timeout de 5 segundos, então o PostgreSQL cancela a consulta e esse teste falha com "canceling statement due to statement timeout"; os outros testes rodam normalmente. A suíte inteira, com a subida do servidor e o seed, tem 20 segundos por padrão e até 30 com time_limit.

Qual plano eu preciso?

O PostgreSQL numa suíte precisa do Pro ou acima, ou de um plano de provas, e de memory_limit de pelo menos 1.024 MB na requisição, porque a memória do banco conta na da execução. O conjunto de respostas de um aluno conta como uma execução, tenha quantas questões tiver. Escolas podem pedir o Pro grátis pelos programas Founding Institutions e Pro grátis. Em todos os planos, questões de SQLite e DuckDB podem ser corrigidas como mostra o guia do SQLite.

Quantos dados o seed pode ter?

Uma requisição leva até 20 arquivos e 1 MB no total, então o seed é um texto SQL dentro disso. Para tabelas maiores, gere as linhas no próprio seed com generate_series. O servidor guarda os dados nos 256 MB de memória do sandbox, divididos com os arquivos da requisição, dos quais o servidor vazio ocupa uns 31 MB, e aceita 8 conexões ao mesmo tempo.