Grails integration test data cannot cross native SQL versus GORM boundary

Question

Goal

When running integration tests, I would like to populate the database with preset data using GORM.

Problem

For certain domains, native SQL queries can't see data inserted by GORM and vice versa (GORM queries can't see data inserted by native SQL). Within a single run, one domain will have this issue and another won't.

Description

Please see the console output near the bottom of this post for a clear description. I think clues to the solution can be seen in the output.

Project: Grails 2.4.2 Datasource:

    test {
    cehCode = "PR"
    schemaName = "AF"
    parallelSchemaName = "AF"
    dataSource {
        dbCreate = "create-drop"
        url = "jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS AF"
        driverClassName = "org.h2.Driver"
        properties {
            initialSize = 2
            minIdle = 1
            maxIdle = 2
            maxActive = 2
            maxWait = 2000
            maxAge = 60000
            minEvictableIdleTimeMillis=30000
            timeBetweenEvictionRunsMillis=30000
            abandonWhenPercentageFull = 50
            numTestsPerEvictionRun=3
            testOnBorrow=true
            testWhileIdle=true
            testOnReturn=true
            validationQuery="SELECT 1"
            validationInterval=500
            //defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_UNCOMMITTED
        }
    }

}

Integration test:

void "Test CUST filter"() {
    when: 'Build test data'
    then:
        assert testQueryService.testInsert() == "Done with inserts"
        assert testQueryService.testQuery() == "Done with queries"
}

Service:

package com.lrs.accrual.common

import com.lrs.contract.TFastCodes
import grails.transaction.Transactional
import groovy.sql.Sql
import com.lrs.accrual.IntermodalRatingAuth

@Transactional
class TestQueryService {
def sessionFactory

def testInsert() {
    println "----------------------START EXECUTING INSERTS ON TABLES------------------------------"
    println "********* START NEW TABLE/DOMAIN TFAST_CODES/TFastCodes *********"
    def nativeInsert = """INSERT INTO AF.TFAST_CODES (APPL_ELE,APPL_CD_VAL,APPL_CD_DESC,CAS_OWNER,ACS_TYPE_CD)
                VALUES ('NATIVE SQL2','1','ORPT-TEST      PR','ZZZZ','BU')"""
    def sql = new Sql(sessionFactory.getCurrentSession().connection())
    sql.executeInsert(nativeInsert)
    sql.commit()
    TFastCodes codes = new TFastCodes(applicationElement: 'GORM new 3', casOwner: 'ZZZZ', applicationCodeDescription: 'JUNK2', applicationCodeValue: '1', accessTypeCode: 'BU', newColumn2: 'Val')
    println "Valid TFastCodes domain? " + codes.validate()
    codes.save(flush: true)
    println "********* END NEW TABLE/DOMAIN *********"

    println "********* START OLD TABLE/DOMAIN TIMDL_RT_AUTH/IntermodalRatingAuth *********"
    def nativeInsert2 = """INSERT INTO AF.TIMDL_RT_AUTH (AGREEMENT_NUMB,MRKT_RATE_KEY,WB_RT_AUTH_REF)
                VALUES ('NATIVE',1,'2016-01-01-01.01.01.000000')"""
    def sql2 = new Sql(sessionFactory.getCurrentSession().connection())
    sql2.executeInsert(nativeInsert2)
    sql2.commit()
    IntermodalRatingAuth auth= new IntermodalRatingAuth(agreementNumber:"GORM", marketingRateKey:1, waybillRateAuthRef: "other string")
    println "Valid IntermodalRatingAuth domain? " + auth.validate()
    auth.save(flush: true)
    println "********* END OLD TABLE/DOMAIN *********"
    println "----------------------END EXECUTING INSERTS ON TABLES------------------------------"
    return "Done with inserts"
}

def testQuery() {
    println "----------------------START EXECUTING QUERIES ON TABLES------------------------------"
    println "********* START QUERIES AGAINST NEW TABLE/DOMAIN TFAST_CODES/TFastCodes *********"
    def codes = TFastCodes.list()
    codes?.eachWithIndex { val, i -> println "Gorm TFastCodes result $i: " + val.properties }
    println "Native TFAST_CODES query result: " + sessionFactory.getCurrentSession()?.createSQLQuery("SELECT * FROM AF.TFAST_CODES").list()
    println "********* END QUERIES AGAINST NEW TABLE/DOMAIN TFAST_CODES/TFastCodes *********"
    println "********* START QUERIES AGAINST OLD TABLE/DOMAIN TIMDL_RT_AUTH/IntermodalRatingAuth *********"
    def auths = IntermodalRatingAuth.list()
    auths?.eachWithIndex { val, i -> println "Gorm IntermodalRatingAuth result $i: " + val.properties }
    println "Native TIMDL_RT_AUTH query result: " + sessionFactory.getCurrentSession()?.createSQLQuery("SELECT * FROM AF.TIMDL_RT_AUTH").list()
    println "********* END QUERIES AGAINST OLD TABLE/DOMAIN TIMDL_RT_AUTH/IntermodalRatingAuth *********"
    println "----------------------END EXECUTING QUERIES ON TABLES------------------------------"
    return "Done with queries"
}
}

