UI Automation, Database Connections and SQL

Context:

Databases have been the backbone of data truth for many years and years to come. Database have come into existence to be able to retrieve and persist data in an efficient manner. There are lot of database vendors and the ones I have worked with are Microsoft SQL, Oracle, MySQL, Postgre SQL, sqlite3 and so on. These days we hear Nosql databases like MongoDB too. Anyways, we are not going into the details of databases here. Web Applications typically interact with application layer and application layer might either directly interact with database of some layer in between that translates eventually to SQL queries in a relational database environment. So data gets served to the web application ultimately maps to a database [and tables, rows, columns thereof]

Relevance to Automation:

While we validate scenarios on web layer, we need “expected data” that can be compared to actual data that appears on web UI. The expected data truth generally lies in a database. By that we mean, instead of manually extracting data from database and then using those string values in our scenarios, can’t we directly make a database query (SQL query) real-time while the Selenium test is executing and compare the values of fields on the browser with the column values returned from the SQL query ? Yes we can ! That is what we are going to demonstrate in this section. The converse is also true, meaning sometimes we have to look up data like city’s , countries [in short it is called look up data] which are stored in databases and validate them on the UI layer, that the set of values are indeed correct or is it being mangled in the layers between web UI and database [potentially a defect in the application layer]. Anyways, with that background, we will see how we can make SQL queries using activerecord gem in Ruby. Active Record gem was originally designed for ruby on rails application development and it recommends a convention to follow to interact with the database, however it also allows to execute free form SQL. We will use whatever applies to us and we will see both examples in this topic.

Agenda:

  • sqlite3 firefox plugin
  • create a simple database
  • activerecord and sqlite3 gem
  • Important lines of code
  • connect dots between selenium operations and sql queries

1) sqlite3 firefox plugin:

Open firefox browser and go to Settings and then “Add-ons”

firefox_addons

 

Search for “sqlite3” and install it. Restart the firefox browser

sqlite3_firefoxaddon

 

Add the sqlite3 manager (blue icon) to the top bar by going to Settings – Customize and drag drop the icon to the top bar.

2) Create a simple database:

You need not do this step as I have provided the database with tables populated with data below, however I would recommend that you do it as you can create your own database and tables if needed. The overall db architecture for this 3 tables are depicted as in this picture.

Open sqlite manager and create new database and name it ‘practiceselenium’ as follows:

sqlite3_newdb

fans table:

Copy the below sql statement in “Execute SQL” tab in sqlite3 manager and hit ‘Run SQL’. There should not be any errors.

products table:

Copy the below sql statement in “Execute SQL” tab in sqlite3 manager and hit ‘Run SQL’. There should not be any errors.

product_categories table:

Copy the below sql statement in “Execute SQL” tab in sqlite3 manager and hit ‘Run SQL’. There should not be any errors.

You can skip creating the tables and download the database from here directly. It is a single file. The overall architecture is depicted below:

overall_db_architecture

3) Activerecord and sqlite3 gems.

Add activerecord and sqlite3 gems to Gemfile in your project and run “bundle update” to install the gems.

gemfile_activerecord_sqlite3

 

require ‘active_record’ and require ‘sqlite3’ in your env.rb file as follows. Please refer to previous pages as by this time we should be well aware of how to add gems to your project and enable them in env.rb

env_activerecord_sqlite3

 

3) Important lines of code.

Create a database connection

We specify the adapter as ‘sqlite3’ and point the database to our database file. In my case I placed the practiceselenium.sqlite in the path as shown below. If the path doesn’t exist, please go ahead and create it. The connection object is held in @sqlite3_connection object.

Execute SQL

Execute arbitrary sql on the connection object. This will be referred to as traditional sql below. As we can see, we can execute any SQL queries we might have built over years for maintenance projects.

Loop through result set

The result set has rows through which we can loop through by specifying column names.

ActiveRecord convention to retrieve rows

Active Record recommends that we extend Base class and define the template at least and it takes care of mapping the fields to the class. As an example below, we go ahead and define an empty class that extends from ActiveRecord::Base Module. Once we do this, at run time, automatically the mapping is done by certain rules. It is an overkill to go into the rules here as if you are a rails developer, you would already know this. Anyways, for those interested, you may read this blog, well written. The “less than” symbol might be garbled in the code below, so please replace it as shown in the picture below.

fan_activerecord

features/support/database/fan.rb

features/support/database/product.rb

features/support/database/product_category.rb

It is perfectly fine to leave the classes as above. The mapping is automatically taken care of by ActiveRecord

Retrieving a row by using the class created above. Observe the find_by_ID. The bold text is the name of the column in the table. You can specify any column which is valid. See the overall db architecture diagram at the top of this page to identify the columns you might need

Update a row using Active Record convention

Of course you can always use traditional sql update query to update if you are comfortable with that. Here we are retrieving a row in product category table whose id is ‘1’, then we are updating the ID to ‘1000’ and saving the row [The ID is not a primary/unique key in this case]

Retrieve a column and compare it with web element

Entire Cucumber Feature:

Step Definitions:

Output:

  • First scenario (out of 6 total) retrieves values from products table and prints it
  • The last two scenarios are very helpful in understanding how we can apply this concept to building a script that is more end-end test automation script.
  • The browser pops up for all scenarios, but that is okay, please ignore it.

Closing Thoughts:

We have looked at one database example i.e. sqlite3 because it is easier to set up and demonstrate, however in large enterprise setting, we generally find Microsoft SQL or Oracle et al. vendors. The general idea here is we have activerecord gem that implements the interface, however we have to install the vendor specifc gem (+client tools) to access the functionality. For example, to connect with oracle, you have to install oracle-enhanced-activerecord gem in addition to installing the client tools. The entire information is here.

I have had hands-on with each of the below database connection using Ruby and activerecord, so if you are stuck anywhere on the below, please write on this website and we will be glad to give you specific code.

  1. Oracle
  2. Mysql
  3. DB2
  4. sql-server

Note: If you are using JRuby, then life is much easier with database connections because jdbc libraries have already done the heavy lifting of abstracting out too many specific details on connecting to multiple database vendors. We will talk about the advantages of running JRuby over MRI and how it is the best of both worlds [Ruby and Java World] in our subsequent pages.