Database

Nest is database agnostic, allowing you to easily integrate with any SQL or NoSQL database. You have a number of options available to you, depending on your preferences. At the most general level, connecting Nest to a database is simply a matter of loading an appropriate Node.js driver for the database, just as you would with Express or Fastify.

You can also directly use any general purpose Node.js database integration library or ORM, such as Sequelize (navigate to the Sequelize integration section), Knex.js (tutorial) and TypeORM, to operate at a higher level of abstraction.

For convenience, Nest provides tight integration with TypeORM and Sequelize out-of-the-box with the @nestjs/typeorm and @nestjs/sequelize packages respectively, which we’ll cover in the current chapter, and Mongoose with @nestjs/mongoose, which is covered in this chapter. These integrations provide additional NestJS-specific features, such as model/repository injection, testability, and asynchronous configuration to make accessing your chosen database even easier.

TypeORM Integration

For integrating with SQL and NoSQL databases, Nest provides the @nestjs/typeorm package. Nest uses TypeORM because it’s the most mature Object Relational Mapper (ORM) available for TypeScript. Since it’s written in TypeScript, it integrates well with the Nest framework.

To begin using it, we first install the required dependencies. In this chapter, we’ll demonstrate using the popular MySQL Relational DBMS, but TypeORM provides support for many relational databases, such as PostgreSQL, Oracle, Microsoft SQL Server, SQLite, and even NoSQL databases like MongoDB. The procedure we walk through in this chapter will be the same for any database supported by TypeORM. You’ll simply need to install the associated client API libraries for your selected database.

$ npm install --save @nestjs/typeorm typeorm mysql

Once the installation process is complete, we can import the TypeOrmModule into the root AppModule.

app.module.ts

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'root',
      database: 'test',
      entities: [],
      synchronize: true,
    }),
  ],
})
export class AppModule {}

The forRoot() method supports all the configuration properties exposed by the createConnection() function from the TypeORM package. In addition, there are several extra configuration properties described below.

Hint Learn more about the connection options here.

Alternatively, rather than passing a configuration object to forRoot(), we can create an ormconfig.json file in the project root directory.

{
  "type": "mysql",
  "host": "localhost",
  "port": 3306,
  "username": "root",
  "password": "root",
  "database": "test",
  "entities": ["dist/**/*.entity{.ts,.js}"],
  "synchronize": true
}

Then, we can call forRoot() without any options:

app.module.ts

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
@Module({
  imports: [TypeOrmModule.forRoot()],
})
export class AppModule {}

Warning Static glob paths (e.g., dist/**/*.entity{ .ts,.js}) won’t work properly with webpack.

Warning Note that the ormconfig.json file is loaded by the typeorm library. Thus, any of the extra properties described above (which are supported internally by way of the forRoot() method - for example, autoLoadEntities and retryDelay) won’t be applied.

Once this is done, the TypeORM Connection and EntityManager objects will be available to inject across the entire project (without needing to import any modules), for example:

app.module.ts

import { Connection } from 'typeorm';
@Module({
  imports: [TypeOrmModule.forRoot(), UsersModule],
})
export class AppModule {
  constructor(private connection: Connection) {}
}

import { Connection } from 'typeorm';
@Dependencies(Connection)
@Module({
  imports: [TypeOrmModule.forRoot(), UsersModule],
})
export class AppModule {
  constructor(connection) {
    this.connection = connection;
  }
}

Repository pattern

TypeORM supports the repository design pattern, so each entity has its own repository. These repositories can be obtained from the database connection.

To continue the example, we need at least one entity. Let’s define the User entity.

user.entity.ts

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;
  @Column()
  firstName: string;
  @Column()
  lastName: string;
  @Column({ default: true })
  isActive: boolean;
}

Hint Learn more about entities in the TypeORM documentation.

The User entity file sits in the users directory. This directory contains all files related to the UsersModule. You can decide where to keep your model files, however, we recommend creating them near their domain, in the corresponding module directory.

To begin using the User entity, we need to let TypeORM know about it by inserting it into the entities array in the module forRoot() method options (unless you use a static glob path):

app.module.ts

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { User } from './users/user.entity';
@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'root',
      database: 'test',
      entities: [User],
      synchronize: true,
    }),
  ],
})
export class AppModule {}

