sq is a much more difficult beast to test than a typical Go project. sq is all about integrating data sources, and that means databases. Much of the test code is testing of interaction with actual databases instances via that database’s Go driver. This is not something that can or should be mocked. We must test against the real thing.

The sq test code generally tests against a version of the Sakila sample database. This sample database was originally created for MySQL many years ago. It was “intended to provide a standard schema that can be used for examples in books, tutorials, articles, samples, and so forth” and the so forth also includes testing. Others have done the legwork of producing Sakila SQL scripts so that the same database is available not just for MySQL, but also SQLite, Postgres, SQL Server, and others.

For file-based sources, the sq repo includes the Sakila data files. For example, there’s a sakila.db file for SQLite, and a sakila.xlsx file for Excel. For typical SQL database sources (Postgres, SQL Server, MySQL), the Sakila databases have been wrapped up into Docker images in a sister project named sakiladb. See the GitHub and DockerHub repos.

To run all of the sq tests, there must be an available Sakila database instance for each database/version. The full set of sources that the test code uses can be found in testh/testdata/sources.sq.yml. That file looks something like (truncated version shown):

    - handle: '@sakila_sl3'
      type: sqlite3
      location: sqlite3://${SQ_ROOT}/drivers/sqlite3/testdata/sakila.db
    - handle: '@sakila_pg9'
      type: postgres
      location: postgres://sakila:p_ssW0rd@${SQ_TEST_SRC__SAKILA_PG9}/sakila?sslmode=disable
    - handle: '@sakila_pg10'
      type: postgres
      location: postgres://sakila:p_ssW0rd@${SQ_TEST_SRC__SAKILA_PG10}/sakila?sslmode=disable
    - handle: '@sakila_my56'
      type: mysql
      location: mysql://sakila:p_ssW0rd@${SQ_TEST_SRC__SAKILA_MY56}/sakila
    - handle: '@sakila_my57'
      type: mysql
      location: mysql://sakila:p_ssW0rd@${SQ_TEST_SRC__SAKILA_MY57}/sakila
    - handle: '@sakila_xlsx'
        type: xlsx
        location: "${SQ_ROOT}/drivers/xlsx/testdata/sakila.xlsx"
            - "true"

Note that for each of the external databases, there is a matching envar. For example, @sakila_pg9 has its location field populated with envar SQ_TEST_SRC__SAKILA_PG9. The envar is simply the host (meaning hostname:port) part of the connection string for that source.

Note: In the yaml snippet above, for local file-based sources such as @sakila_sl3 with location: sqlite3://${SQ_ROOT}/drivers/sqlite3/testdata/sakila.db, you’ll notice a variable ${SQ_ROOT}. It is not necessary to explicitly set this variable as an envar: the sq test framework calculates it automatically.

Importantly: When running sq tests, if the envar for a source is not populated, any test that uses that source is skipped.

Thus, to run all of the sq tests, there must be available instances of all of the Sakila database/versions. These databases could be run locally, or on a remote server. For local dev/test, it is typical to export these envars in .bashrc/.zshrc or similar. For example (in this case, the Docker containers are running on a remote server):

# Postgres

Start Sakila containers

There is a mage target to locally start a Sakila container for each database/version. Note that this requires a significantly beefy machine with lots of RAM. It is typically more advisable to run the containers on an external server, or to simply start one or a few containers at a time while working or testing against that particular database.

# Remove any existing containers
> mage sakila:removeall

# Start all containers
> mage sakila:startall
Starting all containers...
'src' the following:
export SQ_TEST_SRC__SAKILA_MY56=localhost:33066
export SQ_TEST_SRC__SAKILA_MY57=localhost:33067
export SQ_TEST_SRC__SAKILA_MY8=localhost:33068
export SQ_TEST_SRC__SAKILA_PG9=localhost:54329
export SQ_TEST_SRC__SAKILA_PG10=localhost:54330
export SQ_TEST_SRC__SAKILA_PG11=localhost:54331
export SQ_TEST_SRC__SAKILA_PG12=localhost:54332
export SQ_TEST_SRC__SAKILA_MS17=localhost:14337

Starting container sakiladb-mysql-8 for sakiladb/mysql:8
Starting container sakiladb-postgres-9 for sakiladb/postgres:9
Starting container sakiladb-postgres-12 for sakiladb/postgres:12
Starting container sakiladb-mysql-5.6 for sakiladb/mysql:5.6