Is the Database or Application the Best Place for a Custom Function?

May 6, 2020 by Robert Gravelle

Deciding whether do create a function in the database or in application code can be a daunting one. All-too-often, you don’t realize that you’ve made the wrong choice until it’s a big hassle to make an about-face. Worse still is the fact that many developers base their decision on whether they’re most familiar with SQL or application coding! A better approach is to rely on the strengths of a technology to help guide your decision. In today’s blog, we’ll break down the decision making process when choosing between a user-defined function (UDF) and one that resides on the application side.

There are things that databases can do well, and things that they struggle with. Like stored procedures, functions are written in SQL. As such, they will excel at tasks where SQL shines. Here’s a list of such tasks, along with why they are best done in SQL as opposed to application code:

• joins: in application code, this could require complex array manipulation
• filtering data (i.e., where clause): in code, this could require heavy inserting and deleting of items in lists
• selecting columns: again, in application code, this could require heavy list or array manipulation
• aggregate functions: in application code, this could require arrays to hold values and complex switch cases
• foreign key integrity: in application code, this could require queries prior to insert and assumes that no one will access the data outside app
• primary key integrity – in application code, this could also require queries prior to insert and assumes that no one will access the data outside the app

Attempting to do any of the above rather than relying in SQL inevitably leads to writing a lot of code and reduced efficiency, which translates to more code to debug and maintain as well as poor application performance.

On the flip-side, DBMS do not excel at complex procedural processing; that’s the domain of application code. It’s a big reason why the debugging facilities of an Integrated Development Environment (IDE) such as VS Code or Eclipse are far superior to anything you will find in a database development environment.

The Sakila Sample Database was developed as a learning tool and has been widely shared throughout the database community. It’s a MySQL database that contains a number of tables, views, stored procedures, and functions pertaining to a fictional video rental store. One of those functions is called inventory_in_stock. It’s a UDF that accepts an inventory_id and returns a boolean value indicating whether or not that film is in stock.

Here is the inventory_in_stock function definition in Navicat Premium‘s Function Designer:

Let’s quickly run it to see how it works.

Clicking the Execute button brings up a dialog to accept the input parameters:

Here are the results:

A value of 1 indicates that the film is in stock

Now, consider what would happen if we were to replace that function with one that resides in the application. It would need to make two calls to the database to execute SQL statements. That would lead to additional network traffic and require us to maintain SQL within the application. This is a bad practice in general because it mixes database and application code together.

In today’s blog we learned that you should place your custom function code where it can best benefit from the technology’s strengths: within the application where complex procedural processing is required, and in the database where SQL is required.

Interested in finding out more about Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!