database 如何使用数据库查询对对象进行单元测试

How to unit test an object with database queries

提问by Teifion

I've heard that unit testing is "totally awesome", "really cool" and "all manner of good things" but 70% or more of my files involve database access (some read and some write) and I'm not sure how to write a unit test for these files.

我听说单元测试“非常棒”、“非常酷”和“各种各样的好东西”，但我 70% 或更多的文件涉及数据库访问（一些读取和一些写入），我不知道如何为这些文件编写单元测试。

I'm using PHP and Python but I think it's a question that applies to most/all languages that use database access.

我正在使用 PHP 和 Python，但我认为这是一个适用于大多数/所有使用数据库访问的语言的问题。

采纳答案by Doug R

I would suggest mocking out your calls to the database. Mocks are basically objects that look like the object you are trying to call a method on, in the sense that they have the same properties, methods, etc. available to caller. But instead of performing whatever action they are programmed to do when a particular method is called, it skips that altogether, and just returns a result. That result is typically defined by you ahead of time.

我建议模拟您对数据库的调用。模拟基本上是看起来像您试图调用方法的对象的对象，从某种意义上说，它们具有相同的属性、方法等可供调用者使用。但是，当调用特定方法时，它不会执行它们被编程为执行的任何操作，而是完全跳过该操作，只返回一个结果。该结果通常由您提前定义。

In order to set up your objects for mocking, you probably need to use some sort of inversion of control/ dependency injection pattern, as in the following pseudo-code:

为了设置用于模拟的对象，您可能需要使用某种控制/依赖注入模式的反转，如下面的伪代码所示：

class Bar
{
    private FooDataProvider _dataProvider;

    public instantiate(FooDataProvider dataProvider) {
        _dataProvider = dataProvider;
    }

    public getAllFoos() {
        // instead of calling Foo.GetAll() here, we are introducing an extra layer of abstraction
        return _dataProvider.GetAllFoos();
    }
}

class FooDataProvider
{
    public Foo[] GetAllFoos() {
        return Foo.GetAll();
    }
}

Now in your unit test, you create a mock of FooDataProvider, which allows you to call the method GetAllFoos without having to actually hit the database.

现在，在您的单元测试中，您创建了 FooDataProvider 的模拟，它允许您调用 GetAllFoos 方法而无需实际访问数据库。

class BarTests
{
    public TestGetAllFoos() {
        // here we set up our mock FooDataProvider
        mockRepository = MockingFramework.new()
        mockFooDataProvider = mockRepository.CreateMockOfType(FooDataProvider);

        // create a new array of Foo objects
        testFooArray = new Foo[] {Foo.new(), Foo.new(), Foo.new()}

        // the next statement will cause testFooArray to be returned every time we call FooDAtaProvider.GetAllFoos,
        // instead of calling to the database and returning whatever is in there
        // ExpectCallTo and Returns are methods provided by our imaginary mocking framework
        ExpectCallTo(mockFooDataProvider.GetAllFoos).Returns(testFooArray)

        // now begins our actual unit test
        testBar = new Bar(mockFooDataProvider)
        baz = testBar.GetAllFoos()

        // baz should now equal the testFooArray object we created earlier
        Assert.AreEqual(3, baz.length)
    }
}

A common mocking scenario, in a nutshell. Of course you will still probably want to unit test your actual database calls too, for which you will need to hit the database.

简而言之，一个常见的模拟场景。当然，您可能仍然希望对实际的数据库调用进行单元测试，为此您需要访问数据库。

回答by Sean Chambers

Ideally, your objects should be persistent ignorant. For instance, you should have a "data access layer", that you would make requests to, that would return objects. This way, you can leave that part out of your unit tests, or test them in isolation.

理想情况下，您的对象应该是持久无知的。例如，您应该有一个“数据访问层”，您可以向其发出请求，并返回对象。这样，您可以将该部分从单元测试中删除，或者单独测试它们。

If your objects are tightly coupled to your data layer, it is difficult to do proper unit testing. the first part of unit test, is "unit". All units should be able to be tested in isolation.

