How to test a view in a SQL database

I had a realisation that I’m looking forward to blogging about soon.  However, when I outlined my post I couldn’t find references for several things that I know to be true.  So I started outlining those posts, and ran into the same lack of references.  Which brings us to the beginning:

How to test a database view

The textbook approach

I advocate Test Driven Design for databases. For procedural code, the first step is to write an acceptance test. once that test is written, and obviously failing, then the next step would be to write some unit tests. However, SQL is a set based, declarative language, so by the end of this post I hope to convince you to use a simpler approach than is standard for testing procedural code.

This post uses the following schema, which is greatly simplified to make the examples shorter:

create schema sales;

create table sales.customers
(customer_id   int primary key
,customer_name varchar(100) not null
);

create table sales.products
(product_id INT primary key
,product_name varchar(100) not null
);

create table sales.orders
(order_id INT primary key
,customer_id int references sales.customers(customer_id)
,product_id int references sales.products(product_id)
,order_date date
,quantity numeric
,price numeric
);

I’m using the pgtap test framework. In all of the SQL test frameworks that I know acceptance tests look like this:

CREATE OR REPLACE FUNCTION pgtap.test_acceptance_customer_orders() RETURNS SETOF text
AS 
$function$

insert into sales.orders 
(order_id, customer_id, product_id, order_date, quantity, price)
values
 (1,1,1,'2017-01-01',10,100)
,(2,1,1,'2017-02-01',1,15)
,(3,1,1,'2017-04-01',0,0);

DECLARE cwant CURSOR FOR SELECT 1::int,'Avril'::varchar(100),3::bigint,115::numeric;
DECLARE chave CURSOR FOR SELECT * FROM sales.customer_orders;

SELECT results_eq(
    'cwant'::refcursor,
    'chave'::refcursor,
    'Customer Order acceptance test'
);

$function$  LANGUAGE sql;

In this example we’re defining a small, static data set that we load into the view’s driving table and a single row that we expect as output. Like most acceptance tests this first test is a simple, happy path.

This test is failing, the view hasn’t even been defined yet. So we now write a whole series of unit tests, similar to this one.

CREATE OR REPLACE FUNCTION pgtap.test_unit_customer_orders_null_price()
 RETURNS SETOF text
 LANGUAGE sql
AS $function$

insert into sales.orders 
(order_id, customer_id, product_id, order_date, quantity, price)
values
 (10,2,1,'2017-01-10',10,null)
,(11,2,1,'2017-02-01',1,null);

DECLARE expect CURSOR FOR 
	SELECT 2::int,'Busayo'::varchar(100),2::bigint,null::numeric;
	
DECLARE actual CURSOR FOR 
	SELECT * FROM sales.customer_orders;

SELECT results_eq(
    'expect'::refcursor,
    'actual'::refcursor,
    'All prices null'
);
$function$

The first thing that you should notice is that the “unit test” looks just like the “acceptance test”. Keen observers will also note that the foreign key dependencies haven’t been satisfied. I use the same setup and teardown for the acceptance test and all of the unit tests.

CREATE OR REPLACE FUNCTION pgtap.setup_customers() RETURNS SETOF text
AS 
$BODY$
begin
	RETURN QUERY 
		select is( MAX(customer_id), NULL, 'Setup customers') 
		FROM sales.customers;
	
	insert into sales.customers (customer_id, customer_name) values
	 (0,'42Meanings Inc.')
	,(1,'Avril')
	,(2,'Busayo')
	,(3,'Chloe');
	
	return;
end;
$BODY$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pgtap.setup_products() RETURNS SETOF text
AS 
$BODY$
begin
	RETURN QUERY 
		select is( MAX(product_id), NULL, 'Setup products') 
		FROM sales.products;
	
	insert into sales.products
	(product_id, product_name)
	values
	 (1,'Electric Car')
	,(2,'Digital Delivery');	

	return;
end;
$BODY$ LANGUAGE plpgsql;

Which brings us to the point where we can safely write the view being tested.

create or replace view sales.customer_orders as
select orders.customer_id
, customers.customer_name
, count(1) number_of_orders
, sum(price) total_price
from sales.orders
left outer join sales.customers on customers.customer_id = orders.customer_id
group by orders.customer_id, customers.customer_name;

The set approach

When I test stored procedures, the approach that I’ve described above is what I do. However, it has a few downsides:

1. There is a lot of repeated code
2. All the parts of the test framework, and the view are called repeatedly, which makes testing slow and verbose.

SQL is a language that operates on sets. After preparing a series of little sets each of which tests a single execution path, I recommend running them all together rather than separately.

CREATE OR REPLACE FUNCTION pgtap.test_customer_orders()
 RETURNS SETOF text
 LANGUAGE sql
AS $function$

insert into sales.orders 
(order_id, customer_id, product_id, order_date, quantity, price)
values
/* Test zero qty order is counted as an order */
 (1,1,1,'2017-01-01',10,100)
,(2,1,1,'2017-01-02',1,15)
,(3,1,1,'2017-01-03',0,0)
/* Test null value orders appear */
,(10,2,1,'2017-02-10',10,null)
,(11,2,1,'2017-02-01',1,null)
/* Test null quantity orders appear */
,(20,3,1,'2017-03-15',null,45)
,(21,3,2,'2017-03-05',null,900)
/* And so on ... */
;
/* Implied tests: 
 * Customers without orders do not appear.
 */

DECLARE expect CURSOR FOR 
	SELECT 1::int,'Avril'::varchar(100) ,3::bigint,115::numeric  union all 
	SELECT 2::int,'Busayo'::varchar(100),2::bigint,null::numeric union all
	SELECT 3::int,'Chloe'::varchar(100) ,2::bigint,945::numeric;
	
DECLARE actual CURSOR FOR 
	SELECT * FROM sales.customer_orders;

SELECT results_eq(
    'actual'::refcursor,
    'expect'::refcursor
);
$function$

The result is that each view ends up with a single test function. The downside to this approach is when the test fails you’ll need to open the test code to learn what the paths that failed were asserting. Which I think is a bargain for the reduction in testing complexity.

Comments