database 如何进行数据库单元测试？

How to do database unit testing?

提问by juur

I have heard that when developing application which uses a database you should do database unit testing. What are the best practices in database unit testing? What are the primary concerns when doing db unit testing and how to do it "right"?

我听说在开发使用数据库的应用程序时，您应该进行数据库单元测试。数据库单元测试的最佳实践是什么？进行数据库单元测试时主要关注什么以及如何“正确”进行？

采纳答案by Pascal Thivent

What are the best practices in database unit testing?

数据库单元测试的最佳实践是什么？

The DbUnitframework (a testing framework allowing to put a database in a know state and to perform assertion against its content) has a page listing database testing best practicesthat, to my experience, are true.

该DbUnit的框架（一个测试框架，允许把一个数据库中知悉的国家，并针对其内容执行断言）有一个网页列表数据库测试的最佳做法是，以我的经验，都是如此。

What are the primary concerns when doing db unit testing

进行db单元测试时主要关注什么

• Creating an up to date schema, managing schema changes
• Setting up data (reference data, test data) and maintainingtest data
• Keeping tests independent
• Allowing developers to work concurrently
• Speed (tests involving database are typically slower and will make your whole build take more time)

• 创建最新架构，管理架构更改
• 设置数据（参考数据、测试数据）和维护测试数据
• 保持测试独立
• 允许开发人员同时工作
• 速度（涉及数据库的测试通常较慢，并且会使您的整个构建花费更多时间）

and how to do it "right"?

以及如何做到“正确”？

As hinted, follow known good practices and use dedicated tools/frameworks:

正如所暗示的，遵循已知的良好实践并使用专用工具/框架：

• Prefer in memory database if possible (for speed)
• Use one schema per developer is a must (to allow concurrent work)
• Use a "database migration" tool (à la RoR) to manage schema changes and update a schema to the ultimate version
• Build or use a test harness allowing to put the database in a known state before each test and to perform asserts against the data after the execution (or to run tests inside a transaction that you rollback at the end of the test).

• 如果可能，最好在内存数据库中（为了速度）
• 每个开发人员必须使用一个模式（以允许并发工作）
• 使用“数据库迁移”工具（à la RoR）来管理架构更改并将架构更新到最终版本
• 构建或使用测试工具，允许在每次测试之前将数据库置于已知状态，并在执行后对数据执行断言（或在测试结束时回滚的事务中运行测试）。

回答by MiWa

A list of items that should be reviewed and considered when staring with database unit testing

开始数据库单元测试时应和考虑的项目列表

• Each tester needs a separate database, in order to avoid interfering with activities of other tester/developer
• To have an easy way of creating a database to be tested (this is related to having a SQL Server database under version control). This is specifically useful when trying to find what went wrong if some tests fail
• Focus on specific areas and creating tests for a single module instead of covering all at once. Adding tests granularly is a good way to be efficient
• Make sure to provide as many details as possible when a test fails, to allow easier debugging
• Use one and the same test data for all tests

• 每个测试人员需要一个单独的数据库，以避免干扰其他测试人员/开发人员的活动
• 有一个简单的方法来创建一个要测试的数据库（这与在版本控制下拥有一个 SQL Server 数据库有关）。如果某些测试失败，这在尝试查找问题时特别有用
• 专注于特定领域并为单个模块创建测试，而不是一次覆盖所有模块。细粒度地添加测试是提高效率的好方法
• 确保在测试失败时提供尽可能多的详细信息，以便于调试
• 对所有测试使用相同的测试数据

If test are implemented using tSQLt framework, the unit testing process could be complicated when dealing with a lot of databases from multiple SQL Server instances. In order to maintain, execute and manage unit tests directly from SQL Server Management Studio, ApexSQL Unit Testcan be used as a solution

如果使用 tSQLt 框架实现测试，则在处理来自多个 SQL Server 实例的大量数据库时，单元测试过程可能会很复杂。为了直接从 SQL Server Management Studio 维护、执行和管理单元测试，ApexSQL 单元测试可以用作解决方案

回答by Abe Miessler

