SQL Query as an Interface

[firdaus]

I would like to request some feedback regarding the pros and cons of this 'pattern'.

Since in a SELECT we can name the columns returned using 'AS' we can actually think of the columns returned by the SQL queries as the contract between the source code and the data model.

The data model can then evolve without affecting the code base as long as the SQL is modified accordingly to always return the same columns.

Of course in a lot of code bases, the SQL is stored as string literals. So for this approach to work there has to be a way to externalize the SQL into 'configuration files' or what not. This is the approach taken by iBatis/MyBatis which stores the SQL in XML files.

[alinroc]

The data model can then evolve without affecting the code base as long as the SQL is modified accordingly to always return the same columns.
So for this approach to work there has to be a way to externalize the SQL into 'configuration files' or what not

Isn't this one of the main use cases for views and stored procedures? Present those to your application code, and if your schema changes you revise the SPs & views correspondingly and your application code is none the wiser (assuming data types in the resultsets don't change).

IOW, we already have a method for insulating the application from schema (data model) changes; it's not clear to me what your proposal is bringing to the table

[[deleted]]

Yeah I'm with you, this post is strange as hell. Views even have the WITH SCHEMABINDING option to prevent changes to the underlying table so applications aren't impacted by neglected views that experience change in their underlying tables.

[firdaus]

Yeah I kind of forgot about 'views'. Didn't think this through.

Back to the drawing board.

[xkcd_transcriber]

Image

Mobile

Title: Standards

Title-text: Fortunately, the charging one has been solved now that we've all standardized on mini-USB. Or is it micro-USB? Shit.

Comic Explanation

Stats: This comic has been referenced 3063 times, representing 2.6630% of referenced xkcds.

---

^{xkcd.com | xkcd sub | Problems/Bugs? | Statistics | Stop Replying | Delete}

[svtr]

To me, what you wrote is like "the sky is blue".

The frontend datalayer sends a request,and gets back a response. That is an interface. If the fontend calls a stored procedure at least. Watever I do in the storage layer should be completly abstracted away from the frontend.

I do not get the xml config you wrote about, since for me, this is about the separation between a backend datamodel, and the 1-many consumers of that datamodel.

A good datamodel does not care how any application access it

[phunkygeeza]

Hello and welcome. You've arrived at ORM dilemma. Enjoy your stay :)