Next, let’s look at the UsersModule:

users.module.ts

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { UsersService } from './users.service';
import { UsersController } from './users.controller';
import { User } from './user.entity';
@Module({
  imports: [TypeOrmModule.forFeature([User])],
  providers: [UsersService],
  controllers: [UsersController],
})
export class UsersModule {}

This module uses the forFeature() method to define which repositories are registered in the current scope. With that in place, we can inject the UsersRepository into the UsersService using the @InjectRepository() decorator:

users.service.ts

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './user.entity';
@Injectable()
export class UsersService {
  constructor(
    @InjectRepository(User)
    private usersRepository: Repository<User>,
  ) {}
  findAll(): Promise<User[]> {
    return this.usersRepository.find();
  }
  findOne(id: string): Promise<User> {
    return this.usersRepository.findOne(id);
  }
  async remove(id: string): Promise<void> {
    await this.usersRepository.delete(id);
  }
}

import { Injectable, Dependencies } from '@nestjs/common';
import { getRepositoryToken } from '@nestjs/typeorm';
import { User } from './user.entity';
@Injectable()
@Dependencies(getRepositoryToken(User))
export class UsersService {
  constructor(usersRepository) {
    this.usersRepository = usersRepository;
  }
  findAll() {
    return this.usersRepository.find();
  }
  findOne(id) {
    return this.usersRepository.findOne(id);
  }
  async remove(id) {
    await this.usersRepository.delete(id);
  }
}

Notice Don’t forget to import the UsersModule into the root AppModule.

If you want to use the repository outside of the module which imports TypeOrmModule.forFeature, you’ll need to re-export the providers generated by it. You can do this by exporting the whole module, like this:

users.module.ts

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { User } from './user.entity';
@Module({
  imports: [TypeOrmModule.forFeature([User])],
  exports: [TypeOrmModule]
})
export class UsersModule {}

Now if we import UsersModule in UserHttpModule, we can use @InjectRepository(User) in the providers of the latter module.

users-http.module.ts

import { Module } from '@nestjs/common';
import { UsersModule } from './user.module';
import { UsersService } from './users.service';
import { UsersController } from './users.controller';
@Module({
  imports: [UsersModule],
  providers: [UsersService],
  controllers: [UsersController]
})
export class UserHttpModule {}

Relations

Relations are associations established between two or more tables. Relations are based on common fields from each table, often involving primary and foreign keys.

There are three types of relations:

To define relations in entities, use the corresponding decorators. For example, to define that each User can have multiple photos, use the @OneToMany() decorator.

user.entity.ts

import { Entity, Column, PrimaryGeneratedColumn, OneToMany } from 'typeorm';
import { Photo } from '../photos/photo.entity';
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;
  @Column()
  firstName: string;
  @Column()
  lastName: string;
  @Column({ default: true })
  isActive: boolean;
  @OneToMany(type => Photo, photo => photo.user)
  photos: Photo[];
}

Hint To learn more about relations in TypeORM, visit the TypeORM documentation.

Auto-load entities

Manually adding entities to the entities array of the connection options can be tedious. In addition, referencing entities from the root module breaks application domain boundaries and causes leaking implementation details to other parts of the application. To solve this issue, static glob paths can be used (e.g., dist/**/*.entity{ .ts,.js}).

Note, however, that glob paths are not supported by webpack, so if you are building your application within a monorepo, you won’t be able to use them. To address this issue, an alternative solution is provided. To automatically load entities, set the autoLoadEntities property of the configuration object (passed into the forRoot() method) to true, as shown below:

app.module.ts

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
@Module({
  imports: [
    TypeOrmModule.forRoot({
      ...
      autoLoadEntities: true,
    }),
  ],
})
export class AppModule {}

With that option specified, every entity registered through the forFeature() method will be automatically added to the entities array of the configuration object.

Warning Note that entities that aren’t registered through the forFeature() method, but are only referenced from the entity (via a relationship), won’t be included by way of the autoLoadEntities setting.

Transactions

A database transaction symbolizes a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in a database (learn more).

There are many different strategies to handle TypeORM transactions. We recommend using the QueryRunner class because it gives full control over the transaction.

First, we need to inject the Connection object into a class in the normal way:

@Injectable()
export class UsersService {
  constructor(private connection: Connection) {}
}

