NextJS App Router using external queries

[jwaugh3]

I'm getting the well-known warning: 'WARNING: Creating a duplicate QueryFile object for the same file -' and an error

TypeError: Invalid query format.
    at Database.$query (webpack-internal:///(rsc)/./node_modules/pg-promise/lib/query.js:82:21)
    at Database.eval (webpack-internal:///(rsc)/./node_modules/pg-promise/lib/query.js:256:23)
    at eval (webpack-internal:///(rsc)/./node_modules/pg-promise/lib/database.js:317:38)

when trying to use external SQL files on Nextjs.

I have followed the documentations and the examples provided but am getting this warning and the error.
Here is some context:

sql.js

const {QueryFile} = require('pg-promise');
const {join: joinPath} = require('path');

// Helper for linking to external query files:
function sql(file) {
    const fullPath = joinPath(process.cwd(), 'src', 'lib', 'db', 'queries', file); // generating full path; ( manually set for nextjs )
    return new QueryFile(fullPath, {minify: true});
}

module.exports = {
  users: {
    get_user_by_id: sql('./users/get_user_by_id.sql'),
  },
};

users/route.js

import {getDB} from '../index';
const {db, users} = getDB();
const {users: sql} = require('../sql');

export async function insertOrUpdateUser({name, email, username = null, external_id}) {
  try {
    const queryFile = sql.get_user_by_id;
    const user = await db.one(queryFile, {user_id: 1});
  } catch(err) {
    console.log(err)
  }

queries/users/get_user_by_id.sql

SELECT email FROM users WHERE user_id = ${user_id}

I don't quite understand why I am getting the 'invalid query format' error. From what I can tell, my QueryFile is correct:

QueryFile {
    file: "C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\src\lib\db\queries\users\get_user_by_id.sql"
    options: {"minify":true,"debug":true}
    query: "SELECT email FROM users WHERE user_id = ${user_id}"
}

[vitaly-t]

That error should include the line+column information to locate the issue in the file, if there is any.

[jwaugh3]

That error should include the line+column information to locate the issue in the file, if there is any.

Here is the full stack trace. Not seeing anything pertaining to the SQL file I'm using.

QueryFile {
    file: "C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\src\lib\db\queries\users\get_user_by_id.sql"
    options: {"minify":true,"debug":true}
    query: "SELECT email FROM users WHERE user_id = ${user_id}"
}
TypeError: Invalid query format.
    at Database.$query (webpack-internal:///(rsc)/./node_modules/pg-promise/lib/query.js:82:21)
    at Database.eval (webpack-internal:///(rsc)/./node_modules/pg-promise/lib/query.js:256:23)
    at eval (webpack-internal:///(rsc)/./node_modules/pg-promise/lib/database.js:317:38)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async insertOrUpdateUser (webpack-internal:///(rsc)/./src/lib/db/users/route.js:52:20)
    at async Object.signIn (webpack-internal:///(rsc)/./src/app/api/auth/[...nextauth]/route.js:24:34)
    at async Object.callback (webpack-internal:///(rsc)/./node_modules/next-auth/core/routes/callback.js:49:39)
    at async AuthHandler (webpack-internal:///(rsc)/./node_modules/next-auth/core/index.js:202:38)
    at async NextAuthRouteHandler (webpack-internal:///(rsc)/./node_modules/next-auth/next/index.js:50:30)
    at async NextAuth._args$ (webpack-internal:///(rsc)/./node_modules/next-auth/next/index.js:85:24)
    at async C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\compiled\next-server\app-route.runtime.dev.js:6:63809
    at async eU.execute (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\compiled\next-server\app-route.runtime.dev.js:6:53964)
    at async eU.handle (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\compiled\next-server\app-route.runtime.dev.js:6:65062)
    at async doRender (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\server\base-server.js:1333:42)
    at async cacheEntry.responseCache.get.routeKind (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\server\base-server.js:1543:40)
    at async DevServer.renderToResponseWithComponentsImpl (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\server\base-server.js:1463:28)
    at async DevServer.renderPageComponent (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\server\base-server.js:1856:24)
    at async DevServer.renderToResponseImpl (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\server\base-server.js:1894:32)
    at async DevServer.pipeImpl (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\server\base-server.js:911:25)
    at async NextNodeServer.handleCatchallRenderRequest (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\server\next-server.js:271:17)
    at async DevServer.handleRequestImpl (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\server\base-server.js:807:17)
    at async C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\server\dev\next-dev-server.js:331:20
    at async Span.traceAsyncFn (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\trace\trace.js:151:20)
    at async DevServer.handleRequest (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\server\dev\next-dev-server.js:328:24)
    at async invokeRender (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\server\lib\router-server.js:163:21)
    at async handleRequest (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\server\lib\router-server.js:342:24)
    at async requestHandlerImpl (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\server\lib\router-server.js:366:13)
    at async Server.requestListener (C:\Users\Gande\Desktop\projects\onePagrPrograms\onePagr\node_modules\next\dist\server\lib\start-server.js:140:13)

[vitaly-t]

Something doesn't make sense here, like the error doesn't correspond the SQL file. Best shot in such situations is to debug the code, see what's going wrong.

[jwaugh3]

I've spent several hours attempting to debug whats going on with this and haven't made much progress.
It is definitely able to get the SQL string from the .SQL file and build the QueryFile class. And on top of that, if I replace the sql.get_user_by_id.sql with the query string, it works just fine.

I've gone ahead and recreated the issue is a separate repo if you want to check that out.
https://github.com/jwaugh3/pg-promise-with-nextjs

[vitaly-t]

I have looked at it. No issues with the query, and it is formatted without any issue.

This is what I'm getting into the console:

QueryFile {
    file: "C:\Projects\tmp\pg-promise-with-nextjs\src\lib\db\queries\users\get_user_by_id.sql"
    options: {"minify":true,"debug":true}
    query: "SELECT email FROM users WHERE user_id = ${user_id}"
}
error Error: SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string
    at Object.continueSession (C:\Projects\tmp\pg-promise-with-nextjs\node_modules\pg\lib\crypto\sasl.js:24:11)
    at Client._handleAuthSASLContinue (C:\Projects\tmp\pg-promise-with-nextjs\node_modules\pg\lib\client.js:272:18)
    at Connection.emit (node:events:518:28)

The second part tells you what you need to know, see this. Basically, your database connection requires a valid password, or else it fails to connect to that database.

[jwaugh3]

this is li

I have looked at it. No issues with the query, and it is formatted without any issue.

This is what I'm getting into the console:

QueryFile {
    file: "C:\Projects\tmp\pg-promise-with-nextjs\src\lib\db\queries\users\get_user_by_id.sql"
    options: {"minify":true,"debug":true}
    query: "SELECT email FROM users WHERE user_id = ${user_id}"
}
error Error: SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string
    at Object.continueSession (C:\Projects\tmp\pg-promise-with-nextjs\node_modules\pg\lib\crypto\sasl.js:24:11)
    at Client._handleAuthSASLContinue (C:\Projects\tmp\pg-promise-with-nextjs\node_modules\pg\lib\client.js:272:18)
    at Connection.emit (node:events:518:28)

The second part tells you what you need to know, see this. Basically, your database connection requires a valid password, or else it fails to connect to that database.

I have gotten this when I didn't have a .env in the past. The repo I provided doesn't have an env file. You will have to add your own credentials to the db/index.js file to get the database connection to work. My database is being connected.

[jwaugh3]

we are getting the same result for the query file. I agree that the query file looks correct. if you provide connection details it will throw the 'invalid query format' error.
I've spent multiple hours attempting to debug this. I'm convinced this is a bug that occurs when using pg-promise with nextjs

[vitaly-t]

This may happen, if NextJs is using a different version of pg-promise than the one that you are using. Presence of multiple versions can result in failed type check, as in the type comparison fails:

pg-promise/lib/query.js

Line 64 in 8f30428

if (query instanceof QueryFile) {

i.e. because type QueryFile created in one version of the library, being compared to one from another version of the library.

I recommend that you check that, because it looks that way.

[jwaugh3]

It does look like that's where its failing, but I'm not sure I understand what you're saying. NextJs doesn't come with pg or pg-promise, so how would it have a different version than the single package I have installed?
(using "pg-promise": "^11.5.4")

[vitaly-t]

Well, if that's where it is failing, then you do have more than one version of pg-promise, somehow. Otherwise, it would not be failing there. Maybe clean up your project, and reinstall things?

[vitaly-t]

@jwaugh3 Did you get to the bottom of it in the end?

[jwaugh3]

No luck. I've confirmed there are no duplicate pg or pg-promise dependencies in the project.
(here are the only dependencies that include 'pg' in the project)

I've seen other discussions where this was the problem, but I'm not convinced this is the same issue.
I've uninstalled all modules and reinstalled them as well with no change.
Do you suggest switching to a different version of pg-promise?