How to auto-grade PostgreSQL exercises with pytest and a real database

Também em portuguêsTambién en español

How does PostgreSQL grading work?

A SQL question has an answer you can check: the rows a query returns. To grade it in PostgreSQL's own dialect, you need a real PostgreSQL server with the same data for every student. Here that is:

1. A seed file of yours, db/01-shop.sql, creates the tables, fills them and creates a role that can only read.
2. Each student writes one file per question: answers/q1.sql, answers/q2.sql and so on.
3. You send the seed, your tests and the student's answers to POST /api/run-tests/ on the python-3.14-tests pack with "services": ["postgresql"]. A fresh server starts, the seed runs, then pytest runs your tests.
4. The answer names each test as passed or failed, with pytest's message, a score and a signed receipt.

Why not SQLite? If your course teaches PostgreSQL, the right answers use it: DISTINCT ON and date_trunc below don't exist in SQLite, so a SQLite grader would mark them wrong. For questions SQLite can answer, the SQLite guide works on every plan, with stored problems and hidden tests.

How do I write the seed?

The exercise uses a small shop: four customers, and six orders placed by three of them. Every .sql file under db/ runs before the tests, in name order, as the superuser 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);

-- The role students' queries run as: it can read every table and change nothing.
CREATE ROLE student LOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO student;

The last two lines matter. Inside the sandbox, postgres is a superuser with no password, so a query run as postgres could change anything. The role student can read every table and change none, and the tests run the students' queries as that role.

What do the students write?

Three questions, one file each. These are the right answers:

1. Customers per city: each city and how many customers live there, in any order.
   answers/q1.sql Copy

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

2. Each customer's latest order: name, date and amount, for customers who ordered, in any order. DISTINCT ON keeps the first row per customer in the 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. Monthly revenue with a running total: each month as YYYY-MM, its revenue and the total so far, months in order. It uses a window function over an aggregate.
   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);

How do the tests check a query?

One pytest test per question, plus one that catches hard-coded answers. The python-3.14-tests pack includes psycopg, and DATABASE_URL points at the server.

import os
from datetime import date
from pathlib import Path

import psycopg

DB = os.environ["DATABASE_URL"]


def run(question):
    """Runs the student's answers/<question>.sql as the read-only role student and returns the rows of its
    last statement."""
    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():
    # Any row order is right, so compare the rows sorted.
    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():
    # The question asks for the months in order, so the order counts here.
    assert run("q3") == [("2026-01", 200, 200), ("2026-02", 250, 450), ("2026-03", 180, 630)]


def test_q3_counts_a_new_order():
    # Catches an answer that prints the expected rows without reading the table.
    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() reads the student's file, connects as student with a 5-second statement_timeout, and returns the rows of the file's last statement, so an answer that builds a temporary table first still works.
• Row order: questions 1 and 2 compare sorted() rows, so any order passes. Question 3 asks for the months in order, so its rows are compared as they come.
• Types: psycopg returns PostgreSQL's types as Python's, so a date column compares with date(2026, 3, 9) and a count or sum with a plain number.
• The fourth test adds an April order as the superuser, runs question 3 again and expects April's row, then removes the order. An answer that only prints the expected rows can't pass it.

How do I grade one student?

Send the five files with the Python client (pip install onlinecompiler), or as a JSON body to https://api.onlinecompiler.io/api/run-tests/ with "compiler": "python-3.14-tests", "suite": {"preset": "pytest", "services": ["postgresql"]}, "memory_limit": 1024 and the files. This student, Ben, forgot DESC in question 2, so DISTINCT ON kept each customer's first order instead of the latest:

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()  # reads 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,  # the database's memory counts in the run's
)
print(result["summary"])  # 3 of 4 tests passed.

The answer, shortened:

{
  "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."
}

pytest's message shows Ben's first row, Asha's January order, where the test expected her March one. The whole run took about 1.2 seconds with the server's start and the seed, and the answers with every question right came back accepted, 4 of 4.

What do other answers get?

Each row is a run of the same tests with one answer changed; the other two answers were right.

The temporary-table answer to question 1:

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

