How to properly parse mysql-query-results into typescript Objects

Question

I'm currently writing some middleware in typescript (transpiles to JavaScript runs on NodeJS). This middleware uses Typeorm in most cases. But sometimes you want to do crazy stuff with SQL. So i wrote some code that sends raw SQL queries and returns them like this:

public query(query: string): Promise<RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[]> {
    return new Promise<RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[]>((resolve, reject) => {
        this.status = DBCstatus.busy;
        this.mysqlConnection.query(query, (err, result, fields)=> {
            this.lastAnswerTime = Date.now();
            if (err === null) {
                this.status = DBCstatus.open;
                resolve(result)
            } else {
                reject(err);
            }
        })
    })
}

So far so good right?

Well not really. Every consumer of this function would have to check whats acutally inside this response.

What i want

I should be able to choose between two basic functions:

Select Queries
Update / Insert into etc.

Lets focus on the function which should be consumed like this:

let cars: cars[] = await queryForData<cars>("Select * from cars");

What i got so far

public queryForData<t>(query: string): Promise<t[]> {
    return new Promise<t[]>( async (resolve, reject) => {
        let queryResult: RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[];
        let dataFromQuery: t[] = [];
        try {
            queryResult = await this.query(query);

            if (queryResult instanceof Array) {
                if (queryResult.length > 0) {
                    if (queryResult[0].constructor.name === QueryResultType.textRow) {
                        let rowData: t[] = queryResult as t[];
                        return rowData;
                    } else {
                        reject(QueryError.unexpectedQueryResult)
                    }
                } else {
                    resolve(dataFromQuery);
                }
            } else {
                reject(QueryError.unexpectedQueryResult)
            }

            resolve([]);
        } catch(error) {
            reject(error);
        }
    })
}

Ok cool it works! But what's your Problem then

If i would consume the function like this:

let foo: Foo[] = await queryForData<Foo>("Select * from Bar");

It would work aswell because typescript would not check if the definition of Foo would match Bar. So there would be errors later while working with foo

What can be done ?

public queryForData<t>( example: t, query: string): Promise<t[]> {
 
   let queryResult: RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[];
   queryResult = await this.query(query);
   if (queryResult instanceof Array) {
      if (queryResult.length > 0) {
        for (let attr in example) {
           // basicly 
           if (queryResult[0][attr] === undefined) {
               // error
           }
        }
    }
}

Would this be a right way of doing things? Is this overkill, since i know the model of the Database? As always thanks in advance!

EDIT Jan 2021

In the last few months i found a way to validate unknown objects for my needs. I've used annotations in the object. I found it to be overkill for when i consume my own database, since i know the model beforehand but it was quiet usefull for consuming 3rd part interfaces or DB. If you want to see how i did it you can look it up here: https://github.com/SpaceNinjaApe/advanced-express-api/tree/master/src/property-validator

Cheers

How to properly parse mysql-query-results into typescript Objects

0 Answers0