Hint The Connection class is imported from the typeorm package.

Now, we can use this object to create a transaction.

async createMany(users: User[]) {
  const queryRunner = this.connection.createQueryRunner();
  await queryRunner.connect();
  await queryRunner.startTransaction();
  try {
    await queryRunner.manager.save(users[0]);
    await queryRunner.manager.save(users[1]);
    await queryRunner.commitTransaction();
  } catch (err) {
    // since we have errors lets rollback the changes we made
    await queryRunner.rollbackTransaction();
  } finally {
    // you need to release a queryRunner which was manually instantiated
    await queryRunner.release();
  }
}

Hint Note that the connection is used only to create the QueryRunner. However, to test this class would require mocking the entire Connection object (which exposes several methods). Thus, we recommend using a helper factory class (e.g., QueryRunnerFactory) and defining an interface with a limited set of methods required to maintain transactions. This technique makes mocking these methods pretty straightforward.

Alternatively, you can use the callback-style approach with the transaction method of the Connection object (read more).

async createMany(users: User[]) {
  await this.connection.transaction(async manager => {
    await manager.save(users[0]);
    await manager.save(users[1]);
  });
}

Using decorators to control the transaction (@Transaction() and @TransactionManager()) is not recommended.

Hoodies, T-shirts, and accessories!

Support our future development by shopping in the official store!

See more

Subscribers

With TypeORM subscribers, you can listen to specific entity events.

import {
  Connection,
  EntitySubscriberInterface,
  EventSubscriber,
  InsertEvent,
} from 'typeorm';
import { User } from './user.entity';
@EventSubscriber()
export class UserSubscriber implements EntitySubscriberInterface<User> {
  constructor(connection: Connection) {
    connection.subscribers.push(this);
  }
  listenTo() {
    return User;
  }
  beforeInsert(event: InsertEvent<User>) {
    console.log(`BEFORE USER INSERTED: `, event.entity);
  }
}

Warning Event subscribers can not be request-scoped.

Now, add the UserSubscriber class to the providers array:

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { User } from './user.entity';
import { UsersController } from './users.controller';
import { UsersService } from './users.service';
import { UserSubscriber } from './user.subscriber';
@Module({
  imports: [TypeOrmModule.forFeature([User])],
  providers: [UsersService, UserSubscriber],
  controllers: [UsersController],
})
export class UsersModule {}

Hint Learn more about entity subscribers here.

Migrations

Migrations provide a way to incrementally update the database schema to keep it in sync with the application’s data model while preserving existing data in the database. To generate, run, and revert migrations, TypeORM provides a dedicated CLI.

Migration classes are separate from the Nest application source code. Their lifecycle is maintained by the TypeORM CLI. Therefore, you are not able to leverage dependency injection and other Nest specific features with migrations. To learn more about migrations, follow the guide in the TypeORM documentation.

Multiple databases

Some projects require multiple database connections. This can also be achieved with this module. To work with multiple connections, first create the connections. In this case, connection naming becomes mandatory.

Suppose you have an Album entity stored in its own database.

const defaultOptions = {
  type: 'postgres',
  port: 5432,
  username: 'user',
  password: 'password',
  database: 'db',
  synchronize: true,
};
@Module({
  imports: [
    TypeOrmModule.forRoot({
      ...defaultOptions,
      host: 'user_db_host',
      entities: [User],
    }),
    TypeOrmModule.forRoot({
      ...defaultOptions,
      name: 'albumsConnection',
      host: 'album_db_host',
      entities: [Album],
    }),
  ],
})
export class AppModule {}

Notice If you don’t set the name for a connection, its name is set to default. Please note that you shouldn’t have multiple connections without a name, or with the same name, otherwise they will get overridden.

At this point, you have User and Album entities registered with their own connection. With this setup, you have to tell the TypeOrmModule.forFeature() method and the @InjectRepository() decorator which connection should be used. If you do not pass any connection name, the default connection is used.

@Module({
  imports: [
    TypeOrmModule.forFeature([User]),
    TypeOrmModule.forFeature([Album], 'albumsConnection'),
  ],
})
export class AppModule {}

You can also inject the Connection or EntityManager for a given connection:

