Grails 2.4.4 Multiple datasources, separate drivers, IntegrationSpec

Question

I am attempting to use multiple datasources in a Grails 2.4.4 project. According to the docs, this should be possible:

http://www.grails.org/doc/2.4.4/guide/conf.html#multipleDatasources

My primary dataSource (the one I want to use for all domain classes) is using H2 at the moment, as configured by the default DataSource.groovy configuration. My second, read-only datasource is SQL Server, and I tried to declare it as follows at the top level of my DataSource.groovy config (shared by all environments):

ds {
    pooled = true
    dialect = "org.hibernate.dialect.SQLServer2008Dialect"
    driverClassName = "net.sourceforge.jtds.jdbc.Driver"
    url = "jdbc:jtds:sqlserver://myserver:1433/mydb;domain=mydomain;useNTLMv2=true;user=myuser"
    dbCreate = "none"
}

(Don't let the URL throw you off - I'm just having to use Windows Auth with JTDS. I've tested this via third-party clients as well.)

I inject this into my service class and use it, and everything appears to hook up well:

def dataSource_ds

def serviceMethod(){
    Sql ds = new Sql(dataSource_ds)
    String query = "SELECT ... "
    def results = ds.rows(query)
    println "Results are ${results.size()}"
    return "Some value"
}

But when I try to access this from an IntegrationSpec-backed Integration Test, I noticed that I was getting "schema not found" errors for valid schemas referred to by my query string, such as "dbo". And the stack trace of any errors from this setup looks like this:

org.h2.jdbc.JdbcSQLException: Schema "DBO" not found; SQL statement: ... at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) at org.h2.message.DbException.get(DbException.java:169) at org.h2.message.DbException.get(DbException.java:146) at org.h2.command.Parser.readTableOrView(Parser.java:4774) at org.h2.command.Parser.readTableFilter(Parser.java:1083) at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1689) at org.h2.command.Parser.parseSelectSimple(Parser.java:1796) at org.h2.command.Parser.parseSelectSub(Parser.java:1683) at org.h2.command.Parser.parseSelectUnion(Parser.java:1526) at org.h2.command.Parser.parseSelect(Parser.java:1514) at org.h2.command.Parser.parsePrepared(Parser.java:404) at org.h2.command.Parser.parse(Parser.java:278) at org.h2.command.Parser.parse(Parser.java:250) at org.h2.command.Parser.prepareCommand(Parser.java:217) at org.h2.engine.Session.prepareLocal(Session.java:414) at org.h2.engine.Session.prepareCommand(Session.java:363) ...

Now why would THIS datasource be trying to use the H2 driver?

In case it's relevant, my Integration test looks like this:

void "serviceMethod" () {
    when: "service method is called"
    String response = myService.serviceMethod()
    then: "we should get the appropriate text back"
    response.equals("Some value")
}

If, in the Service class, I hard-code the connection using a constructor of the Groovy Sql object, the integration test works fine, and any stack traces go through the JTDS driver.But when I try to use the injected datasource, things are strange.

Any idea what I'm doing wrong here?

Looks like this may due to some magic around integration tests and data sources. grails.org/doc/latest/guide/testing.html#integrationTesting "Grails uses an in-memory H2 database for integration tests and clears out all the data from the database between tests" Will retry as a functional test to verify, and update here. — bryantrobbins

bryantrobbins bryantrobbins · Accepted Answer · 2015-06-11T11:38:39

Just to close the loop on this and hopefully save someone pain on this oversight in the future:

Grails uses an in-memory database when running tests. Make sure to read up on the other differences between integration tests and production here: http://www.grails.org/doc/latest/guide/testing.html#integrationTesting

This feature makes the use of external (read-only) datasources during any tests pretty interesting, but some of that is to be expected (a test which depends on an external datasource is not a very good test in the long run). I hope to refactor my app and its testing approach at some point (e.g., to use a simple DAO and mock that during the test), because I don't really care about asserting the contents of the external datasource from my app's tests.

Grails 2.4.4 Multiple datasources, separate drivers, IntegrationSpec

1 Answers