# 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.
# Getting started
To get started with TypeORM, you will need to install the following:
- Install the npm package:
- You will need to install
reflect-metadata
shim:
- Install a database driver:
- for MySQL or MariaDB
- 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/"),
},
};
# 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);
}
}