How to get screenshots of your Android App with Appium… by hacking the database

This article demonstrates the power of Appium tests manipulating the SQLite database of your Android app.

Hansjörg Keser
Jun 5 · 8 min read
Get better Android App Screenshots with Appium + SQLite

A lot of apps save items and certain data of their features locally in a SQLite database. Having the debug build of the app, one can use SQL statements to manipulate this stored data before taking a screenshot.

Why we automated the taking of screenshots for our mobile apps

The idealo Android and iOS app support several countries in Europe and therefore several languages. Taking the necessary screenshots for each country for phone, and two tablet sizes by hand took a lot of time and the job itself was not very exciting.

The idealo mobile app team already used Appium for UI test automation, so we could use the existing page objects and predefined flows to take screenshots automatically. The advantage is by maintaining the mentioned UI tests, this newly created “screenshot tool” is always up-to-date with very little extra work.

Two screens that needed optimising

Now let’s have a look at the idealo Android app. The following screenshot shows one of our useful features: your personal favourites list.

Favourites (country and language set to Germany)

There are more features like price drops and price alerts we would like to show users in the screenshots on Google Play. But how do you make that happen without implementing mocks which would lead to more unpleasant maintenance of mock files?

You could…

  • let your Appium tests execute all these UI actions which would cause real API requests on production… not a good choice.
  • mock the app API requests in the app or in the related backends… but who would do that just to get screenshots?!
  • just insert the necessary data in the app’s database tables… sounds clean and easy to me. Well… easy as soon as the initial setup is done once :-)

Let’s have a look at the start page, which displays the last viewed products module (“Zuletzt angesehen”). Here the Appium test prepared the app in a way our real users would do it:

  • Search for several products
  • Have a look at the product details
  • Go back to Start
Start page in site Germany — last viewed module prepared by Appium

This took a lot of time, so I’d prefer a shorter way to reach this state.

The solution

Before taking the screenshot let’s execute adb shell commands to get the app database table in the state we want it to be. Then we just open the target page and voila… the view is ready for the screenshot. No additional UI interaction needed, the app gets the desired data straight from the database.

Preconditions:

  • works only with debug builds
    (you can’t just download and extract an Android app release build from your phone)
  • Appium server must run with — relaxed-security flag
  • You must know the structure of your app database and adjust your insert or update SQL statement accordingly
  • Device or emulator must have root rights,
    Emulator must not be configured with Play Store
    (they can not be restarted with adb root command)
Do not use emulators with Play Store

How it works — Startpage

We prepare the process for the start page screenshot in which we just want to insert product data in the item history table. The optimisation for the start page screenshot in short will be:

  • Get product data via API call
  • Insert data in the database table
  • Go to start page

Therefore we first examine the table structure by entering adb root in terminal, using an emulator. The emulator is either already started as root or will be restarted as so. Go to the database location and investigate the database by executing

sqlite3 idealo.db ".tables"

Now you know the name of the table you want to insert the data. Get the columns and its datatypes of the desired table itemHistory by executing

sqlite3 idealo.db "PRAGMA table_info(itemHistory)"
Get table structure by adb shell commands

To know which columns are set to auto generated values, or which columns do not need data, enter

sqlite3 idealo.db ".schema itemHistory"

and check for AUTOINCREMENT and DEFAULT declarations. Now you are ready to prepare the insert statement with suitable values. At this point you could manually insert one entity like this:

sqlite3 idealo.db "INSERT INTO itemHistory values(nullif(?, 0), 1, 0, 585643, 1557682483302, 3972, nullif(?, ''))"

The first column contains an autogenerated index id and can be handled in the insert statement with nullif(?, 0), where the 0 is a default value. But the default value will not be used as the id will be autogenerated. Values of the column cluster_query are optional and can be handled in the statement with an empty string (see the last nullif).

In the Appium test-automation code we get the product data by web-service request and store the product objects in a list. Then we read the product values we need and prepare each string as part of the insert statement:

Note that all the code examples in this article are written in Kotlin. In line 6 a random time value is generated as all saved products need to have a unique time value (the date when the user had a look at the product in the app).

