ORM

Sequelize or Type ORM

Sequelize + Typescript

- Written in Javascript. No official Typescript typings.

- The maintainers have rejected the proposal to provide official typings.

- The community driven typings at definitely typed make Sequelize workable but do not provide any significant advantage for typescript.

- Complicated and confusing documentation

- Higher learning curve

- ts-sequelize is available but not popular

Why not Sequelize? (completely opinionated)

- We explored the following options:

- Knex

- Loopback

- Typeorm

In search for an Alternative

- Knex has an amazing query builder

- But it is not a full blown ORM per se

Why not Knex?

- Typescript typings are maintained by the community

- knex-next (typescript + internal rewrite) is a work in progress but does not have a complete release

- It is a complete framework like express and koa and not an ORM

- Loopback 4 is in Typescript but wasn't released back then

Why not Loopback?

- Loopback 3 doesn't have official typings

- Written in Typescript

- Influenced by Hibernate and Entity Framework

- Easy to learn

- Easy to maintain

- Typescript classes allows extension for custom logic

- Well documented

- Built in Cache (with a catch)

- Amazing knex like query builder

- Support for all major databases

- And much more!

Why TypeORM?

- Entities

import { Entity, PrimaryGeneratedColumn, Column, OneToMany, Index } from 'typeorm';
import { LanguageData } from './language-data';

@Entity('states')
export class State {
  @PrimaryGeneratedColumn()
  public id: number;

  @Column({
    type: 'character varying',
    length: 255,
    unique: true,
    nullable: false,
  })
  @Index()
  public code: string;

  @OneToMany(() => LanguageData, languageData => languageData.state)
  public languageData: LanguageData[];
}

// complete decorator reference:
// https://github.com/typeorm/typeorm/blob/master/docs/decorator-reference.md
// https://github.com/typeorm/typeorm/blob/master/docs/entities.md

TypeORM 101

- Simple Data Fetch

const user = new User();
user.firstName = "Timber";
user.lastName = "Saw";
user.age = 25;
await repository.save(user);

const allUsers = await repository.find();
const firstUser = await repository.findOne(1); // find by id
const timber = await repository.findOne({ firstName: "Timber", lastName: "Saw" });

await repository.remove(timber);

TypeORM 101

- Simple Data Fetch

const user = new User();
user.firstName = "Timber";
user.lastName = "Saw";
user.age = 25;
await user.save();

const allUsers = await User.find();
const firstUser = await User.findOne(1);
const timber = await User.findOne({ firstName: "Timber", lastName: "Saw" });

await timber.remove();

TypeORM 101

Install

Install the npm package:

npm install typeorm --save
You need to install reflect-metadata shim:

npm install reflect-metadata --save

and import it somewhere in the global place of your app (for example in app.ts):

import "reflect-metadata";
You may need to install node typings:

npm install @types/node --save

Quick start

First, install TypeORM globally:
```
npm install typeorm -g
```
Then go to the directory where you want to create a new project and run the command:
```
typeorm init --name MyProject --database mysql
```

ORM config

{
   "type": "mysql",
   "host": "localhost",
   "port": 3306,
   "username": "test",
   "password": "test",
   "database": "test",
   "synchronize": true,
   "logging": false,
   "entities": [
      "src/entity/**/*.ts"
   ],
   "migrations": [
      "src/migration/**/*.ts"
   ],
   "subscribers": [
      "src/subscriber/**/*.ts"
   ]
}

Simple Connection

import "reflect-metadata";
import {createConnection} from "typeorm";
import {Photo} from "./entity/Photo";

createConnection({
    type: "mysql",
    host: "localhost",
    port: 3306,
    username: "root",
    password: "admin",
    database: "test",
    entities: [
        Photo
    ],
    synchronize: true,
    logging: false
}).then(connection => {
    // here you can start to work with your entities
}).catch(error => console.log(error));

- Entities

import { Entity, PrimaryGeneratedColumn, Column, ManyToOne, OneToMany, JoinColumn } from 'typeorm';
import { LanguageData } from './language-data';
import { State } from './state';

@Entity('cities')
export class City {
  @PrimaryGeneratedColumn()
  public id: number;

  @Column({
    type: 'number',
    default: false,
  })
  public stateId: number;

