Home > Blogs > VMware vFabric Blog > Monthly Archives: July 2011

Monthly Archives: July 2011

SQLFire and Grails

Chris Harris has written a series of blog posts showing how to use SQLFire from within Grails. In the process he introduces a couple of very interesting features of SQLFire that I wanted to talk more about, specifically how your app should take advantage of SQLFire's elasticity and the possibility of using SQLFire as a cache. Grails is a web development framework based on the Groovy programming language, and is designed to make writing web applications quick and painless. Using SQLFire from Grails is quite simple, mainly because SQLFire supports a JDBC interface, so connecting Grails apps to SQLFire only requires changing two lines.

Getting Started With SQLFire and Grails

Chris's first post shows the basics of creating an application and connecting it to SQLFire. All total there are 10 lines of code, excluding whitespace, which is really good considering Chris introduces constraints around maximum and minimum valid name lengths. The "grails create-controller" command handles the messy details of actually doing these constraint checks so the developer can focus on more important things.

Taking Advantage of SQLFire's Elasticity

Next, Chris introduces the concept of the SQLFire Locator. SQLFire is elastic by design, meaning that you can add and remove nodes whenever you want. When you do that, data is automatically rebalanced across all SQLFire nodes. You can see how this could be a problem, your app needs to be sure it's talking to something that's not going to go away some time in the future. SQLFire solves this using things called Locators. Locators keep track of what nodes exist in the SQLFire system and where data resides within the system. They are also persistent so your app can rely on the Locator to always be in the same place.

Let's look at an example. In this example we have a SQLFire system with two nodes and a locator.  Two Node SQLFire System With Locator

Now let's say that either the size of the database needs to grow or we need to add more processing power to it. We can just add a node. Again, when we do that the data is automatically shared with the new node and balanced across the whole system. Since the app is talking to the locator it doesn't notice any changes.

Three Node SQLFire System With Locator
The reverse works as well, if I need to get rid of a node I can just pull it out and my app will keep working fine. Chris's second blog post shows how this works in Grails. There's not much to it really, in Grails just make sure your JDBC URL points to the Locator.

SQLFire as a cache

Chris's third post shows how you can use SQLFire as an in-memory frontend to a MySQL database. Let's be clear, SQLFire is a real database, you can store your application's real data inside it, back it up, restore it, do all the things you would do with databases you're used to. There's no need to pair SQLFire with another database, but there are times when you may want to do it. For example you may have a data warehouse with huge amounts of data you wouldn't want to try to fit in memory.

Another common thing these days is people who hit a performance wall with their database, so they look at putting an in-memory cache on top of the database, such as memcache. This has worked very well but introduces a lot of complexity, for one thing you've got a new set of software you have to care and feed, as well as a new and incompatible set of APIs to access in-memory data versus on-disk data. Adding this sort of in-memory layer introduces a lot of stack complexity both for developers and operations.

When we look at relational databases, we believe that ultimately SQLFire is a better choice than using a combination of relational database plus any sort of in-memory caching. After all, why care for 2 databases when you only need 1? However it takes time to get there and until it happens SQLFire as a cache in front of an RDBMs may be the right thing for many people. It's less disruptive than a switch directly to an in-memory database, and is less complex than introducing a non-relational cache on top of a relational database.

Whatever the case may be Chris shows that it's pretty easy to make SQLFire into an in-memory cache on top of a MySQL database. The magic is in a couple of CALL commands made within the sqlf utility. Note that Chris does not need to write code that maps the SQLFire tables to the MySQL tables, this is the advantage of our cache being a real SQL database, we don't have to go through this error-prone process like we would if we were using something like memcache.

Thanks Chris for your hard work in writing these blog posts and I hope we can continue to see more from you.

As always to learn more or ask questions be sure to join our community to connect with the experts.