How do I grade a whole class?

Keep your seed and tests where they are and each student's answers in submissions/<name>/, as q1.sql, q2.sql and q3.sql. This script grades every student and writes grades.csv:

import csv
from pathlib import Path
from onlinecompiler import OnlineCompiler

client = OnlineCompiler()  # reads 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(["student", "verdict", "passed", "total", "score", "failed tests"])
    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']}")

student,verdict,passed,total,score,failed tests
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

Each student is one run, with a server of their own. To give questions different points, look the test names up in a table of your own, as the Java autograder page shows.

What are the limits?

• Plan: Pro and above, and the exam tiers, with memory_limit of at least 1,024 MB (pricing).
• Server: PostgreSQL 18.6, 8 connections at once, data in memory and gone when the run ends.
• Data: the seed travels with the request, up to 20 files and 1 MB in all; the server's data shares the sandbox's 256 MB with the files.
• Time: the whole suite has 20 seconds by default and up to 30, server start and seed included.
• A broken seed stops the run before any test, with PostgreSQL's message and the verdict no_report.

Every setting and error is in the run-tests reference.

Can I write the tests in JavaScript instead?

Yes. The nodejs-24-tests pack has the same services, with pg for the connection and Jest, Mocha or node:test for the tests; the seed works as it is. The pytest and Jest page grades an Express app on PostgreSQL that way.

Where does this fit in a course?

Suites run through run-tests, so your seed and tests stay in your own system and travel with each request. For SQLite questions on any plan, with stored problems, hidden tests and a browser editor for students, see the SQLite guide. For web apps that use a database, see the pytest and Jest autograder, and for grading every other language, the autograder API. Get an API key to start; schools can apply for free Pro through the Founding Institutions program.

Frequently asked questions

Can I auto-grade PostgreSQL queries, not only SQLite?

Yes, on Pro and above. Add "services": ["postgresql"] to a test suite on the python-3.14-tests pack and a fresh PostgreSQL 18.6 server starts in the sandbox, seeded from your .sql files under db/. Your pytest tests run each student's query against it and compare the rows, so answers that use DISTINCT ON, date_trunc, window functions or any other part of PostgreSQL's dialect are graded as PostgreSQL runs them. Each student's answers are one request and one run.

Can a student's query change or delete the data?

Not when the tests run it as a role that may only read. The seed creates the role student with SELECT on every table, and the tests connect as that role, so a DELETE stops with "permission denied for table orders" and a CREATE TABLE with "permission denied for schema public". A temporary table still works, which helps answers built in two steps. Every student also gets a fresh server, so nothing one student runs reaches another.

Does the order of the rows matter?

Your test decides. Compare sorted(rows) when any order is right, and compare the rows as they come when the question asks for ORDER BY. In this guide, question 1 and question 2 accept any order, and question 3 asks for the months in order.

How do I stop students from hard-coding the expected rows?

Add a test that changes the data and checks that the answer changes with it. Here the fourth test inserts an April order as the superuser, runs the student's query again and expects an April row with the new running total; an answer that only prints the three expected rows with VALUES passes question 3 and fails that test.

What happens when a query never finishes?

The tests connect with statement_timeout set to 5 seconds, so PostgreSQL cancels the query and that test fails with "canceling statement due to statement timeout"; the other tests still run. The whole suite, server start and seed included, has 20 seconds by default and up to 30 with time_limit.

Which plan do I need?

PostgreSQL in a suite needs Pro or above, or an exam tier, and a memory_limit of at least 1,024 MB in the request, since the database's memory counts in the run's. A student's whole set of answers counts as one run however many questions it has. Schools can apply for free Pro through the Founding Institutions and free Pro programs. On every plan, SQLite and DuckDB questions can be graded as the SQLite guide shows.

How much data can the seed hold?

A request carries up to 20 files and 1 MB in all, so the seed is SQL text within that. For larger tables, generate the rows in the seed with generate_series. The server keeps its data in the sandbox's 256 MB of memory, shared with the request's files, of which the empty server takes about 31 MB, and allows 8 connections at once.