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

How does automatic SQL grading work?

A SQL question has an answer you can check: the rows a query returns. To grade it you need a database the same for every student, a way to run their query against it, and a rule for comparing the result with what you expect. Here that is:

1. You keep one SQLite database file with the question, as a data file of a stored problem.
2. Each test gives the query a small input, such as a number, so one question can check several cases. Each test also has the rows you expect.
3. The sandbox runs the student's query with the database in its working directory, prints the rows one per line as a|b, and the checker compares them.
4. The result says accepted or wrong_answer for each test, with a score, and a signed receipt you can keep.

The program the sqlite-3 compiler runs is the SQL itself: the student writes a query, not a Python script around it. For CSV files and for queries that read their data without a database, use duckdb-1.5 (see the DuckDB section).

How do I build the database and the problem?

This guide uses a small library: a table of books each member borrowed and for how many days.

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

1. Write the database as a script and build the file with the sqlite3 command-line tool (any SQLite tool works):
   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);

   Terminal

   sqlite3 library.db < library.sql

2. Describe the problem. The drivers entry is a wrapper that opens the database read-only on the line before the student's query (##CODE##), so the student writes only the query. The reference_solution is yours; it must pass every test before the problem is published. Mark the first test hidden: false as the sample students see; the others are hidden unless you say otherwise.
   active-borrowers.json

   {
     "title": "Active borrowers",
     "slug": "active-borrowers",
     "statement": "library.db is a SQLite database with a table loans(member TEXT, book TEXT, days INTEGER). Write one query that prints each member whose loans add up to at least the number in the input, with that total, as member|total. Read the input with readfile('input').",
     "languages": [
       "sqlite-3"
     ],
     "checker": "rows",
     "tests": [
       {
         "name": "sample",
         "input": "30\n",
         "expected_output": "ada|35\n",
         "hidden": false
       },
       {
         "input": "20\n",
         "expected_output": "ada|35\nbob|23\n"
       },
       {
         "input": "100\n",
         "expected_output": ""
       }
     ],
     "drivers": {
       "sqlite-3": ".open --readonly library.db\n##CODE##\n"
     },
     "reference_solution": {
       "compiler": "sqlite-3",
       "code": "SELECT member, SUM(days) FROM loans GROUP BY member HAVING SUM(days) >= CAST(readfile('input') AS INTEGER);"
     }
   }

3. Get an API key (the Free plan has 100,000 runs a month and room for 25 stored problems). Create the problem, upload the database as the raw body, and publish:
   Terminal

   # 1. Create the problem from active-borrowers.json
   curl -X POST https://api.onlinecompiler.io/api/problems/ \
     -H "Authorization: YOUR_API_KEY" \
     -H "Content-Type: application/json" \
     -d @active-borrowers.json
   
   # 2. Add the database to the draft, as the raw body
   curl -X PUT https://api.onlinecompiler.io/api/problems/active-borrowers/draft/files/library.db/ \
     -H "Authorization: YOUR_API_KEY" \
     -H "Content-Type: application/octet-stream" \
     --data-binary @library.db
   
   # 3. Publish: the reference solution runs with library.db before the version goes live
   curl -X POST https://api.onlinecompiler.io/api/problems/active-borrowers/publish/ \
     -H "Authorization: YOUR_API_KEY"

Publishing runs your reference solution against the version first, so a wrong expected output or a missing file is caught before a student sees the problem. The answer is 202 with the check queued, and the version goes live when the check passes:

{
  "problem": "prob_2MfgqHmHY6ww5irWsJRAISin",
  "version": 1,
  "state": "checking",
  "check": "sub_Qk7cJTUNc8jon2DXhZcmsWfo"
}

The stored problems docs cover versions, drivers, checkers, limits and the dashboard's problem editor, where you can also upload the file instead of using curl.

What does a student's query get back?

Send the student's query to the problem with POST /api/problems/active-borrowers/submissions/. The response is the verdict for the whole submission and for each test. Add an external_ref of your own, such as a student id, to find the submission again later.

