Is using an ORM for a RESTful API a good idea?

[Jim Malone]

My company is looking to build a couple new RESTful APIs.  Previously we just had SOAP/xml services.  We are researching the best way to expose our data such that the end users have access to virtually all of the useful combinations of our resources without having to bring data back from several queries and manipulate it themselves (mostly all Reads).  It seems like an ORM would be a useful tool for this so that we could take advantage of LINQ and let our developers quickly code up the combinations of data that we want to represent.  Otherwise it seems like we'd have to write up an exhaustive list of stored procedures to handle each scenario.  Are there other options we are missing?

Of course our database team is balking at the idea of using an ORM, with good reason.  So we're trying to do some research to find out how common it is for mid-sized to large companies to use ORMs in their backend for RESTful APIs.  There isn't much easily found on the web for examples of companies doing this.  Do you know of any?

And if ORM's are being used by any well known/reputable companies, do you know which ORM they're using?  We are mostly considering Entity Framework, but are open to others such as NHibernate.

Any help would be appreciated!

[Dmitry Pavlov]

It seems that you are talking about implementation details of how to store your data. Not sure that it somehow relative to API design and rest concepts.

ORM is just a way to get data from a relational DB, and it should not be the first decision point of api making.

For the first iteration, I'd suggest you to try designing api without thinking about any implementation. Visual diagrams with resources, and transitions are good start point, after that you can select a media format.
During design you can try to think how your clients would interact with api considering it to be a black box (from implementation stand point). I even suggest you to make some static mock files before you start coding the real impl.
After that you would have an understanding of what do you want and can start thinking how to imlement it.

21.04.2014 18:24 пользователь "Jim Malone" <malo...@gmail.com> написал:

--
You received this message because you are subscribed to the Google Groups "API Craft" group.
To unsubscribe from this group and stop receiving emails from it, send an email to api-craft+...@googlegroups.com.
Visit this group at http://groups.google.com/group/api-craft.
For more options, visit https://groups.google.com/d/optout.

[Stephen Rylander]

For the most part, API design has little to do with data storage and retrieval.  There are some cases, I actually have one, where they are closer, but normally not.  If you want to debate ORM vs Stored Procedure vs X there are definitely plenty of places willing to have that flame war! :)

Good luck.
p.s. love ORM's.

S//R

[Kijana Woodard]

As the others have pointed out, ORMs and data storage are orthogonal to API design.

As a starting point on API design, I suggest: http://www.amazon.com/Building-Hypermedia-APIs-HTML5-Node/dp/1449306578/ref=sr_sp-atf_title_1_2?s=books&ie=UTF8&qid=1398096961&sr=1-2

Couch is used, but it is not an important detail. Design the API without concern for persistence.

I'll participate in the flame:

"Of course our database team is balking at the idea of using an ORM"

Why? They probably have zero or poor reasons for this. I myself eschew ORMs but for completely different reasons. If I'm using a document db, this is a non-issue. If not, I prefer dapper/massive. 

But I've used EF/NHib to produce queries that are exactly what one would write by hand. Plus, actually profiling in SQL Server proves that the query plans are cached and the performance is equal to stored procedures...without the overhead, boilerplate, and loss of flexibility of that approach. Stored Procedures have a place. "For Everything" is not that place.

Generally, DBAs want stored procedure so they can "code live in production". They'll put it in other terms ["ability to react to operational complexity"], but it all boils down to "hit save and my changes are in production". THAT should scare management to death. Instead, they've been sold that it is A Good Thing when it merely highlights complete organizational dysfunction.

--

[Jørn Wildt]

+1 (and more!) for the DBA observation :-)

/Jørn

--
/Jørn

[Hennadii Omelchenko]

Let me briefly divert you from the original question.

But what about the idea itself to be able "to access virtually all of the useful combinations of our resources without having to bring data back from several queries"? 

I've faced this dilemma several times but haven't seen a good answer. Usually this intention caused lots of trouble in uri design, led to OData issues or the like. Moreover, REST style itself usually causes much more 'calls' than alternatives.

Понеділок, 21 квітня 2014 р. 17:24:10 UTC+3 користувач Jim Malone написав:

[Jørn Wildt]

As others have said: persistence technology is orthogonal to API design. But here are my two cents about ORMs/DB access ...

I have used NHibernate on a couple of projects and its a wonderful tool for OO programming in the "Domain Driven Design" style - mostly because of its ability to automatically check for changes to objects and thus manage which objects to write back to the DB. But it comes with a price - writing is done "behind the scenes" once your application code (unit of work) has finished its work. This makes it *really* difficult to correlate SQL errors with actual .NET code.