如果您的对象与数据层紧密耦合，则很难进行适当的单元测试。单元测试的第一部分是“单元”。所有单元都应该能够进行隔离测试。

In my c# projects, I use NHibernate with a completely seperate Data layer. My objects live in the core domain model and are accessed from my application layer. The application layer talks to both the data layer and the domain model layer.

在我的 c# 项目中，我使用 NHibernate 和一个完全独立的数据层。我的对象位于核心域模型中，可以从我的应用程序层访问。应用层与数据层和领域模型层进行对话。

The application layer is also sometimes called the "Business Layer".

应用层有时也称为“业务层”。

If you are using PHP, create a specific set of classes for ONLYdata access. Make sure your objects have no idea how they are persisted and wire up the two in your application classes.

如果您使用的是 PHP，请为仅数据访问创建一组特定的类。确保您的对象不知道它们是如何持久化的，并将两者连接到您的应用程序类中。

Another option would be to use mocking/stubs.

另一种选择是使用模拟/存根。

回答by BZ.

The easiest way to unit test an object with database access is using transaction scopes.

对具有数据库访问权限的对象进行单元测试的最简单方法是使用事务范围。

For example:

例如：

    [Test]
    [ExpectedException(typeof(NotFoundException))]
    public void DeleteAttendee() {

        using(TransactionScope scope = new TransactionScope()) {
            Attendee anAttendee = Attendee.Get(3);
            anAttendee.Delete();
            anAttendee.Save();

            //Try reloading. Instance should have been deleted.
            Attendee deletedAttendee = Attendee.Get(3);
        }
    }

This will revert back the state of the database, basically like a transaction rollback so you can run the test as many times as you want without any sideeffects. We've used this approach successfully in large projects. Our build does take a little long to run (15 minutes), but it is not horrible for having 1800 unit tests. Also, if build time is a concern, you can change the build process to have multiple builds, one for building src, another that fires up afterwards that handles unit tests, code analysis, packaging, etc...

这将恢复数据库的状态，基本上就像事务回滚一样，因此您可以根据需要多次运行测试而不会产生任何副作用。我们已经在大型项目中成功地使用了这种方法。我们的构建运行起来确实需要一点时间（15 分钟），但对于 1800 个单元测试来说并不可怕。此外，如果构建时间是一个问题，您可以将构建过程更改为具有多个构建，一个用于构建 src，另一个用于处理单元测试、代码分析、打包等。

回答by Alan

I can perhaps give you a taste of our experience when we began looking at unit testing our middle-tier process that included a ton of "business logic" sql operations.

当我们开始考虑对我们的中间层流程进行单元测试时，我也许可以让您体验一下我们的经验，其中包括大量“业务逻辑”sql 操作。

We first created an abstraction layer that allowed us to "slot in" any reasonable database connection (in our case, we simply supported a single ODBC-type connection).

我们首先创建了一个抽象层，允许我们“插入”任何合理的数据库连接（在我们的例子中，我们只支持单个 ODBC 类型的连接）。

