Best Practices on Database Testing

[Josh Padnick]

Hello,

I am wondering if our approach to testing our DAOs and Service Methods is a sound approach, or if there are better ways of setting up tests?  I've read http://blog.jooq.org/2014/06/26/stop-unit-testing-database-code/, and understand the main points there, as well as reviewed prior forum posts on the topic.

The example below deals with a very simple DAO, but it serves as a good case study.  Thank you in advance for any input!

Our Class

We've written a simple class that will CRUD "emailTemplates" from our database.  You can think of "Mnemonic" as just a unique identifier.  This allows us to get fancy autocomplete in our IDE when requesting a particular email template.  Anyway, here's the interface:

public interface EmailTemplateDao {
    EmailTemplate findByMnemonic(EmailTemplateMnemonic emailTemplateMnemonic);

    void setEmailTemplate( EmailTemplate emailTemplate );

    EmailTemplate insert();

    EmailTemplate update();

    void delete( EmailTemplateMnemonic emailTemplateMnemonic );
}

All these methods are implemented using standard jOOQ tools: the DSLContext and UpdatableRecord.  Our testing approach is basically:

• Mock any dependencies this class has
• Connect to the real database (both locally and our testDb on Jenkins)
• Do our database operations in the test (e.g. Insert)
• Delete the item just tested

Again, nothing fancy.  I've pasted our file below.  My questions are:

1. We are directly inserting data, and then deleting our data as part of our test. We strategically chose to insert TEST_EMAIL_TEMPLATE to avoid conflicting with other prepopulated data in our test environment.  Is the "do something in the database, and then clean up after yourself" paradigm pretty standard?  Logically, I can't imagine any other way to do this.
2. Are there are any issues with scaling this approach?  For example, when we have hundreds of tests, will we want to be doing something different here.
3. Can I safely assume that a test like this MUST be run sequentially and can never be run in parallel?  Do you address that slowness by just setting up a nightly test run?
4. Any other points or links to good resources for testing DAOs and Service Methods?  Or top books on the topic?

Thank you again for your input!

Josh

public class EmailTemplateDaoTest {

    private Http.Request requestMock = mock(Http.Request.class);
    private OrgContext orgContextMock = mock(OrgContext.class);

    private AragornDatabaseConnection aragornDatabaseConnection;
    private EmailTemplateDao emailTemplateDao;

    @Before
    public void setup() {
        // Define an HTTP.context since our Database Connection is passed around via the ctx() object
        Http.Context.current.set( new Http.Context( 1L, null, requestMock, new HashMap<String, String>(), new HashMap<String, String>(), new HashMap<String, Object>() ) );

        // Default to OrgId #1
        when( orgContextMock.getOrgIdFromRequestHostname() ).thenReturn( 1 );

        // Directly instantiate other dependencies
        aragornDatabaseConnection = new AragornDatabaseConnectionImpl( orgContextMock );
        emailTemplateDao = new EmailTemplateDaoImpl( aragornDatabaseConnection );
    }

    @Test
    public void testInsert() {
        running( fakeApplication(), () -> {

            aragornDatabaseConnection.setConnection( DB.getConnection( "mydb" ) );

            EmailTemplate emailTemplate = new EmailTemplate();
            emailTemplate.setEmailTemplateMnemonic( EmailTemplateMnemonic.TEST_EMAIL_TEMPLATE.getValue() );
            emailTemplate.setFromEmail( "m...@mail.com" );
            emailTemplate.setFromName( "Josh Padnick" );
            emailTemplate.setMessageBody( "Hello, this is an email!" );
            emailTemplate.setMessageSubject( "Testing!" );
            emailTemplate.setMetaDescription( "Description here" );
            emailTemplate.setMetaTitle( "Test Email" );
            emailTemplateDao.setEmailTemplate( emailTemplate );

            EmailTemplate emailTemplateInserted = new EmailTemplate();

            try {
                emailTemplateInserted = emailTemplateDao.insert();
            } catch (SQLException e) {
                Assert.fail();
            }

            assertThat( emailTemplateInserted.getEmailTemplateId() ).isNotEqualTo( emailTemplate.getEmailTemplateId() );
            assertThat( emailTemplateInserted.getEmailTemplateMnemonic() ).isEqualTo( emailTemplate.getEmailTemplateMnemonic() );
            assertThat( emailTemplateInserted.getMessageBody() ).isEqualTo( emailTemplate.getMessageBody() );
        });
    }


