ORM: to the rescue

When it comes to back-end development, working with databases can become a hassle if you’re not a master at the sql being used.

There are many database systems to choose from. The list includes:

> Mysql

> PostgresSQL

> MariaDB

> MongoDB

> Oracle

and many others. However, the database system being used depends entirely on the requirements of the project which may change with time.

The scenarios where the DB system changes along with time can be a very complicated one; if you need to change the entire database setup from the connection to the queries.

Here is where ORM or Object-Relational-Mapping comes to the rescue — It makes it easy to write code once you get the hang of them. Not just this, an Object-Relational-Mapping tool entirely takes away the pain of writing SQL. As object-oriented programmers, we tend to think in terms of objects

Let’s take an example:

Objects mapped to tables

As we can see on the Relational side, the table Persons has the attributes Id, Name , Age and AddressId. Addresses is another table with attributes Id , Street, Number and Zip-code which references the table Persons with foreign key as AddressId.

This is mapped to the Object side and the table Persons is considered as an object: Person with the attributes as keys and the relation with the Addresses table can be represented within the object itself where Address itself can be a key inside the Person object! This is how simple the mapping is..

Tip: ORM converts the name of the table into singular form

Now, let’s get our hands dirty with few simple queries. I will be using sequelize, which is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server for comparing the sql queries for now, but this is mostly same for any ORM of your choice.

//raw
SELECT * FROM Person  
//orm                           
const data = await Person.findAll();

//raw
INSERT INTO Persons INSERT INTO (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...) ;                 
//orm
const data = await Person.create({
column1: value1, column2: value2, column3:value3 ... });

You can see how the raw query has been abstracted down to a simple javascript function keeping you dwelled with the programming language being used.

Lets take another example:

//raw
SELECT Persons.Id, Addresses.Street, Persons.Name
FROM Persons
INNER JOIN Addresses ON Persons.AddressID=Addresses.Id;
//orm
const data = await Person.findAll({
      include: [{
            model: Address
            attributes: ['Street']
               },
           ]
      attributes: ['Id', 'Name']
      });

We all agree that raw queries are specially scary with joins among the tables. But ORMs have a very easy way of querying tables with associations . Given above, is a simple example of fetching data from two tables.

Depending on the ORM you get a lot of advanced features out of the box, such as support for transactions, connection pooling, migrations, seeds, streams, and all sorts of other goodies.

Conclusion:

Pros:

• Much less time spent to interact with a database in your program
• Abstracts away the database being used, which makes it easier to swap to another backend
• If you have weak SQL skills, the generated queries are at least as good as if you wrote them, if not more performant.

Cons:

• If you need a very highly optimized query and you can write said query, it may perform better than the generated ones.
• There is some amount of mental overhead related to learning an ORM library
• Most ORMs require some amount of configuration

In the end it is always the way you like. Don’t forget to give the 👏 😇

Thanks to Apurva Jain for her advice and encouragement.