  @ManyToOne(() => State, { nullable: false })
  @JoinColumn({
    name: 'stateId',
    referencedColumnName: 'id',
  })
  public state: State;

  @Column('point', { nullable: true })
  public geom: object;

  @OneToMany(() => LanguageData, languageData => languageData.city)
  public languageData: LanguageData[];
}
// complete decorator reference:
// https://github.com/typeorm/typeorm/blob/master/docs/decorator-reference.md
// https://github.com/typeorm/typeorm/blob/master/docs/entities.md

TypeORM 101

- Extending entities

import { CreateDateColumn, UpdateDateColumn } from 'typeorm';

export abstract class AuditColumn {
  @UpdateDateColumn()
  public updatedAt: Date;

  @CreateDateColumn()
  public createdAt: Date;
}

@Entity('users')
export class User extends AuditColumn {}

TypeORM 101

- Queries with builtin caching

export const findById = async (id: number, roleName?: string):
  Promise<City | undefined> => {
  const cacheKey = `${CachePrefixes.CITY}_findById_id_${id}`;
  const cityRepo = RepositoryFactory.getCityRepository(roleName);
  return cityRepo.findOne({
    where: {
      id,
    },
    cache: {
      id: cacheKey,
      milliseconds: config.default.database.cacheDuration,
    },
  });
};

// https://github.com/typeorm/typeorm/blob/master/docs/caching.md
// https://github.com/typeorm/typeorm/blob/master/docs/find-options.md

TypeORM 101

- Query Builder

export const countAllOwners = async (roleName?: string): Promise<number> => {
  return await RepositoryFactory.getUserRepository(roleName)
    .createQueryBuilder('users')
    .innerJoin('users.userType', 'userType')
    .where(`userType.key in ('${UserTypes.CLIENT}', '${UserTypes.BETTS}')`)
    .getCount();
};

// https://github.com/typeorm/typeorm/blob/master/docs/select-query-builder.md
// https://github.com/typeorm/typeorm/blob/master/docs/relational-query-builder.md

TypeORM 101

- Extending TypeORM through the Repository Factory

TypeORM 101

class TypeORMRepositoryWrapper<Entity> {
  public entityTableName: string;
  public roleName: string;
  public entityClass: ObjectType<Entity>;
  public repository: Repository<Entity>;

  constructor(
    entityTableName: string, roleName: string, entityClass: ObjectType<Entity>,
    connectionName: string = 'default',
  ) {
    this.entityTableName = entityTableName;
    this.roleName = roleName;
    this.entityClass = entityClass;
    this.repository = getConnectionManager()
      .get(connectionName)
      .getRepository<Entity>(this.entityClass);

    this.repository.save = async ( // and more methods like update, find, findOne etc
      entityOrEntities: Entity | Entity[], options?: SaveOptions,
    ): Promise<any> => {
      let sanitizedData: any;
      sanitizedData = await sanitizeRecursively(entityOrEntities, this.roleName);
      return this.repository.manager.save(
        this.repository.metadata.target,
        sanitizedData as any,
        options,
      );
    };
}

- Extending TypeORM through the Repository Factory

TypeORM 101

export class RepositoryFactory {

  public static getUserRepository = (roleName: string = ROLES.default.name): Repository<User> => {
    return new TypeORMRepositoryWrapper(ENTITIES.users.tableName, roleName, User).repository;
  };

  public static getCandidateRepository = (
    roleName: string = ROLES.default.name,
  ): Repository<Candidate> => {
    return new TypeORMRepositoryWrapper(ENTITIES.candidates.tableName, roleName, Candidate)
      .repository;
  };

  public static getCityRepository = (roleName: string = ROLES.default.name): Repository<City> => {
    return new TypeORMRepositoryWrapper(ENTITIES.cities.tableName, roleName, City).repository;
  };
}
// and many more

- TypeORM is far from being as mature as Sequelize

- TypeORM 1.0 is still not released but its expected to be available by the end
of this year

- A lot of major features are not supported such as views

- Builtin Cache creates its own Redis Connection

- The documentation for the next version of TypeORM does not exist so we
have to go look into the code.

- Slow response on github issues.

Some Gotchas and Drawbacks of TypeORM

Questions?

https://github.com/tkssharma/blog

Baseline Repo for Nest and Type ORM

Thank You