All these strings need to be attached to the first part of a full SQL statement. The obstacle here is that Appium executes the whole statement including adb shell at once. Hence you have to put the statement in apostrophes after adb shell. A full insert statement executed in one line in terminal would be like this:

adb shell 'sqlite3 /data/data/de.idealo.android/databases/idealo.db "INSERT INTO itemHistory values(nullif(?, 0), 1, 0, 585643, 1557682483302, 3972, nullif(?, ''))"'

Note the ‘ (apostrophe) before sqlite3 and after the last “ (quotation mark).

Back to the automated solution:
Appium executes the SQL statement with JavaScript. The saved values are delivered as List<String> to the method below, see the insertData object:

In line 11 the script parameter mobile: shell inserts the adb shell command before the SQL statement. In line 8 the sqlite3 is added to the command and is followed by the arguments, declared in lines 3 to 5. Note that the quotation marks of the SQL statement have to be escaped with backslash like in line 5 before INSERT. This is the solution that worked as substitution of the necessary apostrophes mentioned in the manual statement above.

You can check the final output of the insert statements in the Appium log:

Appium log in Terminal

Find more detailed information about this Appium feature here:
Appium Pro Edition 3

Now we managed to make the last viewed model on the start page displayed without the need to view all the products over the app user interface.

How it works — Favourites

Now let’s get back to the favourites table and get the data as described above. Once the products are saved in the favorites table we could create price alerts. Instead of doing this:

  • tap on a product
  • tap on price alert button
  • enter desired target price
  • tap on save/create button
  • go back to favourites page
  • repeat this X times

We better just add the data in the table by UPDATE SQL statements:

In line 4 the path to the database is saved as constant variable. Apart from that you can see that the code is similar to the insert statement example above. Difference here lies in the the args object with the update statement, adding values to the column pricewatcher_price.


In addition we want to show potential users, checking our screenshots in the Play Store, that the favourites list also displays price drops. As soon as the product got cheaper offers the price is displayed in green. So let’s reach that state by updating the table again.

Meaning that this time we use Appium to execute an additional SELECT statement before we manipulate the prices in the table. Here we get the current prices of the products in the favourites list:

The column we need here is minPrice. In line 10 is the SELECT statement and we save the result as string in line 16. Because the table may have several lines, one for each saved product, we have to split the results in line 4.

Now that we have the price values we can lower it and finally execute the update statements as explained above in the price alert example.

For example if we have a product “Tonies Toniebox” with a minPrice of 68,98 EUR we could update the table entry with a new minPrice of 62,08 EUR. A different product can be made more expensive, leading to red text colour in the app UI (see the Diesel watch in the screenshots below).

After all these modifications the favourites list is ready for the screenshot:

Favourites without and with database manipulation

In the right upper corner of the right screenshot you can see an orange bell. This is the symbol for a created price alert of 95,55 EUR set to the hoody. And of course you may notice the changed prices in green and red text colour.


Finally get the screenshot and proceed with the next view to be prepared and “photographed”:

driver.getScreenshotAs(OutputType.BYTES)

You can check all our screenshots here: idealo Android App

Benefits

This approach

  • saves time taking screenshots,
    for example the preparation of the start page for one country without database manipulation “the old way” takes about 3 min.
    Versus
    Taking the screenshot with SQL statements “the fast way” takes 40 seconds
  • gives more flexibility to prepare or simulate certain situations in the app, which would originally rely on data in server responses

Want to give it a try?

Then check out this sample project. There you can exercise the main steps quickly and easily with a small sample app.

And of course you can ask questions in the comment section below. I would be happy about any feedback, whether suggestions, improvements or critique… If you need help with the setup, let me know :-)


If you found this article useful, give it a clap 👏🏻 so others can find it too, and share it with your friends and colleagues.

idealo Tech Blog

👨‍💻👩‍💻 idealo tech and product folks writing about their work 💡⚙️💻 🛒 and their endeavour to build, ship and run a great user experience on https://idealo.de

Thanks to David Paschke.

Hansjörg Keser

Written by

QA Engineer at idealo Internet GmbH

idealo Tech Blog

👨‍💻👩‍💻 idealo tech and product folks writing about their work 💡⚙️💻 🛒 and their endeavour to build, ship and run a great user experience on https://idealo.de