Pg-promise: [help wanted] Query formatting issue

[keerthivasan-r]

hi, I have a normal sql insert statement where i am trying to insert UUID value. I have been struggling with this error for hours. What is the problem in the Query format? Here is the below code that i use to execute the query from Queryfile. I am using pg-promise named parameters passed with object. I'm not sure it also throws the error twice.

Source code

pgdb.one(postgresMapper.Registry.TransactionRequest.insert,{
        transaction_request_guid: transactionRequestGuid,
        transaction_request_attributes_id: docId.toString(),
      })
        .then((data) => {
          request.app.transaction_id = data.transaction_request_id;
  });

Where postgresMapper.Registry.TransactionRequest.insert is the QueryFile

Console error
QueryFile {
file: "D:\aud-plugin\build\sqls\insert-transaction-request.sql"
options: {"debug":true,"minify":true,"compress":false,"noWarnings":false}
query: "INSERT INTO public.transaction_request (transaction_request_guid,transaction_reques
t_attributes_id,createdon_utc) VALUES(${transaction_request_guid},${transaction_request_attribu
tes_id},CURRENT_TIMESTAMP) RETURNING transaction_request_id"
}
QueryFile {
file: "D:\aud-plugin\build\sqls\insert-transaction-request.sql"
options: {"debug":true,"minify":true,"compress":false,"noWarnings":false}
query: "INSERT INTO public.transaction_request (transaction_request_guid,transaction_reques
t_attributes_id,createdon_utc) VALUES(${transaction_request_guid},${transaction_request_attribu
tes_id},CURRENT_TIMESTAMP) RETURNING transaction_request_id"
}
(node:6164) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 4): Ty
peError: Invalid query format.
(node:6164) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 6): Ty
peError: Invalid query format.

[vitaly-t]

Looks like postgresMapper.Registry.TransactionRequest.insert is not really a QueryFile object.

[keerthivasan-r]

Yeap, seems like. It's not getting into any type checking of the query pass through (QueryFile, ExternalQuery etc) Its typeof query === object returns true

[keerthivasan-r]

The code that actually loads the external sql file into QueryFile

import { QueryFile, TQueryFileOptions } from 'pg-promise';
const path = require('path');

export class PostgresMapperModel {


  public static queryFiles : {[key : string] : QueryFile} = {};


  public get Registry() {
    return {
      TransactionRequest: {
        insert: sql('../../sqls/insert-transaction-request.sql'),
      },
      TransactionResponse: {
        insert: sql('../../sqls/insert-transaction-response.sql'),
      },
    };
  }
}

function sql(file: string): QueryFile {
  const fullPath: string = path.join(__dirname, file); 
  const options: TQueryFileOptions = {
    minify: true,
  };
  let query = null;
  if (PostgresMapperModel.queryFiles.filePath == null) {
    query = new QueryFile(fullPath, { debug : true, minify: true });
    PostgresMapperModel.queryFiles.filePath = query;
  }else {
    query = PostgresMapperModel.queryFiles.filePath;
  }
  return query;
}

[vitaly-t]

Must be something trivial, but it takes debugging the code, which I cannot do for you.

[keerthivasan-r]

Yeap, I'm doing the same. I will keep the post with updates. Thanks for the quick reply.

[keerthivasan-r]

This is where the problem is, it didn't pass the query instanceof $npm.QueryFile test

