In the constantly changing landscape of database design, a persistent argument continues: where should the business logic be placed? For decades, DBA experts advised to preserve the business rules in a database. However, the opposite meaning has emerged and gained popularity very fast.
Like many other things in the software engineering domain, things are seldom black or white, and conventional wisdom doesn’t hold universally. This article will help you understand why migrating logic to the application layer is a great idea and how to do that.
What is Business Logic?
Database business logic is the backbone of modern enterprises, quietly orchestrating the flow of information behind the scenes. In essence, it's the set of rules and procedures that govern how data is stored, retrieved, and manipulated within a database system.
At its core, business rules encapsulates the business rules and policies that dictate how data should be handled. This can include everything from validating input to enforcing security measures and maintaining data integrity. Besides, business rules determine how data is created, stored, and managed.
Traditionally, the storage of domain rules within the database itself has been a common practice. Stored procedures, triggers, and functions find their natural abode at the database level, tightly integrated with the data they operate on. However, as data management evolved, so did the approaches to housing domain rules.
The question of where to store core functionality is an issue of heated discussions. However, it becomes clearer for many users that storing domain logic at the app layer brings to the table a number of benefits. Before we move to the discussion of migration benefits, let’s figure out what the three-tier architecture is.
Three-tier architecture is a well-established software structure that organizes app into three tiers: the presentation tier, application tier (often referred as a business logic or a middle tier), and data tier. In this architecture, each level is responsible for managing a specific task, adhering to the principle of separation of concerns.
The task of the presentation layer is to provide information to the user in a meaningful manner. This layer facilitates the user's interaction with the application's interface. It oversees user input, data visualization, and the formatting of data for display. Since the presentation layer is responsible for presenting information from the app to the user, and collecting data from the users, it should not contain any business rules.
The app tier is where the application's rules are executed. It receives instructions from the presentation layer, performs the necessary computations, and then returns the results. Server-side programming languages like Java, C#, or PHP are typically used to build the middle layer.
The data tier is responsible for storing and retrieving data from a database or other data storage systems. It oversees all interactions with the database, including data addition, modification, and deletion. Relational database management systems (RDBMS) like MySQL, Oracle, or Microsoft SQL Server are commonly used to construct the data layer.
Basically most of the applications have business rules within the data layer in the form of stored procedures and functions. However, such an approach of storing software rules may create additional load on the database. Migrating business logic to Java, for example, opens up new opportunities for development and allows companies to leverage a robust Java ecosystem. In fact, it is not the only advantage of migrating business processes to a middle tier, it offers numerous other opportunities.
Business Logic at the App Level: Top Benefits
Placing application rules within the app server offers a myriad of advantages. Let’s review the main ones:
- Performance. Previously, the speed of execution of the database rules, which is often actually higher, was important. But with the huge increase in the number of users, increasing data transfer speeds, scaling has become much more influential on system performance. App servers are much easier to scale than database servers. For example, an application can be placed in a container and used as needed, depending on the load created by users. As a result, it will be much easier and cheaper to get the performance you need from the system.
- Don’t Repeat Yourself (DRY). Moving core functionality from the data to the middle level enhances adherence to the "Don't Repeat Yourself" principle by eliminating redundancy and centralizing processes. The DRY principle stands for removing repetitive patterns and duplicate rules in favor of referenceable code. One of the arguments for this principle is that it makes a codebase easier to maintain and eliminates code redundancy. However, complying with this principle can be challenging as the SQL queries are based on the sets of rows and columns which can make it tricky to avoid repetition when dealing with similar but not identical queries. Overall, this approach fosters a more maintainable, scalable, and reliable codebase.
- Enhanced configuration and version control capabilities. Such Integrated Development Environments as IntelliJ IDEA, Eclipse, Netbeans, and VScode provide extensive configuration options for managing code. By centralizing business processes within the application, developers can leverage IDE features to configure and customize the logic more effectively than when it's scattered across database objects. Additionally, version control systems like Git enable precise management of changes to the application codebase. With domain logic in the middle tier, developers can use Git to track, review, and revert changes systematically, ensuring better collaboration and code stability.
- Faster development. Debugging and testing app code is easier with robust IDE tools (IntelliJ IDEA, for example), streamlining the development process. Such code also benefits from greater reusability and flexibility, enabling quicker iterations and adaptations to changing requirements without impacting the underlying RDBMS. Overall, these advantages contribute to accelerated development cycles and faster time-to-market for software products.
Apart from that, some databases have a number of restrictions for developing business logic in SQL. First of all, this pertains to such data warehouses as Amazon Redshift, Azure Synapse Analytics, BigQuery, Snowflake, Vertica, etc.
If you plan to use them, then you have virtually no choice but to place the domain rules in the application. In addition, if you use Oracle with a complex business logic and want to switch to one of the RDBMS with limited SQL capabilities, then the most rational solution would be to convert business logic from PL/SQL to Java.
How to migrate business logic to Java?
Considering that the process of migrating business logic is a meticulous one, the best option would be automating this process.
SQLWays stands out as a reliable solution for automated db migration. It has an intelligent migration core that consists of thousands of conversion rules, which make the automated process smooth and hassle-free.
This tool provides a number of features that facilitate migration to an app tier:
- Automated conversion of SQL to Java. The tool supports a wide list of sources, including Oracle, Sybase, Microsoft SQL Server, PostgreSQL, Informix, MySQL, Teradata, Interbase, Firebird, DB2 LUW, DB2 OS/390, DB2 AS/400. SQLWays automates the entire database migration, including data migration, migrating database objects - procedures, stored functions, constraints, etc.). Regardless of the source you have, SQLWays ensures a high level of automation rate that definitely saves your time and effort.
- Migration with/without connection to the source database. SQLWays functions seamlessly with or without a db connection. Regardless of the option chosen, it facilitates the smooth migration of business logic from the data to the application level. Nevertheless, it's noteworthy that establishing a connection to the database enables Ispirer Toolkit to incorporate supplementary data from the database, thus enhancing the conversion process.
- If there's a switch of the RDBMS, Embedded SQL undergoes automatic rewriting to conform to the syntax of the new database seamlessly. This ensures that the application remains compatible and functional across various database platforms, saving time and effort in manual code adjustments. When converting, you can choose a database access API: Hibernate or Spring JDBC.
- Customization. To account for peculiarities of each specific source RDBMS, SQLWays can be customized by our experts. In order to improve the automation rate, we can add new conversion rules to the core of the tool and ensure that the automation will be close to 100%.
Oracle PL/SQL to Java Conversion Demo
Check out how Ispirer Toolkit automatically converts Oracle PL/SQL to Java application.
Closing word
In this article, we looked closely at moving business logic from the database to the application layer. Of course, this article is unlikely to resolve many years of controversy about where business logic should be stored. However, we hope that this article will be useful to those who are faced with a difficult choice and will be able to more easily decide to migrate to the application tier.
If you are interested in migrating from SQL to Java, try SQLWays. We provide a free plan for those who want to try our tool before making a purchase.
