{"id":1096,"date":"2011-04-08T13:56:57","date_gmt":"2011-04-08T13:56:57","guid":{"rendered":"http:\/\/blog.agilityfeat.com\/?p=74"},"modified":"2011-04-08T13:56:57","modified_gmt":"2011-04-08T13:56:57","slug":"agile-database-testing","status":"publish","type":"post","link":"https:\/\/agilityfeatpanama.com\/en\/blog\/2011\/04\/agile-database-testing\/","title":{"rendered":"Agile Database Testing"},"content":{"rendered":"<p><em>Last month the QANews newsletter by Sogeti was released, and I was pleased to have an article I wrote on Agile Database Testing published in it.\u00a0 I&#8217;ve reproduced the article below, and you can also see the original article <a href=\"http:\/\/www.es.sogeti.com\/PageFiles\/173\/Arin%20Sime_Agile%20Database%20Testing%20Article.pdf\">here.<\/a><\/em><\/p>\n<h2 id=\"internal-source-marker_0.1651450669142719\">Agile Database Testing<\/h2>\n<h3>Testing database changes<\/h3>\n<p>Testing is rarely easy, and is more often thankless. \u00a0This is particularly true when testing a heavily database driven application. \u00a0Those responsible for testing are asked to test large sets of functionality in a short period of time, and they have the final \u201csign off\u201d before an application can be deployed.<\/p>\n<p>When an application is heavily dependent on a database, there is extra stress in testing just before deployment. \u00a0The tester has to worry about questions like:<\/p>\n<ul>\n<li>Am I testing against the right version of the database code (ie, stored procedures, views, tables, etc)?<\/li>\n<li>Are any errors due to software bugs, or a configuration issue?<\/li>\n<li>Am I testing against the right data to uncover bugs?<\/li>\n<li>How can I recreate this problem in the development environment?<\/li>\n<\/ul>\n<p>How can the testing team be more confident when testing large data sets and large database changes just before deployment? \u00a0Adoption of agile database development and testing techniques can create this confidence, but only if they are adopted correctly.<\/p>\n<h3>What agile brings to the table<\/h3>\n<p>Agile methodologies like Scrum and Kanban have two important impacts on a testing team:<\/p>\n<ul>\n<li>Shorter iterations of development (2-3 weeks) mean more frequent testing cycles, ideally on a more limited set of features.<\/li>\n<li>XP practices like Test Driven Development (TDD) mean that developers should have already tested and verified much of their code before sending it to a testing team.<\/li>\n<\/ul>\n<p>Both of these impacts can be very beneficial to testing teams. \u00a0The shorter development iterations (or \u201csprints\u201d) mean that the testing team has less new functionality to test at once. \u00a0Ideally you are only testing the new features being developed in that iteration.<\/p>\n<p>Full regression testing is still good practice, but the shortened release cycle will not allow you to fully regression test the system manually. \u00a0You will need to invest the time to automate regression testing so that you can focus on testing the new functionality.<\/p>\n<p>However, you don\u2019t have to do the testing alone anymore. \u00a0TDD means the developers should have already written many automated tests for you, and these automated tests should be running on a continuous integration server every time code is checked in.<\/p>\n<h3>How agile complicates traditional testing<\/h3>\n<p>In many traditional projects, the developers work on chunks of functionality for weeks at a time without integrating their code with each other. \u00a0Then they go through an integration phase, where they spend a lot of time getting their code to work together, and only then do they turn it over to testing.<\/p>\n<p>In a way this traditional integration cycle is convenient for testers. \u00a0You only have to do your testing once, and you don\u2019t have to start until developers get through the pain of integration.<\/p>\n<p>In agile teams however, the developers should be integrating code daily. \u00a0Each day they all check their code into a repository, and a continuous integration server grabs that code and runs their automated tests against it. \u00a0Instead of a painful integration phase where developers spend days trying to reconcile their separate changes, the merging and integration are done daily in small chunks that are more easily accomplished.<\/p>\n<p>This means that a testing team must be ready to constantly test the system, following right behind the development team. \u00a0Regression testing needs to be automated and efficient so that testers can focus on exploratory testing each day.<\/p>\n<p>From a database perspective, the frequent changes by developers also mean frequent database changes. \u00a0Table columns will be added and dropped as the database design evolves, and test data will need to be changed and repopulated easily to support the automated testing. \u00a0These frequent database changes need to be easily applied to multiple environments so that the testing team can easily synch their test database with a particular version of the software being developed.<\/p>\n<h3>Agile Database Testing lifecycle<\/h3>\n<p>First, let\u2019s consider the simpler scenario. \u00a0Assume that your test database is already up to date and in synch with the software under test. \u00a0How do automated tests manage their test data? \u00a0Consider the following scenario where one test causes a problem in another test:<br \/>\n<img loading=\"lazy\" class=\"alignleft\" src=\"https:\/\/lh5.googleusercontent.com\/PhYw5JwRTyO04EEA7BwPxU9Jl5HsbuucATctLxO8MFlE4jSloGH9V2F4-VRsBCIeVgZrHp3a5RdrAztD6vn7UUFIMow3j_ReIhQGoW0d2lab9JCErSo\" alt=\"\" width=\"NaN\" height=\"NaN\" \/><br \/>\nFigure 1: \u00a0Dependent tests causes failure when the data changes<\/p>\n<p>The way to handle this is by creating the test data you need for each test. \u00a0While the terminology will change in different languages, the basic steps always follow the same concepts of Setup, Test, and Teardown.<br \/>\n<img loading=\"lazy\" class=\"alignleft\" src=\"https:\/\/lh5.googleusercontent.com\/-o5hf3Ww2jz8sTlVswUFARdNJPK3cI-2NU9mrOVqF3vTCI7tyXi2Is52aMM49fUxr5E_wE5GBJUkFQ8Ywp8OOiBJBRw_ET4yGnaygOZAWDiisbuBonk\" alt=\"\" width=\"NaN\" height=\"NaN\" \/><br \/>\nFigure 2: \u00a0Independent tests will pass since data changes are unique to the test<\/p>\n<p>In this case, both tests will execute successfully because there is no data dependency between them. \u00a0Following this pattern allows you to build tests that adhere to the FIRST properties: \u00a0Fast, Isolated, Repeatable, Self-verifying, and Timely.<\/p>\n<h3>Agile Database Development lifecycle<\/h3>\n<p>Now that we understand how our tests are written against a changing data set, let\u2019s consider how we keep our database in synch with the code we are testing. \u00a0In a traditional testing environment this can be difficult, because it involves multiple people and likely time delays.<br \/>\n<img loading=\"lazy\" src=\"https:\/\/lh5.googleusercontent.com\/QeJ_aI81sdQBmxzPCqWgt-Uy5h0_XrTHBgwrsh4kg2q8ssXEx9O_5cFb2IKxOhLph1pibppL0QbiS8Ui6AQzEV0ucod9h_mnF7ULmc2Qhc5Ybznpn00\" alt=\"\" width=\"486px;\" height=\"237px;\" \/><br \/>\nFigure 3: Database testing with multiple handoffs. \u00a0Handoffs are indicated by the relay runners, and each can introduce an unknown delay of hours to the testing process.<\/p>\n<p>Instead, if the developer scripts out the database changes that are needed both to add their change and to remove their change, then we can automate this process using agile development tools and cut out some of the manual hand offs.<br \/>\n<img loading=\"lazy\" src=\"https:\/\/lh5.googleusercontent.com\/gO47WjJTVd3BHrG262p5FoDkWZyPiKZwick_ZdSDIONhVNMMl46rmJNhdSVw05k-qKtkcrD9eky_R8d6X4t3D4City0wQdBmR5Kb_I5ZYUzobmYyXL4\" alt=\"\" width=\"342px;\" height=\"229px;\" \/><br \/>\nFigure 4: \u00a0Database migrations can be run automatically using scripts written by the developers, which make it easy for the testing team to switch the database to a version which matches the software being deployed.<\/p>\n<p>The terminology varies depending on the language you are using, but we\u2019ll use the term \u201cmigrations\u201d, which is most familiar to Ruby on Rails developers.<\/p>\n<p>A migration is a simple script that the developer writes. \u00a0This script has two parts:<\/p>\n<p>1) Up migration: \u00a0Makes the change needed for their new code<br \/>\n2) Down migration: \u00a0Reverses the change needed if you are going back to the previous version<\/p>\n<p>For example, if we write a migration that adds a new table, it might look like this:<br \/>\n____________________________<br \/>\nVersion 1:<br \/>\nUp:<br \/>\ncreate table my_users (name varchar(100), username varchar(100))<br \/>\nDown:<br \/>\ndrop table my_users<br \/>\n____________________________<\/p>\n<p>After working with version 1 of the database for a while, we might realize that the my_users table needs to store birthday for each user. \u00a0A migration for version 2 might look like:<br \/>\n____________________________<br \/>\nVersion 2:<br \/>\nUp:<br \/>\nalter table my_users add birthday datetime<br \/>\nDown:<br \/>\nalter table my_users drop column birthday<br \/>\n____________________________<\/p>\n<p>Note that in both cases, the down migration removes the changes added in the up migration. \u00a0Ideally they should be mirror images of each other.<\/p>\n<p>You can also use the migration scripts for populating table data that will consistent across all tests, and that will exist in your production system. \u00a0For example, you might want to create a migration that populates a look up table of country names. \u00a0But test data should be created and removed in the \u201csetup\u201d and \u201cteardown\u201d steps before and after each test, rather than in migrations.<\/p>\n<p>Think of the migrations as the place you will write all database changes that ultimately go to production, and the setup\/teardown as the place where you handle all database data specific to a particular test.<\/p>\n<h3>Tools that support Agile Database Testing<\/h3>\n<p>This may sound hard to implement, but fortunately there are a number of open source tools that support agile database development and testing.<\/p>\n<p>If you use Ruby on Rails or Grails, then the concept of migrations and fixtures (the setup\/teardown) is already built in for you.<\/p>\n<p>For other platforms you can look into tools like dbunit and dbmaintain. \u00a0Both are free open source tools with good examples and communities.<\/p>\n<h3>Challenges to adoption<\/h3>\n<p>Learning the tool sets that support these techniques can be a challenge to adoption, particularly for teams who have never done any agile database development before. \u00a0For those teams, an outside coach may be beneficial to help the team see how they need to change their coding practices.<\/p>\n<p>Most of the challenges you will face are process oriented, rather than technical. \u00a0For example, if your team currently shares one development database across all developers, then you probably already know that it can be a pain for the development team when developers are making frequent database changes. \u00a0But once you get up and running with agile database techniques, it will be easier for developers to each have and maintain their own database, and then their changes won\u2019t immediately affect everyone else (though it is still important that they check in their database changes daily).<\/p>\n<p>Environments with a very strict database security policy or build processes will also have some trouble adopting these techniques. \u00a0However, this is an argument for loosening security policies for development environments, or automating build processes. \u00a0The efficiency benefits of these techniques outweigh the initial work to support them.<\/p>\n<p>Finally, you can see that these techniques will give developers more power than some database administrators are comfortable with. \u00a0This is a reality of efficient development and cannot be avoided. \u00a0For database administrators who are nervous about this, they will need to change their role to one of code reviewer and architect, rather than hands on script writing. \u00a0Hopefully they will recognize this as a blessing that allows them to focus on other issues and not worry as much about day to day maintenance of development and testing environments.<\/p>\n<h3>Conclusion<\/h3>\n<p>Testing database driven applications is much easier when all you have to say is \u201cGo run version 342 of the database, execute this automated test, and you\u2019ll see what I mean.\u201d<\/p>\n<p>There are no concerns over what version of the database tables or procedures are being used, or what test data will recreate the problem. \u00a0With a few simple commands and the right agile database driven techniques, you will be able to confidently and efficiently identify the source of bugs. \u00a0Or at least easily recreate the environments to prove they exist.<\/p>\n<h3>About the author<\/h3>\n<p>Arin Sime is an Agile Coach with AgilityFeat. \u00a0Arin is based in Virginia in the United States, but able to work internationally. \u00a0He is a Certified Scrummaster and has spoken at several Agile conferences. Arin holds a Masters degree in Management of Information Technology from the University of Virginia\u2019s McIntire School of Commerce. \u00a0You can reach him at <a href=\"mailto:Arin@AgilityFeat.com\">Arin@AgilityFeat.com<\/a> or on twitter @ArinSime. \u00a0He will be speaking at the upcoming XP2011 conference in Madrid on range estimation techniques in agile.<\/p>\n<h3>References<\/h3>\n<p><a href=\"http:\/\/www.agiledata.org\/\">http:\/\/www.agiledata.org\/<\/a><br \/>\nExcellent information and books by Scott Ambler<\/p>\n<p><a href=\"http:\/\/michaelbaylon.wordpress.com\/2010\/10\/08\/agile-database-development-101\/\">http:\/\/michaelbaylon.wordpress.com\/2010\/10\/08\/agile-database-development-101\/<\/a><br \/>\nProvides an indepth overview of agile database development techniques<\/p>\n<p><a href=\"http:\/\/agileinaflash.blogspot.com\/2009\/02\/first.html\">http:\/\/agileinaflash.blogspot.com\/2009\/02\/first.html<\/a><br \/>\nAn explanation of the FIRST properties of unit tests by Brett Schuchert and Tim Ottinger<\/p>","protected":false},"excerpt":{"rendered":"<p>Last month the QANews newsletter by Sogeti was released, and I was pleased to have an article I wrote on Agile Database Testing published in it.\u00a0 I&#8217;ve reproduced the article below, and you can also see the original article here. Agile Database Testing Testing database changes Testing is rarely easy, and is more often thankless. [&hellip;]<\/p>","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":""},"categories":[4],"tags":[5,24,25,26,27],"jetpack_featured_media_url":"","yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v15.7 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Agile Database Testing - AgilityFeat Panama Software Test Center<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/agilityfeatpanama.com\/en\/blog\/2011\/04\/agile-database-testing\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Agile Database Testing - AgilityFeat Panama Software Test Center\" \/>\n<meta property=\"og:description\" content=\"Last month the QANews newsletter by Sogeti was released, and I was pleased to have an article I wrote on Agile Database Testing published in it.\u00a0 I&#8217;ve reproduced the article below, and you can also see the original article here. Agile Database Testing Testing database changes Testing is rarely easy, and is more often thankless. [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/agilityfeatpanama.com\/en\/blog\/2011\/04\/agile-database-testing\/\" \/>\n<meta property=\"og:site_name\" content=\"AgilityFeat Panama Software Test Center\" \/>\n<meta property=\"article:published_time\" content=\"2011-04-08T13:56:57+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/lh5.googleusercontent.com\/PhYw5JwRTyO04EEA7BwPxU9Jl5HsbuucATctLxO8MFlE4jSloGH9V2F4-VRsBCIeVgZrHp3a5RdrAztD6vn7UUFIMow3j_ReIhQGoW0d2lab9JCErSo\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\">\n\t<meta name=\"twitter:data1\" content=\"9 minutes\">\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/34.200.113.64\/#website\",\"url\":\"https:\/\/34.200.113.64\/\",\"name\":\"AgilityFeat Panama Software Test Center\",\"description\":\"AgilityFeat Panama offers customized, multilevel web and mobile software testing for a variety of industries.\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/34.200.113.64\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/agilityfeatpanama.com\/en\/blog\/2011\/04\/agile-database-testing\/#primaryimage\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/lh5.googleusercontent.com\/PhYw5JwRTyO04EEA7BwPxU9Jl5HsbuucATctLxO8MFlE4jSloGH9V2F4-VRsBCIeVgZrHp3a5RdrAztD6vn7UUFIMow3j_ReIhQGoW0d2lab9JCErSo\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/agilityfeatpanama.com\/en\/blog\/2011\/04\/agile-database-testing\/#webpage\",\"url\":\"https:\/\/agilityfeatpanama.com\/en\/blog\/2011\/04\/agile-database-testing\/\",\"name\":\"Agile Database Testing - AgilityFeat Panama Software Test Center\",\"isPartOf\":{\"@id\":\"https:\/\/34.200.113.64\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/agilityfeatpanama.com\/en\/blog\/2011\/04\/agile-database-testing\/#primaryimage\"},\"datePublished\":\"2011-04-08T13:56:57+00:00\",\"dateModified\":\"2011-04-08T13:56:57+00:00\",\"author\":{\"@id\":\"https:\/\/34.200.113.64\/#\/schema\/person\/c8d60d597071526db386b2b8a4afac64\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/agilityfeatpanama.com\/en\/blog\/2011\/04\/agile-database-testing\/\"]}]},{\"@type\":\"Person\",\"@id\":\"https:\/\/34.200.113.64\/#\/schema\/person\/c8d60d597071526db386b2b8a4afac64\",\"name\":\"arin\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/34.200.113.64\/#personlogo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/cc498e210512c707ed769986dd745896?s=96&d=mm&r=g\",\"caption\":\"arin\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","_links":{"self":[{"href":"https:\/\/agilityfeatpanama.com\/en\/wp-json\/wp\/v2\/posts\/1096"}],"collection":[{"href":"https:\/\/agilityfeatpanama.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/agilityfeatpanama.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/agilityfeatpanama.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/agilityfeatpanama.com\/en\/wp-json\/wp\/v2\/comments?post=1096"}],"version-history":[{"count":0,"href":"https:\/\/agilityfeatpanama.com\/en\/wp-json\/wp\/v2\/posts\/1096\/revisions"}],"wp:attachment":[{"href":"https:\/\/agilityfeatpanama.com\/en\/wp-json\/wp\/v2\/media?parent=1096"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/agilityfeatpanama.com\/en\/wp-json\/wp\/v2\/categories?post=1096"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/agilityfeatpanama.com\/en\/wp-json\/wp\/v2\/tags?post=1096"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}