@Injectable()
export class AlbumsService {
  constructor(
    @InjectConnection('albumsConnection')
    private connection: Connection,
    @InjectEntityManager('albumsConnection')
    private entityManager: EntityManager,
  ) {}
}

Testing

When it comes to unit testing an application, we usually want to avoid making a database connection, keeping our test suites independent and their execution process as fast as possible. But our classes might depend on repositories that are pulled from the connection instance. How do we handle that? The solution is to create mock repositories. In order to achieve that, we set up custom providers. Each registered repository is automatically represented by an <EntityName>Repository token, where EntityName is the name of your entity class.

The @nestjs/typeorm package exposes the getRepositoryToken() function which returns a prepared token based on a given entity.

@Module({
  providers: [
    UsersService,
    {
      provide: getRepositoryToken(User),
      useValue: mockRepository,
    },
  ],
})
export class UsersModule {}

Now a substitute mockRepository will be used as the UsersRepository. Whenever any class asks for UsersRepository using an @InjectRepository() decorator, Nest will use the registered mockRepository object.

Custom repository

TypeORM provides a feature called custom repositories. Custom repositories allow you to extend a base repository class, and enrich it with several special methods. To learn more about this feature, visit this page.

In order to create your custom repository, use the @EntityRepository() decorator and extend the Repository class.

@EntityRepository(Author)
export class AuthorRepository extends Repository<Author> {}

Hint Both @EntityRepository() and Repository are imported from the typeorm package.

Once the class is created, the next step is to delegate instantiation responsibility to Nest. For this, we have to pass theAuthorRepository class to the TypeOrm.forFeature() method.

@Module({
  imports: [TypeOrmModule.forFeature([AuthorRepository])],
  controller: [AuthorController],
  providers: [AuthorService],
})
export class AuthorModule {}

Afterward, simply inject the repository using the following construction:

@Injectable()
export class AuthorService {
  constructor(private authorRepository: AuthorRepository) {}
}

Async configuration

You may want to pass your repository module options asynchronously instead of statically. In this case, use the forRootAsync() method, which provides several ways to deal with async configuration.

One approach is to use a factory function:

TypeOrmModule.forRootAsync({
  useFactory: () => ({
    type: 'mysql',
    host: 'localhost',
    port: 3306,
    username: 'root',
    password: 'root',
    database: 'test',
    entities: [__dirname + '/**/*.entity{.ts,.js}'],
    synchronize: true,
  }),
});

Our factory behaves like any other asynchronous provider (e.g., it can be async and it’s able to inject dependencies through inject).

TypeOrmModule.forRootAsync({
  imports: [ConfigModule],
  useFactory: (configService: ConfigService) => ({
    type: 'mysql',
    host: configService.get('HOST'),
    port: +configService.get<number>('PORT'),
    username: configService.get('USERNAME'),
    password: configService.get('PASSWORD'),
    database: configService.get('DATABASE'),
    entities: [__dirname + '/**/*.entity{.ts,.js}'],
    synchronize: true,
  }),
  inject: [ConfigService],
});

Alternatively, you can use the useClass syntax:

TypeOrmModule.forRootAsync({
  useClass: TypeOrmConfigService,
});

The construction above will instantiate TypeOrmConfigService inside TypeOrmModule and use it to provide an options object by calling createTypeOrmOptions(). Note that this means that the TypeOrmConfigService has to implement the TypeOrmOptionsFactory interface, as shown below:

@Injectable()
class TypeOrmConfigService implements TypeOrmOptionsFactory {
  createTypeOrmOptions(): TypeOrmModuleOptions {
    return {
      type: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'root',
      database: 'test',
      entities: [__dirname + '/**/*.entity{.ts,.js}'],
      synchronize: true,
    };
  }
}

In order to prevent the creation of TypeOrmConfigService inside TypeOrmModule and use a provider imported from a different module, you can use the useExisting syntax.

TypeOrmModule.forRootAsync({
  imports: [ConfigModule],
  useExisting: ConfigService,
});

This construction works the same as useClass with one critical difference - TypeOrmModule will lookup imported modules to reuse an existing ConfigService instead of instantiating a new one.

Example

A working example is available here.

Official enterprise support

• Providing technical guidance
• Performing in-depth code reviews
• Mentoring team members
• Advising best practices

Explore more

Sequelize Integration

