I have the following line in a program written in Node.js using Knex and SQLite:

await db.table("books")
         .innerJoin("items", "items.id", "books.item_id")
         .with("idx", db.raw(`instr(items.name, ?) asc`, name))
         .where("idx > 0")
         .orderBy("idx")
         .select()

Where db is a variable created by calling knex(config). However, the function raw(sql) doesn't seem to work, as it keeps throwing this error at runtime:

TypeError: The operator "undefined" is not permitted at Formatter.operator (I:\git\server\node_modules\knex\lib\formatter.js:138:13)

at QueryCompiler_SQLite3.whereBasic (I:\git\server\node_modules\knex\lib\query\piler.js:525:100)

at QueryCompiler_SQLite3.where (I:\git\server\node_modules\knex\lib\query\piler.js:314:32)

What am I doing incorrectly?

If it's relevant, I'm writing in Typescript, and as you can see with await, I am using ES6. However, this query executes fine if I exclude the with(), and with() refuses to accept something that is not created by raw().

Edit:

If I test this, it shows that the problem is in with() and not in raw():

console.log("name: " + name);
console.log("db.raw(name): " + db.raw(`instr(items.name, ?) asc`, name));

Gives the expected output.

I have the following line in a program written in Node.js using Knex and SQLite:

await db.table("books")
         .innerJoin("items", "items.id", "books.item_id")
         .with("idx", db.raw(`instr(items.name, ?) asc`, name))
         .where("idx > 0")
         .orderBy("idx")
         .select()

Where db is a variable created by calling knex(config). However, the function raw(sql) doesn't seem to work, as it keeps throwing this error at runtime:

TypeError: The operator "undefined" is not permitted at Formatter.operator (I:\git\server\node_modules\knex\lib\formatter.js:138:13)

at QueryCompiler_SQLite3.whereBasic (I:\git\server\node_modules\knex\lib\query\piler.js:525:100)

at QueryCompiler_SQLite3.where (I:\git\server\node_modules\knex\lib\query\piler.js:314:32)

What am I doing incorrectly?

If it's relevant, I'm writing in Typescript, and as you can see with await, I am using ES6. However, this query executes fine if I exclude the with(), and with() refuses to accept something that is not created by raw().

Edit:

If I test this, it shows that the problem is in with() and not in raw():

console.log("name: " + name);
console.log("db.raw(name): " + db.raw(`instr(items.name, ?) asc`, name));

Gives the expected output.

• javascript
• node.js
• sqlite
• typescript
• knex.js

• What is the value of the name variable? – Matt Commented Nov 2, 2017 at 1:51
• @Matt if it matters, it's just a string like "hello world" – user7706068 Commented Nov 2, 2017 at 1:55
• Just checking if that was the undefined value. – Matt Commented Nov 2, 2017 at 2:04

1 Answer 1

Turns out the problem was with where(), which I didn't see until I inspected the stack trace more closely. Replacing .where("idx > 0") with .where("idx", ">", 0) fixed it.