Calling stored procedures from Ember-Data


#1

I’m in the beginning phases of building an Ember-Data adapter for the DreamFactory REST API. I’ve got basic CRUD functionality working with params for GET calls. DreamFactory just released a new version that supports stored procedures via their REST API. A Live demo API is available for testing (expand db then scroll to the bottom for the sproc calls)

The basic endpoint is

http://your.rest-endpoint.com/rest/db/_proc/{your-proc-here}?queryParams=here

This endpoint supports both GET (no params) and POST (with params) requests.

I’ve got a couple of ideas on how to go about implementing this and would love some feedback from the community on my general direction. There are likely better approaches to this than what I’ve outlined below but I’ve had a hard time coming up with any. Here’s what I’ve got so far.

First off I’m pretty sure I will need an entirely new set of methods on the RESTSerializer called something like findWithProc and saveWithProc that would handle any requests using stored procedures. This would still allow the adapter to use the standard REST calls while having support for procedures as well.

HTTP Verb + Type (Convention over Configuration)

The thought here is that the procedure would be named for the HTTP VERB and Type it was related to. So the corresponding procedure for a GET request for Posts would be named GetPosts. Updating a post would be PutPost, etc, etc… buildUrl would be overridden to output the correct endpoint.

Pros

  • Straightforward implementation for new projects
  • No real configuration outside of the adapter customizations
  • Use EmberData just like you always have.

Cons

  • Inflexible for existing schemas
  • Forces a naming convention onto the DBAs which can be considered blasphemy in some circles (usp_Select_Top_Posts_for_User_by_UserId)

###Explicitly defined properties on the Controller (Convention AND Configuration)

Controllers that have CRUD actions would be required to have properties for each of the actions they support: GETProcedure, POSTProcedure, PUTProcedure, and DELETEProcedure. These would then be passed to ED and buildUrl would be overridden to output the correct endpoint.

Getting all posts

this.store.findWithProc('post', { 'proc': this.get('GETProcedure'), 'otherParam': true } );

Deleteing a post

post.deleteRecord();
post.saveWithProc(this.get('DELETEProcedure'));

Pros

  • Flexible. Would work with existing schemas or new schemas

Cons

  • Uses convention but requires a fair amount of configuration as well.
  • Could get out of control in a hurry with larger applications.

Feedback/glowing praise/scathing criticism is appreciated!


#2

I think a better approach would be to use the standard ember-data api for models and let the adapter do the work of translating everything into a request that dreamfactory understands.

For instance, instead of having a PutPost you would just call post.save() as usual and your dreamhost adapter would translate that into a POST to your PutPost procedure.

For procedures that perform a search you could use something like store.findAll(‘post’, {procedure: ‘usp_Select_Top_Posts_for_User_by_UserId’}).

If your DBAs are sensitive about naming you can always let them decide what to call the procedures and then bake them into your adapter (or even make the names configurable if really necessary).