How to auto-grade SQL exercises against a real SQLite database

If you teach SQL, you've probably tried grading by comparing a student's query with your own. It doesn't work, because two correct queries rarely look alike. All three of these answer the same question, and all three pass the grader in this post:

SELECT member, SUM(days) FROM loans GROUP BY member HAVING SUM(days) >= CAST(readfile('input') AS INTEGER);

SELECT member, total FROM (SELECT member, SUM(days) AS total FROM loans GROUP BY member)
WHERE total >= CAST(readfile('input') AS INTEGER);

WITH totals AS (SELECT member, SUM(days) AS total FROM loans GROUP BY member)
SELECT * FROM totals WHERE total >= 0 + readfile('input') ORDER BY total DESC;

What does work is running each query against the same database and comparing the rows it returns. This post builds that grader in about 40 lines of Python. It also covers the traps I hit while testing it: a wrong query that passed my first tests, row order, students who hard-code the answer, and a student who runs DELETE FROM loans.

The exercise

A small library database: which member borrowed which book, and for how many days.

member	book	days
ada	Dune	12
ada	Emma	8
bob	Dune	20
cy	Emma	5
ada	Ulysses	15
bob	Ulysses	3

The question: print each member whose loans add up to at least N days, with that total.

N is the test's input, so one question can check several cases. Each test gives a different N and the rows it expects: N = 30 expects only ada|35, N = 20 expects ada|35 and bob|23, and N = 100 expects nothing.

Build the database with the sqlite3 command-line tool from a script:

-- library.sql
CREATE TABLE loans (member TEXT, book TEXT, days INTEGER);
INSERT INTO loans VALUES
  ('ada', 'Dune', 12),
  ('ada', 'Emma', 8),
  ('bob', 'Dune', 20),
  ('cy', 'Emma', 5),
  ('ada', 'Ulysses', 15),
  ('bob', 'Ulysses', 3);

sqlite3 library.db < library.sql

The grader

Each query has to run somewhere safe, because it's code a student wrote. I send it to a code execution API that runs it in a sandbox with no network access. That's OnlineCompiler.io's run-tests endpoint, which I build. Its sqlite-3 compiler runs the SQLite shell, so the program is the SQL itself, and the student writes only a query.

One request carries everything: the query, the database file, the tests and how to compare. The answer comes back with a verdict for each test.

# grade.py: grade one student's query. Run it in the folder that holds library.db.
import base64
import os
import sys
from pathlib import Path

import requests

query = Path(sys.argv[1]).read_text()  # the student's answer, such as answer.sql

response = requests.post(
    "https://api.onlinecompiler.io/api/run-tests/",
    headers={"Authorization": os.environ["ONLINECOMPILER_API_KEY"]},
    json={
        "compiler": "sqlite-3",
        # Open the database read-only on the line before the student's query.
        "code": ".open --readonly library.db\n" + query,
        "files": [
            {
                "name": "library.db",
                "content": base64.b64encode(Path("library.db").read_bytes()).decode(),
                "encoding": "base64",
            }
        ],
        "checker": "rows",  # same rows in any order
        "tests": [
            {"input": "30\n", "expected_output": "ada|35\n"},
            {"input": "20\n", "expected_output": "bob|23\nada|35\n"},
            {"input": "23\n", "expected_output": "bob|23\nada|35\n"},  # exactly on bob's total
            {"input": "100\n", "expected_output": ""},
        ],
    },
    timeout=60,
)
if not response.ok:
    sys.exit(f"HTTP {response.status_code}: {response.text}")
result = response.json()
print(result["verdict"], result["passed"], "of", result["total"])
for test in result["tests"]:
    first_error_line = (test.get("error") or "").split("\n")[0]
    print(" ", test["verdict"], repr(test.get("output", "")), first_error_line)

How each part works:

• .open --readonly library.db is a SQLite shell command. Your grader puts it in front of the query, so the student never sees it.
• The database travels with the request as base64, since it's a binary file in a JSON body, and every test reads it.
• readfile('input') is how the query reads the test's input in SQLite. The sandbox saves each test's input as a file named input beside the database.
• The SQLite shell prints rows as member|total, one per line, and that's what the tests expect.

Get a free key at onlinecompiler.io, then:

pip install requests
export ONLINECOMPILER_API_KEY=your_api_key
python grade.py answer.sql

With the first query from the top of the post in answer.sql:

accepted 4 of 4
  accepted 'ada|35\n'
  accepted 'ada|35\nbob|23\n'
  accepted 'ada|35\nbob|23\n'
  accepted ''

The other two correct queries give the same four accepted lines. Look at the second and third tests: they expect bob|23 first, the query printed ada|35 first, and they still pass. That's the checker, next.

Trap 1: row order

A query without ORDER BY may return its rows in any order, and a student who writes the query differently shouldn't lose marks for it. That's what "checker": "rows" is for. It compares the lines as an unordered set, so ada|35 then bob|23 matches bob|23 then ada|35. Duplicates still count: a row printed twice must be expected twice. Numbers match within a small tolerance, so 35 and 35.0 are the same.

If the question is about order ("sort by total, highest first"), use "checker": "exact" instead, which compares the output byte for byte.

Trap 2: a wrong query passed my first tests

My first version had only the tests for 30, 20 and 100. Then I tried a query with > where it should say >=:

