How To Do Prepared Statement LIKE With SQLlite 3 and The Better SQLite 3 Package

I love SQLite and I am using it in my Aurelia 2 book for the server aspect to provide users with a real server backed by a database. I am using the equally brilliant Better SQLite 3 library which allows you to work with SQLite 3 databases in a performant and easy way.

This is how I did a prepared statement for my LIKE query which searched products by title in my database. The statement itself needs LIKE ? and then on the all method we provide the argument.

Using the template literal syntax with backticks, we handle our wildcard syntax and value in there. You can use whatever pattern you want here, but my use case needed %${query}%

const db = sqlite('mydatabase.db');

const rows = db.prepare(`SELECT * FROM products WHERE title LIKE ?`).all(`%${query}%`);

It’s simple and it works beautifully. Presumably, this would also work for other SQLite 3 libraries, but I highly recommend the Better SQLite 3 library for working with SQLite databases.