What is ORM (Object-Relational Mapping) technologies

ORM (Object-Relational Mapping) technologies are used to map objects in programming languages to database tables, allowing developers to interact with databases using the object-oriented paradigm. This simplifies database operations by abstracting the SQL queries and enabling CRUD (Create, Read, Update, Delete) operations through object methods. Here are examples of ORM technologies in Node.js and .NET.

Node.js ORM Technologies

1. Sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It supports many database systems and provides a powerful toolkit for interacting with databases using JavaScript.

• Installation:

  npm install sequelize
  npm install mysql2  # For MySQL
  

• Example:

  const { Sequelize, DataTypes } = require('sequelize');
  const sequelize = new Sequelize('database', 'username', 'password', {
    host: 'localhost',
    dialect: 'mysql'
  });
  
  const User = sequelize.define('User', {
    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true
    }
  });
  
  (async () => {
    await sequelize.sync({ force: true });  // Create tables
    const user = await User.create({ name: 'John Doe', email: '[email protected]' });
    console.log(user.toJSON());
  })();
  

2. TypeORM

TypeORM is an ORM that can run in Node.js, browsers, Cordova, PhoneGap, Ionic, React Native, NativeScript, Expo, and Electron platforms. It supports multiple databases and is written in TypeScript.

• Installation:

  npm install typeorm reflect-metadata
  npm install mysql  # For MySQL
  

• Example:

  import { Entity, PrimaryGeneratedColumn, Column, createConnection } from 'typeorm';
  
  @Entity()
  class User {
    @PrimaryGeneratedColumn()
    id: number;
  
    @Column()
    name: string;
  
    @Column()
    email: string;
  }
  
  createConnection({
    type: 'mysql',
    host: 'localhost',
    port: 3306,
    username: 'test',
    password: 'test',
    database: 'test',
    entities: [User],
    synchronize: true,
  }).then(async connection => {
    const userRepository = connection.getRepository(User);
    const user = new User();
    user.name = 'John Doe';
    user.email = '[email protected]';
    await userRepository.save(user);
    console.log(await userRepository.find());
  });
  

3. Prisma

Prisma is an ORM technology, specifically designed for modern database access in JavaScript and TypeScript applications. Prisma offers a type-safe and auto-generated query builder that makes database interactions more intuitive and less error-prone.

.NET ORM Technologies

1. Entity Framework Core

Entity Framework Core (EF Core) is a lightweight, extensible, open-source, and cross-platform version of the popular Entity Framework data access technology. EF Core can serve as an ORM to a wide variety of databases.

• Installation:

  dotnet add package Microsoft.EntityFrameworkCore.SqlServer
  

• Example:

  using Microsoft.EntityFrameworkCore;
  using System;
  using System.Linq;
  
  public class ApplicationDbContext : DbContext
  {
      public DbSet<User> Users { get; set; }
  
      protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
      {
          optionsBuilder.UseSqlServer(@"Server=.;Database=TestDb;Trusted_Connection=True;");
      }
  }
  
  public class User
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public string Email { get; set; }
  }
  
  class Program
  {
      static void Main()
      {
          using (var context = new ApplicationDbContext())
          {
              context.Database.EnsureCreated();
              context.Users.Add(new User { Name = "John Doe", Email = "[email protected]" });
              context.SaveChanges();
  
              var user = context.Users.First();
              Console.WriteLine($"User: {user.Name}, Email: {user.Email}");
          }
      }
  }
  

2. Dapper

Dapper is a simple object mapper for .NET that extends IDbConnection. It provides a balance between control over raw SQL and the simplicity of an ORM.

• Installation:

  dotnet add package Dapper
  

• Example:

  using System;
  using System.Data.SqlClient;
  using Dapper;
  
  class Program
  {
      static void Main()
      {
          using (var connection = new SqlConnection("Server=.;Database=TestDb;Trusted_Connection=True;"))
          {
              connection.Execute("CREATE TABLE IF NOT EXISTS Users (Id INT PRIMARY KEY IDENTITY, Name NVARCHAR(100), Email NVARCHAR(100));");
              connection.Execute("INSERT INTO Users (Name, Email) VALUES (@Name, @Email)", new { Name = "John Doe", Email = "[email protected]" });
  
              var user = connection.QueryFirst<User>("SELECT * FROM Users WHERE Name = @Name", new { Name = "John Doe" });
              Console.WriteLine($"User: {user.Name}, Email: {user.Email}");
          }
      }
  }
  
  public class User
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public string Email { get; set; }
  }