0

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.

  • 1
    do you mean you want both sprocs on the db at the same time and to be able to call both, but the problem is they cant have the same name? – Ewan Aug 6 at 12:02
  • yes thats what i want – Shanid Aug 6 at 12:51
  • This seems to be a problem you've caused yourself by using stored procedures. The obvious and simplest option is just to stop using them from now on and keep your SQL in your application. What motivation do you have to continue using them? – Nathan Cooper Aug 6 at 13:37
  • @NathanCooper This isn't a stored-procedure-specific problem and can happen without a database being involved. – Blrfl Aug 6 at 16:59
  • I once worked at a place where they stored their procedures in an actual table and retrieved them for execution using an ordinary query. Versioning the procedures becomes a trivial exercise. – Robert Harvey Aug 6 at 18:25
6

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.

  • Unfortunately, some procedures have already been modified in the development environment. What can be done in that case ? Is there any way to handle this such that we do not need to change the procedure name ? For example, like changing schemas of procedures, is there any such way ? – Shanid Aug 6 at 12:53
  • 6
    @Shanid If the development database is being treated as a gold copy and can't be rebuilt into the same state from scratch, it would be wise to stop what you're doing and reconsider your development processes. – Blrfl Aug 6 at 17:02
6

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.

  • Actually, some of the existing sprocs are already modified. Can anything be done in that case ? – Shanid Aug 6 at 13:52
  • 2
    roll them back by applying your migration roll back script (you did make a rollback script didnt you?.....) – Ewan Aug 6 at 14:10
  • Unfortunately no :( – Shanid Aug 6 at 14:27
1

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...

  • Using mssql database – Shanid Aug 7 at 5:13
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.