I was out at lunch with some clients and started discussing properties of database systems and the idea of MySQL not having stored procedures popped up its ugly head again. I explained it did have stored procedures as from 5.0 version, but started a heated debate about whether they were actually of any use any more? Why was there a debate over this issue, I simply said, they are no longer important as they once were.

I put forward the question as to why stored procedures were used and after discussion everyone came to the opinion that they are used primarily for two purposes:

1. A data-level API interface.
2. For encapsulating and abstracting larger, multiple queries or transactions, thus keeping the load on the server, rather than on the network.

Everyone agreed that the second option was still valid, although seemed to be less required now, it was the idea of using stored procedures to maintain an API interface that was intriguing. Let me explain the scenario.

In the last decade, many of the larger database systems looked at implementing a secure API layer using stored procedures to access their database. This had a number of advantages for the business as they saw it including implementing their own security interface, and making access via static api calls.

The api functionality was of great value as it allowed the data administrators to abstract the system they used from the developers. This enables them to change or adjust the database without affecting any third-party applications using their custom api. Although not often a matter of changing the whole system, smaller changes such as table schema, indexes and various everyday operations are invisible to the developers outside. In a similar manner, the outside developers do not need to be restricted to certain libraries or languages to right their applications to interface with the database system. So the question became "why are they not applicable now, or are they?".

Looking at the security feature first, MySQL has the future potential to change this need altogether. Although there is a decent level of control using an acl-style table system to have access defined from host, database right down to the column level, there is still room for improvement. Security defined through methods such as LDAP, RADIUS etc are more common methods today than table definitions. These are potentials in MySQL 5.2 or higher with the implementation of pluggable architecture which may eventually allow a user-defined authentication system with little administrative changes.

The other option of static api is a different matter however. Do we still need to define an api for accessing the data layer? I reasoned that this was no longer the case as there are a number of new technologies that will do this instead. The first technology is that of the object-relational mappers (ORM's) which allow developers to program queries without needing to understand the SQL syntax in great details. One of the most common ORM's in use today is Hibernate, used within the Java programming language. The concept with ORM's is that the developer does not need to understand SQL in detail, but treat the query as an object and use methods such as "filter()" or "sort()" etc to construct the query that will be sent to the database server.

The other option for third-party access is that of web services. It is no longer considered necessary to have direct access to the data, and rather than a proprietary api being used, a standardised method of web services could be implemented. This will provide the same benefits in terms of getting data, setting data etc, but is done using standards that are platform, database and language independent. The ability to use services via protocols such as SOAP or REST are a much better option given todays technologies and provide more structured and better defined access to data without compromising security or integrity.

It seems that by the end of the conversation everyone agreed that web services was a better method than stored procedures for the api layer. Does anyone disagree with the resolution that we came to here? Are we looking at this the wrong way - are stored procedure api's still a good option?