Raw sql`` quoting issue

AAlcaponeYou4/17/2023
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. I got around this by using
new SQL([new StringChunk(`${someColumn} < 10`)])
new SQL([new StringChunk(`${someColumn} < 10`)])
LLuxaritas4/17/2023
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"
Bbloberenober4/17/2023
asuming someColumn is a string, you can do either sql.raw(`'${someColumn}'`) or name(someColumn) (2nd one is preferred)
AAlcaponeYou4/17/2023
Thanks Luxaritas, I'm hacking about w/ generated columns so there's no typings yet. Cool thanks Dan, I'll give that a shot.
Bbloberenober4/17/2023
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}`;
AAlcaponeYou4/17/2023
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 "<"
Bbloberenober4/17/2023
can you enable query logging and post the resulting query that's being run?
AAlcaponeYou4/17/2023
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"]`
Bbloberenober4/17/2023
that's really weird
AAlcaponeYou4/17/2023
the query looks like valid sql the queryChunks is odd it looks like it's incorrectly parsing the chunks
Bbloberenober4/17/2023
for some reason, it puts < to params list
AAlcaponeYou4/17/2023
oh, that's on purpose my comparator is from a variable
Bbloberenober4/17/2023
but you posted this query:
sql`${name(someColumn)} < 10`
sql`${name(someColumn)} < 10`
AAlcaponeYou4/17/2023
sorry that's right. I'm mixing examples w/ my project
Bbloberenober4/17/2023
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
AAlcaponeYou4/17/2023
perfect, that fixed it. Thanks a lot!
Bbloberenober4/17/2023
I'm craving proper sql documentation 😖 it's really flexible, and poorly documented right now

Looking for more? Join the community!

Want results from more Discord servers?
Add your server
Recommended Posts
Nuxt3 type errorHello, is there somebody who used Drizzle in Nuxt3 project? I have problem with inheriting types froSyntax error mysql migration using composite primary key```sql --> statement-breakpoint CREATE TABLE `member_to_address` ( `member_id` varchar(32) NOT Nwhen using planetscale, using the `.$with()` and `.with()` clauses causes errorIs it not possible to use `$with()` and `with()` with planetscale? it gives the following error: `DaZod prototype mismatchEven though the Zod object constructor name created by Drizzle is ZodObject, it is not an instanceofunrecognized_keysGetting the following error when running generate:pg ``` ZodError: [ { "code": "unrecognized_kRaw SQL / Postgres stored generated column in schemaIs it possibile to add raw sql, or more specifically stored generated columns to the schema? Trying I think i have found another bugtypescript complaining when there's no overridingCustom getter/setter for model's propertyHi, are there any plans for custom getters? (like in TypeORM `transformer`)drizzle-zod type inferLooks there is problem in createInsertSchema type inference. 🤔 (+ need drizzle-zod tag in this posWhy is drizzle-zod converting a string to enum?It seems like it is converting mysql text() or varchar to enums<unknown>?how to do ANYneed to do `where 'myvariable' = ANY(mycolumn)`[BUG?] Postgres transactions throwing connection timeouts after a lot of queriesI think there's a syntax error in Postgres transactions ----> see screenshot cc: @bloberenoberUnique Key SchemaI am trying to create an unique key on a table using a custom schema, the generated sql migration fisqliteTable wrapper which modifies fields, with proper typesMore of a TypeScript question, but I'm trying to create a wrapper for sqliteTable to add a set of stHow to count joined table?I'm trying to count rows returned by join for a query, basically the same as this: https://dba.stackConfig in Typescript?Hello, after upgrading drizzle-kit to 0.17.4 my `drizzle.config.ts` file stopped working. Is it now Problem running a migrationHello there, I'm currently facing an issue with a database migration. I have two simple tables definHow do I connect to sqlite on fly.io?I followed the instructions on fly.io on how to setup a project using sqlite. I believe I did it allEnumsCan pgEnum be converted to zod enums?Typescript build fails with large number of columnsI have a table with ~460 columns, and on trying to run typescript on my app, I'm getting "error TS25