Skip to content

SQL Testing

SQL Testing is done with the Bats Test (Active) Test type. Generally, SQL tests compare output from the learner's SQL queries with output from a canonical, correct query. This ensures that different query structures with the same result can still be correctly passed.

SQL Workspace Structure

There are two SQL workspaces: SQLite (SQL in Author) and PostgreSQL.

SQLite

Learner code is run on a database file in the workspace itself. When a learner runs a query in a SQLite workspace, four things happen:

  1. The query is run on the database file db.sqlite
  2. The database resulting from the query is stored in output.sqlite. For example, if the learner is INSERTing a new row to a table, db.sqlite will be unchanged while output.sqlite will be a copy of db.sqlite with the additional row INSERTed
  3. The results of the query (e.g. a set of rows returned from SELECT) are written to output.txt
  4. The current checkpoint test is run

PostgreSQL

Learner code is run behind-the-scenes in a PostgreSQL schema named cc_user. When a learner runs a PostgreSQL query, four things happen:

  1. The file db.sql is run to restore the cc_user schema to its initial state
  2. We add the line SET SEARCH_PATH = cc_user; to the beginning of the query and run the query
  3. The results of the query (e.g. a set of rows returned from SELECT) are written to output.txt
  4. The current checkpoint test is run

SQL Test Structure

Best practice for SQL tests usually involves multiple Bats test assertions in the following order:

  1. Test whether the query returned results (if expected)
  2. Test whether the query output matches any common mistakes
  3. Test whether the query output matches either the checkpoint solution or the full exercise solution

Individual tests will typically have the structure

@test "Message displayed if the test is failed" {
  # test code that returns a variable $status that is 0 or 1
  [ "$status" -eq __ ] # test if $status is equal to a given value
}

If $status doesn't match the requested value, the checkpoint is failed and the error message displayed to the learner. If $status matches the requested value, the test is passed. If the learner's code passes all the checkpoint tests, the checkpoint is passed.

Example Tests for SELECT queries

Test for results

It's helpful to start with a test to catch when a learner's query returns no results, and provide them with feedback to that effect:

@test "Your query didn't return results, did you \`SELECT\` anything?" {

  # create an empty file
  touch empty.txt

  # run diff will return $status=1 if the files are different
  run diff output.txt empty.txt

  # remove the empty file
  rm empty.txt

  # test that the learner's output is nonempty
  [ "$status" -eq 1 ]
}

Test for mistakes

Sometimes there are predictable errors that learners can make. For example, we might expect learners to forget to include a WHERE clause. It's best practice to test for these, so we can provide specific feedback to learners. These tests differ slightly depending the SQL workspace (SQLite or PostgreSQL).

SQLite

@test "Don't forget to include a \`WHERE\` clause" {

  # run the mistaken query and write the output to a temporary file
  sqlite3 output.sqlite "SELECT * FROM table_name;" > tmp.txt

  # run diff will return $status=1 if the files are different
  run diff output.txt tmp.txt

  # remove the temporary file
  rm tmp.txt

  # pass if the learner's output is different from the mistaken query
  [ "$status" -eq 1 ]
}

PostgreSQL

@test "Don't forget to include a \`WHERE\` clause" {

  # set the schema and output the query results to tmp.txt
  echo "SET SEARCH_PATH = cc_user; SELECT * FROM table_name;" | psql -A > tmp.txt

  # run diff will return $status=1 if the files are different
  run diff output.txt tmp.txt

  # remove the temporary file
  rm tmp.txt

  # pass if the learner's output is different from the mistaken query
  [ "$status" -eq 1 ]
}

Test for solution

The last test in a checkpoint should always check if the learner's output matches either

  1. the correct output for that checkpoint, or
  2. the output from the final savepoint / solution

Note: some checkpoints update the database structure but do not produce output. For these, we generally write queries that rely on the expected database structure and compare against pre-made solution files (see examples).

SQLite

@test "General hint for completing the checkpoint" {
  # run the checkpoint query
  sqlite3 output.sqlite "CHECKPOINT QUERY;" > tmp.txt
  run diff output.txt tmp.txt
  rm tmp.txt

  # if the learner's code doesn't match, check against the solution
  if [ "$status" -eq 1 ]; then
    sqlite3 output.sqlite "GET UNSTUCK SOLUTION;" > tmp.txt
    run diff output.txt tmp.txt
    rm tmp.txt
    [ "$status" -eq 0 ]
  else
    # pass the checkpoint
    [ "$status" -eq 0 ]
  fi
}

PostgreSQL

@test "General hint for completing the checkpoint" {
  # set the schema and run the checkpoint query
  echo "SET SEARCH_PATH = cc_user; CHECKPOINT QUERY;" | psql -A > tmp.txt
  run diff output.txt tmp.txt
  rm tmp.txt

  # if the learner's code doesn't match, check against the solution
  if [ "$status" -eq 1 ]; then
    echo "SET SEARCH_PATH = cc_user; GET UNSTUCK SOLUTION;" | psql -A > tmp.txt
    run diff output.txt tmp.txt
    rm tmp.txt
    [ "$status" -eq 0 ]
  else
    # pass the checkpoint
    [ "$status" -eq 0 ]
  fi
}

Examples in Author

These are live, do not edit.

Other Guidelines

  • Actual code (real variable names, language syntax, etc. ) in feedback messages should be code-ticked (`). The escape character \ is necessary before the `s.
  • SQL tests should follow Test standards.