    @Test
    // Aragorn should not permit the same template to be inserted twice because each mnemonic must be unique
    public void testDuplicateInsertThrowsException() {
        running( fakeApplication(), () -> {

            aragornDatabaseConnection.setConnection( DB.getConnection( "mydb" ) );

            // Insert the first template (should succeed)
            EmailTemplate emailTemplate = new EmailTemplate();
            emailTemplate.setEmailTemplateMnemonic( EmailTemplateMnemonic.TEST_EMAIL_TEMPLATE.getValue() );
            emailTemplate.setFromEmail( "m...@mail.com" );
            emailTemplate.setFromName( "Josh Padnick" );
            emailTemplate.setMessageBody( "Hello, this is an email!" );
            emailTemplate.setMessageSubject( "Testing!" );
            emailTemplate.setMetaDescription( "Description here" );
            emailTemplate.setMetaTitle( "Test Email" );
            emailTemplateDao.setEmailTemplate( emailTemplate );

            try {
                emailTemplateDao.insert();
            } catch (SQLException e) {
                Assert.fail("Failed to insert first EmailTemplate");
            }

            // Now insert the second, duplicate template (should fail)
            EmailTemplate emailTemplateDupe = new EmailTemplate();
            emailTemplateDupe.setEmailTemplateMnemonic( EmailTemplateMnemonic.TEST_EMAIL_TEMPLATE.getValue() );
            emailTemplateDupe.setFromEmail( "mys...@mail.com" );
            emailTemplateDupe.setFromName( "Joshua Padnick" );
            emailTemplateDupe.setMessageBody( "Hello, this is an email." );
            emailTemplateDupe.setMessageSubject( "Testing..." );
            emailTemplateDupe.setMetaDescription( "Description here!" );
            emailTemplateDupe.setMetaTitle( "Test Email of Amazement" );
            emailTemplateDao.setEmailTemplate( emailTemplateDupe );

            try {
                emailTemplateDao.insert();
            } catch (SQLException | org.jooq.exception.DataAccessException e) {
                // We don't permit the same mnemonic to be entered twice
                Assert.assertTrue( e.getMessage().contains( "violates unique constraint" ) );
            }
        });
    }

    @Test
    public void testUpdate() {
        running( fakeApplication(), () -> {
            aragornDatabaseConnection.setConnection( DB.getConnection( "mydb" ) );

            // Insert the first template (should succeed)
            EmailTemplate emailTemplate = new EmailTemplate();
            emailTemplate.setEmailTemplateMnemonic( EmailTemplateMnemonic.TEST_EMAIL_TEMPLATE.getValue() );
            emailTemplate.setFromEmail( "m...@mail.com" );
            emailTemplate.setFromName( "Josh Padnick" );
            emailTemplate.setMessageBody( "Hello, this is an email!" );
            emailTemplate.setMessageSubject( "Testing!" );
            emailTemplate.setMetaDescription( "Description here" );
            emailTemplate.setMetaTitle( "Test Email" );
            emailTemplateDao.setEmailTemplate( emailTemplate );

            try {
                emailTemplateDao.insert();
            } catch (SQLException e) {
                Assert.fail("Failed to insert first EmailTemplate");
            }

            // Now update it
            EmailTemplate emailTemplateUpdated = new EmailTemplate();
            emailTemplateUpdated.setEmailTemplateMnemonic( EmailTemplateMnemonic.TEST_EMAIL_TEMPLATE.getValue() );
            emailTemplateUpdated.setFromEmail( "mys...@mail.com" );
            emailTemplateUpdated.setFromName( "Joshua Padnick" );
            emailTemplateUpdated.setMessageBody( "Hello!" );
            emailTemplateUpdated.setMessageSubject( "Hola!" );
            emailTemplateUpdated.setMetaDescription( "Description here" );
            emailTemplateUpdated.setMetaTitle( "Test Email" );
            emailTemplateDao.setEmailTemplate( emailTemplateUpdated );

            EmailTemplate emailTemplateUpdatedResponse = new EmailTemplate();

            try {
                emailTemplateUpdatedResponse = emailTemplateDao.update();
            } catch (SQLException e) {
                Assert.fail();
            }

            assertThat( emailTemplateUpdatedResponse.getFromEmail().equals( "mys...@mail.com" ) );
            assertThat( emailTemplateUpdatedResponse.getFromName().equals( "Joshua Padnick" ) );
        });
    }


    @After
    public void teardown() {
        running( fakeApplication(), () -> {
            aragornDatabaseConnection.setConnection( DB.getConnection( "mydb" ) );

            try {
                emailTemplateDao.delete( EmailTemplateMnemonic.TEST_EMAIL_TEMPLATE );
            } catch (SQLException e) {
                Assert.fail();
            }
        });
    }


}

[Lukas Eder]

Hi Josh,

There's by no means a single best solution to this problem, even if we said on that linked blog post that you should absolutely not mock databases and run "integration tests" (or "end to end tests") instead (the jOOQ blog often polarises to get a bit more traction and more interesting discussions).

I guess your listing kind of wraps up the generally best strategy here. 

- When speed is of the essence, mocking may become important. In your case, the EmailTemplateDao is a good candidate for being mocked. 

- When complex state transitions are of the essence, actual end-to-end database testing will be more important, as simulating transactions is very very hard. Your point 1) seems pretty standard, yes.

You *can* parallelise some of the end-to-end tests. Ideally, you'd be using several identical test databases and use jOOQ's schema mapping feature (or different JDBC connections) to implement "multi-tenancy" among test databases. But again, here, there are no absolute best practices. Only things that work better in certain contexts.

I've recently heard an interesting talk at JavaZone by Seb Rose. He's referenced a lot of great books and citations, certainly a good watch for you (recording at the bottom):

http://2014.javazone.no/presentation.html?id=5d747df3

Cheers

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.