The `like` operator is not working in sqlite

[guoqiao]

I have a sqlite db, and a simple articles table, with a varchar title column. I wrote this function to query articles by title keyword:

// search articles by title
async function searchArticles(q){
    const db = await dbPromise;
    const sql = SQL`select * from articles where title like '%${q}%'`;
    console.log(sql.text);
    console.log(sql.values);
    const articles = await db.all(sql);
    return articles;
}

It failed with this:

select * from articles where title like '%$1%'
[ 'Willam ' ]
(node:3709410) UnhandledPromiseRejectionWarning: Error: SQLITE_RANGE: column index out of range
(Use `node --trace-warnings ...` to show where the warning was created)
(node:3709410) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
(node:3709410) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

To workaround, I have to append the like statement as raw string and then it's working:

const sql = `select * from articles where title like '%${q}%'`;

[absolux]

@guoqiao try this

SQL`select * from articles where title like ${`%${q}%`}`;