Object-Relational Mapping (ORM)

What is ORM?

ORM, short for Object-Relational Mapping, is an additional piece of software or a service that acts as a bridge between your application code and the database. Its main responsibility is to help you retrieve or save data to the database with ease, without writing complex SQL queries.

---

Famous ORMs

• TypeORM: Ideal for SQL databases like PostgreSQL, MySQL, and others. It also supports MongoDB but isn’t as optimized for it as Mongoose.

• Mongoose: A great ORM specifically for MongoDB.

• Other ORMs:

  • Prisma

  • MikroORM

  • Sequelize

---

Popular Databases

Databases can broadly be categorized into SQL and NoSQL. Here are some examples:

• SQL Databases:

  • PostgreSQL

  • MySQL

  • MariaDB

  • CockroachDB

  • Oracle

• NoSQL Databases:

  • MongoDB

---

ORM as an Abstraction Layer

ORMs provide an abstraction over query languages like SQL. Instead of writing raw SQL queries, you can use simple methods in your code.

For example:

• SQL Query:

    SELECT * FROM users WHERE id = 1;
  

• TypeORM Query:

    userRepository.findOne(id);
  

This makes your code easier to write, read, and maintain.

---

Advantages of ORMs

1. Simplified Query Writing: Allows you to write complex queries in a simple, language-specific way (e.g., JavaScript/TypeScript).

2. Database Relationships in Code: ORM makes it easy to manage relationships (e.g., one-to-many, many-to-many) in your entities or schemas.

3. Switching Databases: ORMs abstract queries, so switching databases (e.g., from PostgreSQL to MySQL) has minimal impact on your code.

4. Indexing Support: ORMs like TypeORM make it easy to define indexes in your entities. For example:

    @Index()
    @Column()
    email: string;
   

   Indexes improve query performance.

---

Disadvantages of ORMs

1. Less Low-Level Control: While ORMs simplify query writing, they abstract away low-level details, which might be necessary for complex use cases.

2. Performance Optimization: In cases of highly complex queries, ORM-generated queries might not be as efficient as hand-written SQL queries.

3. Direct Query Support: While ORMs abstract queries, tools like TypeORM allow you to write direct database queries when needed.

For example:

await entityManager.query('SELECT * FROM users WHERE id = $1', [id]);

---

TypeORM and Indexing

One of the useful features of TypeORM is its support for adding indexes directly in the code. Indexes help optimize query performance by allowing the database to search specific columns faster.

Example:

import { Entity, PrimaryGeneratedColumn, Column, Index } from 'typeorm';

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Index()
  @Column()
  email: string;
}

---

Summary

• ORM simplifies the interaction between your codebase and databases.

• It reduces the need for complex SQL queries, helps in managing relationships, and supports indexing.

• While ORMs like TypeORM are powerful, they also allow direct query writing for performance optimization when needed.

• Choosing the right ORM depends on your database and project requirements.