Software testing enables development teams to maintain the quality of a software system while it evolves. The database manipulation code requires special attention in this context. However, it is often neglected and suffers from software maintenance problems. In this paper, we study the current state-of-the-practice in testing database manipulation code. We first analysed the tests of 72 open-source projects to gain insight into the coverage of database access code. The database was poorly tested: 46% of the projects did not cover with tests half of their database access methods, and 33% did not cover the database code at all. This poor coverage motivated us to study developers’ challenges and best practices. (i) First, we analysed 532 questions on Stack Exchange sites and deduced a taxonomy of issues. Developers mostly looked for general best practices to test database access code. Their technical questions were related to database management, mocking, parallelisation, or framework/tool usage. (ii) Next, we examined the answers to these questions. We manually labelled 598 answers to 255 questions. We distinguished 363 solutions and organised them in a taxonomy of best practices. Most of the suggestions considered the testing environment and recommended various tools or configurations. The second largest category was database management, where many addressed database initialisation and clean-up between tests. Other categories pertained to code structure or design, concepts, performance, processes, test characteristics, test code, and mocking. We illustrate the two taxonomies through intriguing examples.

Code smells indicate software design problems that harm software quality. Data-intensive systems that frequently access databases often suffer from SQL code smells besides the traditional smells. While there have been extensive studies on traditional code smells, recently, there has been a growing interest in SQL code smells. In this paper, we conduct an empirical study to investigate the prevalence and evolution of SQL code smells in open-source, data-intensive systems. We collected 150 projects and examined both traditional and SQL code smells in these projects. Our investigation delivers several important findings. First, SQL code smells are indeed prevalent in data-intensive software systems. Second, SQL code smells have a weak co-occurrence with traditional code smells. Third, SQL code smells have a weaker association with bugs than that of traditional code smells. Fourth, SQL code smells are more likely to be introduced at the beginning of the project lifetime and likely to be left in the code without a fix, compared to traditional code smells. Overall, our results show that SQL code smells are indeed prevalent and persistent in the studied data-intensive software systems. Developers should be aware of these smells and consider detecting and refactoring SQL code smells and traditional code smells separately, using dedicated tools.