4 replies

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

temp.id

to integer in each subquery, otherwise I get

PostgresError: operator does not exist: integer = text

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

string

, which I then have to cast again.
3. Also, since the whole query is of type

SQL<unknown>

SQL<unknown>

, when parsing the result I need to force the wrong/unwanted type

string

string

onto the typescript compiler first, just to be able to cast the value, like this

result.map( v => +(v.id as string) )

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

param

method in drizzle but no docs and no idea how to use it or if it could be useful anyhow. Please enlighten me.

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

temp.id

to integer in each subquery, otherwise I get

PostgresError: operator does not exist: integer = text

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

string

, which I then have to cast again.
3. Also, since the whole query is of type

SQL<unknown>

SQL<unknown>

, when parsing the result I need to force the wrong/unwanted type

string

string

onto the typescript compiler first, just to be able to cast the value, like this

result.map( v => +(v.id as string) )

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

param

method in drizzle but no docs and no idea how to use it or if it could be useful anyhow. Please enlighten me.

How to specify type using sql template

Similar Threads

How to specify type using sql template

Similar Threads

Similar Threads

Similar Threads