if (!error && typeof query === 'object') {
    if (query instanceof $npm.QueryFile) {
        query.prepare();
        if (query.error) {
            error = query.error;
            query = query.file;
        } else {
            query = query.query;
        }
    }

[vitaly-t]

I don't follow. How is this a problem?

[keerthivasan-r]

This is not a problem or issue with pg-promise. I also guess that loading the pg-promise in different projects might get this trouble. I have a difference npm package that uses pg-promise. Please shed your thoughts, if that may create issue with QueryFile inheritance.

I think this is where, it doesn't get through.

if (!error && typeof query === 'object') { if (query instanceof $npm.QueryFile) { query.prepare(); if (query.error) { error = query.error; query = query.file; } else { query = query.query; } }
The other thing that i see is

Object.getPrototypeOf(query) returns QueryFile object and Object.getPrototypeOf($npm.QueryFile) returns function(){}.

[vitaly-t]

Which version of the library are you using?

If this code - if (query instanceof $npm.QueryFile) doesn't pass, means this is not a properly created QueryFile. I can't say more, not without running the code.

This is the first time someone has this issue, so must be something either very special or trivial.

[vitaly-t]

Possibly, your code if (PostgresMapperModel.queryFiles.filePath == null) doesn't pass the first time, since you do not set it to null, so your query becomes undefined, and hence the error.

[keerthivasan-r]

I'm using the version - pg-promise": "^5.9.7

[vitaly-t]

I don't think it is relevant, but I still would suggest an upgrade to the latest v6.5.1

[keerthivasan-r]

Thanks for your time and guidance

[vitaly-t]

Did you figure out the problem?

[keerthivasan-r]

Nope, It's hard for me to identify the root cause, the same external query file is working in another project

[keerthivasan-r]

Hi, I'm just reopening this issue since the constructor of __proto__ property of both query variable (my loaded queryfile) and npm.QueryFile of pg-promise are equal. I guess if that should be added on top of checking the instanceof test like so

  if (!error && typeof query === 'object') {
        if (query instanceof npm.QueryFile ||  query.__proto__.constructor == npm.QueryFile.__proto__.constructor) {
            query.prepare();

I guess the instanceof is actually failing in my case due to some loading order or so. Please don't think rude of me that i'm reopening the issue again and again,. Please let me know if i am understanding it wrongly.

[vitaly-t]

I'd need to to be able to reproduce the issue, in order to recognize that there is a problem, and to understand the value of any change. Right now I do not understand how to reproduce the problem you are having.

[keerthivasan-r]

ok, i understand. Let me try upgrading the node version. It might be a bug in node version 6.10.0

[keerthivasan-r]

Upgrading nodejs 7.10.0 also didn't work. I'm facing this issue still. The only difference i see that i am trying to add my package using npm link to the project and then running the application. Do you think that makes something different than the regular npm install

[vitaly-t]

I don't think that Node.js version or NPM got anything to do with your issue.

Your best shot is to create a full reproducible example that I could run and see what the issue is.

[keerthivasan-r]

Sure, I will let you know. Its a huge project, may be i should create a demonstrating example and share with you?

[vitaly-t]

may be i should create a demonstrating example and share with you?

Isn't it exactly what I asked you just prior?

[vitaly-t]

@keerthivasan-r did u make no progress with the issue?

[keerthivasan-r]

I was able to resolve it. I had two versions of pg-promise. Both ^6.5.1 and ^5.6.7. The npm package was using the older version. When i changed both to ^6.5.1, it worked just awesome. I'm not sure why does this create an issues. Do we need to maintain only single version of pg-promise in our application? some of the packages we use use has their own pg-promise version. we can't prevent it. What's your view? Please help me understand.

[vitaly-t]

npm loads the version according to the package.json configuration, so multiple versions can be loaded.

Newer versions of NPM do it better though.

You were probably creating QueryFile using one version of the library, and then executing them using a different version. That would explain the mess.

[keerthivasan-r]

yeah, you are right! Thanks for the help.

[vitaly-t]

@keerthivasan-r I've added an answer here: https://stackoverflow.com/questions/45954954/pg-promise-query-formatting-issue-in-queryfile

[LRagji]

I think this issue is still there in 10.7.1 cause i got one and its not related to different versions, according to MDN we should not only rely on instance type as it has a problem when class are instantiated in different global environments(and i think this is exactly whats happening since Query file was created as a static) still debugging this issue and trying to get a sample code for it but it looks like it only happens across packages even with same pgp versions, any how i have fixed it with duck typing as shown below post which you will arrive at symbol problem which can be solved by query._inner.sql

query.js line 63

 if (query instanceof QueryFile || query && query.prepare) {
            query.prepare();
            if (query.error) {
                error = query.error;
                query = query.file;
            } else {
                query = query[QueryFile.$query] || query._inner.sql;
            }

Once i get code to reproduce it will post it here just wanted to hear from @vitaly-t if this makes sense...

[vitaly-t]

@LRagji If you are using a package that in turn uses an ancient version of pg-promise, then there is nothing that can be done from this side. Otherwise, I do not see how the same codebase can work in one module, and then not work in another.

So yes, do post more details, should you find those ;)

[vitaly-t]

P.S. The bottom line, I would need to be able to reproduce the issue, in order to warrant any change.