How to use in-memory database to manage test data

Assuming there is a project containing VIVIDUS-based tests (e.g. VIVIDUS Starter) and the goal is to manage test data in the in-memory database (e.g. H2 database). The database is deployed on tests start, accessible from tests and shutdown on tests end. The test data can be created from SQL files located in the project.

Add the required dependencies (DB plugin, H2 database) to the project build.gradle

implementation(group: 'org.vividus', name: 'vividus-plugin-db', version: '0.5.10')
implementation(group: 'com.h2database', name: 'h2', version: '2.1.214')

Configure connection to the H2 database and initialization

DDL/DQL scripts could be run using either INIT property of the connection string or the step executing SQL queries.

Whichever method of database initialization is chosen, RUNSCRIPT statement with the path to SQL files could be used. Here you can specify absolute path to file or use the 'classpath:/' prefix - files will be searched from 'src/main/resources' folder relatively.

The details on executing SQL statements on connection could be found in the official H2 guide.

2.1 Configure using INIT property

using INIT property data loaded into the database once and can no longer be change.

Example 1. Configure connection and initialization of the database

db.connection.testh2db.url=jdbc:h2:mem:test;\
  INIT=create schema if not exists test\\;\
  SET SCHEMA test\\;\
  DROP TABLE IF EXISTS test.students\\;\
  CREATE TABLE test.students (firstname VARCHAR(50) NOT NULL, lastname VARCHAR(50) NOT NULL)\\;\
  INSERT INTO students (firstname, lastname) VALUES ('Maria', 'Miller')\\;
db.connection.testh2db.username=
db.connection.testh2db.password=

Example 2. Configure connection and initialization of the database with RUNSCRIPT

db.connection.testh2db.url=jdbc:h2:mem:test;\
  INIT=create schema if not exists test\\;\
  SET SCHEMA test\\;\
  RUNSCRIPT FROM 'classpath:/path/to/sql/file1.sql'\\;\
  RUNSCRIPT FROM 'classpath:/path/to/sql/file2.sql'
db.connection.testh2db.username=
db.connection.testh2db.password=

2.2 Configure using test step

if you use database initialization in the step, you only need to specify the connection parameters in the properties file and execute DDL script in the test.

Example 3. Configure connection to database

db.connection.testh2db.url=jdbc:h2:mem:test;\
  DB_CLOSE_DELAY=-1;
db.connection.testh2db.username=
db.connection.testh2db.password=

If you are using database initialization in the step, do not forget to set the property DB_CLOSE_DELAY.

Example 4. Initialization of the database

When I execute SQL query `
  CREATE SCHEMA IF NOT EXISTS test;
  SET SCHEMA test;
  CREATE TABLE test.students (firstname VARCHAR(50) NOT NULL, lastname VARCHAR(50) NOT NULL);
  INSERT INTO students (firstname, lastname) VALUES ('Maria', 'Miller');
` against `testh2db`

Example 5. Initialization of the database with RUNSCRIPT

When I execute SQL query `
  CREATE SCHEMA IF NOT EXISTS test;
  SET SCHEMA test;
  RUNSCRIPT FROM 'classpath:/path/to/sql/file1.sql';
  RUNSCRIPT FROM 'classpath:/path/to/sql/file2.sql'
` against `testh2db`

Use the configured connection and the initialized database to execute SQL

Example 6. Execute SQL script against H2 database

When I execute SQL query `
SELECT * FROM students
` against `testh2db` and save result to scenario variable `h2-records`

Example 7. Execute DML script against H2 database initialized in the step with the schema test and check result

When I execute SQL query `
INSERT INTO test.students (firstname, lastname) VALUES ('Alex', 'Magnus');
` against `testh2db`
When I execute SQL query `
UPDATE test.students SET firstname='Mary' WHERE lastname='Miller';
` against `testh2db`
When I execute SQL query `
SELECT * FROM test.students
` against `testh2db` and save result to scenario variable `h2-records`
Then `${h2-records}` is equal to `[{FIRSTNAME=Mary, LASTNAME=Miller}, {FIRSTNAME=Alex, LASTNAME=Magnus}]`