NestJS/TypeORM. TypeORM doesn't update entity in DB, uses old cached entity instead

Question

I am experiencing immense difficulties all day trying to save an entity into a MySQL database. I am using NestJS and TypeORM.

teacher.entity.ts

import { BeforeInsert, Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
import * as bcrypt from 'bcrypt';
import { bcryptConstants } from 'src/bcrypt/bcrypt.constants';

@Entity({'name': 'teacher'})

export class Teacher {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    username: string;

    @Column()
    email: string;

    @Column()
    password: string;

    @BeforeInsert()
    async hashPassword(): Promise<void> {
        const salt = await bcrypt.genSalt(bcryptConstants.saltRounds);
        const hash = await bcrypt.hash(this.password, salt);
        this.password = hash;
    }

}

app.module.ts

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { SubjectsController } from './subjects/subjects.controller';
import { SubjectService } from './subjects/subject/subject.service'
import { TeacherModule } from './teacher/teacher.module';
import { AuthModule } from './auth/auth.module';

@Module({
  imports: [ TypeOrmModule.forRoot({
    type: 'mysql',
    host: 'localhost',
    port: 3306,
    username: 'root',
    password: 'root',
    database: 'test',
    entities: ["dist/**/*.entity{.ts,.js}"],
    synchronize: true,
    logging: true
  }), TeacherModule, AuthModule],
  controllers: [AppController, SubjectsController],
  providers: [AppService, SubjectService],
})
export class AppModule { }

Here's the error:

+10767ms
query: START TRANSACTION
query: INSERT INTO `teacher`(`id`, `username`, `email`, `password`) VALUES (DEFAULT, ?, ?, ?) -- PARAMETERS: ["babbb","babbb","$2b$10$CMyzTJU6g1gJX2eO8Ulleez.LKo1XTCHvVKeUFKJS2FF9bwXivNR."]
query: COMMIT
+120008ms
query: START TRANSACTION
query: INSERT INTO `teacher`(`id`, `username`, `email`, `password`) VALUES (DEFAULT, ?, ?, ?) -- PARAMETERS: ["babbb","babbb","$2b$10$XU8QNxCRL4Ole2OxWkInruLogt0/e/SAfJoAhw.dBbad3MBb5D.iS"]
query failed: INSERT INTO `teacher`(`id`, `username`, `email`, `password`) VALUES (DEFAULT, ?, ?, ?) -- PARAMETERS: ["babbb","babbb","$2b$10$XU8QNxCRL4Ole2OxWkInruLogt0/e/SAfJoAhw.dBbad3MBb5D.iS"]

error: Error: Duplicate entry 'babbb' for key 'teacher.IDX_76fd0cda3fc6719d3109237c72'
{
  code: 'ER_DUP_ENTRY',
  errno: 1062,
  sqlState: '23000',
  sqlMessage: "Duplicate entry 'babbb' for key 'teacher.IDX_76fd0cda3fc6719d3109237c72'"
}
query: ROLLBACK
[Nest] 5360   - 03/06/2021, 9:59:03 PM   [ExceptionsHandler] Duplicate entry 'babbb' for key 'teacher.IDX_76fd0cda3fc6719d3109237c72' +192ms

The entity actually gets saved in the database but it takes a whole minute for NestJS to finish this task and return a response. I am using Angular which waits for this response in order to redirect the user to a login page after registering as a teacher. I only get an error with status 500 a minute after sending a post request to create a teacher in the database.

mysql table

id  username    email   password
1   user12  user    $2b$10$kYZ3F2Hv2MkuvQJIBUsK5Ogq4PHQPLiOBp1t9x3.psOwL984/KTQe
4   babbb   babbb   $2b$10$CMyzTJU6g1gJX2eO8Ulleez.LKo1XTCHvVKeUFKJS2FF9bwXivNR.

I've inserted 2 entries but for some reason, it skipped saving on ID 2 and 3.

I tried using "uuid" as an ID like this:

@PrimaryGeneratedColumn("uiid")
id: string;

But still it gives me an error that the generated uuid string is too long to be saved in an integer value, when I've clearly defined the column as a string.

Any suggestions are welcome!

UPDATE

TypeORM adds a unique index to my columns "username" and "email" even though I haven't specified it in "@Column()".

query: SELECT VERSION() AS `version`
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'test' AND `TABLE_NAME` = 'typeorm_metadata'
query: ALTER TABLE `teacher` CHANGE `username` `username` varchar(255) NOT NULL
query: ALTER TABLE `teacher` ADD UNIQUE INDEX `IDX_76fd0cda3fc6719d3109237c72` (`username`)
query: ALTER TABLE `teacher` CHANGE `email` `email` varchar(255) NOT NULL
query: ALTER TABLE `teacher` ADD UNIQUE INDEX `IDX_00634394dce7677d531749ed8e` (`email`)
query: COMMIT

Even if I use "@Column({ unique: false})", it will still add unique index to them. I don't have this problem with other tables, just this one.

SECOND UPDATE

Alright, I know what the problem now is for sure. TypeORM doesn't synchronize my entity properly, more specifically the "teacher" one. When I add new columns to "teacher" table, it updates properly. When I remove the columns "email", "password", "username" from my code, they are still in the table, there's no way I can alter these columns. I don't know if it is related to some cache problem. My synchronization "synchronize: true" is on.

Hugo Sohm Hugo Sohm · Accepted Answer · 2021-03-08T08:38:32

You need to specify to your @PrimaryGeneratedColumn to generate a UUID and not ~~UIID~~ or Integer as you tried.

# Not like this
@PrimaryGeneratedColumn("uiid")
id: string;

# Like this
@PrimaryGeneratedColumn("uuid")
id: string;

Here is how your entity need to be wrote specifying the UUID column type.

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

    @Column()
    username: string;

    @Column()
    email: string;

    @Column()
    password: string;

    @BeforeInsert()
    async hashPassword(): Promise<void> {
        const salt = await bcrypt.genSalt(bcryptConstants.saltRounds);
        const hash = await bcrypt.hash(this.password, salt);
        this.password = hash;
    }
}

About the delay when saving, Bcrypt is hashing your passwords and it takes a long time usually.

NestJS/TypeORM. TypeORM doesn't update entity in DB, uses old cached entity instead

2 Answers