In this chapter, we covered some aspects of software testing and the way in which it can be applied to databases.

Unit testing techniques can be used in databases, which is good practice. However, database unit testing has its own specificities. First, the objects for unit testing are not only program modules, like functions, but also views, triggers, and the data itself. Secondly, the data in the database sometimes defines the behavior of the modules being tested. That makes database unit testing different from testing other kinds of software.

Unit tests for databases could be written as SQL scripts or stored functions in the database. There are several frameworks that help in writing unit tests and in processing the results of testing.

Another aspect of testing the database software is comparing data in the same database or between databases. This can be done via SQL queries, and sometimes, it requires establishing connections between databases. Connections between databases can be established via dblinks or foreign data wrapper objects.

Database schemas can be compared quite simply by using command-line tools provided with PostgreSQL and operating systems. Additionally, there are open source and commercial software products that do that too.

pgbench is a utility that can be used to assess the performance of a database system.