<p>Does anyone have some good hints for writing test code for database-backend development where there is a heavy dependency on state?</p>

<p>Specifically, I want to write tests for code that retrieve records from the database, but the answers will depend on the data in the database (which may change over time).</p>

<p>Do people usually make a separate development system with a 'frozen' database so that any given function should always return the exact same result set?</p>

<p>I am quite sure this is not a new issue, so I would be very interested to learn from other people's experience.</p>

<p>Are there good articles out there that discuss this issue of web-based development in general?</p>

<p>I usually write PHP code, but I would expect all of these issues are largely language and framework agnostic.</p>

[{'answer_id': 2918, 'author': 'Mike Stone', 'author_id': 122, 'author_profile': 'https://Stackoverflow.com/users/122', 'pm_score': 4, 'selected': True, 'text': '<p>You should look into DBUnit, or try to find a PHP equivalent (there must be one out there).  You can use it to prepare the database with a specific set of data which represents your test data, and thus each test will no longer depend on the database and some existing state.  This way, each test is self contained and will not break during further database usage.</p>\n\n<p>Update: A quick google search showed a <a href="http://www.ds-o.com/archives/63-PHPUnit-Database-Extension-DBUnit-Port.html" rel="noreferrer">DB unit extension</a> for PHPUnit.</p>\n'}, {'answer_id': 2919, 'author': 'Teifion', 'author_id': 1384652, 'author_profile': 'https://Stackoverflow.com/users/1384652', 'pm_score': 1, 'selected': False, 'text': '<p>I have the exact same problem with my work and I find that the best idea is to have a PHP script to re-create the database and then a separate script where I throw crazy data at it to see if it breaks it.</p>\n\n<p>I have not ever used any Unit testing or suchlike so cannot say if it works or not sorry.</p>\n'}, {'answer_id': 2923, 'author': 'davewasthere', 'author_id': 376, 'author_profile': 'https://Stackoverflow.com/users/376', 'pm_score': 1, 'selected': False, 'text': "<p>If you can setup the database with a known quantity prior to running the tests and tear down at the end, then you'll know what data you are working with.</p>\n\n<p>Then you can use something like Selenium to easily test from your UI (assuming web-based here, but there are a lot of UI testing tools out there for other UI-flavours) and detect the presence of certain records pulled back from the database.</p>\n\n<p>It's definitely worth setting up either a test version of the database - or make your test scripts populate the database with known data as part of the tests.</p>\n"}, {'answer_id': 3074, 'author': 'Chris Farmer', 'author_id': 404, 'author_profile': 'https://Stackoverflow.com/users/404', 'pm_score': 2, 'selected': False, 'text': "<p>If you're mostly concerned with data layer testing, you might want to check out this book:  <strong>xUnit Test Patterns: Refactoring Test Code</strong>.  I was always unsure about it myself, but this book does a great job to help enumerate the concerns like performance, reproducibility, etc.</p>\n"}, {'answer_id': 3308, 'author': 'John', 'author_id': 33, 'author_profile': 'https://Stackoverflow.com/users/33', 'pm_score': 1, 'selected': False, 'text': '<p>You could try <a href="http://selenium.openqa.org/" rel="nofollow noreferrer">http://selenium.openqa.org/</a> it is more for GUI testing rather than a data layer testing application but does record your actions which then can be played back to automate tests across different platforms.</p>\n'}, {'answer_id': 3388, 'author': 'Chris Roberts', 'author_id': 475, 'author_profile': 'https://Stackoverflow.com/users/475', 'pm_score': 2, 'selected': False, 'text': "<p>I guess it depends what database you're using, but Red Gate (www.red-gate.com) make a tool called SQL Data Generator.  This can be configured to fill your database with sensible looking test data.  You can also tell it to always use the same seed in its random number generator so your 'random' data is the same every time.</p>\n\n<p>You can then write your unit tests to make use of this reliable, repeatable data.</p>\n\n<p>As for testing the web side of things, I'm currently looking into Selenium (selenium.openqa.org).  This appears to be a cross-browser capable test suite which will help you test functionality.  However, as with all of these web site test tools, there's no real way to test how well these things <em>look</em> in all of the browsers without casting a human eye over them!</p>\n"}, {'answer_id': 7780, 'author': 'Nicholas Trandem', 'author_id': 765, 'author_profile': 'https://Stackoverflow.com/users/765', 'pm_score': 1, 'selected': False, 'text': "<p>Here's my strategy (I use JUnit, but I'm sure there's a way to do the equivalent in PHP):</p>\n\n<p>I have a method that runs before all of the Unit Tests for a specific DAO class. It puts the dev database into a known state (adds all test data, etc.). As I run tests, I keep track of any data added to the known state. This data is cleaned up at the end of each test. After all the tests for the class have run, another method removes all the test data in the dev database, leaving it in the state it was in before the tests were run. It's a bit of work to do all this, but I usually write the methods in a DBTestCommon class where all of my DAO test classes can get to them.</p>\n"}, {'answer_id': 16710, 'author': 'Peter Stuifzand', 'author_id': 1633, 'author_profile': 'https://Stackoverflow.com/users/1633', 'pm_score': 1, 'selected': False, 'text': "<p>I would propose to use three databases. One production database, one development database (filled with some meaningful data for each developer) and one testing database (with empty tables and maybe a few rows that are always needed).</p>\n\n<p>A way to test database code is:</p>\n\n<ol>\n<li>Insert a few rows (using SQL) to initialize state</li>\n<li>Run the function that you want to test</li>\n<li>Compare expected with actual results. Here you could use your normal unit testing framework</li>\n<li>Clean up the rows that were changed (so the next run won't see the previous run)</li>\n</ol>\n\n<p>The cleanup could be done in a standard way (of course, only in the testing database) with <code>DELETE * FROM table</code>.</p>\n"}, {'answer_id': 53798, 'author': 'Juraj', 'author_id': 1128722, 'author_profile': 'https://Stackoverflow.com/users/1128722', 'pm_score': 1, 'selected': False, 'text': "<p>In general I agree with Peter but for creating and deleting of test data I wouldn't use SQL directly. I prefer to use some CRUD API that is used in product to create data as similar to production as possible...</p>\n"}, {'answer_id': 53838, 'author': 'user5545', 'author_id': 5545, 'author_profile': 'https://Stackoverflow.com/users/5545', 'pm_score': 2, 'selected': False, 'text': '<p>We use an in-memory database  (hsql : <a href="http://hsqldb.org/" rel="nofollow noreferrer">http://hsqldb.org/</a>). Hibernate (<a href="http://www.hibernate.org/" rel="nofollow noreferrer">http://www.hibernate.org/</a>) makes it easy for us to point our unit tests at the testing db, with the added bonus that they run as quick as lightning..</p>\n'}]
['https://Stackoverflow.com/questions/2913', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/277/']