CODEWITHSUNDEEP
typeorm
Question3r
Question3r

Reputation: 3832

delete database entries if expired (based on date) using query builder

I would like to delete all my invalidated json web tokens from the database that have expired already. I'm using TypeORM and I think I have to use the query builder for that.

My token entity has a column representing the time the token expires

  @Column()
  public expiresOn: Date;

I want to setup a cronjob deleting all expired tokens from the database. So what I currently have is

  @Interval(2000) // gets called every 2 seconds - for demo purposes
  public async handleInterval(): Promise<void> {
    const currentDate: Date = new Date();

    const deleteExpiredTokensQuery: SelectQueryBuilder<Token> = this.tokensRepository.createQueryBuilder('token');
    deleteExpiredTokensQuery.where('token.expiresOn <= :currentDate', { currentDate });

    await deleteExpiredTokensQuery.delete();
  }

Unfortunately no token gets deleted, although the token expires on 

1970-01-19 07:32:20.093

So how to fix my query? I think the plain Query would be

DELETE FROM tokens WHERE expiresOn < GETDATE()

Update

I thought I would have to create a delete query builder

const deleteExpiredTokensQuery: DeleteQueryBuilder<Token> = this.tokensRepository.createQueryBuilder('token');

but then I get this error

Type 'SelectQueryBuilder' is missing the following properties from type 'DeleteQueryBuilder': output, returning, createDeleteExpression

Upvotes: 1

Views: 6792

Answers (1)

iY1NQ
iY1NQ

Reputation: 2514

By calling delete() on QueryBuilder it only declares this query as a deletion. To execute the query you have to call execute(). See the docs for delete.

await this.tokensRepository
    .createQueryBuilder('token')
    .delete()
    .where('token.expiresOn <= :currentDate', { currentDate })
    .execute();

About the second problem: You do not have to specify the type of the variable explicitly, since it get inferred automatically.

Upvotes: 1

Related Questions