Legacy DB, Grails & Domain Classes

When having to build Grails apps around a legacy DB, this plugin is a great start.

The DB reverse engineer plugin (works with Grails 3)

It is a good idea to create a Integration test to just check you can access these tables. The integration test will be run on your development envrionment and you will need to have some data in there. Be careful obviously as it is bad practise to rely on existing data, but this helped me get all the mappings properly wired up.
Start with something as simple as:

package com.webtracker.api.prodcat

import com.webtracker.api.Company
import com.webtracker.api.Project
import com.webtracker.api.WpActivationKey
import grails.test.mixin.integration.Integration
import grails.transaction.Rollback
import spock.lang.Specification

/**
 * Sanity domain Integration tests, uses development DB
 *
 * Created by demian on 14/06/16.
 */
@Integration
@Rollback
class DomainSpec extends Specification{

    def setup() {
    }

    def cleanup() {

    }

    void "Can we list and load an activation key"() {
        when:
        def keys = WpActivationKey.list()
        def key1 = WpActivationKey.get keys[0].id
        def key2 = WpActivationKey.findByActivationKey keys[0].activationKey

        then:
        keys.size() > 0
        key1 == key2

    }

    void "Can we list and load a company"() {
        when:
        def companies = Company.list()
        def company1 = Company.get companies[0].id
        def company2 = Company.findByName companies[0].name

        then:
        companies.size() > 0
        company1 == company2
    }

    void "Can we list and load a project"() {
        when:
        def projects = Project.list()

        then:
        projects.size() > 0
    }
}

I ran into a few stumbling blocks, all easily overcome
In my case the auto timestamping did not work out the box (as column names where different), but that was incredibly easy to fix.

Here is my Domain Class snippet

 Date dateCreated
 Long projectId

 static mapping = {
  version false
 }

 static constraints = {
  activationKey maxSize: 100, unique: true
        dateCreated column: 'created_date'
 }

I started getting this error

Caused by: java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp

This is because that is a valid SQL date but not in Gorm, this was fixed by appending the following to application.yml datasource:url: connection string

?zeroDateTimeBehavior=convertToNull