Querying data sources

(Enterprise customers only) Hoverfly Cloud supports the use of CSV data sources so that you can do key value data lookups and simulate persistence within your simulations.

Reading from a CSV Data Source

You can read data from a CSV data source in a number of ways.

For illustration we will use this data source called "pets":

id	category	name	status
1000	birds	Archie	available
1001	dogs	Zipper	available
1002	dogs	Teddy	sold

Single value lookup with {{csv}}

The most basic query function is to return the value of one field (selected-column) given a field name to search (column-name) and a value to search for in that field (query-value). Of course the query-value would normally be pulled from the request.

Syntax:

{{csv '(data-source-name)' '(column-name)' '(query-value)' '(selected-column)' }}

Template Example: Display the Name of the pet with Id 1001

{{ csv 'pets' 'id' '1001' 'name' }}

Output:

Zipper

Retrieve all rows as a map with {{csvAsMap}}

Additional functions are available to query the CSV data source to return all or a filtered subset of it’s rows, as a map.

Retrieving the results as a map makes it simple to render back into the template, as you can use the {{this}} expression with the column names to identify which fields you want to render.

To return all the data from the csv as an array of maps. (Note that you would need to wrap this in an #each block. ) See examples below.

Syntax:

{{csvAsMap '(data-source-name)' }}

Template Example:

{
    "All-The-Pets": [ 
    {{#each (csvAsMap 'pets’)}
    {
        "id":{{this.id}}, 
        "category":"{{this.category}}", 
        "name":"{{this.name}}", 
        "status":"{{this.status}}"
    }
    {{#unless @last}},{{/unless}} 
    {{/each}} 
    ]
}

Output:

{
    "All-The-Pets": [
        {
            "id": 1000,
            "category": "cats",
            "name": "Sylvester",
            "status": "available"
        },
        {
            "id": 1001,
            "category": "dogs",
            "name": "Zipper",
            "status": "available"
        },
        {
            "id": 1002,
            "category": "dogs",
            "name": "Teddy",
            "status": "sold"
        }
    ]
}

Retrieve filtered rows as a map with {{csvMatchingRows}}

To filter the map on a single column value at the time of query you can use csvMatchingRows. This will return all the rows where the (column-name) = (query-value)

{{csvMatchingRows '(data-source-name)' '(column-name)' '(query-value)'}}

Template Example:

{
    "Dogs-Only": [ 
        {{#each (csvMatchingRows 'pets' 'category' 'dogs')}}
        {
            "id":{{this.id}}, 
            "category":"{{this.category}}", 
            "name":"{{this.name}}", 
            "status":"{{this.status}}"
        }
        {{#unless @last}},{{/unless}} {{/each}} 
        ]
}

Output:

{
    "Dogs-Only": [
        {
            "id": 1001,
            "category": "dogs",
            "name": "Zipper",
            "status": "available"
        },
        {
            "id": 1002,
            "category": "dogs",
            "name": "Teddy",
            "status": "sold"
        }
    ]
}

Advanced querying with SQL SELECT {{csvSqlCommand}}

A simplified SQL like syntax can be used to query the data in a csv data source and retrieve a map of the results. (This syntax can also be used to UPDATE and DELETE from the data source. You cannot insert using this syntax however there is a mechanism which can be read about here.)

You can read more about using this SQL like syntax here.

Syntax:

{{csvSqlCommand '(sql select, update or delete statement)'}}

Template Example:

{
    "Dogs-With-Big-Ids-Only": [
    {{#each (csvSqlCommand "SELECT * FROM pets WHERE category = 'dogs' AND id >= '1002'")}}
        {
            "id":{{this.id}}, 
            "category":"{{this.category}}", 
            "name":"{{this.name}}", 
            "status":"{{this.status}}"
        }{{#unless @last}},{{/unless}}
    {{/each}}
    ]
}

Output:

{
    "Dogs-With-Big-Ids-Only": [
        {
            "id": 1002,
            "category": "dogs",
            "name": "Violet",
            "status": "sold"
        }
    ]
}