Mapping Natural Keys using GORM

I needed to use GORM within Grails to map a legacy database today.

This in itself isn't particularly difficult, however there's quite a neat trick available when you need to map a 'natural key' onto the internal id field required by GORM.

The table in question was called users and took the following form:

login     varchar(20) not null primary key,
password  varchar(20),
full_name varchar(20)

I know - a good example or how not to implement a database schema. Nevertheless, this is what I was presented with...

Forgetting the natural 'natural-key' field (login) for a moment, the Grails domain-class took the following form:

class User {
  String password
  String fullName

  static constraints = {
    password(nullable:true,maxSize:20)
    fullName(nullable:true,maxSize:20)
  }

  static mapping = {
    table 'users'
    version false
  }
}

So far so good - both the password and full_name columns are varchar types, which map to Strings via GORM. They can both be nullable (yes, really) and I have added a max-size constraint for input validation.

Note that I do not need to include these properties in the mapping section, as password matches its column name and GORM will automatically translate the field called fullName to a column called full_name.

I then used a mapping closure to map to the users table (plural - otherwise GORM would look for a table called user). We also need to indicate that the version column does not exist in our legacy database.

Now the clever bit - we map the id field within GORM to a transient field, then use a custom getter and setter to control it.

This gives us the following:

class User {
  String id
  String password
  String fullName

  static transients = ['login']

  static constraints = {
    id(unique:true,blank:false)
    password(nullable:true,maxSize:20)
    fullName(nullable:true,maxSize:20)
  }

  static mapping = {
    table 'users'
    id column: 'login'
    version false
  }

  void setLogin(String login) {
    id = login
  }

  String getLogin() {
    id
  }

  String toString() {
    id
  }
}

We have created a transient field called login - this simply means that it will exist within our domain object, but will never be persisted to the underlying database. The domain class now effectively has a virtual property called login that is set via setLogin() and returned via getLogin().

So when we create a new User object, complete with a login property, the setter will automatically update the [persistent] id property instead. GORM will then use the id property and map it to the underlying login column within the database table.

This really can be life-saver when using Grails to communicate with legacy databases