Enable fts (full text search) for Deno-sqlite

[dyedgreen]

I've just run into this too. I was trying to load the fts5 extension.

(Originally posted by @hobochild in #100 (comment) )

Full text search seems like a common problem and it might make sense to enable full text search by default (since there is currently no way to import extensions at run-time). But I wanted to see how many others are interested in this functionality. (It it turns out to be too niche, it might make sense to ask people to compile their own WASM module with the extension enabled.)

For reference: SQLite full text search docs

[artisonian]

I'd like to see fts5 support by default. What about rtree (considering it is also available in the sqlite CLI)?

[dyedgreen]

Hm. I would be curious to test how much each feature effects the WASM binary size vs how much they are used to better understand the tradeoffs involved.

It might also be interesting to offer some more documentation on how to compile your own version if you have specific needs.

[artisonian]

This is anecdotal, but I ran deno info after enabling fts5 and rtree:

https://deno.land/x/[email protected]/build/sqlite.js (810.68KB)
file:///src/vendor/deno-sqlite/build/sqlite.js (1.03MB)

And fts5 alone:

file:///src/vendor/deno-sqlite/build/sqlite.js (1018.54KB)

[dyedgreen]

A thought on loading sqlite extensions: with Deno.dlopen it is possible to run code from a shared object. I’m not sure how much memory sharing SQLite extensions require; but if it’s sufficiently little (we can’t really easily expose the WASM memory to a so I believe), it might be possible to run arbitrary SQLite extensions.

[iuioiua]

Having fts supported would be amazing. Are there any workarounds in the meantime?

[lusbuab]

@ashersaupingomez

Having fts supported would be amazing. Are there any workarounds in the meantime?

You'd need to compile the WASM module yourself with the necessary build options enabled. https://www.sqlite.org/fts5.html should include all necessary information.

[mdkq]

Any update on this? I'm having no problem with a custom build with fts5 enabled but would prefer not to have the repo sitting in my project

[dyedgreen]

I've had some thoughts around using denos dll loader / a wash loader set-up to support loading custom extensions, but I don't have any specific progress on that yet.

[EtienneK]

+1 for this feature. It is something I really need.

[teleclimber]

It might also be interesting to offer some more documentation on how to compile your own version if you have specific needs.

I think this would be welcome at this point. It seems some people were able to enable fts without too much trouble. Perhaps @artisonian would be willing to publish his changes? I took a look at doing this myself but I'm quickly out of my depth.

A maintained fork or branch with commonly requested configurations could be one way to address this.

Thanks!

[alexgleason]

@mdkq Can you share the commands you're using to build SQLite and use it with this library?

[teleclimber]

@alexgleason You can fork this repo, make the changes to the config, and let Github actions build it for you. See the commits I made on my fork:

https://github.com/teleclimber/deno-sqlite

Then you can import it directly from Github:

import { DB } from "https://github.com/teleclimber/deno-sqlite/raw/master/mod.ts";

const db = new DB();

db.execute(`
CREATE VIRTUAL TABLE posts 
USING FTS5(title, body)
`);

//.. more FTS5 fun

Feel free to us mine to experiment with, but I make zero promises to keep it up to date right now (and it's already behind).

[alexgleason]

@teleclimber Fantastic, thank you!!!

[alexgleason]

Well, FTS works awesome with @teleclimber's fork. I'm even using it with Kysely query builder and it's wonderful.

@dyedgreen Would you be open to merging 05ce37d? I can make a pull request.

[dyedgreen]

I think this is probably fine; I’d like to check if this extension is also enabled in nodes better sqlite but fts5 seems useful enough that we can enable it by default 😄