How to prevent Kysely from crashing node app if database goes down

[AnthonyPhan]

Hi All,

I am trying to incorporate Kysely into a larger Node.js application to write data to a MSSQL database. I create a instance of Kysely exactly like in the doc as shown below and execute inserts with an error catch handler however when the SQL database is not reachable the whole node application crashes.

How can I best handle situation where the SQL database is unreachable without it crashing the whole app?

db
        .insertInto('commandQueue').values({
            command,
            status: 'pending',
            terminal_id: terminalId,
            server_flag: msgId,
        })
        .execute()
        .then(() => console.log(date.toLocaleTimeString(),`[${terminalId}]`, `command ${command} queued`))
        .catch(e => console.log(e))

Error

TimeoutError: Failed to connect to localhost:1433 - Could not connect (sequence)
    at /Users/anthonyphan/repos/nex-gps/node_modules/tarn/dist/PendingOperation.js:14:27
    at runNextTicks (node:internal/process/task_queues:60:5)
    at listOnTimeout (node:internal/timers:538:9)
    at processTimers (node:internal/timers:512:7)
    at async MssqlDriver.acquireConnection (/Users/anthonyphan/repos/nex-gps/node_modules/kysely/dist/cjs/dialect/mssql/mssql-driver.js:40:16)
    at async RuntimeDriver.acquireConnection (/Users/anthonyphan/repos/nex-gps/node_modules/kysely/dist/cjs/driver/runtime-driver.js:46:28)
    at async DefaultConnectionProvider.provideConnection (/Users/anthonyphan/repos/nex-gps/node_modules/kysely/dist/cjs/driver/default-connection-provider.js:10:28)
    at async DefaultQueryExecutor.executeQuery (/Users/anthonyphan/repos/nex-gps/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:36:16)
    at async InsertQueryBuilder.execute (/Users/anthonyphan/repos/nex-gps/node_modules/kysely/dist/cjs/query-builder/insert-query-builder.js:528:24)
/Users/anthonyphan/repos/nex-gps/node_modules/kysely/dist/cjs/dialect/mssql/mssql-driver.js:40
        return await this.#pool.acquire().promise;
               ^
TimeoutError: Failed to connect to localhost:1433 - Could not connect (sequence)
    at /Users/anthonyphan/repos/nex-gps/node_modules/tarn/dist/PendingOperation.js:14:27
    at async MssqlDriver.acquireConnection (/Users/anthonyphan/repos/nex-gps/node_modules/kysely/dist/cjs/dialect/mssql/mssql-driver.js:40:16)
    at async RuntimeDriver.acquireConnection (/Users/anthonyphan/repos/nex-gps/node_modules/kysely/dist/cjs/driver/runtime-driver.js:46:28)
    at async DefaultConnectionProvider.provideConnection (/Users/anthonyphan/repos/nex-gps/node_modules/kysely/dist/cjs/driver/default-connection-provider.js:10:28)
    at async DefaultQueryExecutor.executeQuery (/Users/anthonyphan/repos/nex-gps/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:36:16)
    at async InsertQueryBuilder.execute (/Users/anthonyphan/repos/nex-gps/node_modules/kysely/dist/cjs/query-builder/insert-query-builder.js:528:24)
[nodemon] app crashed - waiting for file changes before starting...

Creating Kysely instance

import { Database } from './types.ts' // this is the Database interface we defined earlier
import * as tedious from 'tedious'
import * as tarn from 'tarn'
import { Kysely, MssqlDialect } from 'kysely'

const dialect = new MssqlDialect({
  tarn: {
    ...tarn,
    options: {
      min: 0,
      max: 10,
    },
  },
  tedious: {
    ...tedious,
    connectionFactory: () => new tedious.Connection({
      authentication: {
        options: {
          password: 'password',
          userName: 'username',
        },
        type: 'default',
      },
      options: {
        database: 'some_db',
        port: 1433,
        trustServerCertificate: true,
      },
      server: 'localhost',
    }),
  },
})

// Database interface is passed to Kysely's constructor, and from now on, Kysely 
// knows your database structure.
// Dialect is passed to Kysely's constructor, and from now on, Kysely knows how 
// to communicate with your database.
export const db = new Kysely<Database>({
  dialect,
})

[koskimas]

Are you absolutely sure you don't have some call somewhere without an exception handler? The stack trace shows the error comes all the way up to the execute method. After that it's just a standard Promise. We don't do anything special there. I don't see how this could be caused by Kysely.

Try converting all kysely calls to await syntax. That way you get the stack trace all the way up to your own code.

[AnthonyPhan]

Hi @koskimas

Thanks for pointer. I went back over the code base to check for instances of execute() without a catch and did find a few, this was the cause of the error above.

However after I ensure a .catch on all instance of execute() i'm still able to crash the node serve by toggling the SQL db off and on. Im not sure if this is actually due to Kysely however i've attached an error log below incase you can spot the issue.

/Users/anthonyphan/repos/nex-gps/node_modules/tedious/src/connection.ts:2313
      this.emit('error', new ConnectionError(message, 'ESOCKET'));
                         ^
ConnectionError: Connection lost - socket hang up
    at Connection.socketError (/Users/anthonyphan/repos/nex-gps/node_modules/tedious/src/connection.ts:2313:26)
    at Connection.socketEnd (/Users/anthonyphan/repos/nex-gps/node_modules/tedious/src/connection.ts:2326:12)
    at Socket.<anonymous> (/Users/anthonyphan/repos/nex-gps/node_modules/tedious/src/connection.ts:1997:35)
    at Socket.emit (node:events:525:35)
    at Socket.emit (node:domain:489:12)
    at endReadableNT (node:internal/streams/readable:1359:12)
    at processTicksAndRejections (node:internal/process/task_queues:82:21) {
  code: 'ESOCKET'
}
[nodemon] app crashed - waiting for file changes before starting...

[koskimas]

I'm not seeing any uncaught exception errors in the log, so it's unclear what's actually crashing the server.

@igalklebanov Any ideas? Could it be we're not listening to the error event and it "leaks out"?

[AnthonyPhan]

I think i might have resolved the issue by creating the the dialect as show below with the error handler. Unsure if this is classed as user error or if the Kysely library should of caught that error. Either way i'm happy that we got to the bottom of it :) Appreciate the help!

import { Database } from './types.ts' // this is the Database interface we defined earlier
import * as tedious from 'tedious'
import * as tarn from 'tarn'
import { Kysely, MssqlDialect } from 'kysely'

const dialect = new MssqlDialect({
  tarn: {
    ...tarn,
    options: {
      min: 0,
      max: 10,
    },
  },
  tedious: {
    ...tedious,
    connectionFactory: () => new tedious.Connection({
      authentication: {
        options: {
          password: 'password',
          userName: 'username',
        },
        type: 'default',
      },
      options: {
        database: 'some_db',
        port: 1433,
        trustServerCertificate: true,
      },
      server: 'localhost',
    }).on('error', console.log),    //THIS LINE RESOLVES THE ISSUE
  },
})

// Database interface is passed to Kysely's constructor, and from now on, Kysely 
// knows your database structure.
// Dialect is passed to Kysely's constructor, and from now on, Kysely knows how 
// to communicate with your database.
export const db = new Kysely<Database>({
  dialect,
})

[koskimas]

I think this is something Kysely should do automatically

[igalklebanov]

@koskimas @AnthonyPhan Yeah, haven't handled this one by mistake. Let's do it.