Guidance on using the SQL like syntax

With Hoverfly Cloud Enterprise you can use SQL-like syntax for querying, updating and deleting from in-memory csv data sources.

Supported SQL syntax

You can use a simplified SQL like syntax to select, update and delete rows.

SQL Commands

• INSERT is not supported. To add data you must use the csvAddRow template function.

• SELECT [column-names] FROM [data-source-name] WHERE [conditions] (* can be used to indicate all colmuns)

• UPDATE [data-source-name] SET [[column-name] = ‘[value]’,] WHERE [conditions]

• DELETE FROM [data-source-name] WHERE [conditions]

Conditions

• Only simple conditions are supported.

• You can chain conditions using AND. OR is not supported.

• The following comparison operators are supported in conditions:

= equals

> greater than

< less than

>= greater than or equal to

<= less than or equal to

!= not equal to

Other considerations

• Capitalization of SQL keywords is required.

• Spaces between components of the SQL statement are required.

• Every value provided to a condition whether a number or a string must be enclosed in quotes.

• Data source names must not be in quotes.

• Joins across different data sources are not supported..

Examples

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

SELECT * FROM pets WHERE category = 'dogs' AND id >= '1002'

SELECT name, status FROM pets WHERE category = 'birds'

UPDATE pets SET status = 'sold' WHERE name = 'Archie'

UPDATE pets SET status = 'sold', name = 'Name expunged' WHERE id > '10'

DELETE FROM pets WHERE category = 'dogs' AND id > '1001'