How to write setval in TypeORM in NestJS?

In NestJS with TypeORM, if you need to adjust the current value of a sequence—for example, during testing or when reconfiguring the database—you may consider using the PostgreSQL-specific setval() function. This function sets the current value of a sequence and is commonly used in PostgreSQL. In TypeORM, you can achieve this by executing native SQL statements.

Step 1: Inject EntityManager

First, ensure your service injects the EntityManager. This entity manager enables you to execute native SQL queries.

typescript
import { Injectable } from '@nestjs/common';
import { EntityManager } from 'typeorm';

@Injectable()
export class YourService {
    constructor(private entityManager: EntityManager) {}
}

Step 2: Execute Native SQL to Set Sequence Value

Use the query() method of entityManager to run native SQL. Provide the sequence name and the new value you want to set.

typescript
async setSequenceValue(sequenceName: string, newValue: number): Promise<void> {
    await this.entityManager.query(`SELECT setval('${sequenceName}', ${newValue}, false)`);
}

Here, setting the third parameter of setval() to false ensures that the subsequent nextval() call returns the specified new value. If set to true, nextval() would return the new value plus the sequence increment (typically 1).

Example: Adjusting User ID Sequence

Suppose you have a user table user with an auto-incrementing ID. In scenarios like data migration, you may need to reset the sequence value for this ID.

typescript
@Injectable()
export class UserService {
    constructor(private entityManager: EntityManager) {}

    async resetUserIdSequence(nextId: number): Promise<void> {
        // Assume sequence name is 'user_id_seq'
        await this.setSequenceValue('user_id_seq', nextId - 1);
    }
}

In this example, calling resetUserIdSequence(100) sets the sequence to 99, so the next nextval()-generated ID will be 100.

Conclusion

By following this approach, you can flexibly adjust sequence values in NestJS and TypeORM environments, which is valuable for database maintenance and specific use cases. However, directly manipulating database sequences may introduce risks—especially in high-concurrency production environments—so exercise caution when implementing this technique.