MSSQL Connector (using Knex)

[dalotodo]

Please find attached a PoC for MSSQL support using Knex.

I'm using libsql dialect from the list of available dialects.

import type { Connector, Primitive } from "db0"
import type { Knex } from 'knex'
import { BoundableStatement } from './_internal/statement'
import knex from "knex";

namespace _mssql {

    export type ClientConfig = {
        url: string
    }

    export type QueryResult = any
    export class Client {
        private readonly _options: ClientConfig
        private _knex: Knex | null = null

        constructor(options: ClientConfig) {
            this._options = options
        }

        async query(arg0: string, params: Primitive[] | undefined): Promise<any> {
            const k = this._knex
            if (!k) throw new Error(`Knex client not available`)
            const stmt = arg0
            const args = params ?? []
            return await k.raw(stmt, args)
        }

        async connect(): Promise<void> {
            const url = new URL(this._options.url)
            const normalize = (str: string) => decodeURIComponent(str).replace(/^\//, '')

            const host = normalize(url.hostname)
            const port = parseInt(url.port)
            const user = normalize(url.username)
            const password = normalize(url.password)
            const database = normalize(url.pathname)

            this._knex = knex({
                client: 'mssql',
                connection: {
                    host,
                    port,
                    user,
                    password,
                    database,
                }
            })
        }

        async disconnect(): Promise<void> {
            const k = this._knex
            if (!k) return
            await k.destroy()
        }

    }
}


export type ConnectorOptions = _mssql.ClientConfig

type InternalQuery = (sql: string, params?: Primitive[]) => Promise<_mssql.QueryResult>;

export default function mssqlConnector(opts: ConnectorOptions): Connector<_mssql.Client> {
    let _client: undefined | _mssql.Client | Promise<_mssql.Client>;
    function getClient() {
        if (_client) {
            return _client;
        }
        const client = new _mssql.Client(opts) //  /*"url" in opts ? opts.url : opts*/ )
        _client = client.connect().then(() => {
            _client = client;
            return _client;
        });
        return _client;
    }

    const query: InternalQuery = async (sql, params) => {
        const client = await getClient()
        return client.query(normalizeParams(sql), params)
    }

    return {
        name: "mssql",
        dialect: 'libsql',
        getInstance: () => getClient(),
        exec: sql => query(sql),
        prepare: sql => new StatementWrapper(sql, query)
    };
}

// In Knex replace params by ? sign (they are replace in the order the are being found)
function normalizeParams(sql: string) {
    return sql.replace(/\?/g, () => `?`);
}

class StatementWrapper extends BoundableStatement<void> {

    #execute: InternalQuery;
    #sql: string;

    constructor(sql: string, query: InternalQuery) {
        super();
        this.#sql = sql;
        this.#execute = query;
    }

    async all(...params: Primitive[]): Promise<unknown[]> {
        const rows = await this.#execute(this.#sql, params);
        return rows
    }


    async run(...params: Primitive[]) {
        const res = await this.#execute(this.#sql, params)
        return {
            success: true,
            ...res,
        };
    }

    async get(...params: Primitive[]) {
        const rows = await this.#execute(this.#sql, params)
        return rows[0]
    }
}