Take a look at this link. It goes over some of the basics for creating unit testing stored procs in SQL Server as well as the different types of unit tests and when you should use them. I'm not sure what DBMS you are using but obviously this article is geared towards SQL Server.

看看这个链接。它介绍了在 SQL Server 中创建单元测试存储过程的一些基础知识，以及不同类型的单元测试以及何时应该使用它们。我不确定您使用的是什么 DBMS，但显然这篇文章是针对 SQL Server 的。

Stolen from the article:

从文章中窃取：

Feature Tests

The first and likely most prevalent class of database unit test is a feature test. In my mind, feature tests test the core features—or APIs, if you will—of your database from the database consumer's perspective. Testing a database's programmability objects is the mainline scenario here. So, testing all the stored procedures, functions, and triggers inside your database constitute feature tests in my mind. To test a stored procedure, you would execute the stored procedure and verify that either the expected results were returned or the appropriate behavior occurred. However, you can test more than just these types of objects. You can imagine wanting to ensure that a view, for example, return the appropriate calculation from a computed column. As you can see, the possibilities in this realm are large.

Schema Tests

One of the most critical aspects of a database is its schema, and testing to ensure that it behaves as expected is another important class of database unit tests. Here, you will often want to ensure that a view returns the expected set of columns of the appropriate data type in the appropriate order. You might want to ensure that your database does, in fact, contain the 1,000 tables that you expect.

Security Tests

In today's day and age, the security of the data that is stored within the database is critical. Thus, another important class of database unit tests are those that test the database security. Here, you will want to ensure that particular users exist in your database and that they are assigned the appropriate permissions. You will often want to create negative tests that attempt to retrieve data from restricted tables or views and ensure that the access is appropriately denied.

Stock-Data Tests

Many databases contain stock data, or seed data. This data changes infrequently and is often used as lookup data for applications or end users. ZIP codes and their associated cities and states are great examples of this kind of data. Therefore, it is useful to create tests to ensure that your stock data does, in fact, exist in your database.

功能测试

第一类也是最流行的数据库单元测试是特性测试。在我看来，功能测试从数据库使用者的角度测试数据库的核心功能或 API，如果您愿意的话。测试数据库的可编程性对象是这里的主线场景。因此，在我看来，测试数据库中的所有存储过程、函数和触发器构成了功能测试。要测试存储过程，您将执行该存储过程并验证是否返回了预期结果或是否发生了适当的行为。但是，您可以测试的不仅仅是这些类型的对象。例如，您可以想象要确保视图从计算列返回适当的计算。如你看到的，

模式测试

数据库最关键的方面之一是其架构，而确保其按预期运行的测试是另一类重要的数据库单元测试。在这里，您通常希望确保视图以适当的顺序返回适当数据类型的预期列集。您可能希望确保您的数据库实际上包含您期望的 1,000 个表。

安全测试

在当今时代，存储在数据库中的数据的安全性至关重要。因此，另一类重要的数据库单元测试是那些测试数据库安全性的测试。在这里，您需要确保特定用户存在于您的数据库中，并为他们分配了适当的权限。您通常希望创建否定测试，尝试从受限制的表或视图中检索数据并确保访问被适当拒绝。

股票数据测试

许多数据库包含股票数据或种子数据。此数据很少更改，通常用作应用程序或最终用户的查找数据。邮政编码及其相关城市和州是此类数据的绝佳示例。因此，创建测试以确保您的股票数据确实存在于您的数据库中非常有用。

回答by Federico Razzoli

I'm glad you asked about Unit Testing, and not testing in general.

我很高兴你问的是单元测试，而不是一般的测试。

Databases have many features that need to be tested. Some examples:

数据库有许多需要测试的功能。一些例子：

• Data Types/Size/Character sets (try inserting a swedish name, or long urls or numbers from the real worlds, and see if your column definitions are ok)
• Triggers
• Contraints (foreign keys, uniqueness...)
• Views (check that data is correctly included/excluded/transformed)
• Stored Procedures
• UDFs
• Permissions
• ...

• 数据类型/大小/字符集（尝试插入瑞典语名称，或现实世界中的长网址或数字，看看您的列定义是否正确）
• 触发器
• 限制条件（外键、唯一性...）
• 视图（检查数据是否正确包含/排除/转换）
• 存储过程
• UDF
• 权限
• ...