SELECT member, SUM(days) FROM loans GROUP BY member HAVING SUM(days) > CAST(readfile('input') AS INTEGER);

It passed all three. No test had N equal to a member's total, so > and >= always gave the same rows. That's why the grader above has a fourth test with N = 23, exactly bob's total. Now the same query gets:

wrong_answer 3 of 4
  accepted 'ada|35\n'
  accepted 'ada|35\nbob|23\n'
  wrong_answer 'ada|35\n'
  accepted ''

The lesson applies to any autograder: write at least one test on each boundary the question has. Your reference query passing every test proves the tests agree with your query. It doesn't prove they would catch a wrong one, so try a few wrong queries yourself before giving the exercise to students.

Trap 3: the query that forgot the filter

SELECT member, SUM(days) FROM loans GROUP BY member;

wrong_answer 0 of 4
  wrong_answer 'ada|35\nbob|23\ncy|5\n'
  wrong_answer 'ada|35\nbob|23\ncy|5\n'
  wrong_answer 'ada|35\nbob|23\ncy|5\n'
  wrong_answer 'ada|35\nbob|23\ncy|5\n'

Every test shows what the query printed, so you can show it to the student next to what was expected.

Trap 4: hard-coding the answer

Students who can see a sample test sometimes print its answer:

SELECT 'ada', 35;

wrong_answer 1 of 4
  accepted 'ada|35\n'
  wrong_answer 'ada|35\n'
  wrong_answer 'ada|35\n'
  wrong_answer 'ada|35\n'

It passes the one test whose answer it copied and fails the others. This is where hidden tests come in: show students one sample and grade on several they never see. In run-tests, mark a test "hidden": true and its result carries only the verdict, never its input or output:

{"verdict": "wrong_answer", "hidden": true}

That makes the whole result safe to pass straight to a student.

Trap 5: syntax errors and DELETE FROM loans

A typo comes back as SQLite's own message, which is the clearest thing to show a student:

runtime_error 0 of 4
  runtime_error '' Parse error near line 2: near "SELEC": syntax error
  runtime_error '' Parse error near line 2: near "SELEC": syntax error
  runtime_error '' Parse error near line 2: near "SELEC": syntax error
  runtime_error '' Parse error near line 2: near "SELEC": syntax error

It says line 2 because the grader's .open line is line 1.

Then there's the student who tries to change the data:

DELETE FROM loans;
SELECT member, SUM(days) FROM loans GROUP BY member HAVING SUM(days) >= CAST(readfile('input') AS INTEGER);

runtime_error 0 of 4
  runtime_error '' Runtime error near line 2: attempt to write a readonly database (8)
  runtime_error '' Runtime error near line 2: attempt to write a readonly database (8)
  runtime_error '' Runtime error near line 2: attempt to write a readonly database (8)
  runtime_error '' Runtime error near line 2: attempt to write a readonly database (8)

Because the database is opened read-only, DELETE, INSERT, UPDATE and DROP stop with an error, and the file stays exactly as you sent it. A query can still create a temporary table, which is gone when the run ends.

One thing read-only doesn't do is hide the data. A query can read every table and print it, so keep the expected answers out of the database and only in the tests.

Partial credit

The default scoring is all or nothing: the score is 100 only if every test passes. For coursework, "scoring": "percent" gives the share of tests passed, and it runs every test even after one fails. The > query above, which fails one test of four, scores 75.0 with it.

CSV instead of a database

If your exercise starts from a CSV file, use DuckDB, which reads CSV directly, so there's no database to build. Send the CSV as a file, set "compiler": "duckdb-1.5", and the student queries it with read_csv. DuckDB has no readfile, so the test's input is read with read_text:

SELECT member, sum(days) FROM read_csv('loans.csv') GROUP BY member
HAVING sum(days) >= (SELECT CAST(content AS INTEGER) FROM read_text('input'));

"files": [{"name": "loans.csv", "content": Path("loans.csv").read_text()}],

With tests for N = 23 and N = 100, both pass. DuckDB prints rows the same way as SQLite (ada|35, no header). The rows checker matters even more here, because DuckDB doesn't promise any row order without ORDER BY.

Storing the exercise instead of sending it each time

With run-tests, the database and the expected outputs travel with every request, so your grading script is the only place that holds them. You can also store the exercise once as a problem, with the database as a data file and hidden tests. Then a student's submission is just the query, and every result comes with a signed receipt you can keep for the record. A widget can also put the problem on your course page, so students write and test their query in the browser. The full guide walks through that setup with real responses: How to auto-grade SQL exercises.

Limits, honestly

• This post's grader runs SQLite and DuckDB, on every plan. For questions in PostgreSQL's own dialect, a test suite can start a fresh PostgreSQL server in the sandbox for each student, on Pro and above: How to auto-grade PostgreSQL exercises. There's no MySQL server, so MySQL-only syntax can't be graded.
• Each test counts as one run. The free plan has 100,000 runs a month (no credit card) and 60 a minute. A class of 40 students submitting a four-test exercise is 160 runs. If they all submit in the same minute, some requests get a 429 (not counted), so have your script wait a few seconds and retry.
• A request carries up to 20 files and 1 MB in total. A stored problem takes up to 8 data files, 5 MB each and 10 MB in all.

Disclosure: I build OnlineCompiler.io. The idea itself (same database, compare rows, test the boundaries) works with any sandbox that can run the SQLite shell.