Here’s a bold claim. Using stored procedures is better than putting
SQL in your application code. I’ll explain why in this video.
When you’re developing an application in a language like Java or C# or JavaScript,
you may eventually learn how to access a database.
You’ll probably do this by putting an SQL query in your application code
and using a query library in the language to interact with the database.
If it’s a Select statement, you’ll get the results back.
This is simple to do and quite a common way of interacting with the database.
It can work well for simple queries.
And there are many advantages to this.
Application code is easily managed in source control.
You can easily make changes and commit them to a GitHub repository.
You’ve got all your logic and code in one place, making it easy to maintain.
It’s also easy to debug if it’s in one place.
Once you start getting more complex queries, that’s when things start getting tricky.
When I say complex queries, I mean queries that are quite a few lines long,
perhaps with joins to a few tables, maybe some functions involved as well.
If you’re putting SQL queries in your application code,
I would suggest using stored procedures instead.
Why?
The first reason is that using stored procedures allows for more business
logic than a simple Insert, Update, or Delete statement.
If your application does simple data updates, you’re likely using an Insert statement to add new
records, an Update statement to modify existing records, and a Delete statement to remove records.
These operations are pretty common.
But what if there comes a time when you need a bit more logic?
For example, maybe you want to create an audit table to keep
track of different versions of a record, or some kind of history.
Or maybe you want to check that a maximum number of records can exist for a certain status before
inserting or updating a record. The kind of logic that can’t be done easily with a check constraint.
You could do this with a trigger, but triggers can get messy and have their own issues.
If you need more logic than a standard operation, then this can be done with a stored procedure.
You create the stored procedure and call it something recognisable like insertEmployee. You
can then call this procedure from your application code, passing the required values as parameters.
The database then follows any logic you have and inserts the record.
You can do the same thing for Update and Delete operations.
So, stored procedures are helpful in this
way. You can have more business logic than standard operations.
Another reason to use stored procedures is to be
able to control the business logic for working with the data in one place.
If you have one procedure for inserting records, one for update, and one for delete,
then you have a single place that the application can work with to modify data.
The application would call the stored procedure to perform whatever data update is needed.
You could argue that you can achieve the same thing by having well-designed code.
For example, having one class or module that contains the data access code,
and all other code that needs to access the database goes through this module.
That would achieve the same effect, as long as you can stick to it.
If you are working in a company where there is a need for more than one
application to access a database, you may want to consider using stored procedures.
If you rely on the application code to access the database and run the Insert,
Update, or Delete statement, this would work. But there’s a risk that each application
would do it slightly differently. This can make it hard to diagnose issues with data.
It can also be hard to make changes in the future.
If you want to rename a column or table, or add new columns, or any change to the structure,
you’ll have to assess the impact to each system to ensure their queries won’t break.
However, if you have your query in a stored procedure, then the application does not care
what the details of the database are. You can make changes in the stored procedure without
impacting other applications. They provide their inputs and should get the same responses back.
So, if you are working with multiple applications on a single database,
using stored procedures could make this easier.
The final reason for using stored procedures is that it can simplify your application code.
When your queries start getting longer, you end up having a lot of SQL code split up over many lines,
and it can look messy with concatenation or however you end up implementing it.
However, if you use a stored procedure, your application code is much simpler.
You have a call to the procedure, and pass in some parameters. The code is quite short.
It’s also clear what it’s doing. The name of the procedure specifies what action is being taken.
You could argue that an Insert statement is pretty clear, but if you have an Update
statement doing a particular task, then perhaps the procedure is clearer.
It’s a small one, but using a stored procedure instead can simplify your application code.
Using stored procedures does have some disadvantages though.
The first disadvantage, and probably the most common one that I hear in
response to using stored procedures, is that it’s too hard to version control.
Application code is easy to use with version control.
GitHub and other tools are widely used, and many IDEs even have built-in integration,
making it easy to check in files to a source code repository.
It’s harder to do that with database code. Traditionally, database objects have been
hard to use with version control, which is why many people prefer not to use stored procedures.
However, the services and tools in recent years have made it a lot easier.
Tools like Flyway and Liquibase allow you to use SQL files to
construct your database objects and store iterations of them in your repository.
I’ve created a video on setting up Flyway here if you want to see how that's done.
Modern CI tools also allow for databases to be easily created and tested,
making source code for databases easier to manage.
So, yes, this is a valid concern, but it’s getting easier to do.
Another disadvantage is that it can be hard to convince a team to do it.
Say you agree that you should be using stored procedures instead of
application code SQL. You want your team to do it, but it’s hard to convince them. So,
you would probably leave it there and stick to the way you’re doing it now.
This is OK, as convincing a team to do something is hard, no matter what it is.
The final disadvantage is one that I hear occasionally, and that is vendor lock-in.
This is where someone says, “if I use stored procedures on my database, that means I’m stuck
with this database vendor! When I want to change databases I’ll have to rewrite the procedures!”
That’s a valid point. If the logic is on an Oracle database, and you want to move to MySQL,
then the Oracle procedures won’t work on MySQL. You’ll have to rewrite them.
However, moving database vendors does not happen very often at all. How many projects have you
been on where you migrated from one vendor to another? Even if you do this quite often,
the work involved is not trivial. It’s not just a modification of a connection string.
There’s a lot to be changed and tested. And, if the stored procedures are being used, that is a
little more effort to migrate. But you’re spending a lot of time doing the migration overall,
and you would have to test the application if the SQL was in the application code.
So, in my opinion, this isn’t a real disadvantage.
Moving database vendors is rare and work will need to be done anyway.
You could even argue that using the vendor-specific features is an advantage
as you’re making the most of the database.
If you want to learn how to set up a GitHub repository for your SQL files,
you can check out my video here. This is a good way to get started with version
control for your database, along with the Flyway video I mentioned earlier.
If you want to learn more about database design and SQL, visit my website at databasestar.com.
If you liked this video, consider subscribing to my channel.
Thanks for watching.