parameter (ex: $1) being converted from int to string

[stockson]

I'm having an issue with parameters being converted from numbers to strings before being passed to the pg server.

Here are the test cases using Typescript + Mocha + Chai:

import { Client } from "pg"
import testDbConfig from "./testDbConfig"

describe.only("pg", () => {

	let client: Client

	beforeEach( async () => {
		client = new Client(testDbConfig.auth)
		await client.connect()
	})
	afterEach( async () => {
		await client.end()
	})

	it("inline-parameter query", async () => {
		const {rows: {0: row}} = await client.query("select 1 as num, 'foo' as str")
		row.num.should.equal(1)
		row.str.should.equal("foo")
	})

	it("parameter query using values arg", async () => {
		const {rows: {0: row}} = await client.query("select $1 as num, $2 as str", [2, "bar"])
		row.num.should.equal(2)
		row.str.should.equal("bar")
	})
})

Test Results:

Logs from the Postgres Server: "duration: 0.271 ms statement: select 1 as num, 'foo' as str" "duration: 0.009 ms execute : select $1 as num, $2 as str", "parameters: $1 = '2', $2 = 'bar'"

[sehrope]

This is not a driver issue, it's how the server treats parameters whose types cannot be inferred from the rest of the query.

The $1 in your query has no explicit type information and there's no way for the server to know that you mean for it to be an integer so when the query gets parsed, it is treated as a string. The server then informs the client that the parsed query has two parameters, both of which are strings, and the client (this driver) serializes the number two (2) as a string when sending the parameter.

If you want to ensure that the parameter is properly interpreted, you need to either use it in a context that will let the server know it's expected data type:

SELECT t.*
FROM foo t
WHERE t.some_int_field = $1

Or you can explicitly cast the parameter usage with it's expected type:

SELECT
  $1::int AS some_int,
  $2::text AS some_text,
  $3::date AS some_date

[stockson]

@sehrope Thanks for getting back to me about this. I'm still confused, the driver/server does know the JS variable type. For example something like,

if / case ( Number.isFinite(param) )
	return param.toString()
else if ( typeof param == "string" )
	return `'${param}'`
etc

[sehrope]

The driver knows about the data type of the parameters but it does not include them in the "parse" request sent to the server with the command SQL. Only the SQL text itself is sent and the server must infer how each parameter will be used.

Once the driver knows how the parameters will be used by the server, it transforms the parameters to match what the server is expecting. So if the server expects a string but you supplied a number, it'll be transformed into text.

See the "Parse" message here for more details on the specifics of how the server handles things: https://www.postgresql.org/docs/current/protocol-message-formats.html

Note that while it's possible for the driver to include parameter information in that Parse message this driver does not do so.

[stockson]

Thanks for helping me understand, I think I get it now:

If you're handing off parameters to a pg variable that expects varchar, node-postgres will convert the JS variable. You prioritize pg's variable type over javascript's type? I guess that makes sense. Just seems to choke and create unintuitive behavior in scenarios where pg doesn't know the type like the one I described above. For the sake of type consistency, it'd be convenient if pg rejected a type that isn't comparable instead of converting it. That way it'd be enforcing the type guard (in TS and JS runtime) instead of potentially converting a variable without me realizing it. And then deferring to JS instead of defaulting to a string if it doesn't have a pg type like the above example.

Does that make sense? As you mentioned other drivers like "mysql" and "mssql" handle it that way and I'm still trying to understand the decision and how the node-postgres driver works.

In case I'm not making sense I tried to illustrate it:

import mysql from "mysql"
import chai from "chai"
chai.should()

import conf from "./conf"

describe.only("mysql parameters", () => {

	// create test_table ( num int, str varchar(32) )
	
	it("should be successful with correct types", done => {
		const con = mysql.createConnection(conf)

		const sql = `insert into test_table (num, str) values (?, ?)`
		const params = [123, "abc"]

		con.connect( er => {
			con.query(sql, params, (err, results, fields) => {
				results.should.be.ok
				con.end( done )
			})
		})
	})

	it("should throw with incorrect parameter types", done => {
		const con = mysql.createConnection(conf)

		const sql = `insert into test_table (num, str) values (?, ?)`
		const params = ["abc", 123] // reversed parameters

		con.connect( er => {
			con.query(sql, params, (err, results, fields) => {
				const msg = "Incorrect integer value: 'abc' for column 'num' at row 1"
				err!.sqlMessage!.should.equal(msg)
				con.end( done )
			})
		})
	})

})