Make SQL queries against service data

When using test data for service virtualization, you sometimes have the requirement to create more complicated data manipulations when using multiple service data models. service data models are stored as standard SQL tables and you can run standard SQLite queries against test data, such as JOINs, GROUP BY, filtering, subqueries.

Example scenarios

You have one service data table profiles_view containing columns named email and role. You want to use this service data in your transactions, but you want to exclude rows with user data for admins. You accomplish this with a standard SQLite query:

SELECT * FROM profiles_view WHERE email = "${request.query.email}" AND role <> "Admin"

Or maybe you have two service data tables, users and user_profiles as service data, and you want BlazeMeter to create the view profiles_view that contains joined columns from both tables. In this case, you can enter a custom SQL bootstrap script to create this custom view.

Prerequisites

Log in to BlazeMeter and go to the service virtualization tab.
Create your transactions and virtual services.
To learn more, see Create your first virtual service.
Define service data tables and reference the variables in transactions.
To learn more, see Use test data with service virtualization.

Define your SQL bootstrap script

You can associate multiple service data models with a service. Either use them as is in your virtual services, or optionally, write a Bootstrap Script to create custom views.

Go to the Virtual services tab and open a virtual service.
Open the Data settings tab.
In the Test data implementation section, choose one of the following options:
1. Default NO-SQL
  BlazeMeter generates a default SQLite query. You can edit the query in the SQL hint tab if needed.
2. SQL (with bootstrap script)
  Write a custom SQLite query to create a view from multiple service data models.
  For example, to join two service data tables called users and user_profiles into a view called profile_view, enter the following:
```
CREATE VIEW profile_view AS
SELECT
   Users.id AS id,
   Users.name,
   Users.role
   Users.email,
   Profiles.birthdate,
   Profiles.language
FROM users Users
JOIN user_profiles Profiles ON Users.id = Profiles.user_id;
```

Define your query

Go to the Asset catalog tab and open the transaction.
Go to the SQL hint tab.
Review the default SQLite query. BlazeMeter automatically generates a SQLite query based on the request field of the transaction. For example, if the request is /users/${user_id}, BlazeMeter generates the following query:
```
select * from users where user_id = ${request.user_ic}
```

Edit this default SQLite query to create your custom complex SQLite query, for example:

select * from users where user_id = ${request.user_ic} and active = true and role <> ‘Admin’

Define your response

Go to the Asset catalog tab and open the transaction.
Go to the Response > Body tab.

Define the response body by calling a SQLite query, for example:

${#each (sql 'SELECT * FROM users WHERE id > 10') as |user| }
name: ${user.name}, email: ${user.email}
${/each}