Add FTS5 support to sqlite

[kkyr]

What do you want to change?

Add FTS5 support to sqlite engine.

schema.sql

CREATE TABLE recipes (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
);

CREATE VIRTUAL TABLE recipes_fts USING FTS5 (
    name,
    content='recipes',
    content_rowid='id'
);

-- (triggers omitted)

query.sql

-- name: SearchRecipes :many
SELECT rowid, * FROM recipes_fts
WHERE recipes_fts MATCH ?;

Running sqlc generate on the above input results in the following error:

db/sqlite/query.sql:17:1: relation "recipes_fts" does not exist

What database engines need to be changed?

sqlite

What programming language backends need to be changed?

Go

[lvignoli]

Hello!
Any plan to implement this feature? :)

[lvignoli]

🎉🎉🎉
Thanks !

[yanc0]

@mcdoker18 @lvignoli I can't figure out how to use this.

SELECT *
FROM documents
WHERE id IN (
    SELECT document_id as id FROM documents_fts
    WHERE documents_fts MATCH ?
    ORDER BY rank
)
LIMIT ?
OFFSET ?;

queries.sql:6:11: column "documents_fts" does not exist

I want MATCH on every columns of the virtual fts5 table. I can't find a way to do this.

[mcdoker18]

@yanc0 Hi, sorry for the late response. This was not implemented in my commit.