How to specify type using sql template

I'm using this piece of code

const ids = [ 3445, 3446, 999 ];
const values = sql.join( ids.map( v => sql`(${v})` ), sql`,` );

const query = sql.empty();
query.append( sql`SELECT * FROM ( VALUES ` );
query.append( values );
query.append( sql` ) AS temp(id)` );
query.append( sql` WHERE NOT EXISTS (` );
query.append( sql` SELECT * FROM ${mytable} WHERE ${mytable.id} = CAST( temp.id AS integer )` );
query.append( sql` )` );

const result = await database.execute( query );

const ids = [ 3445, 3446, 999 ];
const values = sql.join( ids.map( v => sql`(${v})` ), sql`,` );

const query = sql.empty();
query.append( sql`SELECT * FROM ( VALUES ` );
query.append( values );
query.append( sql` ) AS temp(id)` );
query.append( sql` WHERE NOT EXISTS (` );
query.append( sql` SELECT * FROM ${mytable} WHERE ${mytable.id} = CAST( temp.id AS integer )` );
query.append( sql` )` );

const result = await database.execute( query );

to create a query like this

{
  query: 'SELECT * FROM ( VALUES ($1),($2),($3) ) AS temp(id) WHERE NOT EXISTS ( SELECT * FROM "mytable" WHERE "mytable"."id" = CAST( temp.id AS integer ) )',
  params: [ 3445, 3446, 999 ]
}

{
  query: 'SELECT * FROM ( VALUES ($1),($2),($3) ) AS temp(id) WHERE NOT EXISTS ( SELECT * FROM "mytable" WHERE "mytable"."id" = CAST( temp.id AS integer ) )',
  params: [ 3445, 3446, 999 ]
}

There is a few problems with it, I guess because this is a parameterized query and parameters are used as literals (need confirmation on this)? 1. I have to cast temp.id to integer in each subquery, otherwise I get PostgresError: operator does not exist: integer = text. I don't know how that affects performance, but it feels dirty. 2. The query results are of type string, which I then have to cast again. 3. Also, since the whole query is of type SQL<unknown>, when parsing the result I need to force the wrong/unwanted type string onto the typescript compiler first, just to be able to cast the value, like this result.map( v => +(v.id as string) ) Is this really the most optimal way of doing things here, or am I missing something? I found a param method in drizzle but no docs and no idea how to use it or if it could be useful anyhow. Please enlighten me.

1 Reply

ccabdOP•16mo ago

To simplify the question. I'm putting in numbers, how can I get numbers out as well? Forgot to update The column types in a row constructor are taken from the first row, so all I had to do is add a CAST() to the first row, which solved the issues I had

  const rowConstructor =
    (v: number, idx: number) => idx === 0 ? sql`(CAST (${ v } AS integer))` : sql`(${ v })`;

  const values = sql.join( appids.map( rowConstructor ), sql`,` );

  const rowConstructor =
    (v: number, idx: number) => idx === 0 ? sql`(CAST (${ v } AS integer))` : sql`(${ v })`;

  const values = sql.join( appids.map( rowConstructor ), sql`,` );

Gaming

Programming

How to specify type using sql template