DT
Drizzle Team•16mo ago
some1chan

Unwanted db.execute() behavior; trying to pass in an array instead of a record or its singular

When trying to pass an array (ex. number[], bigint[], string[]) that contains only one element into sql``​, it will be transformed into their non-list type (ex. number, bigint, string respectively). When I put two elements in, it then turns into a Postgres record, which is not what I want. What I expect is that the array stays an array.
22 Replies
some1chan
some1chanOP•16mo ago
My incredibly rough reproduction that may or may not even work: 1. Create a function in PostgreSQL with a parameter that takes in an array. 
CREATE OR REPLACE FUNCTION public.insert_vote(
    IN poll_id uuid,
    IN voting_user_id uuid,
    IN choice_ids uuid[]
) RETURNS TABLE (
    status text,
    existing_choices bigint[],
    votes_created bigint[],
    votes_deleted bigint[],
    max_votes smallint
) AS $BODY$
BEGIN
    -- Can probably just leave empty
END;
$BODY$ LANGUAGE plpgsql;

-- This call shouldn't error out
SELECT public.insert_vote(
    '018abb841f4ae4257adbba232b34f848'::uuid,
    '018abb841f4ae4257adbba232b34f848'::uuid,
    '{018abb841f4ae4257adbba232b34f848}'::uuid[]);
CREATE OR REPLACE FUNCTION public.insert_vote(
    IN poll_id uuid,
    IN voting_user_id uuid,
    IN choice_ids uuid[]
) RETURNS TABLE (
    status text,
    existing_choices bigint[],
    votes_created bigint[],
    votes_deleted bigint[],
    max_votes smallint
) AS $BODY$
BEGIN
    -- Can probably just leave empty
END;
$BODY$ LANGUAGE plpgsql;

-- This call shouldn't error out
SELECT public.insert_vote(
    '018abb841f4ae4257adbba232b34f848'::uuid,
    '018abb841f4ae4257adbba232b34f848'::uuid,
    '{018abb841f4ae4257adbba232b34f848}'::uuid[]);
2. Run the following (at least for the array -> singlar): 
// Environment is ESM-native
import { eq, } from 'drizzle-orm';
import postgres from 'postgres';
import { drizzle, type PostgresJsDatabase } from 'drizzle-orm/postgres-js';

const client = postgres({
    hostname: process.env.POSTGRES_HOST,
    database: process.env.POSTGRES_DB,
    username: process.env.POSTGRES_USER,
    password: process.env.POSTGRES_PASSWORD,
    port: process.env.POSTGRES_PORT
        ? Number(process.env.POSTGRES_PORT)
        : 5432,
});
const db = drizzle(client, { schema, logger: new DefaultLogger() });

const pollHexId = "018abb841f4ae4257adbba232b34f848";
const userId = "018abb841f4ae4257adbba232b34f848";
const choiceIds = ["018abb841f4ae4257adbba232b34f848"];
const sqlQuery = sql`SELECT public.insert_vote(${pollHexId}::uuid, ${userId}::uuid, ${choiceIds}::uuid[]);`;
console.log((await import('util')).inspect(sqlQuery, undefined, 5));

const insertVote = await db.execute(sqlQuery);
// Environment is ESM-native
import { eq, } from 'drizzle-orm';
import postgres from 'postgres';
import { drizzle, type PostgresJsDatabase } from 'drizzle-orm/postgres-js';

const client = postgres({
    hostname: process.env.POSTGRES_HOST,
    database: process.env.POSTGRES_DB,
    username: process.env.POSTGRES_USER,
    password: process.env.POSTGRES_PASSWORD,
    port: process.env.POSTGRES_PORT
        ? Number(process.env.POSTGRES_PORT)
        : 5432,
});
const db = drizzle(client, { schema, logger: new DefaultLogger() });

const pollHexId = "018abb841f4ae4257adbba232b34f848";
const userId = "018abb841f4ae4257adbba232b34f848";
const choiceIds = ["018abb841f4ae4257adbba232b34f848"];
const sqlQuery = sql`SELECT public.insert_vote(${pollHexId}::uuid, ${userId}::uuid, ${choiceIds}::uuid[]);`;
console.log((await import('util')).inspect(sqlQuery, undefined, 5));