Domain created from scratch (works):

    class TFastCodes implements Serializable {

String applicationElement
String applicationCodeValue
String applicationCodeDescription
String casOwner
String accessTypeCode
static mapping = {
    table 'AF.TFAST_CODES'
    version false
    id composite: ['applicationElement', 'applicationCodeValue']
    applicationElement column:'APPL_ELE'
    applicationCodeValue column:'APPL_CD_VAL'
    applicationCodeDescription column:'APPL_CD_DESC'
    casOwner column:'CAS_OWNER'
    accessTypeCode column:'ACS_TYPE_CD'
}

static constraints = { applicationElement( blank:false)
                       applicationCodeValue( blank:false)}

}

Existing domain (does not work):

class IntermodalRatingAuth {

String agreementNumber=LRAccrualConstants.STRING_EMPTY
Integer marketingRateKey= LRAccrualConstants.ZERO
String waybillRateAuthRef

static mapping = {
    table 'AF.TIMDL_RT_AUTH'
    version false
    agreementNumber column:'AGREEMENT_NUMB'
    marketingRateKey column:'MRKT_RATE_KEY'
    waybillRateAuthRef column:'WB_RT_AUTH_REF'
}
// Read-only. No constraints needed.
}

Console Output:

----------------------START EXECUTING INSERTS ON TABLES------------------------------
********* START NEW TABLE/DOMAIN TFAST_CODES/TFastCodes *********
Valid TFastCodes domain? true
Hibernate: select tfastcodes_.APPL_ELE, tfastcodes_.APPL_CD_VAL, tfastcodes_.ACS_TYPE_CD as ACS_TYPE3_37_, tfastcodes_.APPL_CD_DESC as APPL_CD_4_37_, tfastcodes_.CAS_OWNER as CAS_OWNE5_37_ from AF.TFAST_CODES tfastcodes_ where tfastcodes_.APPL_ELE=? and tfastcodes_.APPL_CD_VAL=?
Hibernate: insert into AF.TFAST_CODES (ACS_TYPE_CD, APPL_CD_DESC, CAS_OWNER, APPL_ELE, APPL_CD_VAL) values (?, ?, ?, ?, ?)
********* END NEW TABLE/DOMAIN *********
********* START OLD TABLE/DOMAIN TIMDL_RT_AUTH/IntermodalRatingAuth *********
Valid IntermodalRatingAuth domain? true
********* END OLD TABLE/DOMAIN *********
----------------------END EXECUTING INSERTS ON TABLES------------------------------
----------------------START EXECUTING QUERIES ON TABLES------------------------------
********* START QUERIES AGAINST NEW TABLE/DOMAIN TFAST_CODES/TFastCodes *********
Hibernate: select this_.APPL_ELE as APPL_ELE1_37_0_, this_.APPL_CD_VAL as APPL_CD_2_37_0_, this_.ACS_TYPE_CD as ACS_TYPE3_37_0_, this_.APPL_CD_DESC as APPL_CD_4_37_0_, this_.CAS_OWNER as CAS_OWNE5_37_0_ from AF.TFAST_CODES this_
Gorm TFastCodes result 0: [applicationCodeDescription:ORPT-TEST      PR, applicationCodeValue:1, accessTypeCode:BU, applicationElement:NATIVE SQL2, casOwner:ZZZZ]
Gorm TFastCodes result 1: [applicationCodeValue:1, accessTypeCode:BU, applicationElement:GORM new 3, casOwner:ZZZZ, applicationCodeDescription:JUNK2]
Hibernate: SELECT * FROM AF.TFAST_CODES
Native TFAST_CODES query result: [[NATIVE SQL2, 1, BU, ORPT-TEST      PR, ZZZZ], [GORM new 3, 1, BU, JUNK2, ZZZZ]]
********* END QUERIES AGAINST NEW TABLE/DOMAIN TFAST_CODES/TFastCodes *********
********* START QUERIES AGAINST OLD TABLE/DOMAIN TIMDL_RT_AUTH/IntermodalRatingAuth *********
Gorm IntermodalRatingAuth result 0: [marketingRateKey:1, agreementNumber:GORM, waybillRateAuthRef:other string]
Hibernate: SELECT * FROM AF.TIMDL_RT_AUTH
Native TIMDL_RT_AUTH query result: [[1, NATIVE, 1, 2016-01-01-01.01.01.000000]]
********* END QUERIES AGAINST OLD TABLE/DOMAIN TIMDL_RT_AUTH/IntermodalRatingAuth *********
----------------------END EXECUTING QUERIES ON TABLES------------------------------

