NestJS with TypeORM and PostgreSQL

#nestjs #typeorm #postgres #seeder

Introduction

TypeORM is an Object-Relational Mapping (ORM) that can be used in NodeJS. It is a good fit for NestJS application. TypeORM stands out as the only JavaScript ORM that supports both Active Record and Data Mapper patterns. This unique feature allows developers to create high-quality, scalable, and maintainable applications in the most efficient manner. TypeORM support lots of DBMS (Database Management System) such as PostgreSQL, MySQL, SQLite, and etc.

So in this article I will share how to install and setup TypeORM in NestJS application with PostgreSQL.

So first of all you need to create NestJS application:

nest new <application-name>

After you create NestJS application you need to install this library:

npm i --save @nestjs/config dotenv

that library is to loads environment variable from .env file.

After you install the library you can create .env file:

NODE_ENV=development

DB_USERNAME="dev"
DB_PASSWORD="dev@developer"
DB_NAME="database_name"
DB_HOST="localhost"
DB_PORT=5432
DB_SEEDER="false"
DB_SYNCRONIZE="false"

and you can add this in app.module.ts:

imports: [
  ConfigModule.forRoot({
    envFilePath: '.env',
    isGlobal: true,
  })
]

After that you can install TypeORM library and its extension:

npm i --save @nestjs/typeorm typeorm pg typeorm-extension typeorm-naming-strategies

typeorm - is a TypeORM library.
pg - is a PostgreSQL client connection library.
typeorm-extension - is a library for seed the database.
typeorm-naming-strategies - is a library for changing naming convention to follow best practice naming convention of PostgreSQL

Now you can add the database configuration and connection in src/config/database.config.ts:

import { DataSource, DataSourceOptions } from 'typeorm';
import * as dotenv from 'dotenv';
import { SnakeNamingStrategy } from 'typeorm-naming-strategies';
import { SeederOptions } from 'typeorm-extension';

dotenv.config();

export const dataSourceOptions: DataSourceOptions & SeederOptions = {
  type: 'postgres',
  username: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  port: +process.env.DB_PORT,
  host: process.env.DB_HOST,
  entities: [
    'dist/api/**/*.entity{.ts,.js}',
  ],
  migrations: ['dist/database/migrations/*{.ts,.js}'],
  seeds: ['dist/database/seeds/*{.ts,.js}'],
  factories: ['dist/database/factories/**/*{.ts,.js}'],
  seedTracking: false,
  synchronize: process.env.DB_SYNCHRONIZE === 'true',
  logging: process.env.NODE_ENV === 'development',
  namingStrategy: new SnakeNamingStrategy(),
  cache: true,
};

const dataSource = new DataSource(dataSourceOptions);
export default dataSource;

dataSource.initialize();

in code above is to initialize the data source, data source is pre-defined connection configuration to a specific database.

After you create the file above, you can register it in app.module.ts file:

imports: [
  TypeOrmModule.forRoot(dataSourceOptions),
]

After that you can add this following script in your package.json:

"typeorm": "npm run build && npx typeorm -d dist/config/database.config.js",
"migration:run": "npm run typeorm -- migration:run",
"migration:generate": "npm run typeorm -- migration:generate",
"migration:revert": "npm run typeorm -- migration:revert",
"migration:create": "npm run typeorm -- migration:create",
"migration:show": "npm run typeorm -- migration:show",
"migrate": "npm run typeorm migration:run -- -t=false",
"schema:sync": "npm run typeorm schema:sync",
"schema:drop": "npm run typeorm schema:drop",
"db:create": "ts-node ./node_modules/typeorm-extension/bin/cli.cjs db:create -d dist/config/database.config.js",
"db:drop": "ts-node ./node_modules/typeorm-extension/bin/cli.cjs db:drop -d dist/config/database.config.js",
"seed:run": "npm run build && ts-node -r tsconfig-paths/register ./node_modules/typeorm-extension/bin/cli.cjs seed:run -d dist/config/database.config.js --preserveFilePaths true -n",
"seed:run:all": "npm run build && ts-node -r tsconfig-paths/register ./node_modules/typeorm-extension/bin/cli.cjs seed:run -d dist/config/database.config.js",
"seed:create": "npm run build && ts-node -r tsconfig-paths/register ./node_modules/typeorm-extension/bin/cli.cjs seed:create"

