Querying data sources

Hoverfly Cloud supports data queries from a CSV Data Source for Enterprise customers to do key value data lookups and simulate persistence.

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
*	no data	no data	no data

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

More advanced examples: Single value lookup with {{csv}}

Template Example: Display the Name of the pet with Id taken from position 2 on the path

{{csv 'pets' 'id' 'Request.Path.[2]' 'name'}}

Template Example: Use conditional logic to check if a pet with Id taken from position 2 on the path exists, and if not return an error. Note that when the csv function is used within an #equal block, it needs to be enclosed in round brackets.

{{#equal (csv 'pets' 'id' 'Request.Path.[2]' 'name') 'no data'}}
    {{setStatusCode 404}}
{{else}}
.
.
{{/equal}}

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.

Note that as you will use this wrapped within an #each or #first block, the csvAsMap function must be enclosed inside round brackets.

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).

Note that as you will use this wrapped within an #each or #first block, the csvMatchingRows function must be enclosed inside round brackets.

{{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.)

Note that as you will use this wrapped within an #each or #first block, the csvSQLCommand must be enclosed inside round brackets.

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"
        }
    ]
}