# TypeORM Model Convention

TypeORM (opens new window) is one of the most mature Object Relational Mapper (ORM) available in the node.js world that is written in TypeScript, it works pretty well with ExpressWebJs framework.

⚠️ TypeORM is not managed by the ExpressWebJs core team. Please, report any issues found with the library in the appropriate repository.

# Getting started

To get started with TypeORM, you will need to install the following:

  1. Install the npm package:
  1. You will need to install reflect-metadata shim:
  1. Install a database driver:
  • for MySQL or MariaDB
⚠️ You can install mysql2 instead as well
  • for PostgreSQL or CockroachDB
  • for SQLite
  • for Microsoft SQL Server
  • for sql.js
  • for Oracle

To make the Oracle driver work, you need to follow the installation instructions from their (opens new window) site.

  • for SAP Hana

SAP Hana support made possible by the sponsorship of Neptune Software (opens new window).

  • for MongoDB (experimental). Instead use Mongoose model convention

# Establishing TypeORM connection

Interaction with the database is only possible once you setup a connection. To do that update your database details in .env file located in the root directory. Let assume we are working with MySql database.

DB_SHOULD_CONNECT=true
DB_DRIVER=postgres
DB_HOST=localhost
DB_PORT=5444
DB_USER=database_user
DB_PASSWORD=database_password
DB_DATABASE=database

Once that is done, you should also update database configuration file in Config/database.ts.

import * as typeorm from "typeorm";
import { env, orm } from "expresswebcorets/lib/Env";
import { Path } from "expresswebcorets/lib/Utils/Path";
import { DBConnection, TypeORMConfigurationType } from "expresswebcorets/lib/Database/DataSourceConfig";

export default {
  /*
  |--------------------------------------------------------------------------
  | Database ORM
  |--------------------------------------------------------------------------
  | ExpressWeb currently supports the following Object Relational Mappers(ORM)
  | Objection for sql databases and  Mongoose for mongo DB. You need to select
  | one depending on the type of database you are working on.
  |
  */
  ORM: env("ORM", orm.TypeORM),

  /*
  |--------------------------------------------------------------------------
  | Database Provider
  |--------------------------------------------------------------------------
  | With respect to the orm you selected, you need to import the provider and
  | assign it to provider.
  | Example:
  | for objection, import * as objection from "objection"
  | for typeorm, import * as typeorm from "typeorm"
  | Then assign typeorm to provider like this:
  | provider: typeorm
  |
  */
  provider: typeorm,

  /*
  |--------------------------------------------------------------------------
  | Database Multitenance
  |--------------------------------------------------------------------------
  | Database multitenance can be activated by switching the value to true and can
  | be deactivated by switching it to false.
  |
  */
  database_multitenance: env("DB_MULTITENANCE", false),
  /*
  |--------------------------------------------------------------------------
  | Multitenance Connections
  |--------------------------------------------------------------------------
  | Database multitenance connection enables interaction with multiple
  | SQL databases where each database is a tenant in your system.
  | The tenant array accepts an object of database connections (tenants).
  |
  */
  multitenant_tenants: DBConnection.multitenant<TypeORMConfigurationType>("TypeORM", []),

  /*
  |--------------------------------------------------------------------------
  | Database Connection
  |--------------------------------------------------------------------------
  | Here we define connection settings for both TypeORM, Objection, and mongoose.
  | For typeORM, npm i --save typeorm
  | For Objection, npm i --save objection
  | For Mongoose, npm i --save mongoose
  | --------------------------------------------------------------------------
  | For SQL db, install the driver of your choice
  | mysql driver, npm i --save mysql mysql2
  | postgres driver, npm i --save pg pg-hstore
  |
  */
  connection: DBConnection.connect<TypeORMConfigurationType>({
    type: env("DB_DRIVER"),
    host: env("DB_HOST"),
    port: Number(env("DB_PORT")),
    username: env("DB_USER"),
    password: env("DB_PASSWORD"),
    database: env("DB_DATABASE"),
    entities: ["App/Model/*.ts"],
    logging: false,
    synchronize: false,
  }),

  /*
  |--------------------------------------------------------------------------
  | Migration Configuration
  |--------------------------------------------------------------------------
  | Here we have database migration configuration.
  | Which includes the following:
  */
  migrations: {
    directory: Path("Database/Migrations/"),
    tableName: "migrations",
    stub: Path("Database/Migrations/migrationLayout.stub"),
    extension: "ts",
  },

  /*
  |--------------------------------------------------------------------------
  | Seed Configuration
  |--------------------------------------------------------------------------
  | Here we have database seed configuration.
  | Which includes the following:
  */
  seeds: {
    directory: Path("Database/Seeds/"),
  },
};
⚠️ Setting synchronize: true shouldn't be used in production - otherwise you can lose production data.

# Model and Repository

To get started, let’s create a Users model. Models typically live in the App directory.

The easiest way to create a model instance is using the make-sql-model command:

 ts-node maker make-sql-model User

If you would like to generate a database migration when you generate the model, use the m option:

Proceeding to creating user model, we need to create user model interface:

export interface IUserModel {
  id: number;
  first_name: string;
  last_name: string;
  email: string;
  password: string;
  created_at?: Date;
  updated_at?: Date;
}

Now, let’s look at an example User model, which we will use to retrieve and store information from our users database table:

import { IUserModel } from "./IUserModel";
import { Entity, Column, PrimaryGeneratedColumn } from "typeorm";

@Entity("users")
export class UserModel implements IUserModel {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column()
  first_name!: string;

  @Column()
  last_name!: string;

  @Column()
  email!: string;

  @Column()
  password!: string;

  @Column({ type: "timestampz", default: () => "CURRENT_TIMESTAMP(6)" })
  created_at?: Date;

  @Column({ type: "timestampz", default: () => "CURRENT_TIMESTAMP(6)", onUpdate: "CURRENT_TIMESTAMP(6)" })
  updated_at?: Date;
}

Lets create our UserRepository class in App/Repository directory.

import { UserModel } from "App/Model/UserModel";
import { IUserModel } from "App/Model/IUserModel";
import { TypORMRepository } from "Elucidate/Repository/TypeORM";

export class UserRepository extends TypORMRepository<IUserModel> {
  constructor() {
    super(UserModel);
  }
}

We can add other methods in our repository

import { Users } from "App/Model/UserModel";
import { IUserModel } from "App/Model/IUserModel";
import { TypORMRepository } from "Elucidate/Repository/TypeORM";

export class UserRepository extends TypORMRepository<IUserModel> {
  constructor() {
    super(Users);
  }

  findByName(first_name: string, last_name: string) {
    return this.findOne({ first_name, last_name });
  }
}

Now we can use it in our UserService class in App/Service/UserService directory.

import { UserRepository } from "App/Repository/UserRepository";

export class UserService {
  public async getUser(user_id: number): Promise<User> {
    return await new UserRepository().findOne(user_id);
  }
}

Instead of always creating new instances of UserRepository, you can register it in App Service Provider and then inject it into any class.

import { UserRepository } from "App/Repository/UserRepository";

class AppServiceProvider extends ServiceProvider {
  /**
   * Register any application services.
   * @return void
   */
  public register() {
    this.singleton(UserRepository);
    this.singleton(UserService);
  }
}

You can now inject UserRepository into UserService

import { UserRepository } from "App/Repository/UserRepository";

export class UserService {
  constructor(private userRepository: UserRepository) {}

  public async getUser(user_id: number): Promise<User> {
    return await this.userRepository().findOne(user_id);
  }
}