How to show the SQL which lead to a syntax error

[convers39]

I have some problems when trying to debug a syntax error. Since there are several nested queries, I could not see the final SQL or the arguments.

    const result = await sql`
            WITH q AS (${sql`
                    SELECT *,
                        ${sql(column)} col_value,
                        ST_AsMVTGeom(
                            ${sql(shapeColumn)},
                            ${stTransform},
                            4096,
                            256,
                            true) geom
                    FROM ${sql(roadTableName)}
                    WHERE ST_Intersects(shape, ${stTransform})
                    ${sql`AND ${condColumn} ${inequalitySign} ${condValue}`}
            `})
            SELECT ST_AsMVT(q, 'layer', 4096, 'geom') layer
            FROM q;
    `;

If I remove the await and try to describe the query, the args just show a pending promise, and the strings are broken.

If I add the await it just crashed and cannot see any helpful debug info about the SQL query itself.

How can I debug this?

[acarstoiu]

I have the same problem. The documentation suggests that describe does not submit the generated query to PostgreSQL, but it does.
So, how can one see what is generated as query before it is executed?

[porsager]

Is this still a problem with v3.3.1 ?

[acarstoiu]

Yes, I'm using the latest version, 3.3.1. Apparently, if the debug option is specified for the DB connections, the thrown exception contains the details about the query, its parameters and what not.
Sorry to hijack this, but why object and number parameters are sent as strings to the database after using their toString()? 😲

[porsager]

Ah, sorry I misinterpreted your question. .describe() will send your query to the db to have it described, but it won't execute it.

The PostgreSQL over the wire protocol is either text or binary, and Postgres.js uses the text version, so that's why it's sent as strings. Using binary is only beneficial in a few corner cases, and might be implemented in the future for those cases, but it's not really a big benefit generally, which is why it's not on the roadmap currently.

If you're talking about actual types value when they reach your db and are inserted, then it's a different matter. There types are decided by PostgreSQL

For instance

sql`select ${ 1 } as x` // returns [{ x: '1' }]

But

sql`select ${ 1 }::int as x` // returns [{ x: 1 }]

[acarstoiu]

Thank you for promptness and for the answer related to describe().

I'll rephrase the other question. How can I control the type of parameters that are sent to the database along with a query like update ... from (values ($1, $2), ($3, $4) ...) as x(a, b) where ... ❓
I'm using exactly what is recommended in this section and all parameters are dispatched as strings (type 25) to the PostgreSQL server, ignoring the fact that I use
${sql([ [1, {...}], [2, {...}, ...])}, namely JS numbers (type 0) and objects (type 114).

I do not have a problem with your sending values as (text) literals instead of some binary representations; I don;t get why these literals do not correspond to numbers, json or whatever else I'm giving as parameter values.

[porsager]

Ah, that's because PostgreSQL can't infer the types in the query through values (...), (...). And then you get an error like operator does not : integer = text ?

If you add explicit casts, then everything works well - like this if we fix the docs sample (notice ::int):

update users set name = x.name::text, age = x.age::int
from (values ${sql(users) }) as x (id, name, age)
where users.id = x.id::int
returning *

You can also force a type from the client by doing eg: sql`select ${ sql.typed(1234, 23) }`. But that will require wrapping your values like that, or at least the first row.

You can also do it with a cte if you think it's clearer.

with xs as (
  select id::int, name::text, age::int from (values ${ sql(users) }) as x (id, name, age)
)
update users set
  name = xs.name,
  age = xs.age
from xs
where users.id = xs.id
returning *