Handling database unavailability

[marknelissen]

I am running a continuous process with a single connection pool to a single database. When the database is shutdown (maintenance, technical issue, etc.), the connection pool becomes unusable, even after the database comes back online.

As long as the database is down, on my requests I get errors like "Error: SHUTDOWN is in progress". But after the database is back up, the error becomes "Error: Invalid object name 'TABLE_NAME'". It seems as if the pool is not capable of recycling the connections and replacing them with new ones in the case of a database service restart.

I didn't find the instructions in the documentation on how to handle this. Is this supposed to be handled by the pool, or should I manually rotate the pool with a new one? Where is the best place to detect this kind of connection errors to initiate replacement of the pool? Or is this somehow fixed in newer versions?

Expected behaviour:

Errors on the requests during database downtime, but requests start resolving again after the database comes back online.

Actual behaviour:

Requests return an error saying the table does not exist.

Configuration:

        const port = process.env.PORT ? { port: Number(process.env.PORT) } : {}

        const pool = new ConnectionPool({
            server: process.env.SERVER,
            database: process.env.DATABASE,
            ...port,
            options: {
                enableArithAbort: true,
                encrypt: false,
                fallbackToDefaultDb: true,
            },
            domain: process.env.DOMAIN,
            user: process.env.USERNAME,
            password: process.env.PASSWORD,
        })

Software versions

• NodeJS: v16.18.0
• node-mssql: 6.4.1
• SQL Server: unknown