DT
Join ServerDrizzle Team
help
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
The syntax above returns no data or sometimes all the data b/c it generates:
If I hardcoded the query to:
Then it returns the correct results.
Am I using
Thanks!
NOTE: I'm doing
I'm dynamically building the
sql query since there's no typings yet.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'`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.I got around this by using
new SQL([new StringChunk(`${someColumn} < 10`)])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 valueTypically 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'}age in this case won't be a raw string, it'll be a data type the sanitizer knows it can inject "raw"asuming
someColumn is a string, you can do either sql.raw(`'${someColumn}'`) or name(someColumn)(2nd one is preferred)
Thanks Luxaritas, I'm hacking about w/ generated columns so there's no typings yet.
Cool thanks Dan, I'll give that a shot.
So the resulting expression might look like this:
const someColumn = 'col-name';
const value = 10;
sql`${name(someColumn)} < ${value}`;sql`${name(someColumn)} < 10`[{"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 "<"can you enable query logging and post the resulting query that's being run?
Query: select `id`, `author`, <more columns> from `test` where (`col-name` ? ?) -- params: ["<", "10"]`that's really weird
the query looks like valid sql
the queryChunks is odd
it looks like it's incorrectly parsing the chunks
for some reason, it puts
< to params listoh, that's on purpose
my comparator is from a variable
but you posted this query:
sql`${name(someColumn)} < 10`sorry that's right. I'm mixing examples w/ my project
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
perfect, that fixed it. Thanks a lot!
I'm craving proper
sql documentation 😖it's really flexible, and poorly documented right now