Usability - Productivity - Business - The web - Singapore & Twins

OData - the fine print

For an upcoming event I had to dig a little deeper into OData and learned a few things along the lines.

Same but different

OData's current version is 4.0 and that's the only version I will focus on in this article.

OData is the love child of an effort that joined Microsoft and SAP at the hip to create an universal internet data access standard, that on first sight has a lot of NIH issues to sort out. But I'm sure on a second (or third or forth) look it will all make sense.

Key components of OData

  • a service document in JSON format. It describes what data endpoints are available and where. Depending on the requested scope (more on that later) that JSON contains different levels of details
  • a meta data document. That document is referenced in the service document. The format is XML. It describes the data format found in each record, relations to other record and actions that can be taken. Looks like XML Schema, but not quite. Record is used loosely here, data can be hierarchical and nested
  • a query language squeezed into an URL, so data can be fetched using HTTP GET without using a body submitted. Query allows data selections, filtering, output size and sorting to be specified
  • Query results in JSON format enhanced with OData specific Metadata

What I liked a lot: OData.org offers a rich set of documentation and tutorials, including a Postman collection to learn

Happy soup

So dealing with OData you can enjoy a mix of technologies:

  • JSON for service document and payloads
  • XML for meta data
  • a dot notation (like dotNet or Java) for property values like data types (go learn about it). E.g. Edm.String defines a String value or Org.OData.Core.V1.ResourcePath the path to data
  • another query language
  • individual items by default get addressed in an uncommon format: serviceurl/People('johndoe'). Luckily this is optional and you can use the more common format for a route: serviceurl/People/johndoe. This seems to be born out of squezing the square pin URL into the round hole of database schemas and flexible primary keys

On the upside: Your Salesforce and Excel users will love it. Import and CRUD operations are well supported.

Initially I was looking at Apache Olingo to implement my needs. Unfortunately the library is too close to the Java servlet model and static defined data models (like your one application, not your data platform), so I disected my needs the hard way.

Lessons learned along the way

  • A OData client (think Excel, Tableau, Salesforce) will initially connect to the service document url. It sends a GET request. To specify what level of detail is required the ACCEPT header is used. So when testing your provider implementation you need to take that into account
  • Excel sends ACCEPT application/json;odata.metadata=minimal
  • Salesforce sends ACCEPT application/json;odata.metadata=full
  • OpenAPI can specify the top level application/json or the full qualified content type in the response. If you fully qualify it, you might end up with a 406 and wonder what happens
  • The meta data url gets specified in your service document and is usually the service document url extended by /$metadata
  • I ran into a potential Salesforce bug, where the meta data URL was ignored and the service document URL extended by $metadata. Note the missing /. Caused a bit of grief since Excel does /$metadata
  • Everything is case sensitive, which makes sense, but is fun in Domino that treats item names case insensitive
  • I couldn't get Salesforce to play nice until I added a field ExternalId to my form. Might be my lack of understanding how to change the primary key Salesforce will play nice with or something missing in my Metadata

Same, same, but different

Swap out the GET for a POST and the XML for something not-quite-JSON and you get GraphQL that tries to address a similar set of functionalities. We really love wheels reinvented

More lessons to be learned, stay tuned!

Posted by on 04 March 2020 | Comments (0) | categories: OpenSource WebDevelopment


  1. No comments yet, be the first to comment