This is useful not only when you change something in your database, but also when you upgrade your dbms, or change something in your settings.

这不仅在更改数据库中的某些内容时有用，而且在升级 dbms 或更改设置中的某些内容时也很有用。

Generally, Integration Testing is done. This means that a Test Suite in a programming language like PHP or Java is created, and the tests issue some queries. But if something fails, or there are some exceptions, it's harder to understand the problem, for 2 reasons:

通常，集成测试已完成。这意味着使用 PHP 或 Java 等编程语言创建测试套件，并且测试会发出一些查询。但是，如果某些事情失败了，或者有一些例外，就很难理解问题，原因有二：

• The problem could be in your PHP code, or in PHP configuration, or in the network, or...
• The SQL statements are harder to read and modify, if they are embedded in another programming language.

• 问题可能出在您的 PHP 代码中，或者出在 PHP 配置中，或者出在网络中，或者……
• 如果 SQL 语句嵌入到另一种编程语言中，则它们更难阅读和修改。

So, in my opinion, for complex databases you need to use a Unit Testing framework which is written in SQL (using stored procedures and tables). You have to choose it carefully, because that kind of tools is not widely used (and thus not widely tested). For example, if you use MySQL I know these tools:

因此，在我看来，对于复杂的数据库，您需要使用用 SQL 编写的单元测试框架（使用存储过程和表）。你必须谨慎选择它，因为这种工具没有被广泛使用（因此没有被广泛测试）。例如，如果您使用 MySQL，我知道这些工具：

• STK/Unit http://stk.wikidot.com/stk-unit
• utMySQL http://utmysql.sourceforge.net/

• STK/单位http://stk.wikidot.com/stk-unit
• utMySQL http://utmysql.sourceforge.net/

回答by Adam Butler

I use junit/nunit/etc and code up database unit tests with java or c#. These can then run on an integration server perhaps using a separate schema to the test database.

我使用 junit/nunit/etc 并使用 java 或 c# 编写数据库单元测试。然后，这些可以在集成服务器上运行，可能使用与测试数据库不同的模式。

The latest oracle sql developer comes with a built in unit testing framework. I had a look into this but would NOTuse it. It uses a GUI to create and run tests and stores all the tests in the database so not so easy to put test cases under version control. There are probably other testing frameworks out there I imagine they might be specific to your database.

最新的 oracle sql 开发人员带有内置的单元测试框架。我看过这个，但不会使用它。它使用 GUI 来创建和运行测试并将所有测试存储在数据库中，因此不太容易将测试用例置于版本控制之下。可能还有其他测试框架，我想它们可能特定于您的数据库。

Good practices are similar to regular unit tests:

良好的实践类似于常规的单元测试：

• put the tests under source control
• make tests that run fast - don't test too much at once
• make your tests reproducible

• 将测试置于源代码控制之下
• 进行快速运行的测试 - 不要一次测试太多
• 使您的测试具有可重复性

回答by Oleksandr

Take a look on DBTestDriven framework. It works great for us. Download it from GitHub or their website.

看看 DBTestDriven 框架。它对我们很有用。从 GitHub 或他们的网站下载。

回答by cchantep

As for JVM development, unit tests can benefit from JDBC abstraction: as soon as you know which JDBC data are raised by DB access, these JDBC data can be 'replayed'.

对于 JVM 开发，单元测试可以从 JDBC 抽象中受益：只要您知道 DB 访问引发了哪些 JDBC 数据，就可以“重放”这些 JDBC 数据。

Thus DB access case can be 'reproduced' for testing, without the target DB: no test/data isolation complexity, ease continuous integration.

因此，可以在没有目标数据库的情况下“复制”数据库访问情况以进行测试：没有测试/数据隔离复杂性，易于持续集成。

My framework Acolyte is an helpful framework in this way (including studio GUI tool to 'record' DB result): https://github.com/cchantep/acolyte

我的框架 Acolyte 在这方面是一个有用的框架（包括用于“记录”数据库结果的工作室 GUI 工具）：https: //github.com/cchantep/acolyte