How to set a nullable database field to NULL with typeorm?

Question

This seems like such a simple question to answer, but finding an answer for this seems impossible.

I am building a password reset feature for a backend application with Express and Typescript. I am using Postgres for the database and Typeorm for data manipulation. I have a User entity with these two columns in my database:

@Column({
    unique: true,
    nullable: true,
})
resetPasswordToken!: string;

@Column({ nullable: true, type: 'timestamp with time zone' })
resetPasswordExpiresAt!: Date;

When a user requests a password reset token the resetPasswordToken and resetPasswordExpiresAt fields get both filled with the desired values. With the token that was sent to the user's e-mail address, the user can reset his/her password. After the user's password is reset, I want to clear these two fields by setting them to null:

user.resetPasswordToken = null;
user.resetPasswordExpiresAt = null;
user.save()

But if I do this Typescript complains about the two lines where I assign the null value:

Type 'null' is not assignable to type 'string'.

and

Type 'null' is not assignable to type 'Date'.

If I change the columns in my entity to accept null like below, the errors disappear:

resetPasswordToken!: string | null;
...
resetPasswordExpiresAt!: Date | null;

But when I start my Express application I get the following error when Typeorm tries to connect to my database:

Data type "Object" in "User.resetPasswordToken" is not supported by "postgres" database.

 

How do I set these fields to null?

Seftton

what's the version of ts-node you use? check here: github.com/TypeStrong/ts-node/issues/569 – Goodtempered

@Mahdi I am using the latest version (9.0.0) of ts-node. The solution in the link you sent me did not work for me. – Seftton

Your question is discussed and answered here github.com/typeorm/typeorm/issues/2567 – Dupion

Answer 1

After a good night rest I managed to solve my problem.

Typeorm sets the type of the database fields based on the typing you give the variables for your entities in typescript. Typeorm casts the code below to a varchar field in my postgres database because I gave it a string as a type in typescript.

@Column({
    unique: true,
    nullable: true,
})
resetPasswordToken!: string;

This is also where lies my problem. Typeorm takes the typing of a field and tries to create that database field based on the typing it reads. While the code below is correct, typescript basically encapsulates both types in a single object and that object is what is being read by Typeorm causing the error that I got.

resetPasswordToken!: string | null;

To fix my problem I had to specifiy the database field type explicitly like this:

@Column({
    type: 'text',
    unique: true,
    nullable: true,
})
resetPasswordToken!: string;

Seftton

can you type resetPasswordToken as string | null now? – Benally

@superJustin, after adding type: 'text' to the column decorator you can add string | null for type checking. – Seftton

Answer 2

The accepted answer is not exactly correct. Default typeorm conversion from string type is "varchar" on MySQL DB. So if you use type: "text" it will define the column incorrectly. If you want to make it compatible with default behavior you should use typescript types like this

@Column({
    type: String,
    unique: true,
    nullable: true,
})
resetPasswordToken!: string | null;

Electrolytic

Answer 3

@Column('text', {
    unique: true,
    nullable: true,
})
resetPasswordToken!: string;

Cinque

Answer 4

To fix my problem I had to specifiy the database field type explicitly like this:

{
   name: "tag_id",
   type: "varchar",
   isNullable: true
},

Burdock

Answer 5

To save Date with null value, just enable nullable:true in @Column() tag and save the object without property assignment

@Column('text', {
    unique: true,
    nullable: true,
})
resetPasswordToken: string;

//user.resetPasswordToken = null

user.save()

Whitewall