How to add a request timeout in Typeorm/Typescript?

user_1248779 0
Viewed 5605

Today, the behavior of Typeorm (Postgres) for

  • getManager().query(...) and
  • getRepositoty().createQueryBuilder(...).getMany()

is to wait for a response indefinitely.

Is there a way to introduce a request timeout that I might've missed?

If this is not possible, does Typeorm expose the connection from its pool so that I can implement a timeout mechanism and close the DB connection manually?

typeorm typescript node.js postgresql
3 Answers

To work with a specific connection from the pool use createQueryRunner there is no info about it in the docs but it is documented in the api.

Creates a query runner used for perform queries on a single database connection. Using query runners you can control your queries to execute using single database connection and manually control your database transaction.

Usage example:

const foo = <T>(callback: <T>(em: EntityManager) => Promise<T>): Promise<T> => {
    const connection = getConnection();
    const queryRunner = connection.createQueryRunner();

    return new Promise(async (resolve, reject) => {
        let res: T;
        try {
            await queryRunner.connect();
            // add logic for timeout
            res = await callback(queryRunner.manager);
        } catch (err) {
            reject(err);
        } finally {
            await queryRunner.release();
            resolve(res);
        }
    });
};
user_102155600

You can change the default behaviour on a per connection basis either by using statement_timeout or query_timeout. You can read more about all possible configurations in the official node pg driver doc. Difference between a statement and query?

A statement is any SQL command such as SELECT, INSERT, UPDATE, DELETE.

A query is a synonym for a SELECT statement.

How to tell typeorm to use these configurations? Add these parameters under extra field in ormconfig.js:

{
  type: "postgres",
  name: "default",
  host: process.env.DB_HOST,
  port: 5432,
  username: process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_NAME,
  synchronize: false,
  logging: false,
  entities: [
    "dist/entity/**/*.js"
  ],
  extra: {
    poolSize: 20,
    connectionTimeoutMillis: 2000,
    query_timeout: 1000,
    statement_timeout: 1000
  },
}

Note the use of poolSize here. This creates a connection pool of 20 connections for the application to use and reuse. connectionTimeoutMillis ensures that if all the connections inside the pool are busy executing statements/transactions, a new connection request out of the pool will timeout after connectionTimeoutMillis ms. More about connection pool configurations of pg-pool here.

user_19737350

from the documentation you can use maxQueryExecutionTime ConnectionOption.

maxQueryExecutionTime - If query execution time exceed this given max execution time (in milliseconds) then logger will log this query.

ConnectionOptions is a connection configuration you pass to createConnection or define in ormconfig

user_11189780
Related