How to do versioning for stored procedure?

Question

Is there any way to create versioning for stored procedures? I have created an API which fetches data from a database using stored procedures. This API is being consumed from a mobile app. I released the v1.0 of API (www.example.com/api/v1.0/endpoints). Now I've to release the v2.0 of the API (www.example.com/api/v2.0/endpoints), and there are many changes both in the API and stored procedures. So how do I release the v2.0 without affecting the older API? Because even if API is independent, stored procedures are the same for both. How is this kind of situation supposed to be handled?

I mean I want both sprocs on the db at the same time and to be able to call both, but the problem is they can't have the same name.

Answer 1

You have, broadly speaking, two choices:

1. Make the stored procedure backward compatible,
2. Add a second, v2, stored procedure to the database and leave the original unchanged.

The first approach can be harder to implement. You may need default values or parameters and complex logic in the stored procedure to handle both the old and new behaviours. Such a stored procedure might be harder to maintain as a result. If not, then you have the advantage of only having one stored procedure still to maintain in the future.

The second approach is often far simpler to implement. Just create a new stored procedure and leave the old one unchanged. But then you have two versions to maintain. A bug might be found that affects both, requiring both to be fixed. And when you come to v3, v4 etc you risk an ever increasing set of similar pieces of code to maintain.

Which approach will suit your specific case really depends on the scope of your changes. You will likely want to pick the approach that will make future maintenance easier even if it makes the change harder to implement initially.

Answer 2

Stored procedures don't support versioning. You will need to make a version of your database with two sprocs with the version in the name

mySproc_v1()
mySproc_v2()

Your API can support versioning in a number of different ways. so you can have two versions of the api deployed pointing to the same database

www.example.com/api/v1.0/endpoint - calls mySproc_v1
www.example.com/api/v2.0/endpoint - calls mySproc_v2

You should be versioning your database with source control and deploying with migration scripts. The general problem is that you generally want to share the db between different versions of your API, so it needs to be backwards compatible.

Sprocs are a good way to achieve this, the downside is you need to update the db with new sprocs all the time. You can simply not use sprocs and put the SQL in the api. This reduces the number of times you need to worry about the DB, but obviously if you remove a table column or something you will be in trouble.

Answer 3

First and foremost, use the right tool for the job. Don't use the database to maintain versioning, use your source control tool (e.g. git) to maintain the revisions of the stored procedure in a regular flee and use your database migration tool to deploy new revisions of those stored procedures to the database (and if necessary, to remove old, unused ones). The "gold copy" of the stored procedure should be what's checked into your source control.

Second, separate your API versioning from stored procedure versioning. A Web API is generally public API, you probably have commitments to maintain those old versions for a certain period of time, stored procedure is generally a private interface, you'd want to deprecated outdated ones as soon as possible. If possible, you'd want to rewrite your v1 Web API so they use the same stored procedure as v2 internally rather than duplicating implementation. You'd want to design your APIs so it doesn't expose implementation detail, so that you can make these kind of internal changes without affecting old API users.

Answer 4

I don't know which database you're using, but is it possible to define stored procedures in different schemas (essentially namespaces) and give them access to the underlying data through synonyms? At least that's what I would try with Oracle.

If that's not possible, you could implement poor man's namespaces using prefixes, so doSomething() becomes v1_doSomething() and v2_doSomething().

Whatever you do, you will need to work a bit to get out of the hole you've dug yourself into...

Answer 5

@hlie-ryan I like your way of maintaining versions for stored procedures using a single copy that s easier to maintain. How can the versions for many internal users be updated on the fly without breaking future versions when the removed versions are archived? I suppose you meant migrate only the stored procedures that are indictable to the version being currently implemented rather than all other versions.Some procedures may be unused and then you won't have implementation issues then :) My three cents!

Web API is great implementations for SOAP and XML processes as long as the legacy versions are deprecated. I.E Versions are thrown out and delink from other versions so that there is not any major breaks in the source libraries :)