Ensure a Robust PostgreSQL Reconnection in Your Next.js App

In modern web development, ensuring that your application can gracefully handle database connection issues is crucial. Recently, I encountered a problem where my Next.js app would not automatically reconnect to my PostgreSQL database after a restart, causing page loads to hang. Through some trial and error, I found a robust solution using TypeScript, which I will share with you in this post.

Thank me by sharing on Twitter 🙏

Introduction

When building a Next.js application that relies on a PostgreSQL database, you might face issues with database reconnection after a restart. If not handled properly, users can experience frustrating delays or errors. In this post, I’ll walk you through how to set up a resilient PostgreSQL connection strategy using pg and Vercel’s async-retry libraries in TypeScript. This approach ensures that your app fails quickly and provides informative error messages to users when the database is down.

Setting Up the Database Connection Pool

The first step is to set up a connection pool using the pg library. Connection pooling helps manage database connections efficiently and can retry connections when they fail. We’ll also incorporate async-retry to handle reconnection attempts.

Installing Dependencies

Start by installing the necessary dependencies:

ShellScript

npm install pg async-retry @types/pg @types/async-retry

Configuring the Connection Pool

Next, create a db.ts file to configure the connection pool and implement retry logic. Here’s the complete setup:

Syntech USB C to USB Adapter Pack of 2 USB C Male to USB 3.0 Female Adapter Compatible with MacBook Pro Air 2024, Microsoft Surface, iPad,Samsung Notebook, Dell XPS and More Type C Devices,Space Grey

(172661)

$9.99 (as of November 20, 2024 06:18 GMT +00:00 - )

Genesis: Artificial Intelligence, Hope, and the Human Spirit

$17.05 (as of November 21, 2024 15:46 GMT +00:00 - )

etguuds USB to USB C Cable 3ft, 2-Pack USB A to Type C Charger Cord Fast Charging for Samsung Galaxy A15 A25 A35 A55 A54, S24 S23 S22 S21 S20 S10 S10E, Note 20 10, Moto G, for iPhone 16 15, Gray

(66889)

$6.99 (as of November 20, 2024 06:18 GMT +00:00 - )

TypeScript

import { Pool, QueryResult, QueryResultRow } from 'pg';
import retry from 'async-retry';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL, // Your database connection string
  // Additional pool options can be added here if needed
});

pool.on('error', (err) => {
  console.error('Unexpected error on idle client', err);
  process.exit(-1);
});

export const query = async <T extends QueryResultRow>(text: string, params?: any[]): Promise<QueryResult<T>> => {
  return await retry<QueryResult<T>>(async (bail) => {
    const client = await pool.connect();
    try {
      return await client.query<T>(text, params);
    } catch (err) {
      if ((err as any).code === 'ECONNREFUSED') {
        console.error('Connection refused, retrying...');
        throw err;
      } else {
        bail(err as Error);
        return {} as QueryResult<T>; // This line should never be reached
      }
    } finally {
      client.release();
    }
  }, {
    retries: 3, // Number of retries
    minTimeout: 500, // Minimum wait time between retries in ms
    maxTimeout: 1000, // Maximum wait time between retries in ms
  });
};

In this configuration:

We create a Pool instance with a connection string from environment variables.
We listen for connection errors and exit the process if they occur.
The query function uses async-retry to handle reconnection attempts. If the connection is refused, it retries up to three times with short delays.

Implementing Error Handling in API Routes

To ensure that our Next.js pages fail quickly and provide useful feedback when the database is down, we need to implement proper error handling in our API routes.

Example API Route

Here’s an example of how to use the query function in an API route:

TypeScript

import type { NextApiRequest, NextApiResponse } from 'next';
import { query } from '../../db';

interface Data {
  id: number;
  name: string;
}

export default async function handler(req: NextApiRequest, res: NextApiResponse) {
  try {
    const result = await query<Data>('SELECT * FROM your_table');
    res.status(200).json(result.rows);
  } catch (err) {
    if ((err as any).code === 'ECONNREFUSED') {
      res.status(503).json({ error: 'Service Unavailable. Please retry in a few minutes.' });
    } else {
      res.status(500).json({ error: 'Internal Server Error' });
    }
  }
}

In this API route:

We use the query function to fetch data from the database.
If a connection refused error occurs, we return a 503 status code, indicating that the service is temporarily unavailable.
For other errors, we return a 500 status code, indicating an internal server error.

This approach ensures that users receive immediate feedback if the database is down, rather than waiting indefinitely for a connection.

Configuring Environment Variables

Make sure your environment variables are properly configured in your .env.local file:

ShellScript

DATABASE_URL=your_database_connection_string

Having the correct connection string is essential for the connection pool to function correctly.

Conclusion

Handling database reconnections gracefully is crucial for maintaining a robust Next.js application. By setting up a connection pool with retry logic using pg and async-retry, and implementing proper error handling in your API routes, you can ensure that your app fails quickly and provides informative feedback to users during database downtime.

This solution has worked well for me, making my application more resilient and user-friendly. I hope you find it helpful in your projects as well.