DEV Community

Discussion on: Dynamically generating SQL queries using Node.js

Collapse
 
gajus profile image
Gajus Kuizinas • Edited

You don't need to: sql tags can be nested.

let field = 'createdAt';
let dir = field.startsWith('+') ? sql`ASC` : sql`DESC`;

let orderToken = sql`
  ORDER BY ${sql.identifier([field])} ${dir}'
`;

let q = sql`
  SELECT * FROM "post"
  ${orderToken}
  LIMIT 3
`;

Collapse
 
ivan_paqmind profile image
Ivan Kleshnin • Edited

Thanks. What about optional ORDER BY? For WHERE you started with TRUE so the absence of conditions results in WHERE (true) which is syntactically correct and has no performance implications. I can't find anything than can be used to order by default:

ORDER BY ??? default ???
Enter fullscreen mode Exit fullscreen mode

Another attempt with ?: breaks the placeholder ordering in query:

SELECT ${makeProjection(pick)} 
FROM ${sql.identifier([table])}
WHERE ${makeWhere(filter)}
${sort ? sql`ORDER BY ${makeOrder(sort)}` : ``} -- doesn't work
LIMIT ${makeLimit(limit)}
OFFSET ${makeOffset(offset)}
Enter fullscreen mode Exit fullscreen mode
syntax error at or near "$1"
Enter fullscreen mode Exit fullscreen mode
Thread Thread
 
gajus profile image
Gajus Kuizinas
${sort ? sql`ORDER BY ${makeOrder(sort)}` : sql``}

Enter fullscreen mode Exit fullscreen mode
Thread Thread
 
ivan_paqmind profile image
Ivan Kleshnin

Causes

InvalidInputError: Unexpected SQL input. Query cannot be empty.
Thread Thread
 
gajus profile image
Gajus Kuizinas
sql`--`

Thread Thread
 
gajus profile image
Gajus Kuizinas

I cannot recall the original reason for this constraint. Will likely remove this constraint. Track github.com/gajus/slonik/issues/93.