Alternative FMP/Rails Integration: External SQL sources

[Authored by Ian]

In my last post, I described my preferred methodology for integrating Rails and FMP. In this post, I’ll discuss an alternative technique using FMP’s external SQL sources functionality. Since IANAFMPD (I am not a FileMaker Pro Developer), I’ll skip the implementation details and just cut to when it’s an appropriate solution.

External SQL sources are really easy to setup. They require no lines of code in Rails (whereas a REST XML interface requires ~ 10). They require some clicking in FMP. Basically, you’re just making FMP use the Rails application’s MySQL database as a data source. You will need to setup MySQL to bind to a non-loopback network interface, to have a user account for the FMP client, and, preferrably, to setup a firewall that limits requests to port 3306 (MySQL) to the FMP host. Once that’s done, FMP can push and pull data to/from Rails.

This is a great quick-n-dirty solution. By pushing the implementation to the database layer, neither application needs to concern itself about the other. In simple situations, this means you don’t increase the complexity of either app. It’s also less error-prone (in my experience) than an HTTP-founded solution because there’s no need for data translation. All the complexity is handled by your database drivers, which are (supposedly) rock solid.

Nonetheless, I think the problems it poses make it poor solution for any but the most simple situations. The biggest problem is that each application will need to separately implement business logic that acts on the concerned data. Unless you want to duplicate functionality and code between apps, you’ll need to just skip things like data validation and assume–at least in one app–that the data is valid. Furthermore, I’ve no idea whether FMP and Rails would deal well with concurrency issues.

Another big problem with external SQL sources is that, with the complexity pushed to the database layer, debugging is much more difficult. Usually developers are trained to debug problems at the application layer–by circumventing the application layer, you render their test suites, debugging tools, and experience useless.

There’s also a bunch of potential security, scalability, and stability issues that could come up. In general, I think it’s a Bad Idea, and should be avoided.

So when are external SQL sources a good idea? Here’s a few examples of when I’ve used it and it worked fine:

• FMP pushes data to a database that Rails publishes to the web in a searchable, AJAXy interface. Since the Rails app doesn’t let users write to the data, and it doesn’t perform any operations on the data, other than rendering it, you don’t need to worry about concurrency, data integrity (beyond the bare minimum), or business logic duplication. There is some duplication of display logic, but you’d need to rewrite that to go from FMP to the web regardless.  We architected the project in this way because we didn’t want to supply backup infrastructure for the Rails database. Since the FMP database was already backed up, and supplied the Rails database with all its content, it was fine to let the Rails database be ephemeral.
• Rails supplies a web front-end to an established FMP application. This is a similar case to the previous one, but with data traveling in reverse. Again, since Rails isn’t doing any operations on the data, the limitations of external sql sources are acceptable. It was helpful to minimize the data that Rails collected to the bare minimum–thereby reducing scope and avoiding potentials for complication.

In short, my opinion is that external SQL sources are really only acceptable in situations where either Rails or FMP are acting as a front-end. As soon as one or the other needs additional functionality, it’s unavoidable that you’ll need to duplicate code and may incur some nasty technical issues that will be difficult to debug.