How to execute SQL against CSV

Assuming there is a project containing VIVIDUS-based tests (e.g. VIVIDUS Starter) and the goal is to execute SQL queries against CSV resources to get the various subsets of the data.

As a prerequisite add the required DB plugin dependency to the project build.gradle

implementation(group: 'org.vividus', name: 'vividus-plugin-db')

Execute SQL against CSV resource

  1. Add the CSV and CSV driver dependencies to the project build.gradle

    implementation(group: 'org.vividus', name: 'vividus-plugin-csv')
implementation(group: 'net.sourceforge.csvjdbc', name: 'csvjdbc', version: '1.0.46')

  2. Configure connection to the CSV resources

    db.connection.csv-data.url=jdbc:relique:csv:classpath:path/to/csv/data

    url includes the mandatory path to CSV resources and may also contain a set of options configuring CSV parsing, e.g.:

    db.connection.csv-data-2.url=jdbc:relique:csv:classpath:another/path?separator=;&quoteStyle=C

  3. Use the configured connection to execute SQL

    Example 1. Execute SQL against CSV
    When I execute SQL query `
SELECT * FROM capitals
` against `csv-data` and save result to scenario variable `csv-records`
Then `${csv-records}` matching rows using `country` from `csv-data` is equal to data from:
|country |capital |population |
|Belarus |Minsk   |1975000    |

    where capitals is the name of CSV resource (the full resource path is path/to/csv/data/capitals.csv).

    The complete guide on SQL syntax and supported functions can be found in CSV Driver documentation.

Execute SQL against in-memory CSV

  1. Configure connection to in-memory database as described here

  2. Populate database with CSV dataset

    Example 2. Create table with data from CSV
    !-- For demonstration purposes we hardcode the CSV file content, you can use variables and expressions as a sources of CSV instead.
When I create temporary file with name `egg-miners` and content `
id,name,race
1,Miner Gor,Redguard
2,Daynila Valas,Dark Elf
3,Miner Juillen,Breton
` and put path to SCENARIO variable `egg-miners-csv-path`

!-- Create table from the CSV contained in previously created temporary file
When I execute SQL query `
CREATE TABLE egg_miners AS SELECT * FROM CSVREAD('${egg-miners-csv-path}');
` against `in-memory-db-key`

    • See CSVREAD function description for more details.

    • All columns read from CSV are of type VARCHAR.

    • Use the CAST function to adjust the column types (if applicable).

  3. Use the created table to execute SQL against

    Example 3. Execute SQL against table created from CSV
    When I execute SQL query `
SELECT * FROM egg_miners WHERE race = 'Dark Elf';
` against `in-memory-db-key` and save result to scenario variable `egg-miners-records`
Then `${egg-miners-records}` is equal to `[{id=1, name=Daynila Valas, race=Dark Elf}]`