I'm trying to use the sqlite3 package to perform a full text search of an SQLite database. To do this, I have made the following function:
const searchDb = (query, column, callback) => {
const sql = `DROP TABLE IF EXISTS urls_fts;
CREATE VIRTUAL TABLE urls_fts USING FTS5(url, name, tags);
INSERT INTO urls_fts (url, name, tags) SELECT url, name, tags FROM urls;
SELECT * FROM urls_fts WHERE ${column} MATCH ?;`
console.log(sql)
db.all(sql, [query], (err, rows) => {
if (err) return console.error(err)
console.log(rows)
callback(rows)
})
}
(db
is an sqlite.Database
object)
The urls table is the following:
CREATE TABLE urls (id STRING PRIMARY KEY, url STRING NOT NULL, name STRING NOT NULL, tags STRING);
However, running the function gives the following error:
[Error: SQLITE_RANGE: column index out of range] {
errno: 25,
code: 'SQLITE_RANGE'
}
Using an SQLite browser and running the query from there, replacing ${column}
with a column name (i.e: url), and the ?
with a search query seems to work correctly, so I'm not really sure where I'm going wrong with my SQL query.
How can I update my function so that I can perform the full text search(es)?