basic pgtap testing

pgtap_example.sql

begin;

create extension pgtap;

create table users(
    username text primary key,
    email text not null,
    firstname text,
    lastname text
);
select plan(9);

select 'Table structure' as describe;

select 
  has_table('users'), 
  has_pk('users'),
  has_column('users', 'username'), 
  col_not_null('users', 'email'), 
  col_type_is('users', 'username', 'text');

select 'Table constraints' as describe;

select 
  lives_ok(
    $$
      insert into users (username, lastname, firstname, email)
      values ('poppi', 'schneider', 'petra', '[email protected]');
    $$,
    'inserting a new user with a unique username should be ok'
  ),
  throws_ok(
    $$
      insert into users (username, lastname, firstname, email)
      values ('poppi', 'müller', 'peter', '[email protected]');
    $$,
    'duplicate key value violates unique constraint "users_pkey"',
    'inserting a username already existing should throw a unique constraint violation'
  ),
  throws_ok(
    $$
      insert into users (username, lastname, firstname, email)
      values ('petermüller', 'müller', 'peter', null);
    $$,
    'null value in column "email" violates not-null constraint',
    'inserting a null email should throw a not-null constraint'
  );

select 'Table data' as describe;

select results_eq (
  $$
    select username, firstname
    from users
    where lastname = 'schneider'
  $$,
  $$
    values('poppi', 'petra')
  $$,
  'should select all users with a lastname "schneider"'
);

select * from finish();
rollback;