An alternative to using TypeORM is to use the Sequelize ORM with the @nestjs/sequelize package. In addition, we leverage the sequelize-typescript package which provides a set of additional decorators to declaratively define entities.

To begin using it, we first install the required dependencies. In this chapter, we’ll demonstrate using the popular MySQL Relational DBMS, but Sequelize provides support for many relational databases, such as PostgreSQL, MySQL, Microsoft SQL Server, SQLite, and MariaDB. The procedure we walk through in this chapter will be the same for any database supported by Sequelize. You’ll simply need to install the associated client API libraries for your selected database.

$ npm install --save @nestjs/sequelize sequelize sequelize-typescript mysql2
$ npm install --save-dev @types/sequelize

Once the installation process is complete, we can import the SequelizeModule into the root AppModule.

app.module.ts

import { Module } from '@nestjs/common';
import { SequelizeModule } from '@nestjs/sequelize';
@Module({
  imports: [
    SequelizeModule.forRoot({
      dialect: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'root',
      database: 'test',
      models: [],
    }),
  ],
})
export class AppModule {}

The forRoot() method supports all the configuration properties exposed by the Sequelize constructor (read more). In addition, there are several extra configuration properties described below.

Once this is done, the Sequelize object will be available to inject across the entire project (without needing to import any modules), for example:

app.service.ts

import { Injectable } from '@nestjs/common';
import { Sequelize } from 'sequelize-typescript';
@Injectable()
export class AppService {
  constructor(private sequelize: Sequelize) {}
}

import { Injectable } from '@nestjs/common';
import { Sequelize } from 'sequelize-typescript';
@Dependencies(Sequelize)
@Injectable()
export class AppService {
  constructor(sequelize) {
    this.sequelize = sequelize;
  }
}

Models

Sequelize implements the Active Record pattern. With this pattern, you use model classes directly to interact with the database. To continue the example, we need at least one model. Let’s define the User model.

user.model.ts

import { Column, Model, Table } from 'sequelize-typescript';
@Table
export class User extends Model<User> {
  @Column
  firstName: string;
  @Column
  lastName: string;
  @Column({ defaultValue: true })
  isActive: boolean;
}

Hint Learn more about the available decorators here.

The User model file sits in the users directory. This directory contains all files related to the UsersModule. You can decide where to keep your model files, however, we recommend creating them near their domain, in the corresponding module directory.

To begin using the User model, we need to let Sequelize know about it by inserting it into the models array in the module forRoot() method options:

app.module.ts

import { Module } from '@nestjs/common';
import { SequelizeModule } from '@nestjs/sequelize';
import { User } from './users/user.model';
@Module({
  imports: [
    SequelizeModule.forRoot({
      dialect: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'root',
      database: 'test',
      models: [User],
    }),
  ],
})
export class AppModule {}

Next, let’s look at the UsersModule:

users.module.ts

import { Module } from '@nestjs/common';
import { SequelizeModule } from '@nestjs/sequelize';
import { User } from './user.model';
import { UsersController } from './users.controller';
import { UsersService } from './users.service';
@Module({
  imports: [SequelizeModule.forFeature([User])],
  providers: [UsersService],
  controllers: [UsersController],
})
export class UsersModule {}

This module uses the forFeature() method to define which models are registered in the current scope. With that in place, we can inject the UserModel into the UsersService using the @InjectModel() decorator:

users.service.ts

import { Injectable } from '@nestjs/common';
import { InjectModel } from '@nestjs/sequelize';
import { User } from './user.model';
@Injectable()
export class UsersService {
  constructor(
    @InjectModel(User)
    private userModel: typeof User,
  ) {}
  async findAll(): Promise<User[]> {
    return this.userModel.findAll();
  }
  findOne(id: string): Promise<User> {
    return this.userModel.findOne({
      where: {
        id,
      },
    });
  }
  async remove(id: string): Promise<void> {
    const user = await this.findOne(id);
    await user.destroy();
  }
}

import { Injectable, Dependencies } from '@nestjs/common';
import { getModelToken } from '@nestjs/sequelize';
import { User } from './user.model';
@Injectable()
@Dependencies(getModelToken(User))
export class UsersService {
  constructor(usersRepository) {
    this.usersRepository = usersRepository;
  }
  async findAll() {
    return this.userModel.findAll();
  }
  findOne(id) {
    return this.userModel.findOne({
      where: {
        id,
      },
    });
  }
  async remove(id) {
    const user = await this.findOne(id);
    await user.destroy();
  }
}

