SQLite for beginners: Full Text Search

SQLite has an extension called FTS5, which provides full text search capabilities to your SQLite database.

So, how do you use the full text search features in SQLite?

Watch this short video to learn more.

[nickang]

This is an amazing tutorial - I particularly liked the series of questions and answers format. Thanks James!

[antonioorsatti]

I was wondering, does that means the size of the db file is doubled?

[nodidog]

This is a fantastic explantation, thank you!

[eric]

Really helpful for a biginner, thank you!

[shadygoat]

It would be quite nice to have ether a github or a pastebin with the sql written in this video!

[boot-strapper]

im really wondering if you can have fts search across all tables. Like, in my case they are searching for d&d stuff, and wont necessarily know exactly what table to search. Would I want to make a table to has a relation to another table and a blob of text that is the search column?

[Promar]

excellent video! I have some questions:
Why do you have to perform a join operation between the virtual and original table if they are always set to be in sync after setting up the triggers? Could you not just query the virtual table directly?
How does something like fts5 compare to lucene based search indexes? When would it make sense to use on over the other?

[kishanbsh]

Does this MATCH do a fuzzy find ? How does the search work?

[jonathanlarsson]

I have a problem with getting a search function to work in my js file:

const sql = "SELECT Journal.title AS Journal_title, Article.a_id, Article.title AS Article_title, Article.i_id AS Article_i_id, Article.au_id AS Article_au_id, Issue.number (SELECT GROUP_CONCAT(Author.name, '; ') FROM Journal.title INNER JOIN Issue ON Issue.j_id=Journal.j_id INNER JOIN Article ON Article.title=Journal.title INNER JOIN Author ON Author.au_id=Article.au_id) ORDER BY Issue FROM Article WHERE Author.name LIKE '%" + q + "%'";

According to the command prompt, its a syntax error. What have I done wrong?