Any ORM that allows developers to traverse relations in code will eventually result in some poor developer making N+1 database access without even knowing it - until it is in production and bogs down the system.

If your system is primarily for reading and you want to optimize for that - perhaps because you already have some sort of backend application that takes care of the writing - then I would avoid advanced ORMs and go with something like Dapper or Massive as Kijana suggests (and maybe EF is fine for this). That will make the DB operations much more visible to your developers.

From the API point of view: do you have some very specific use cases/scenarios to open the API for or do you (really?) need some sort of flexible "do any SQL" web API? As Hennadii says, OData would be a good place to look for query formats ... I believe Microsoft backs OData with .NET tools for SQL querying (meaning you don't have to code anything yourself).

> such that the end users have access to virtually all of the useful combinations of our resources without having to bring data back from several queries and manipulate it themselves (mostly all Reads).
> It seems like an ORM would be a useful tool for this so that we could take advantage of LINQ

Would you then let client developers send HTTP requests that are somehow magically (by your code) translated into LINQ expressions? Why not translate it into SQL and skip the LINQ abstraction?

/Jørn

--

[Stephen Rylander]

It has to be a dw behind it with appropriate dimensional and meta modeling.

It does not map to rest cleanly - but if that's what you need it's what you need. Rest is not a panacea.

- Stephen

--
You received this message because you are subscribed to a topic in the Google Groups "API Craft" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/api-craft/3ZjLmVnMUj0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to api-craft+...@googlegroups.com.

[Jim Malone]

Thanks for all the feedback everyone.  I should have been a little clearer, but we have most of the API design already figured out.  We know the resources we want to expose and they all exist in our current (and rather antiquated) relational db.  We have decided on a RESTful service using the OData protocol and Web API 2.  So one of the last bridges we want to cross before we begin development is to decide our data access method.  That's where we basically are stuck between choosing an ORM and Stored procedures.  Since we know we'll have a lot of different combinations of resource get requests, we were concerned about the overhead of handling all of that through SQL.  One thing we are strongly considering is replicating the data in a new database that is transformed into more of a DW type of structure, which should hopefully handle a lot of the ORM mapping issues that relational DB's can cause.

I was just curious if anyone had come across a similar issue before and had any good advice as to how to make the decision. It may turn out that a hybrid ORM/SQL solution is best.

And Kijana, I agree with your assessment of the DBA mindset. But I feel like they think their jobs are threatened if we use an ORM, even though there's still plenty of work for them IMO.  But as someone mentioned, that's a whole other discussion. :)

[Kijana Woodard]

There's a ton of work that they need to do that doesn't get done because they're looking at every "select too from table where" query. There's no value there.

Fwiw, if I were going to do odata, I'd just use the auto wiring that m$ gives you for free and be done with it. OData is just SQL statements over http. From an API POV, it's about as primitive as you can get. You're basically saying "we have no idea what users really need, here's a connection string to the db so they can query it themselves".

There are times when this is all that's needed. Since your app is read only, this might be one of them " browse our catalog". Just like CRUD apps, odata has it's place and a value. So I've decided that ReST-ful has come to mean NOT ReST.

I don't find any of that technologically objectional as long as we understand what we are doing.

Projecting the data into another data that has a shape more conducive to odata queries seems like a win on several fronts. Don't infect the write application with the inevitable changes for the read API. Don't impact performance of the normal writes. Take away the arguments from the DBAs about letting odata do its thing directly against the db.

--

You received this message because you are subscribed to the Google Groups "API Craft" group.

To unsubscribe from this group and stop receiving emails from it, send an email to api-craft+...@googlegroups.com.

[Louis Schilling]

In our API, we are using Dapper with stored procedures, they're certainly not mutually exclusive. I suspect that if our data relationship logic was more complex, we might rethink this, but it works well for us. I use multiple result sets in each stored procedure, into a Dapper "multi" object, and then manipulate the data from there as needed to create more complex domain objects. There are other ways in Dapper to do this as well, but I haven't investigated them.

[Kijana Woodard]

"they're certainly not mutually exclusive"

Certainly not. I recently used Massive with stored procedures to great effect. It doesn't have the multi feature of dapper though. :-( There was only one case where I missed it though.

The team [2 of us] and the requirements lent themselves well to that solution.

--

[Kijana Woodard]

My original point was that none of "how you get the data" has any necessary relationship to the Resources exposed by the API.

If the use case of the OP is really "just let the users query the db", then I think the OP's suggestion of a odata optimized read store plus auto-wiring odata is a reasonable approach.