Databases: Reserved Keywords

With technologies like Hibernate, deployment of database-driven application should've become a no-brainer: change the driver, change the DataSource's configuration in some XML file and off you go!

Reality is slightly different: a Grails-based web-application we're working on right now is configured for three different databases - depending on the system we're deploying to. During development, it's configured to use an in-memory HSQLDB. When working in development mode, things just work - there has not been a single reserver-keyword hickup so far.

For production, we have two different databases that we can test on: MS SQL Server 2005 and MySQL. For MS SQL Server 2005, first-time deployment has been a pain. During schema generation only the first error is that occurs is reported in the logs and thus we had to perform a couple of deploy -> bugfix -> deploy -> ... cycles as a couple of issues with reserved keywords popped up.

In MySQL, things have been going pretty well with no need to change mapping because of keywords. Just yesterday, however, I added a new field to one of our domain classes and ran into the first keyword issue on MySQL, too.

Now here's the list of keywords you need to avoid while working on the different databases. I'll try to keep this up-to-date as I encounter more such issues.

MS SQL Server 2005

• "user" - Won't be accepted as table name in schema generation
  
• "rule" - Won't be accepted as table name in schema generation

MySQL 5.1

• "key" - Even though this keyword can be used as a column name in schema creation, adding to the table with an "insert into" will lead to an unhelpful 'you have a syntax error near ...' SQLGrammarException to be thrown
  

Which brings me to another question? Why does the Hibernate database dialect not automatically prevent these keywords to be used in mappings and provide replacement values? The dialect could, for example, prefix every known reserved keyword with a "_" character - deployment problem solved.