Objection.js: Full-Text Search?

[mycahjay-nms]

Hi there - Absolutely love using Objection.js! Switched from Sequelize, and I've never, ever looked back!

Does Objection support full-text searches on pgsql or mysql? Any tips on implementing fulltext search on an existing postgres DB?

Thank you!

[elhigu]

Full text search in postgresql is quite limited, but should work ok if you limit on searching complete words. It doesn't really handle fuzzy searching etc. What kind of usecase you have for it?

If indexed stuff are for example text docuements, postgres full text search might be good enough, since stemming helps mapping typoed / close enough words to real ones, but if you want to use it to search user names etc. it might not work as good as you wish.

Anyways there is no special support for full text search in knex nor in objection.js. So you would need to write raw queries... Would be interesting to write plugin for that stuff though.

[devinivy]

It is pretty simple with raw queries. Say you have an index on a column searchview of tsvectors. Then you can search that (in postgres) with,

YourModel.query().whereRaw('searchview @@ plainto_tsquery(?)', [term])

Also, check-out postgres's trigram indexes for more "fuzziness."

[koskimas]

As @elhigu mentioned there is no special support for full text search and no plans to add it. I'll close this for now. Feel free to continue the conversation here though.

[cameronblandford]

Since Objection doesn't support FTS or the tsvector type, I wrote out a sample migration that auto-generates indexed tsvectors on row insert.

https://gist.github.com/cameronblandford/808ca0f66acffb8b50b4e3704d6063a1

It helped me out a lot, hope it helps some of you as well :)