Once this was in place, we were then able to do something like this in our code (we work in C++, but I'm sure you get the idea):

一旦到位，我们就可以在我们的代码中做这样的事情（我们在 C++ 中工作，但我相信你明白了）：

GetDatabase().ExecuteSQL( "INSERT INTO foo ( blah, blah )" )

GetDatabase().ExecuteSQL("INSERT INTO foo (blah, blah)")

At normal run time, GetDatabase() would return an object that fed all our sql (including queries), via ODBC directly to the database.

在正常运行时，GetDatabase() 将返回一个对象，该对象通过 ODBC 直接向数据库提供我们所有的 sql（包括查询）。

We then started looking at in-memory databases - the best by a long way seems to be SQLite. (http://www.sqlite.org/index.html). It's remarkably simple to set up and use, and allowed us subclass and override GetDatabase() to forward sql to an in-memory database that was created and destroyed for every test performed.

然后我们开始研究内存数据库——从长远来看，最好的似乎是 SQLite。（http://www.sqlite.org/index.html）。它的设置和使用非常简单，并允许我们子类化和覆盖 GetDatabase() 将 sql 转发到内存数据库，该数据库为执行的每个测试创建和销毁。

We're still in the early stages of this, but it's looking good so far, however we do have to make sure we create any tables that are required and populate them with test data - however we've reduced the workload somewhat here by creating a generic set of helper functions that can do a lot of all this for us.

我们仍处于早期阶段，但到目前为止看起来不错，但是我们必须确保创建所需的任何表并使用测试数据填充它们 - 但是我们通过创建在某种程度上减少了工作量一组通用的辅助函数，可以为我们做很多事情。

Overall, it has helped immensely with our TDD process, since making what seems like quite innocuous changes to fix certain bugs can have quite strange affects on other (difficult to detect) areas of your system - due to the very nature of sql/databases.

总的来说，它对我们的 TDD 过程有很大帮​​助，因为为了修复某些错误而进行看似非常无害的更改可能会对系统的其他（难以检测）区域产生非常奇怪的影响 - 由于 sql/数据库的本质。

Obviously, our experiences have centred around a C++ development environment, however I'm sure you could perhaps get something similar working under PHP/Python.

显然，我们的经验集中在 C++ 开发环境上，但是我相信您可能会在 PHP/Python 下获得类似的工作。

Hope this helps.

希望这可以帮助。

回答by Martin Klinke

You should mock the database access if you want to unit test your classes. After all, you don't want to test the database in a unit test. That would be an integration test.

如果你想对你的类进行单元测试，你应该模拟数据库访问。毕竟，您不想在单元测试中测试数据库。那将是一个集成测试。

Abstract the calls away and then insert a mock that just returns the expected data. If your classes don't do more than executing queries, it may not even be worth testing them, though...

将调用抽象化，然后插入一个只返回预期数据的模拟。如果你的类除了执行查询之外没有做更多的事情，那么它甚至可能不值得测试它们，但是......

回答by Chris Farmer

The book xUnit Test Patternsdescribes some ways to handle unit-testing code that hits a database. I agree with the other people who are saying that you don't want to do this because it's slow, but you gotta do it sometime, IMO. Mocking out the db connection to test higher-level stuff is a good idea, but check out this book for suggestions about things you can do to interact with the actual database.

xUnit Test Patterns一书描述了一些处理访问数据库的单元测试代码的方法。我同意其他人的看法，他们说你不想这样做，因为它很慢，但你必须在某个时候这样做，IMO。模拟数据库连接来测试更高级别的东西是一个好主意，但请查看本书以获取有关与实际数据库交互的建议。

回答by Marcin

Options you have:

您拥有的选项：

• Write a script that will wipe out database before you start unit tests, then populate db with predefined set of data and run the tests. You can also do that before every test – it'll be slow, but less error prone.

• Inject the database. (Example in pseudo-Java, but applies to all OO-languages)

  class Database {
   public Result query(String query) {... real db here ...}
  }
  
  
  class MockDatabase extends Database {
    public Result query(String query) { 
      return "mock result"; 
    }
  }
  
  class ObjectThatUsesDB {
   public ObjectThatUsesDB(Database db) { 
     this.database = db; 
   }
  }
  

  now in production you use normal database and for all tests you just inject the mock database that you can create ad hoc.

• Do not use DB at all throughout most of code (that's a bad practice anyway). Create a "database" object that instead of returning with results will return normal objects (i.e. will return Userinstead of a tuple {name: "marcin", password: "blah"}) write all your tests with ad hoc constructed realobjects and write one big test that depends on a database that makes sure this conversion works OK.

• 在开始单元测试之前编写一个脚本来清除数据库，然后用预定义的数据集填充数据库并运行测试。您也可以在每次测试之前这样做——它会很慢，但不容易出错。

• 注入数据库。（伪 Java 中的示例，但适用于所有 OO 语言）

  class Database {
   public Result query(String query) {... real db here ...}
  }
  
  
  class MockDatabase extends Database {
    public Result query(String query) { 
      return "mock result"; 
    }
  }
  
  class ObjectThatUsesDB {
   public ObjectThatUsesDB(Database db) { 
     this.database = db; 
   }
  }
  

  现在在生产中，您使用普通数据库，对于所有测试，您只需注入可以临时创建的模拟数据库。

• 不要在大部分代码中都使用 DB（无论如何这是一个不好的做法）。创建一个“数据库”对象，而不是返回结果将返回普通对象（即将返回User而不是元组{name: "marcin", password: "blah"}）使用临时构造的真实对象编写所有测试并编写一个依赖于确保此转换的数据库的大测试工作正常。

Of course these approaches are not mutually exclusive and you can mix and match them as you need.

当然，这些方法并不相互排斥，您可以根据需要混合搭配。

回答by Toran Billups

Unit testing your database access is easy enough if your project has high cohesion and loose coupling throughout. This way you can test only the things that each particular class does without having to test everything at once.

如果您的项目始终具有高内聚性和松散耦合，那么单元测试您的数据库访问就很容易了。通过这种方式，您可以只测试每个特定类所做的事情，而不必一次测试所有内容。

For example, if you unit test your user interface class the tests you write should only try to verify the logic inside the UI worked as expected, not the business logic or database action behind that function.

例如，如果您对用户界面类进行单元测试，您编写的测试应该只尝试验证 UI 内部的逻辑是否按预期工作，而不是该功能背后的业务逻辑或数据库操作。

If you want to unit test the actual database access you will actually end up with more of an integration test, because you will be dependent on the network stack and your database server, but you can verify that your SQL code does what you asked it to do.

如果您想对实际的数据库访问进行单元测试，您实际上最终会进行更多的集成测试，因为您将依赖于网络堆栈和数据库服务器，但是您可以验证您的 SQL 代码是否按照您的要求执行做。

The hidden power of unit testing for me personally has been that it forces me to design my applications in a much better way than I might without them. This is because it really helped me break away from the "this function should do everything" mentality.

对我个人而言，单元测试的隐藏力量是它迫使我以比没有它们时更好的方式设计我的应用程序。这是因为它确实帮助我摆脱了“这个功能应该做所有事情”的心态。

Sorry I don't have any specific code examples for PHP/Python, but if you want to see a .NET example I have a postthat describes a technique I used to do this very same testing.

抱歉，我没有任何针对 PHP/Python 的特定代码示例，但是如果您想查看 .NET 示例，我有一篇文章描述了我用来进行相同测试的技术。

回答by akmad

I usually try to break up my tests between testing the objects (and ORM, if any) and testing the db. I test the object-side of things by mocking the data access calls whereas I test the db side of things by testing the object interactions with the db which is, in my experience, usually fairly limited.

我通常尝试在测试对象（和 ORM，如果有的话）和测试数据库之间分解我的测试。我通过模拟数据访问调用来测试事物的对象端，而我通过测试对象与 db 的交互来测试事物的 db 端，根据我的经验，这通常相当有限。

I used to get frustrated with writing unit tests until I start mocking the data access portion so I didn't have to create a test db or generate test data on the fly. By mocking the data you can generate it all at run time and be sure that your objects work properly with known inputs.

我曾经对编写单元测试感到沮丧，直到我开始模拟数据访问部分，所以我不必创建测试数据库或即时生成测试数据。通过模拟数据，您可以在运行时生成所有数据，并确保您的对象在已知输入下正常工作。

回答by chakrit

You could use mocking frameworksto abstract out the database engine. I don't know if PHP/Python got some but for typed languages (C#, Java etc.) there are plenty of choices

您可以使用模拟框架来抽象出数据库引擎。我不知道 PHP/Python 是否有一些但对于类型语言（C#、Java 等）有很多选择

It also depends on how you designed those database access code, because some design are easier to unit test than other like the earlier posts have mentioned.

它还取决于您如何设计这些数据库访问代码，因为有些设计比之前的帖子提到的其他设计更容易进行单元测试。