Now you can create the entity file in your folder, i'll create in src/api/user/user.entity.ts:

import {
  Column,
  CreateDateColumn,
  DeleteDateColumn,
  Entity,
  PrimaryGeneratedColumn,
  UpdateDateColumn,
} from 'typeorm';

@Entity({ name: 'users' })
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ unique: true })
  email: string;

  @Column()
  firstName: string;

  @Column()
  lastName: string;

  @Column()
  password: string;

  @CreateDateColumn({ nullable: true })
  createdAt?: Date;

  @UpdateDateColumn({ nullable: true })
  updatedAt?: Date;

  @DeleteDateColumn({ nullable: true })
  deletedAt?: Date;
}

After you create the entity file, the data source will detect your file and after you run this following command it will create the migration file:

npm run migration:generate src/database/migrations/CreateUserTable

it generate this file <timestmap>-CreateUserTable.ts and here inside its file:

import { MigrationInterface, QueryRunner } from 'typeorm';

export class CreateUserTable1742662098102 implements MigrationInterface {
  name = 'CreateUserTable1742662098102';

  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `CREATE TABLE "users" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "email" character varying NOT NULL, "first_name" character varying NOT NULL, "last_name" character varying NOT NULL, "password" character varying NOT NULL, "created_at" TIMESTAMP DEFAULT now(), "updated_at" TIMESTAMP DEFAULT now(), "deleted_at" TIMESTAMP, CONSTRAINT "UQ_97672ac88f789774dd47f7c8be3" UNIQUE ("email"), CONSTRAINT "PK_a3ffb1c0c8416b9fc6f907b7433" PRIMARY KEY ("id"))`,
    );
    await queryRunner.query(
      `CREATE TABLE "query-result-cache" ("id" SERIAL NOT NULL, "identifier" character varying, "time" bigint NOT NULL, "duration" integer NOT NULL, "query" text NOT NULL, "result" text NOT NULL, CONSTRAINT "PK_6a98f758d8bfd010e7e10ffd3d3" PRIMARY KEY ("id"))`,
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP TABLE "query-result-cache"`);
    await queryRunner.query(`DROP TABLE "users"`);
  }
}

After you generate the file migration now you can run the migration by this command:

npm run migration:run

and if you want to create seeder you can use this command:

npm run seed:create -n src/database/seeds/user

it will generate this file <timestamp>-user.ts and here inside the file:

import { DataSource } from 'typeorm';
import { Seeder, SeederFactoryManager } from 'typeorm-extension';

export class User1742663731380 implements Seeder {
  track = false;

  public async run(
    dataSource: DataSource,
    factoryManager: SeederFactoryManager,
  ): Promise<any> {}
}

i will add seed 3 users and the code will be like this:

mport { User } from 'src/api/user/user.entity';
import { DataSource } from 'typeorm';
import { Seeder, SeederFactoryManager } from 'typeorm-extension';
import * as bcrypt from 'bcrypt';

export class User1742663731380 implements Seeder {
  track = false;

  public async run(
    dataSource: DataSource,
    factoryManager: SeederFactoryManager,
  ): Promise<any> {
    const user = dataSource.getRepository(User);
    const hash = await bcrypt.hash('Password@123', 10);
    await user.save([
      {
        firstName: 'Dadang',
        lastName: 'Jebred',
        email: 'dadang@jebred.com',
        password: hash,
      },
      {
        firstName: 'Atin',
        lastName: 'Mustofa',
        email: 'atin@mustofa.com',
        password: hash,
      },
      {
        firstName: 'Vina',
        lastName: 'Vini',
        email: 'vino@dadang.com',
        password: hash,
      },
    ]);
  }
}