Reputation: 2245

SQL Server tedious connection never fires any callbacks

I am trying to connect to a SQL Server instance hosted in Azure. I have verified that I can connect to the server using DBeaver and there I can browse all data.

I've installed tedious using:

npm install tedious
npm install @types/tedious

Here's the exact code I'm running:

import { Connection, ConnectionConfig, InfoObject, Request } from "tedious"

export class SqlConnection {

    private static readonly CONFIG: ConnectionConfig = {
        authentication: {
            options: {
                userName: "xxx",
                password: "xxx"
            },
            type: "default"
        },
        server: "xxx",
        options: {
            database: "xxx",
            encrypt: true
        }
    }

    private _connection: Connection

    /**
     * Creates a new SQL connection using environment variables.
     */
    public static create() {
        return new SqlConnection()
    }

    private constructor() {
        console.log("Creating connection using config")
        this._connection = new Connection(SqlConnection.CONFIG)
        console.log("Connection created, creating listeners")
        this._connection.on("connect", (error: Error) => {
            if (error) {
                console.error("Something went wrong when connection %s", error.message)
            } else {
                console.log("Successfully connected")
            }
        })

        this._connection.on("error", (error: Error) => {
            if (error) {
                console.error("Something went wrong %s", error.message)
            } else {
                console.log("Successfully connected")
            }
        })

        this._connection.on("infoMessage", (message: InfoObject) => {
            console.log(JSON.stringify(message))
        })

        this._connection.on("errorMessage", (error: Error) => {
            console.log(JSON.stringify(error))
        })


        this._connection.on("debug", (message: string) => {
            console.log(message)
        })

        console.log("Boyakasha")
    }

    public query(request: Request) {
        console.log("Executing query")
        this._connection.execSql(request)
    }
}

(function exec() {
    SqlConnection.create()
})()

I run using ts-node:

npx ts-node index.ts

And the output from that:

Creating connection using config
Connection created, creating listeners
Boyakasha

And then execution finishes, no callbacks are ever fired.

I've also tried running this in the Azure Function runtime which is the final intended destination, where it is also kept alive.

I've tried this by running my code in the runtime:

func start --typescript

And at the end of the constructor I've added a timeout:

setTimeout(() => {
    console.log("Timeout")
}, 20000)

I chose 20 000 ms because the default timeout of tedious is 15 000, so I thought that might fire some error callback, but alas, nothing. The timeout has the same behavior when running using ts-node and func start

What am I missing here? It feels like it should be simple according to Getting started section in the documentation.

Update I've done some experimenting and rewrote the connect logic using promises. This solution instead stalls indefinitely (longer than the 15 000 ms that is supposed to be the default time out value):

import { Connection, ConnectionConfig, InfoObject, Request } from "tedious"

export class SqlConnection {

    private static readonly CONNECTION_CONFIG: ConnectionConfig = {
        authentication: {
            options: {
                userName: SqlConnection.getEnvironmentVariable("SQL_USER_NAME"),
                password: SqlConnection.getEnvironmentVariable("SQL_PASSWORD")
            },
            type: "default"
        },
        server: SqlConnection.getEnvironmentVariable("SQL_FQDN"),
        options: {
            database: SqlConnection.getEnvironmentVariable("SQL_DATABASE"),
            encrypt: true
        }
    }

    private static getEnvironmentVariable(key: string) {
        const value = process.env[key]
        if (value === undefined) {
            throw new Error(`Required environment variable ${key} could not be found`)
        }
        return value
    }

    private _connection: Connection

    /**
     * Creates a new SQL connection using environment variables.
     */
    public static async connect() {
        const connection = new SqlConnection()
        console.log("Starting connection")
        await connection.start()
        return connection
    }

    private async start() {
        console.log("Awaiting connection")
        return new Promise<void>((resolve, reject) => {
            this._connection.on("connect", (error: Error) => {
                if (error) {
                    console.log("Error connecting")
                    reject(error)
                }
                console.log("Successfully connected")
                resolve()
            })
        })
    }
    

    private constructor() {
        this._connection = new Connection(SqlConnection.CONNECTION_CONFIG)
    }

    public query(request: Request) {
        console.log("Executing query")
        this._connection.execSql(request)
    }
}

(async function exec() {
    await SqlConnection.connect()
})()

Upvotes: 1