sqlite fts5 migration

[ak5]

I want to make a table with the fts5 sqlite extension like so:

CREATE VIRTUAL TABLE mytable_fts using fts5(content="mytable", mycolumn);

this works in sqlite3 but not in a dbmate migration with the error Error: no such module: fts5.

[amacneil]

We currently build sqlite into the dbmate binary, with extension support disabled:

https://github.com/amacneil/dbmate/blob/main/Makefile#L6

I believe this was necessary because we want to build a static binary (that can work in alpine containers, etc). You could try changing those build flags and rebuilding sqlite with fts5 enabled?

I don't know much about how sqlite extensions work. Do they always need to be compiled into the binary or can they be loaded at runtime?

Open to suggestions on how to improve the current state of things. Maybe we could use the system sqlite executable if one is available, and fall back to our bundled version.

[ak5]

I think using system sqlite binaries as a flag like --sqlite-binary=/path/to/sqlite3 is probably the best/easiest to implement solution without breaking anything...

[ak5]

For now I am going with making my own binary, seems like I can handle that, thanks!

[amacneil]

It also might be fine to just bundle common extensions?

I’m not exactly sure how to use the system sqlite, but we could look into it.

[ak5]

Maybe just do what ubuntu or another common distro does in that case?

[Enrico204]

Using system sqlite3 means that the binary is no more a static binary that can runs "everywhere" (meaning that it's not tied to a particular version of sqlite or userland in general).

One possibility is to build two flavors: the main one like we are doing right now, and a different one that uses libraries from the O.S. I don't know if it's possible and how to do this, however this can be a solution that doesn't require too much effort (I think).

[dossy]

Is this discussion now obsolete that FTS5 has been included in dbmate as of v2.22.0 thanks to #582?