Interacting with Databases in Automated Functional Test Scripts
How to query relational databases to assist your automated end-to-end testing
You can find more API testing examples like this one in this eBook: API Testing Recipes in Ruby. This is also included in my “How to in Selenium WebDriver” series.
In this tutorial, I’ll go over how to interact with a database in automated test scripts. Three types of situations would require writing to/reading from a database in test scripts.
Verify data
Sometimes in tests, some data cannot be easily verified in the User Interface (i.e. not displayed). Instead, we can verify the data directly in the database.Retrieve test data
For example, we want to get the latest signed-up user’s username as test data in an automated UI test or an application number lodged last month.Create test data
Write to database tables to make test data available for functional test scripts.
Zhimin: I often use database-interactive statements and UI (Web or Mobile) driving statements together in one automated test script. I call it Synergy.
I will show some examples:
Executing Raw SQL (Sqlite3)
I will use a file-based SQLite3 database named sample.db as an example. To interact with SQLite3, we need to install the sqlite3 gem first.
it "Sqlite 3 - Retrieve data records from database" do
require 'sqlite3'
db_file = File.join(File.dirname(__FILE__),"testdata","sample.db")
db = SQLite3::Database.new db_file
oldest_user_login = ""
# Users table: with login, name, age
result_set = db.execute("SELECT * FROM users ORDER BY age DESC")
puts result_set
# first user's first attribute (username)
oldest_user_login = result_set[0][0]
expect(oldest_user_login).to eq("mark")
enddb.execute runs an SQL statement against the database (as a file) and returns the result in a multi-dimensional array.
Here’s the output of result_set from the above script:
[
["mark", "Mark Herb", 76],
["zhimin", "Zhimin Zhan", 46],
["eileen", "Eileen Jones", 10]
]Because the output is an array, we can loop over the results like this:
db.execute("select * from users order by age desc") do |row|
oldest_user_login = row[0]
break
endQuery a MySQL Database
The below example will use one of the most popular databases, MySQL. The Ruby library for interacting with MySQL is mysql2. Of course, we need to set up the connection details (database name, host, etc.)
require "mysql2"
client = Mysql2::Client.new(:host => "localhost",
:username => "root",
:password => ENV["MYSQL_PASSWORD"] || "",
:database => "buildwise_local")
results = client.query("SELECT duration FROM builds ORDER BY duration DESC")
headers = results.fields # <= an array of field names, in order
puts headers # ["duration"]
longest_build_time = nil
results.each do |row|
longest_build_time = row["duration"]
break
end
puts longest_build_time
client.closeBesides the connection at the start, the query part is similar to SQLite3.
Also, it is good practice to remember to close the connection after you finish.
DSL-friendly database queries with ActiveRecord
The above two examples showed Raw SQL queries. In Ruby, we can make our database testing script easier to read and maintain.
Active Record is a well-known Data Source pattern. I first read it in Martin Fowler’s Patterns of Enterprise Application Architecture book. This pattern was beautifully implemented in the Ruby on Rails framework as an independent gem: ActiveRecord.
The ActiveRecord gem makes the best of Ruby, an excellent scripting language, and presents a simple, intuitive yet powerful syntax to interact with databases.
Let’s see an example from the BuildWise Samples’ E2E RSpec tests. The below script prints the build count and the time taken for the longest build.
puts Build.count
longest_build = Build.order(“duration desc”).first
puts longest_build.durationSample output:
2
19.0Some may say this doesn’t look like SQL or a database script. This is because we have set up the models based on ActiveRecord.
# gem install activerecord
require 'active_record'
class Project < ActiveRecord::Base
has_many :builds
end
class Build < ActiveRecord::Base
belongs_to :project
has_many :test_files
end
class TestFile < ActiveRecord::Base
belongs_to :build
endZhimin: I have successfully implemented a few test automation solutions for software products that were considered ‘impossible to automate’, with ActiveRecord powered models and other technologies. IMO, ActiveRecord is another good reason to choose Ruby as the test scripting language.
With the above (<20 lines), we can now interact with the database in a much more user-friendly syntax.
Next, I will show you a more complex example: query with associations. Usually, we use JOINs in SQL, which are considered quite difficult and easier to get wrong.
The script below will get all the filenames (in alphabetical order) of TestFiles from the most recent build.
most_recent_build = Build.all.last
puts most_recent_build.project.namebuild_test_files = most_recent_build.test_files
puts build_test_files.collect{|x| x.filename }.sortSample output:
AgileTravel RSpec Sequential
["flight_spec.rb", "login_spec.rb", "passenger_spec.rb"]Some ActiveRecord Query examples:
Customer.find(10) # by id, equivalent to SQL below
SELECT * FROM customers WHERE (customers.id = 10) LIMIT 1
---
customer = Customer.first
SELECT * FROM customers ORDER BY customers.id ASC LIMIT 1
---
customers = Customer.last(3)
SELECT * FROM customers ORDER BY customers.id DESC LIMIT 3
---
Customer.where(first_name: 'Lifo').take
SELECT * FROM customers WHERE (customers.first_name= 'Lifo') LIMIT 1
---
Book.where("title = ?", "Practical Web Test Automation")
SELECT * FROM books WHERE (books.title = 'Practical Web Test Automation')
---
Customer.where.not(orders_count: [1,3,5])
SELECT * FROM customers WHERE (customers.orders_count NOT IN (1,3,5))
---
Book.joins(:author, :reviews)
SELECT books.* FROM books
INNER JOIN authors ON authors.id = books.author_id
INNER JOIN reviews ON reviews.book_id = books.idLooks easy, right? :)
Related reading:
 | A guest post by
|