Easy, Automatic REST API for a SQL (MySQL, PostgreSQL, etc) Database or DaaS

Question

I need REST API access to my SQL database for end user queries in a mobile application.

I am trying to avoid having to program my own REST API because I come from a statistical programming (R, SAS, Stata) background and it would not be the easiest thing for me to write and maintain. I can be flexible about the type of SQL database that's used -- MySQL, Postgres, Cloud SQL,etc... basically anything I can load a .csv into -- and I'm flexible about if it's hosted by me or if it's a Database as a Service (DaaS) or Mobile Backend as a Service (MBaaS).

Ideally, I'd like to keep my data in Google's Cloud SQL (which is just MySQL hosted by Google) for stability and find an automatic REST API to work with it.

I've tried a few things, but have hit roadblocks in each case. Here's what I've tried thusfar:

• PostgreREST + sqitch -- asides from being a little labor intensive in its setup and having to host everything myself, this didn't work out because sqitch had permissions issues during deployment. The author of sqitch had some suggested solutions but none of them seem to work for me.
• Appery.io's API Express (Beta) -- I was very excited about this solution and it nearly worked out, but in the end the "Beta" aspect killed it, because it can't currently handle string comparisons (pattern matching) and thus can't support searches
• DreamFactory -- another near solution, this "Dev Sandbox" hosted version was unable to connect and query my DB, but I was told that if I installed a paid version to be hosted elsewhere that it would work. I actually gave this a try but each of their suggestions for integration with an external host (i.e. Google Cloud via Bitnami, Heroku, etc) led to an error message at some point, which not only created a roadblock but also made me question how stable and well-developed this thing really is.

Answer 1

I've tried a couple of solutions for this before, including writing my own Node.js server which redirect calls to a MongoDB table and stores responses as objects. It's easy and simple, but you lack control of about anything.

If I would do it today, I would use a Symfony 2 application with the Rest Bundle from Stan Lemon. A Symfony 2 application is quite extensible, if anything is missing from the bundle, just add it. And the expressive way to describe Doctrine models is easy on beginners and quite powerful once you get to know it. As a bonus, you can use a generator to create an AngularJS admin interface for your models.