Simplify Database Migration by Using Object Relational Mapping

0
4212

Web applications are developed using different programming languages for various components. Effecting changes to the database applications within the app is a cumbersome process and is time consuming. Object relational mapping is a lazy person’s way out, making it simple to migrate data.

ORM or object relational mapping is a technique or a toolkit to access the database from programming languages or frameworks. Though ORM has been used in the industry for a long time, many people are not too aware about it. This article throws light on this topic, with budding developers as its target audience.

The normal scenario of a project
Let’s look at a Web application. The front-end/Web pages might be programmed in HTML/ jQuery and the server side may be in any scripting language like PHP or a framework like Django. The database may be Oracle or SQL Server.

The connection to the database is coded (called the connection string) in the scripting language, specific to the database, by giving the following parameters:

dbname, user, password, port number, etc.

Inside the application, we might have written several SQL queries that are specific to the vendor database.

Disadvantages
Let’s look at a scenario in which, for certain business purposes, the client wants to change to some open source database like MySQL. It is a difficult/ cumbersome activity for the developers involved to change all the DB queries to MySQL, including the connection string. This would mainly affect the time required to do the development/changes and the testing – both of which might result in a lower profit margin.

How ORM fits into the picture
Tables in the database schema are defined as classes
in Django, the widely used and the most popular
framework of Python.
An example of an employee class/table is:

from django.db import models
class Employee(models. Model):
first_name = models.CharField(max_length=30)
last_name = models.CharField(max_length=30)
age = models.IntegerField()

After the classes are defined, we need to create the migration file.

Migration
Migration is to a database what GIT or SVN is to the code base. It keeps track of changes in the schema by creating files for each one. For each change, we need to create a migration file
and then execute this file to replicate the specified change to the underlying database. The commands for these operations vary based on the ORM we use.

Why ORM is needed
While developing any application, by using ORM, we get the following advantages.

1. Productivity

  • Eliminates lots of repetitive code
  • Database schema is generated automatically

2. Maintainability

  • Fewer lines of code – easier to understand
  • Easier to manage changes in the table/class

3. Performance

  • Lazy loading – associations are fetched when needed
  • Caching

4. Vendor-independent

  • The underlying database is abstracted away
  • In the code below, ORM supports multiple databases. A query in ORM looks like what follows:
Employee.objects.filter(first_name='xxxx')

…which is the same as what’s shown below:

select * from employee where first_name='xxxx'

In the above fashion, any SQL query can be written
in ORM. There is no need for the developer to write complex SQL queries. An ORM query is converted into an equivalent vendor-specific database query for execution. This is a lazy way of doing things, i.e unless we try to retrieve some properties from the query result set, the conversion process or the query execution won’t happen.
Django has its own built-in ORM. We can also use SQLalchemy, which is an open source ORM toolkit for the Python frameworks. Apart from these two, there are some other open source ORMs such as SQLObject, Propel, Redbean, and so on.
To learn more about the ORM in Django, go to https://docs.djangoproject.com/en/2.2/topics/db/models/.

LEAVE A REPLY

Please enter your comment!
Please enter your name here