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. The 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 loaded from a file or a database. I would like to show how this works in a small example with an address book. One 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.

I used the address book example from a Squish for Windows package and JavaScript as the script language. The use of JavaScript grant access to the SQL Object which allows fetching of 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 the 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 address book as a pre-condition. The “addEntry” function takes the fetched data as arguments and adds them into the address book.

Address Book After Running the Script

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

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