Notice Don’t forget to import the UsersModule into the root AppModule.

If you want to use the repository outside of the module which imports SequelizeModule.forFeature, you’ll need to re-export the providers generated by it. You can do this by exporting the whole module, like this:

users.module.ts

import { Module } from '@nestjs/common';
import { SequelizeModule } from '@nestjs/sequelize';
import { User } from './user.entity';
@Module({
  imports: [SequelizeModule.forFeature([User])],
  exports: [SequelizeModule]
})
export class UsersModule {}

Now if we import UsersModule in UserHttpModule, we can use @InjectModel(User) in the providers of the latter module.

users-http.module.ts

import { Module } from '@nestjs/common';
import { UsersModule } from './user.module';
import { UsersService } from './users.service';
import { UsersController } from './users.controller';
@Module({
  imports: [UsersModule],
  providers: [UsersService],
  controllers: [UsersController]
})
export class UserHttpModule {}

Relations

Relations are associations established between two or more tables. Relations are based on common fields from each table, often involving primary and foreign keys.

There are three types of relations:

To define relations in entities, use the corresponding decorators. For example, to define that each User can have multiple photos, use the @HasMany() decorator.

user.entity.ts

import { Column, Model, Table, HasMany } from 'sequelize-typescript';
import { Photo } from '../photos/photo.model';
@Table
export class User extends Model<User> {
  @Column
  firstName: string;
  @Column
  lastName: string;
  @Column({ defaultValue: true })
  isActive: boolean;
  @HasMany(() => Photo)
  photos: Photo[];
}

Hint To learn more about associations in Sequelize, read this chapter.

Auto-load models

Manually adding models to the models array of the connection options can be tedious. In addition, referencing models from the root module breaks application domain boundaries and causes leaking implementation details to other parts of the application. To solve this issue, automatically load models by setting both autoLoadModels and synchronize properties of the configuration object (passed into the forRoot() method) to true, as shown below:

app.module.ts

import { Module } from '@nestjs/common';
import { SequelizeModule } from '@nestjs/sequelize';
@Module({
  imports: [
    SequelizeModule.forRoot({
      ...
      autoLoadModels: true,
      synchronize: true,
    }),
  ],
})
export class AppModule {}

With that option specified, every model registered through the forFeature() method will be automatically added to the models array of the configuration object.

Warning Note that models that aren’t registered through the forFeature() method, but are only referenced from the model (via an association), won’t be included.

Transactions

A database transaction symbolizes a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in a database (learn more).

There are many different strategies to handle Sequelize transactions. Below is a sample implementation of a managed transaction (auto-callback).

First, we need to inject the Sequelize object into a class in the normal way:

@Injectable()
export class UsersService {
  constructor(private sequelize: Sequelize) {}
}

Hint The Sequelize class is imported from the sequelize-typescript package.

Now, we can use this object to create a transaction.

async createMany() {
  try {
    await this.sequelize.transaction(async t => {
      const transactionHost = { transaction: t };
      await this.userModel.create(
          { firstName: 'Abraham', lastName: 'Lincoln' },
          transactionHost,
      );
      await this.userModel.create(
          { firstName: 'John', lastName: 'Boothe' },
          transactionHost,
      );
    });
  } catch (err) {
    // Transaction has been rolled back
    // err is whatever rejected the promise chain returned to the transaction callback
  }
}

Hint Note that the Sequelize instance is used only to start the transaction. However, to test this class would require mocking the entire Sequelize object (which exposes several methods). Thus, we recommend using a helper factory class (e.g., TransactionRunner) and defining an interface with a limited set of methods required to maintain transactions. This technique makes mocking these methods pretty straightforward.

Migrations

Migrations provide a way to incrementally update the database schema to keep it in sync with the application’s data model while preserving existing data in the database. To generate, run, and revert migrations, Sequelize provides a dedicated CLI.

Migration classes are separate from the Nest application source code. Their lifecycle is maintained by the Sequelize CLI. Therefore, you are not able to leverage dependency injection and other Nest specific features with migrations. To learn more about migrations, follow the guide in the Sequelize documentation.

Learn the right way!

