Grails hip tip: exporting schema DDLs

If you utilize Grails out-of-the-box and don’t change any of the connection profiles, your hip application will be bound to an in memory instance of HSQLDB — this is fine and dandy for developmental purposes, but oftentimes, you’ll want to run Grails on top of a more permanent data store (keep in mind that you can operate in such a manner with HSQLDB’s file based persistence). For example, if it’s your bag, you might want to eventually deploy to an instance of MySQL, once your data model stabilizes somewhat — in that case, you’ll have to create a database in your MySQL instance.

In previous versions of Grails, this was challenging; however, in more recent versions, you can quickly understand the underlying schema definition via the <a href="<a href="https://grails.org/doc/1.1/ref/Command%20Line/schema-export.html">https://grails.org/doc/1.1/ref/Command%20Line/schema-export.html</a>">schema-export</a> command. What’s more, with this command, you can create a new instance of a schema in your target database environment provided you take care to set the proper Hibernate dialect.

The schema-export command works quite copacetically; indeed, it’ll generate a valid DDL file for HSQLDB — that is, even if you change your DataSource.groovy file to point to MySQL, the DDL file will still be specific to HSQLDB, which, of course, can cause issues with MySQL. For instance, the SQL DDL

id bigint generated by default as identity (start with 1)

isn’t valid within MySQL; in fact, the same statement in MySQL parlance would be:

id bigint not null auto_increment

Thus, to force the schema-export command to generate a dialect (i.e. specific database DDL), you must set the dialect property inside the top datasource section of your DataSource.groovy file like so:

dialect=org.hibernate.dialect.MySQLDialect.class

In the case above, I’ve forced the dialect to be specific to MySQL; accordingly, when I execute the schema-export command (i.e. %>grails schema-export) I’ll have a DDL generated that is valid for MySQL (as opposed to HSQLDB). Of course, you can do the same for Oracle, DB2, etc. Can you dig it, man?