Connect to Azure Postgresql Flexible server Database from Nestjs using any ORM

Question

We are builing a nestjs application that connects to Azure Postgresql Flexible Database server using Typeorm. We are following the passwordless apparoach using managed identity and using Entra token. Our application is able to connect to Database but after the token expires( in our case 24 hours) our application goes down as DB connection is not getting refreshed automatically.

has anyone else faced this issue?

We tried to refresh the token after 23 hours using the https://learn.microsoft.com/en-us/javascript/api/overview/azure/identity-readme?view=azure-node-latest , but we could see that a new token was not getting generated even after 24 hours.

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { ManagedIdentityCredential } from '@azure/identity';

@Module({
  imports: [
    TypeOrmModule.forRootAsync({
      useFactory: async () => {
        const credential = new ManagedIdentityCredential(); // Use ManagedIdentityCredential

        // Fetch the access token
        const tokenResponse = await credential.getToken('https://ossrdbms-aad.database.windows.net/.default');

        return {
          type: 'postgres',
          host: '<YOUR_DATABASE_SERVER_NAME>.postgres.database.azure.com', // Replace with your server name
          port: 5432,
          username: '<YOUR_AAD_USER>@<YOUR_DATABASE_SERVER_NAME>', // Replace with your Azure AD user
          password: tokenResponse.token, // Use the fetched access token as the password
          database: '<YOUR_DATABASE_NAME>', // Replace with your database name
          ssl: { rejectUnauthorized: false }, // Adjust based on your SSL requirements
          entities: [/* Your entities here */],
          synchronize: true, // Set to false in production
        };
      },
    }),
  ],
})
export class DatabaseModule {}

import { Module } from '@nestjs/common';
import { DatabaseModule } from './database/database.module'; // Adjust the path as necessary

@Module({
  imports: [
    DatabaseModule, // Import the DatabaseModule here
    // Other modules can be added here as needed
  ],
  controllers: [],
  providers: [],
})
export class AppModule {}

What is the problem you are facing, could you please share your error? — Bhavani
– Bhavani, Commented Nov 11, 2024 at 8:11
@Bhavani, the issue is the db connection is not getting refreshed automatically. After 24 hours our pods gets restarted because of db connection failure ( liveness and readiness probe will fail) — amn
– amn, Commented Nov 11, 2024 at 16:34

OMartinez-NeT · Accepted Answer · 2024-11-11 13:54:33Z

0

I would inject the dataSource in a service with a cron job to refresh the token periodically. However, there are some caveats: if requests come in while the connection is being restarted, they might fail.

Here's an example of how to refresh the token and reinitialize the DataSource with a new access token from a managed identity:

@Injectable()
export class TokenRefresher {
  constructor(
    private readonly dataSource: DataSource
  ) {}

  @Cron('*/15 * * * * *')
  async refresh(): Promise<Credentials[]> {
 

    // here custom logic to restart the database
    const destroyDatabase = true;

    if (destroyDatabase) {
      const credential = new ManagedIdentityCredential(); // Use ManagedIdentityCredential

      // Fetch the access token
      const tokenResponse = await credential.getToken(
        "https://ossrdbms-aad.database.windows.net/.default"
      );

      await this.dataSource.destroy();
      this.dataSource.setOptions({
        password: tokenResponse.password,
      });
      await this.dataSource.initialize();
    }
  }
}

answered Nov 11, 2024 at 13:54

OMartinez-NeT

3231 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

amn Over a year ago

Thanks @OMartinez-NeT. We also thought about this approach, but was reluctant becaue of the the caveat that you rightly pointed. Just thinking if there is a way of keeping a pool of connection and then refresh the pool before it expires.

OMartinez-NeT Over a year ago

I would use a Nest Global Interceptor to check if the connection is active, and if it's closed, refresh the token. For the connection pool approach, it seems more involved and looks to me that is beyond NestJS scope and more a database-specific approach.

Collectives™ on Stack Overflow

Connect to Azure Postgresql Flexible server Database from Nestjs using any ORM

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related