An interesting experiment with JSON, Postgres and Omnis

Bastiaan Olij bastiaan at
Thu Jan 12 07:01:52 EST 2017

Hey Clifford,

I think you missed the main point of my post. I am not suggesting that
the functions I created in Postgres will be the API made available on
the middle tier or that this is how you should be writing a 3-tiered
approach. Far from it, like I said, if I was starting from scratch, I
would NOT go down this route.

Yes in Omnis I will call the stored function getInvoice but for the
middle tier we'll adhere to REST, as you say
At this point in time the middle tier will call the getInvoice stored
function so it returns the same data, I see no reason to duplicate code
for this particular situation.

If we decide to have Omnis talk to the API instead of directly to the
do Database.getInvoice(1234) returns JSON
do Rest.Get('api/invoices/1234') returns JSON
And that would be the time we move the logic out of the stored function
and into the middle tier.

But we may never get there because it is not our intention to replace
our Omnis desktop application by something else.

About 80% of our Omnis application will remain as it is today, using
normal client-server code. We have 25 years invested in this which we
can't throw over to an API in any feasible fashion and there is nothing
to be gained in doing so.

About 20% of our Omnis application will be changed over to this approach
because we need to share functionality between our Omnis application and
our mobile application and website and as I mentioned in my previous
email, for the foreseeable future we don't want Omnis to have to talk to
both the database and the API, especially as we may not even deploy the
API to many clients that have no interest in our mobile app nor our

The mobile application we are building, and the website we are planning
are designed to add on to our existing logic and most things build for
it, will be build nicely in our middle tier, we won't be using stored
functions here because that just complicates matters unnecessarily.



On 12/01/2017 5:18 PM, CLIFFORD ILKAY wrote:
> Hi Bas,
> The difference between the three tier architecture and the PostgreSQL
> specific method that you've outlined is that you're locking yourself
> into PostgreSQL with the latter and what you have may not necessarily
> be a RESTful API unless you've gone to the trouble of creating a URL
> router, HTTP middleware and such. Your scheme of storing JSON objects
> using PG's JSON datatype is good. However, a middle tier would enable
> you to support a wide range of backends, including noSQL backends like
> CouchDB, have schema migration tools built-in so that you wouldn't
> have to invent them or be locked into the ones that you've probably
> built in Omnis, build asynchronous applications, use WebSockets, and
> of course, expose a RESTful API. it would also provide you an escape
> hatch out of Omnis if that ever becomes a priority. It's tough to
> justify Omnis for web applications, particularly if you're aiming for
> a SaaS where you need the flexibility of offering free or heavily
> discounted trials, and monthly subscription pricing.
> By the way, your function signature, getInvoice(invoiceID), is a REST
> API naming anti-pattern. You're still thinking in client/server terms.
> Just because it returns JSON doesn't necessarily mean it's a RESTful
> API. Returning JSON isn't even a requirement of having a RESTful API.
> The best practice would be something like the following.
> To create a new invoice, do a POST to http://yourHost/api/invoices
> To read invoice #12345, do a GET to http://yourHost/api/invoices/12345
> To update invoice #12345, do a PUT to http://yourHost/api/invoices/12345
> To delete invoice #12345, do a DELETE to
> http://yourHost/api/invoices/12345
> Planning for API versioning is also important. There are two schools
> of thought on this. Some say the API version should be in the request
> header . Some say it should be in the URL. I prefer the latter because
> it makes for easy discoverability.
> E.g. http://yourHost/api/v1/invoices/12345 and
> http://yourHost/api/v2/invoices/12345 for the next version.
> As for your challenges with JSON to Omnis list conversion, Dave
> McKeone has an XCOMP <> that
> I've played around with. It was fast and it worked quite well. I don't
> know if it will address your challenges. Once we'd decided that we
> were going to migrate out of Omnis and we managed to get the CEF XCOMP
> working well, the reason for using the JSON XCOMP was eliminated.

Kindest Regards,

Bastiaan Olij
e-mail: bastiaan at
Skype: Mux213

More information about the omnisdev-en mailing list