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 ensure that different query structures with the same result can still be correctly passed.

SQL Test Structure

Best practice for SQL tests usually involves multiple Bats test assertions.

In SQL workspaces, when learners run their code, the output of their queries is displayed in their SQL output component, but it is also piped into an output.txt file. This file is used for testing purposes and not displayed to learners directly.

Example Tests

We almost always test that learners are writing queries that will actually return results. It's helpful to start with a test case to catch when a learner's query returns no results, and provide them with feedback to that effect:

@test "The query didn't return any results." {
  touch empty.txt
  run diff output.txt empty.txt
  [ "$status" -eq 1 ]
}

The basic "empty query" test case is usually followed by a test case to check a learner's results against a solution query:

@test "SELECT all columns from the \`table_name\` table." {
  sqlite3 output.sqlite "SELECT * FROM table_name;" > tmp.txt
  run diff output.txt tmp.txt
  [ "$status" -eq 0 ]
}

SQL Bats tests generally follow this format:

  1. @test label with the feedback text (In Bats tests, code ticks must be escaped: \`)
  2. sqlite command followed by a hidden output filename (usually output.sqlite), the 'solution' SQL query piped into a temporary file (tmp.txt).
  3. A diff command run on the learner's output (output.txt) and the expected output (tmp.txt)
  4. [ "$status" -eq 0 ] to assert that the expected and actual results were identical.

Other Guidelines

  • Actual code (real variable names, language syntax, etc. ) in feedback messages should be code-ticked (`)
  • SQL tests should follow Test standards.