curl -X POST https://api.onlinecompiler.io/api/problems/active-borrowers/submissions/ \
  -H "Authorization: YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "compiler": "sqlite-3",
    "code": "SELECT member, SUM(days) FROM loans GROUP BY member HAVING SUM(days) >= CAST(readfile('\''input'\'') AS INTEGER);",
    "external_ref": "student-17"
  }'

This is the reference query, so every test passes (shortened: the signed receipt and timings are left out). A hidden test shows only its verdict, never its input or output:

{
  "verdict": "accepted",
  "score": 100,
  "passed": 3,
  "total": 3,
  "tests": [
    {"verdict": "accepted", "hidden": false, "output": "ada|35\n", "error": "", "exit_code": 0},
    {"verdict": "accepted", "hidden": true},
    {"verdict": "accepted", "hidden": true}
  ],
  "summary": "1 of 1 sample test passed. 2 of 2 hidden tests passed.",
  "submission": "sub_rXeOyWC0LAzCbeM6cN8WrT5t"
}

Here is what other queries get, each sent to the same problem:

The wrong query's full response (shortened), as an example of what you show a student:

{
  "verdict": "wrong_answer",
  "score": 0,
  "passed": 0,
  "total": 3,
  "tests": [
    {"verdict": "wrong_answer", "hidden": false, "output": "ada|35\nbob|23\ncy|5\n", "error": "", "exit_code": 0},
    {"verdict": "skipped", "hidden": true},
    {"verdict": "skipped", "hidden": true}
  ],
  "summary": "0 of 1 sample test passed. Sample 1 printed a wrong answer. 0 of 2 hidden tests passed. 2 tests did not run."
}

Can a query return the rows in any order?

Yes, with "checker": "rows", which the problem above uses. A SQL query without ORDER BY may print the same rows in any order, and a student who writes GROUP BY differently should not lose points for it. The rows checker trims the output and compares the lines as an unordered set. A line printed twice must be expected twice, so duplicate rows still count. Numbers match within a tolerance, and every other field must match as text. The checker list has the exact rules.

The expected output ada|35\nbob|23\n accepts a query that prints bob|23 first. If the question is about the order itself (ORDER BY days DESC), use the exact checker so the order counts.

What happens when a query tries to change the data?

It is refused. Each test gets its own read-only copy of the database, and the driver opens it with .open --readonly. A DELETE, INSERT, UPDATE, DROP or CREATE TABLE on the database stops with an error, the program exits with code 1, and the next test starts again from the file you uploaded. This is the response to DELETE FROM loans; placed before the right query (shortened):

{
  "verdict": "runtime_error",
  "score": 0,
  "passed": 0,
  "total": 3,
  "tests": [
    {
      "verdict": "runtime_error",
      "hidden": false,
      "output": "",
      "error": "Runtime error near line 2: attempt to write a readonly database (8)\n",
      "exit_code": 1
    },
    {"verdict": "skipped", "hidden": true},
    {"verdict": "skipped", "hidden": true}
  ],
  "summary": "0 of 1 sample test passed. Sample 1 stopped with a runtime error (exit code 1). 0 of 2 hidden tests passed. 2 tests did not run."
}

Two limits to know about. A query can still create a temporary table, or a database of its own inside the run, because the sandbox lets a program write new files; those are gone when the run ends. And the database is not secret: a student's query can read every table and print it, so put the data the question needs in the database, and the expected rows only in the tests. Hidden tests keep their inputs and outputs from the student.

How do I grade a CSV-based exercise?

Use duckdb-1.5. DuckDB reads CSV files directly, so there is no database to build: send the CSV with the run and the student queries it with read_csv('loans.csv'). The program is the SQL, like sqlite-3, and DuckDB prints the rows as a|b without a header. DuckDB has no readfile; a test's input is read with read_text('input'). A stored problem takes the CSV as a data file in the same way. This request has no stored problem:

curl -X POST https://api.onlinecompiler.io/api/run-tests/ \
  -H "Authorization: YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "compiler": "duckdb-1.5",
    "code": "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": "member,book,days\nada,Dune,12\nada,Emma,8\nbob,Dune,20\ncy,Emma,5\nada,Ulysses,15\nbob,Ulysses,3\n"
      }
    ],
    "tests": [
      {
        "input": "20\n",
        "expected_output": "bob|23\nada|35\n"
      },
      {
        "input": "100\n",
        "expected_output": ""
      }
    ],
    "checker": "rows"
  }'

