Interacting with Databases in Automated Functional Test Scripts

How to query relational databases to assist your automated end-to-end testing

A repost of my daughter’s article with permission and slight modification. I added it here for the convenience of my blog subscribers. You can find more API testing examples like this one in my 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, there are data that 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)
• Query a MySQL Database
• DSL-friendly database queries with ActiveRecord

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")
end