Google Cloud SQL with Grails

2014-06 NOTE: the following article doesn't apply anymore since Google Cloud SQL is now available publicly as a service without special authentication. see https://developers.google.com/cloud-sql/

Here are the steps to access a Google Cloud SQL database with a Grails application not hosted on Google App or Compute Engine. That means that a Grails application running on your local machine, a server or one of the cloud platform can still use the Google Cloud SQL services. First you need to enable SQL in your account and create your SQL instance and the database from the Google APIs console see https://developers.google.com/cloud-sql/docs/before_you_begin.
Then check the following docs: the Google Cloud SQL online doc for external access is at https://developers.google.com/cloud-sql/docs/external . It explains what the driver configuration is and how to get the OAuth tokens on your machine. For that download the command line tools at http://dl.google.com/cloudsql/tools/google_sql_tool.zip You need to run the command line tool once, it will ask you to use your browser to authorize access from your machine see the doc here https://developers.google.com/cloud-sql/docs/commandline .

Here are the steps:
1) enable SQL on your Google Account
2) create a SQL instance (container for your databases). You can use the console to interact with your database (click on the "SQL Prompt" tab).
3) download the command line tools, unzip them in the folder of your choice
4) run them with the name of your SQL instance (the same name that the APIs console shows)
5) follow the instructions to get the authorization code to access the SQL instance from your machine
6) the "sql>" prompt shows up.
7) use the mySQL commands to verify the connection like "show databases"...
8) copy the "google_sql.jar" file from the command line folder created earlier to the /lib folder in the grails app. It contains the jdbc driver to access the Google SQL instance
9) change the Grails datasource.groovy file with your instance and database params as:

environments {
    development {
        dataSource {
        dbCreate = "create"
pooled = false // true doesn't work with Google SQL
driverClassName = "com.google.cloud.sql.Driver"
dialect = "org.hibernate.dialect.MySQL5InnoDBDialect"
username = 'root'
password = ''
//jdbc:google:rdbms://instance_name/database_name"
url = 'jdbc:google:rdbms://yourinstancename/yourdatabasename'
        }
...

10) run your app.

Notes:
1) The driver doesn't seem to support dbCreate = "create-drop" as of April 2013. Grails fails with a "DatabaseMetaData; nested exception is
 com.google.cloud.sql.jdbc.internal.SQLFeatureNotYetImplementedException: Not implemented Yet" error. It works with "create" or "update", not so bad. If you find a way to get the dbCreate = "create-drop" mode to work, please let me know! See update below.
2) Once your app is up and running, create a user/password for it, don't use the "root" as shown above.
3) "pooled = true" doesn't work. See https://developers.google.com/cloud-sql/docs/external : "Note that connections to a cloud hosted database should be managed differently to those on a conventional server. In particular, be aware that your database instance may be taken offline while not in use, and any pooled connections would be closed. Rather than using a connection pool we recommend that a new connection is created for each new transaction since the time to create a new connection is similar to that required to test the liveness of an existing connection."

Update: thanks to Sergio Michels on the Grails Users list and Burt Beckwith's blog http://burtbeckwith.com/blog/?p=312 , the connection works now with dbCreate = "create-drop". I changed the file "myapp\grails-app\conf\spring\resources.groovy" to:

import org.springframework.jdbc.support.lob.DefaultLobHandler

beans = {
// for the Google Cloud SQL config
// see http://burtbeckwith.com/blog/?p=312
lobHandlerDetector(DefaultLobHandler)
}