Drizzle Team•2y ago

Raw sql`` quoting issue

Hi, I doing a work around until Drizzle supports generated columns. I have a manual script that adds the generated column + its index. I'm dynamically building the sql query since there's no typings yet.

const column = 'col-name'
sql`${column} < ${'10'}`

const column = 'col-name'
sql`${column} < ${'10'}`

The syntax above returns no data or sometimes all the data b/c it generates: 'col-name' < '10', which doesn't match b/c of the quote around the column name. If I hardcoded the query to:

sql`col-name < '10'`

sql`col-name < '10'`

Then it returns the correct results. Am I using sql incorrectly? Thanks! NOTE: I'm doing ${'10'} to test the ${} syntax which seems to be working fine. It's only an issue w/ the column.

17 Replies

AlcaponeYouOP•2y ago

I got around this by using

new SQL([new StringChunk(`${someColumn} < 10`)])

new SQL([new StringChunk(`${someColumn} < 10`)])

Luxaritas•2y ago

What you're seeing here is expected. SQL does not "understand" left side vs right side of expressions. Whenever you interpolate, it always escapes the value you put in as if it's a value Typically you want to refer to the column name via the schema you created Eg, if you have a users table with a age column,

sql`${user.age} < ${'10'}

sql`${user.age} < ${'10'}

age in this case won't be a raw string, it'll be a data type the sanitizer knows it can inject "raw"

Dan•2y ago

asuming someColumn is a string, you can do either sql.raw(`'${someColumn}'`) or name(someColumn) (2nd one is preferred)

AlcaponeYouOP•2y ago

Thanks Luxaritas, I'm hacking about w/ generated columns so there's no typings yet. Cool thanks Dan, I'll give that a shot.

Dan•2y ago

So the resulting expression might look like this:

const someColumn = 'col-name';
const value = 10;
sql`${name(someColumn)} < ${value}`;

const someColumn = 'col-name';
const value = 10;
sql`${name(someColumn)} < ${value}`;

AlcaponeYouOP•2y ago

sql`${name(someColumn)} < 10`

sql`${name(someColumn)} < 10`

[{"queryChunks":[{"value":[""]},{"value":"col-name"},{"value":[" "]},"<",{"value":[" "]},"10",{"value":[""]}],"decoder":{},"shouldInlineParams":false}]

[{"queryChunks":[{"value":[""]},{"value":"col-name"},{"value":[" "]},"<",{"value":[" "]},"10",{"value":[""]}],"decoder":{},"shouldInlineParams":false}]

When I log the sql, it looks like there's empty chunks. It throws a syntax error near "<"

Dan•2y ago

can you enable query logging and post the resulting query that's being run?

AlcaponeYouOP•2y ago

Query: select `id`, `author`, <more columns> from `test` where (`col-name` ? ?) -- params: ["<", "10"]`

Query: select `id`, `author`, <more columns> from `test` where (`col-name` ? ?) -- params: ["<", "10"]`

Dan•2y ago

that's really weird

AlcaponeYouOP•2y ago

the query looks like valid sql the queryChunks is odd it looks like it's incorrectly parsing the chunks

Dan•2y ago

for some reason, it puts < to params list

AlcaponeYouOP•2y ago

oh, that's on purpose my comparator is from a variable

Dan•2y ago

but you posted this query:

sql`${name(someColumn)} < 10`

sql`${name(someColumn)} < 10`

AlcaponeYouOP•2y ago

sorry that's right. I'm mixing examples w/ my project

Dan•2y ago

if you need the comparator to be a variable, wrap it in sql.raw() so that sql knows it's not a parameter but a SQL piece

AlcaponeYouOP•2y ago

perfect, that fixed it. Thanks a lot!

Dan•2y ago

I'm craving proper sql documentation 😖 it's really flexible, and poorly documented right now

Gaming

Programming

Raw sql`` quoting issue