Relational databases are an integral part of any company’s software stack and are widely used in the IT industry. There is hardly any company that does not use a relational database to store important data. It is a $32 billion industry that keeps growing every year.
Most applications that use a relational database to store data are often very tightly integrated with the backend, making it challenging to migrate them to a different vendor. This article will help you learn how to overcome this challenge and make database migration between various RDBMS a breeze. It is divided into three sections.
It is very easy to overlook long-term extensibility when developers have a deadline to meet for their current project. As a result, many developers write code that adheres to every functional requirement of the project but cannot decouple the database in the future. This practice makes database migration a highly complex task.
The following are several examples of what not to do when writing queries against a database.
Consider the following pseudo-code written in a typical web application that accepts input from a web form and inserts data into a table within the database.
Set firstName = $FormValue(“fn”) Set lastName = $FormValue(“ln”) Set query = “INSERT INTO WebUser values(?, ?, GetDate()) query.prepare(firstName, lastName) query.execute();
In the above example, a SQL query is being executed in the same part of the code that fetches the values from the HTML form. There are several problems with this code:
GetDate()method in the example above, which inserts the current date into the row. Every database does not support this syntax. Therefore, you will have to change every occurrence of this insert statement if you decide to migrate to a different database in the future.
This is similar to the merged tier problem discussed above but with a more granular insight. Consider the following billing object definition as an example:
class Billing( + fullName + address + email + sendMonthlyNewsletter() + processPayments() )
The class above is responsible for two things:
Notice how the business logic is coded in the exact location where the data resides. As a result, it is very likely that server SQL queries with complex JOIN statements are also hardcoded inside the two example methods.
Mixing logical code with static data objects makes future migration very difficult, particularly when SQL queries are hardcoded inside the method. Imagine you switch from a database that is case insensitive to a different RDBMS where case matters. In such instances, you will have to modify and test all of your business logic to ensure the system’s integrity.
Triggers and Stored Procedures can be easy to implement and often render better performance. However, they tie you up with a specific RDBMS. The SQL dialect used across multiple vendors is often quite different from each other. Therefore, porting code from one backend to a different one is not easy when you need to migrate to a different database.
It is important not to mix the code that fetches and modifies data with any other business logic in order to keep your system portable and extensible. One common practice to enforce such a rule is to use Object-relational mapping (ORM) tools. These tools facilitate mapping between tables and classes of any object-oriented language.
There are two types of ORM:
Tools that offer such mapping create dynamic objects that can be either queried through reflection or abstract methods. Consider the following example demonstrating a pseudo-code that uses such a library.
fn = db.getTable(“customer”).fetchPk(10).getField(“fn”)
In the above example, the variable
Such tools are often embedded into third-party libraries, which could have licensing restrictions when deployed. Additionally, there may be a slight performance penalty at runtime, mainly if reflection is used extensively.
Several companies offer such tools, many of which are available in the open-source community.
Here is a list of some of them, along with their supported language.
This approach involves generating code for classes based on the back-end design. The generated code is typically not tied to any library and does have any dependencies. You will have to create the code if the back-end design changes.
Since the generated code is static, typically, there are no runtime library requirements or licensing issues.
One such tool that enables automatic code generation is WinSQL, which can generate classes in different languages based on the tables.
Switching the backend database to a different RDBMS becomes relatively more straightforward if your application is designed in a portable way, as described above. The following section lays out a step-by-step guide that you can follow to migrate from one database to another effortlessly. Migrating a database involves the following steps:
The SQL language is about 90% similar across every database. However, the remaining 10% makes migrating from one database to another a tedious task. Many RDBMS offer reverse engineering the schema of an existing database.
The output of these tools is often a file containing SQL statements. Unfortunately, it is not always possible to run the exact script against a different database because of:
Translating security objects is a tedious task, and unfortunately, there are very few migration tools. Although you could generate generic statements like GRANT permissions from the source database and run it against the target, no database will allow you to capture user passwords in a script. Therefore, creating user accounts on the target database usually requires human intervention.
Converting SQL dialect from one vendor from a different back-end is the most tedious step. The dialect of TransactSQL used in MS SQL Server is utterly different from PL/SQL in Oracle.
It is this step that prevents companies from switching the backend database to a different vendor. If possible, avoid writing complex stored procedures unless you are confident you will not be migrating to a different RDBMS in the future.
Several tools are available in the market that can help you move the actual data from a database. There are two types of migrations:
WinSQL, besides being an excellent querying tool, helps programmers and DBA when performing data migration tasks. On one hand, It can help programmers adhere to best practices when writing their code. On the other hand, it could help administrators move schema and data.
Programmers can generate thousands of lines of code with just a few mouse clicks. This generated code represents static objects within a database increasing productivity as well as facilitating future database migration projects. The code generation feature in WinSQL generates 3 classes for every table:
If the database design change in the future, all you have to do is to recreate the ValueObject and DataAccessObject. The following image display a class diagram of these classes.
When it comes to data migration, WinSQL can not only help administrators migrate schema but also the actual data. Additionally, there are several methods of migration:
Migrating the backend RDBMS system is often a time-consuming and expensive task. However, with careful software design and the right tools, this task can become more manageable and save days worth of work.
Tools like WinSQL can help developers write tedious code and help move the actual data with proper data conversion. It can generate thousands of lines of code within seconds that are royalty-free and can be used in any application.
|Created on:||May 6, 2021|
|Last updated on:||Jun 11, 2021|