Additional details

In my Grails project, I have setup an in-memory DB for the test environment. I would like to save a set of test data fresh every time when running specific integration tests.

When saving the data using GORM (domain.save()), the GORM queries in the service are able to find the data just fine, but are not able to see the data inserted by the native SQL inserts. The sessionFactory native SQL queries, on the other hand, are not able to see the GORM data inserted, but are able to see the data inserted by the native SQL.

As you can see below, I do flush:true and have tried every command I know to commit the data run. Most of the domains I have tested with are existing domains and it never works for them right off. However, when I create new domains from scratch (using Grails "create domain" command), it often works. It seems to me that the issue is somewhere deeper in the Grails or Hibernate architecture. The code examples below are simplified, but I have a service that demonstrates an old domain and new domain side by side:

Both get inserted to by both GORM and native SQL (two rows in each table)
Both get queried by both GORM and native SQL
The old domain shows native inserted data for the native query and GORM inserted data for the GORM query
The new domain shows both results for the native query and both results for the GORM query

If it works, it works for both GORM and native. If it doesn't work, neither sees the data inserted by the other.

Debugging steps I have taken:

The data source below is much larger than what I started with. My first one was very basic, with no AF schema, no additional properties, etc. Everything was added to try to troubleshoot.
I have tried removing the Serializable, composite ID, and pretty much anything else that I could find was making this more complicated.
Between each run, I do a grails clean-all command and delete the target directory
I am having trouble creating a consistent scenario that makes a domain start working, but it is usually related to a combination of changing class, moving packages, changing the table name defined in the domain, or creating a completely new domain/table.

Did you try with sql.commit() after sql.executeInsert(nativeInsert) ? — droggo
@droggo, thanks for your comment. I added the commit statement as you said, and did not see any change. To better illustrate, I also added the second domain that I was mentioning before where it works for one and not the other and improved the logging. Two things to point out: 1. As you can see, TFAST_CODES still successfully finds both insert statements for each query, but TIMDL_RT_AUTH (old domain) still only finds the one. 2. The Hibernate log statements are different for the inserts and queries on these. Clue to the root cause? Not sure. — Chris H
Some thoughts: you can try to wrap your inserts in one transaction to save it before those query's. You can also use a new session to query and get the state of your database. — user800014

Chris H Chris H · Accepted Answer · 2016-05-17T19:31:15

Sergio/droggo,

Thank you for your help! I think I tracked it down. As is often the case, it is difficult to know exactly what code to share to allow others to help troubleshoot. It seems that the native SQL was hitting the datasource config, but the GORM/domains were mocked so were not persisted to the datasource. I did not realize this, because I am using a Grails plugin called build-test-data whose @Build annotation is a wrapper for the @Mock annotation (obviously I was not aware of this). @Build is meant for unit tests only, not integration tests. For anyone to be able to help me, they would have needed to see the integration test's annotations (below). Sorry for the missing source code!

@TestFor(TestQueryService)
@Build([ContractFilter, ContractHeader, Qualifier, Provision, TFastCodes])
class ContractFilterServiceSpec extends Specification {

Grails integration test data cannot cross native SQL versus GORM boundary

1 Answers