Test 1 (input 20): accepted, printed ada|35 and bob|23 (DuckDB may print them in either order)
Test 2 (input 100): accepted, printed nothing

Both tests are accepted, and the first would be accepted in either row order, which matters here because DuckDB does not promise an order without ORDER BY. The read-only behaviour in the previous section was checked for SQLite only; for DuckDB, treat the data file as something the student's query can read, and do not rely on it being protected from writes.

Can I grade without a stored problem?

Yes. run-tests takes the program, the files and the tests in one request, so a script of yours can grade one student's query and keep everything else in your own system. It has no driver, so your script puts the .open --readonly library.db line in front of the student's query itself, and sends the database as a file. A binary file in a JSON body goes in as base64 text with "encoding": "base64".

# grade.py: grades one student's query with run-tests. Run it in the folder that holds library.db.
import base64
from pathlib import Path

import requests

query = Path("answer.sql").read_text()  # what the student wrote

response = requests.post(
    "https://api.onlinecompiler.io/api/run-tests/",
    headers={"Authorization": "YOUR_API_KEY"},
    json={
        "compiler": "sqlite-3",
        # Your grader adds the line that opens the database read-only; the student writes only the 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",
        "tests": [
            {"input": "30\n", "expected_output": "ada|35\n"},
            {"input": "20\n", "expected_output": "bob|23\nada|35\n"},
            {"input": "100\n", "expected_output": ""},
        ],
    },
    timeout=60,
)
if not response.ok:
    raise SystemExit(f"HTTP {response.status_code}: {response.text}")
result = response.json()
print(result["verdict"], result["passed"], "of", result["total"])
for test in result["tests"]:
    print(test["verdict"], repr(test.get("output", "")))

With the reference query as answer.sql:

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

With SELECT member, SUM(days) FROM loans GROUP BY member; in answer.sql, run-tests stops starting tests once one fails, and marks the tests it did not start skipped:

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

The difference from a stored problem: here the expected outputs and the database travel with every request, so a grading script on your server is the only place that holds them. A stored problem keeps them on the service, adds hidden tests, versions and signed receipts, and can be opened by a widget on your course page, so students run their query in a browser editor.

Where does this fit in a course?

If you use Moodle, the CodeRunner page covers the hosted Jobe server and question packs. If you build your own course site, the autograder API page and how to build an online judge show the whole loop around a request like the ones above. For plain test cases without SQL, see online compiler with test cases.

Frequently asked questions

Can a student see the answers in the database?

A student's query can read every table in the database and print it, so the database must hold the data the question asks about and never its answers. Keep the expected outputs in the tests. Mark the tests that check the real work hidden: a hidden test shows a student only its verdict, never its input or output. The public view of a problem and the widget list no data files, so describe the tables in the statement.

Does the order of the rows matter?

Not with the rows checker. It trims the output and compares the lines as an unordered set, so a query that returns the same rows in another order is accepted, and a line printed twice has to be expected twice. A line with |, a comma or a tab is split into fields, and numbers match within a tolerance as in the float checker. If your question asks for ORDER BY, use the exact checker instead, which compares the output byte for byte.

Can a student change or delete data in the database?

No. Each test gets its own read-only copy of the file, and the driver opens it with .open --readonly. A DELETE, INSERT, UPDATE or DROP stops with "attempt to write a readonly database (8)" and exit code 1, and the next test starts from the original file. A query can still create a temporary table or a database of its own inside the run.

What are the file limits?

A stored problem's version holds up to 8 data files, each up to 5 MB and 10 MB together. A run-tests request without a stored problem takes up to 20 files and 1 MB in total. A binary database sent in a JSON body is base64 text with "encoding": "base64".

Which SQL databases can I grade against?

The sqlite-3 compiler runs the SQLite command-line shell, so exercises are in SQLite SQL against a SQLite file. The duckdb-1.5 compiler runs DuckDB, which reads CSV and other data files directly and also takes a DuckDB database file. There is no MySQL or PostgreSQL server in the sandbox, so questions that depend on their dialects cannot be graded here.