1

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)?

Nick
  • 85
  • 8

0 Answers0