Category: mysql

Hello everyone!

I’m back here to give you some extra tips on Software Testing using Protractor, this time how to connect to a MySQL database when testing your application at localhost.

Node.js is one of the coolest Javascript engines which provides tons of libraries to perform the most varied kinds of tasks. And should you need any task and do not have enough time to work around it using the native library, you may use the vast collection of 3rd party modules available at npm (Node Package Manager).

Today, we are going to connect to a MySQL database using the mysql library, developed by a bunch of developers and which has got only 140 206 downloads last month. Quite a lot don’t you think? And it works amazingly!

First, you will need to download the library running the following command from your testing root directory:

npm install mysql --save-dev

Remember, the flag

--save-dev

saves the reference to the module into your package.json file, so next time you install your testing system, you should only run:

npm install

And install all your dependencies at once.

Given the above, lets see how to organize the files to keep a modularised testing code. In my last post Protractor: The Page Object Model, I introduced an architectural model to keep your tests organised, and what we are going to need now is a PageObject.js, a testspec.js and a ConnectDatabase.js object.

In your PageObject.js, you are going to define attributes to store web elements and methods to perform actions on those attributes, for example addUser(callback). Please refer to Protractor: The Page Object Model for details.

Your ConnectDatabase.js will require the Node.js module mysql, define connection attributes to hold values and methods to perform actions.

function ConnectDatabase (){
var mysql = require('../../node_modules/mysql');

this.connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : 'your_db_password',
database: 'your_db_name'
});
};
module.exports = ConnectDatabase;

Now imagine you wish to test if the creation of a user is really adding a new row into your users table in MySQL. You should better count the rows before adding the user and then after. Logically, the math is after = before + 1, and if it is not true, something is wrong.

After creating your connection object, which happens to be a function, you can use it within your testspec.js

//Require and instantiate the page object
var PageObject = require('../pageobjects/PageObject');
var pageObject = new PageObject();
//Database connection
var ConnectDatabase = require('../ConnectDatabase');
var connectDatabase = new ConnectDatabase();
var sql = 'SELECT COUNT(*) AS result FROM users_table';
connectDatabase.connection.connect();

it('Should add user and verify new entry in mysql', function() {
    //Defines variables to hold row count before and after addUser()
    var before;
    var after;
    connectDatabase.connection.query(sql, function(err, rows) {
        if (!err) {
            before = rows[0].result;
        }
    });
    //Inserts user and verifies row insertion
    pageObject.addUser(function() {
        //Counts rows after import
    connectDatabase.connection.query(sql, function(err, rows) {
            if (!err) {
                after = rows[0].result;
            }
            //Tests rows after against rows before addUser()
        expect(after).toBe(before + 1);
    });
    });
}, 10000);

So that’s it guys, hope you enjoyed. Leave your comments, I’ll be glad to help you out. And remember, TESTING IS QUALITY!

Best,

Raf.