const insertVote = await db.execute(sqlQuery);
3. It should have failed; check logs in your console output. 
// Note that the following sql`` structure is correct
SQL {
  queryChunks: [
    StringChunk { value: [ 'SELECT public.insert_vote(' ] },
    '018abb841f4ae4257adbba232b34f848',
    StringChunk { value: [ '::uuid, ' ] },
    '018abb841f4ae4257adbba232b34f848',
    StringChunk { value: [ '::uuid, ' ] },
    [ '018abb841f4ae4257adbba232b34f848' ],
    StringChunk { value: [ '::uuid[]);' ] }
  ],
  decoder: { mapFromDriverValue: [Function: mapFromDriverValue] },
  shouldInlineParams: false
}
// However, this isn't, as the 3rd parameter becomes a non-array.
Query: SELECT public.insert_vote($1, $2, ($3)); -- params: ["018abb841f4ae4257adbba232b34f848", "018abb841f4ae4257adbba232b34f848, "018abb841f4ae4257adbba232b34f848"]
PostgresError: malformed array literal: "018ac05623d3b7dec2b69bb78c8e0844"
// Note that the following sql`` structure is correct
SQL {
  queryChunks: [
    StringChunk { value: [ 'SELECT public.insert_vote(' ] },
    '018abb841f4ae4257adbba232b34f848',
    StringChunk { value: [ '::uuid, ' ] },
    '018abb841f4ae4257adbba232b34f848',
    StringChunk { value: [ '::uuid, ' ] },
    [ '018abb841f4ae4257adbba232b34f848' ],
    StringChunk { value: [ '::uuid[]);' ] }
  ],
  decoder: { mapFromDriverValue: [Function: mapFromDriverValue] },
  shouldInlineParams: false
}
// However, this isn't, as the 3rd parameter becomes a non-array.
Query: SELECT public.insert_vote($1, $2, ($3)); -- params: ["018abb841f4ae4257adbba232b34f848", "018abb841f4ae4257adbba232b34f848, "018abb841f4ae4257adbba232b34f848"]
PostgresError: malformed array literal: "018ac05623d3b7dec2b69bb78c8e0844"
Environment is the following: - Node.js 18.17.1 - ARM64 - pnpm 8.7.6 - PostgreSQL 15 You can get the weird record behavior by just adding another uuid into the array. 
// ...
const choiceIds = ["018abb841f4ae4257adbba232b34f848", "018abb841f4ae4257adbba232b34f848"];
// ...
// ...
const choiceIds = ["018abb841f4ae4257adbba232b34f848", "018abb841f4ae4257adbba232b34f848"];
// ...
// This is using inlineParams() for when I was testing. Error is the same for either-or.
Query: SELECT public.insert_vote('018abb841f4ae4257adbba232b34f848'::uuid, '018abb841f4ae4257adbba232b34f848'::uuid, ('018abb841f4ae4257adbba232b34f848', '018abb841f4ae4257adbba232b34f848')::uuid[]);
PostgresError: cannot cast type record to uuid[]
// Hope is that it would've turned into this:
// SELECT public.insert_vote(
//    '018abb841f4ae4257adbba232b34f848'::uuid,
//    '018abb841f4ae4257adbba232b34f848'::uuid,
//    '{018abb841f4ae4257adbba232b34f848, 018abb841f4ae4257adbba232b34f848}'::uuid[]);
// This is using inlineParams() for when I was testing. Error is the same for either-or.
Query: SELECT public.insert_vote('018abb841f4ae4257adbba232b34f848'::uuid, '018abb841f4ae4257adbba232b34f848'::uuid, ('018abb841f4ae4257adbba232b34f848', '018abb841f4ae4257adbba232b34f848')::uuid[]);
PostgresError: cannot cast type record to uuid[]
// Hope is that it would've turned into this:
// SELECT public.insert_vote(
//    '018abb841f4ae4257adbba232b34f848'::uuid,
//    '018abb841f4ae4257adbba232b34f848'::uuid,
//    '{018abb841f4ae4257adbba232b34f848, 018abb841f4ae4257adbba232b34f848}'::uuid[]);
Note that the UUIDs are technically invalid (they're ULIDs), but this shouldn't matter, assuming Drizzle nor Postgres does any UUID validation. I'm also using the same one for sake of example, but that also shouldn't matter, presumably. I could be doing this thing all wrong (I'm very much new to PostgreSQL and SQL in general), but for the complexity of whatever function I've just done, I think this is how I'm supposed to do it. I'm creating a Postgres function, writing my SELECT query, and conditional logic in it, then do my inserts depending on that, and then RETURN QUERY for whatever. I don't think DrizzleORM has an equivalent to do this in one transaction, nor if I should even attempt to do that at all in DrizzleORM. I'm looking more into the docs; I'm wondering if I just have to use sql.raw perhaps for this one parameter, and somehow connect them all together? Or would that be considered unsafe? Would the typecast save me from raw SQL injection?
Angelelz
Angelelz•16mo ago
This could be a workaround: 
sql`[${choiceId[0]}]`
sql`[${choiceId[0]}]`
some1chan
some1chanOP•16mo ago
and i'd just repeat and join that per choice ID? not entirely sure how this'll work?
Angelelz
Angelelz•16mo ago
I thought the weird behavior was only when the array only had one element I think there are issues with how an array is handled right now in drizzle, give me a sec, I show you a workaround
some1chan
some1chanOP•16mo ago
ah got it; 1. there's two issues, that feel connected. it'd either be one element, or it'd be a record, instead of an array 2. square brackets don't seem to be valid syntax? i'd presume it'd be {} instead, unless im mistaken, at least judging by my raw SQL query that for sure works
Angelelz
Angelelz•16mo ago
Can you show me your raw sql query?
some1chan
some1chanOP•16mo ago
SELECT public.insert_vote(
    '018ac056-23e3-e0e9-1219-a5827a481a27',
    '018ac03e-243b-405e-da02-6c18c7c82b00',
    '{018ac056-26d3-4c2d-e99c-9f6acd25e802, 018ac056-26d3-4c2d-e99c-9f6acd25e803}'::uuid[]);
SELECT public.insert_vote(
    '018ac056-23e3-e0e9-1219-a5827a481a27',
    '018ac03e-243b-405e-da02-6c18c7c82b00',
    '{018ac056-26d3-4c2d-e99c-9f6acd25e802, 018ac056-26d3-4c2d-e99c-9f6acd25e803}'::uuid[]);
This query is whatever I have right now in pgAdmin, and it works just sanity-checked it; this function's been the bane of my existance for a few days 😝 would be unsurprised if it suddenly broke
Angelelz
Angelelz•16mo ago
You would like those values to be dynamic? Or are they hardcoded like that?
some1chan
some1chanOP•16mo ago
yeah, i'd like the values to be dynamic currently, i pull these values from the client from a Discord button + a client fetch of a user's UUID for certain future plans, im worried about malicious user input inside that button somehow
Angelelz
Angelelz•16mo ago
I got you, there are workaround, give me a couple secs 
  const choiceIds = ["018abb841f4ae4257adbba232b34f848", "018abb841f4ae4257adbba232b34f848"];

  const sanitizedChoiceIds: SQL<unknown>[] = [sql.raw('{')]
  const rawChoiceIds: SQL<unknown>[] = []

  for (const choiceId of choiceIds) {
    rawChoiceIds.push(sql`${choiceId}`)
  }

  sanitizedChoiceIds.push(sql.join(rawChoiceIds, sql`, `))
  sanitizedChoiceIds.push(sql.raw('}::uuid[]'))

  const q = await db.select({
    ...
    choiceIds: sql.join(sanitizedChoiceIds)
  }).from(...)
  const choiceIds = ["018abb841f4ae4257adbba232b34f848", "018abb841f4ae4257adbba232b34f848"];

  const sanitizedChoiceIds: SQL<unknown>[] = [sql.raw('{')]
  const rawChoiceIds: SQL<unknown>[] = []

  for (const choiceId of choiceIds) {
    rawChoiceIds.push(sql`${choiceId}`)
  }

  sanitizedChoiceIds.push(sql.join(rawChoiceIds, sql`, `))
  sanitizedChoiceIds.push(sql.raw('}::uuid[]'))

  const q = await db.select({
    ...
    choiceIds: sql.join(sanitizedChoiceIds)
  }).from(...)
Oh, forgot to mention, you might want to use the mapWith() method for choiceIds to have types 
choiceIds: sql.join(sanitizedChoiceIds).mapWith(fnToMapWhateverYourDriverReturnsToAJavaScriptArray)
choiceIds: sql.join(sanitizedChoiceIds).mapWith(fnToMapWhateverYourDriverReturnsToAJavaScriptArray)
some1chan
some1chanOP•16mo ago
is the .mapWith required? i'm currently hitting PostgresError: syntax error at or near "{" and im not sure if that's related, as I have to change your example to match what query I actually need to do (which is a direct sql one, to call a postgres function) Also to sanity check; is this the expected output (at least without .mapWidth)?
Query: SELECT public.insert_vote('018ac05623e3e0e91219a5827a481a27'::uuid, '018ac03e-243b-405e-da02-6c18c7c82b00'::uuid, 
{'018ac05623d3b7dec2b69bb78c8e0844', '018ac05623de2205e235e092b07c48d0'}::uuid[])
// Should have been...
'{018ac05623d3b7dec2b69bb78c8e0844, 018ac05623de2205e235e092b07c48d0}'::uuid[])
Query: SELECT public.insert_vote('018ac05623e3e0e91219a5827a481a27'::uuid, '018ac03e-243b-405e-da02-6c18c7c82b00'::uuid, 
{'018ac05623d3b7dec2b69bb78c8e0844', '018ac05623de2205e235e092b07c48d0'}::uuid[])
// Should have been...
'{018ac05623d3b7dec2b69bb78c8e0844, 018ac05623de2205e235e092b07c48d0}'::uuid[])
if it is required, any leads on how I'd figure that fnToMap out for Postgres.js? I'm looking at their GitHub right now, and nothing particularly stands out to me. Tried using choice.id from my schema
Angelelz
Angelelz•16mo ago
No, I believe I've made a mistake, I'm doing it from memory, I don't have anything pg setup to test
some1chan
some1chanOP•16mo ago
ah, i should specify; im using postgres.js, not pg or so i believe
Angelelz
Angelelz•16mo ago
But the idea is to create a SQL[], and then join it with the sql.join method That way you can sanitize your input This a very constrained example/issue. If you send a reproduction repo I could assist further
some1chan
some1chanOP•16mo ago
https://github.com/some1chan/drizzle-repro
GitHub
GitHub - some1chan/drizzle-repro
Contribute to some1chan/drizzle-repro development by creating an account on GitHub.
some1chan
some1chanOP•16mo ago
hope this helps! let me know if you run into any issues with running the repro
Angelelz
Angelelz•16mo ago
This is the important bit: 
const sqlQuery =
  sql`SELECT public.insert_vote(${pollHexId}::uuid, ${userId}::uuid, `.inlineParams();

const sanitizedChoiceIds: SQL<unknown>[] = [sql`'{`];
const rawChoiceIds: SQL<unknown>[] = [];
for (const choiceId of choiceIds) {
  rawChoiceIds.push(sql.raw(sql`${choiceId}`.queryChunks[1]!.toString()));
}
sanitizedChoiceIds.push(sql.join(rawChoiceIds, sql`, `));
sanitizedChoiceIds.push(sql`}'::uuid[]`);
const sanitizedChoiceIdsQuery = sql.join(sanitizedChoiceIds);

sqlQuery.append(sanitizedChoiceIdsQuery);
sqlQuery.append(sql`)`);
const sqlQuery =
  sql`SELECT public.insert_vote(${pollHexId}::uuid, ${userId}::uuid, `.inlineParams();

const sanitizedChoiceIds: SQL<unknown>[] = [sql`'{`];
const rawChoiceIds: SQL<unknown>[] = [];
for (const choiceId of choiceIds) {
  rawChoiceIds.push(sql.raw(sql`${choiceId}`.queryChunks[1]!.toString()));
}
sanitizedChoiceIds.push(sql.join(rawChoiceIds, sql`, `));
sanitizedChoiceIds.push(sql`}'::uuid[]`);
const sanitizedChoiceIdsQuery = sql.join(sanitizedChoiceIds);

sqlQuery.append(sanitizedChoiceIdsQuery);
sqlQuery.append(sql`)`);
This is workaround after workaround lol The important part is that the sanitation is done with 
sql`${choiceId}`
sql`${choiceId}`
some1chan
some1chanOP•16mo ago
oh wow, that's quite some code seems to work on my end, thank you! update: need to sanity check a few things update: forgot to remove a line, which broke the query. oops! also just to clarify on Drizzle's stability; would I be able to expect this code to be stable, or should I really do some test cases (especially for the queryChunks[1]!)? or should I just not be using Drizzle in prod at all, and I'm doing something really risky
Angelelz
Angelelz•16mo ago
I don't see the sql operator changing any time soon We have to do all this, just because drizzle doesn't support the syntax around arrays passed to functions Would you like to submit an issue for this? on the way we could implement the syntax for '{sanitizedString, sanitizedString}'? Other than that, I don't see a problem with this code cc @a_sherman, any thoughts?
some1chan
some1chanOP•16mo ago
Will do! i'll probably add mention of also the singular issue too, as I'd believe this would be related
some1chan
some1chanOP•16mo ago
https://github.com/drizzle-team/drizzle-orm/issues/1289 posted!
GitHub
[FEATURE]: Allow passing in an array into functions · Issue #1289 ·...
Describe what you want Currently, the default behavior of Drizzle will turn arrays into either its singular element, or a record. This behavior is unintuitive, when you're trying to pass in an ...
Andrii Sherman
Andrii Sherman•16mo ago
query chunks and SQL will work this way always, so if it's working, it will work later as well and this one is helpful! thanks for a GH issue, we will try to fix this one that's why we have SQL operators to not block you while we are improving those parts and great explanations in the ticket, very helpful
Want results from more Discord servers?
Add your server