• 60+ chapters
• 4+ hours of videos
• Official certificate
• Deep-dive sessions

Explore official courses

Multiple databases

Some projects require multiple database connections. This can also be achieved with this module. To work with multiple connections, first create the connections. In this case, connection naming becomes mandatory.

Suppose you have an Album entity stored in its own database.

const defaultOptions = {
  dialect: 'postgres',
  port: 5432,
  username: 'user',
  password: 'password',
  database: 'db',
  synchronize: true,
};
@Module({
  imports: [
    SequelizeModule.forRoot({
      ...defaultOptions,
      host: 'user_db_host',
      models: [User],
    }),
    SequelizeModule.forRoot({
      ...defaultOptions,
      name: 'albumsConnection',
      host: 'album_db_host',
      models: [Album],
    }),
  ],
})
export class AppModule {}

Notice If you don’t set the name for a connection, its name is set to default. Please note that you shouldn’t have multiple connections without a name, or with the same name, otherwise they will get overridden.

At this point, you have User and Album models registered with their own connection. With this setup, you have to tell the SequelizeModule.forFeature() method and the @InjectModel() decorator which connection should be used. If you do not pass any connection name, the default connection is used.

@Module({
  imports: [
    SequelizeModule.forFeature([User]),
    SequelizeModule.forFeature([Album], 'albumsConnection'),
  ],
})
export class AppModule {}

You can also inject the Sequelize instance for a given connection:

@Injectable()
export class AlbumsService {
  constructor(
    @InjectConnection('albumsConnection')
    private sequelize: Sequelize,
  ) {}
}

Testing

When it comes to unit testing an application, we usually want to avoid making a database connection, keeping our test suites independent and their execution process as fast as possible. But our classes might depend on models that are pulled from the connection instance. How do we handle that? The solution is to create mock models. In order to achieve that, we set up custom providers. Each registered model is automatically represented by a <ModelName>Model token, where ModelName is the name of your model class.

The @nestjs/sequelize package exposes the getModelToken() function which returns a prepared token based on a given model.

@Module({
  providers: [
    UsersService,
    {
      provide: getModelToken(User),
      useValue: mockModel,
    },
  ],
})
export class UsersModule {}

Now a substitute mockModel will be used as the UserModel. Whenever any class asks for UserModel using an @InjectModel() decorator, Nest will use the registered mockModel object.

Async configuration

You may want to pass your SequelizeModule options asynchronously instead of statically. In this case, use the forRootAsync() method, which provides several ways to deal with async configuration.

One approach is to use a factory function:

SequelizeModule.forRootAsync({
  useFactory: () => ({
    dialect: 'mysql',
    host: 'localhost',
    port: 3306,
    username: 'root',
    password: 'root',
    database: 'test',
    models: [],
  }),
});

Our factory behaves like any other asynchronous provider (e.g., it can be async and it’s able to inject dependencies through inject).

SequelizeModule.forRootAsync({
  imports: [ConfigModule],
  useFactory: (configService: ConfigService) => ({
    dialect: 'mysql',
    host: configService.get('HOST'),
    port: +configService.get('PORT'),
    username: configService.get('USERNAME'),
    password: configService.get('PASSWORD'),
    database: configService.get('DATABASE'),
    models: [],
  }),
  inject: [ConfigService],
});

Alternatively, you can use the useClass syntax:

SequelizeModule.forRootAsync({
  useClass: SequelizeConfigService,
});

The construction above will instantiate SequelizeConfigService inside SequelizeModule and use it to provide an options object by calling createSequelizeOptions(). Note that this means that the SequelizeConfigService has to implement the SequelizeOptionsFactory interface, as shown below:

@Injectable()
class SequelizeConfigService implements SequelizeOptionsFactory {
  createSequelizeOptions(): SequelizeModuleOptions {
    return {
      dialect: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'root',
      database: 'test',
      models: [],
    };
  }
}

In order to prevent the creation of SequelizeConfigService inside SequelizeModule and use a provider imported from a different module, you can use the useExisting syntax.

SequelizeModule.forRootAsync({
  imports: [ConfigModule],
  useExisting: ConfigService,
});

This construction works the same as useClass with one critical difference - SequelizeModule will lookup imported modules to reuse an existing ConfigService instead of instantiating a new one.

Example

A working example is available here.