SQLite driven testing using Javascript

SQLite driven testing using Javascript

Data Driven Testing (DDT) is a method to structure tests. It is recommended for a huge set of data which may grow in the future. Code has to be written only once and new data can be fed into the test without touching the code at all which is the biggest advantage of this strategy. Data can be load from a file or a database. I would like to show how this works in a small example with the addressbook. An example of it is available in every Squish edition. As the title already told you – we are going to fetch data from a SQLite database.

SQLite database

To create a new database I downloaded the « DB Browser for SQLite » software from their homepage to do so. It’s straightforward to create the database and insert data via SQL. SQLite databases are file based thus they do not have a server (Host IP and Port) to connect to.

The following screen shows the DB Browser software and some data fetched via an SQL query. I used this website to generate random user data.

DB Browser SQLite

I used the addressbook example from a Squish for Windows package and JavaScript as script language. JavaScript has a standard SQL library which allows us access to the SQL Object, which in turn, permits us to fetch data without additional imports.

In the test script we have to establish a connection to the database file. For more We will use the SQL object proposed by Squish (see documentation). With the « connection » ready, we can send our query and handle the return value. The if statement in the code is needed to make sure the database file isn’t empty.

function fetchDataFromSQLiteDB() {
//used http://sqlitebrowser.org/ for creating database
var conn = SQL.connect( { Driver: "SQLite",
Database: "C:\\Users\\franke\\Desktop\\squish.db"} );

var result = conn.query("SELECT id, forename, surname, email, phone FROM addressbook;");
if(result.isValid == false) {
test.log("Result is not valid, maybe no entries in database?")
} else {
while (result.isValid) {
// do something with the result
var id = result.value(0)
var forename = result.value(1)
var surname = result.value(2)
var email = result.value(3)
var phone = result.value(4)
//test.log(id + forename + surname + email + phone)
addEntry(forename, surname, email, phone)
result.toNext();
}
test.log("added " + id + " entries in the addressbook application")
}

To use this code properly you have to start the application and create a new addressbook as pre-condition. The addEntry function takes the fetched data as arguments and adds them into the addressbook.

Addressbook after running the script

The following screenshot shows the addressbook example application after running the test script.

SQL Lite Addressbook

Beware that the test execution time increases by every additional entry in the database / data file.

Christian Franke started as a support engineer in 2017 right after finishing his Bachelor's degree. Since then, he's taken over sales-relevant tasks as well as conducting trainings and offering consulting from time to time.