[Grails] Mock MySQL database in test environment

Grails supports different data sources configured per environment.
If you use MySQL database in production and want to write integration tests you may prefer H2 database because it simpler.
For example it can automatically create in-memory database from the domain classes of GORM model.
And after running tests it will drop this database when application will shutting down.
H2 allows you to use full set of SQL commands, but they may differ from MySQL.
To imitate MySQL behavior you need to add options Mode=MySQL;DATABASE_TO_UPPER=FALSE;IGNORECASE=TRUE to connection string.

From documentation:

MySQL Compatibility Mode

To use the MySQL mode, use the database URL jdbc:h2:~/test;MODE=MySQL or the SQL statement SET MODE MySQL.

  • When inserting data, if a column is defined to be NOT NULL and NULL is inserted, then a 0 (or empty string, or the current timestamp for timestamp columns) value is used. Usually, this operation is not allowed and an exception is thrown.
  • Creating indexes in the CREATE TABLE statement is allowed using INDEX(..) or KEY(..). Example: create table test(id int primary key, name varchar(255), key idx_name(name));
  • Meta data calls return identifiers in lower case.
  • When converting a floating point number to an integer, the fractional digits are not truncated, but the value is rounded.
  • Concatenating NULL with another value results in the other value.

Text comparison in MySQL is case insensitive by default, while in H2 it is case sensitive (as in most other databases). H2 does support case insensitive text comparison, but it needs to be set separately, using SET IGNORECASE TRUE. This affects comparison using =, LIKE, REGEXP.

But raw SQL may not work, because MySQL field names are case sensitive, GORM expect them lowercased, but by default H2 creates them uppercased.
To fix that you need to set property DATABASE_TO_UPPER to FALSE

Database setting DATABASE_TO_UPPER (default: true).
Database short names are converted to uppercase for the DATABASE() function, and in the CATALOG column of all database meta data methods. Setting this to «false» is experimental. When set to false, all identifier names (table names, column names) are case sensitive (except aggregate, built-in functions, data types, and keywords).

So final Config.groovy will looks like:

environments {
    test {
        dataSource {
            dbCreate = 'create'
            pooled = true
            driverClassName = org.h2.Driver.class.canonicalName
            dialect = H2Dialect
            username = 'sa'
            password = ''
            logSql = true
            formatSql = true
            pooled = true

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s