Full-Text Search support for SQLite

[matthewmueller]

(follow-up from #1684)

Problem

In 2.30.0, we shipped Full-Text Search support for Postgres in Preview.

We have plans to also support SQLite full-text search. Some of the prerequisites for this feature is being able to manage indices in Migrate and Introspection and also compile the extension into SQLite.

Suggested solution

It should have the same Full-Text Search API.

[Secretmapper]

Anyone found a way to do this manually through raw SQL while waiting for this to ship? How can we add migrations for this manually?

[jackbravo]

@Secretmapper, yes. You can do something like what is reported on #8106. Where you add custom SQL code to your database, adding full text indexes on sqlite. The problem, as reported on #8106 is that the shadow tables created by SQLite are not ignored when runngin prisma migrate dev. What I've done in a personal project is to run this full text index creation code outside of the migration steps. When I want to create a new migration I do:

1. update the prisma schema code with the changes I want
2. in my dev environment I run prisma migrate reset, to reset the database to not use my custom full text index.
3. then run prisma migrate dev to generate the new migration
4. run again my custom SQL to generate the full text index
5. if all test go well, deploy code to prod, but on prod I only run prisma migrate deploy

I hope this makes sense

[benjaminknauer]

Any timeline for this?

[janpio]

No. We will put this on our roadmap when we are ready to look into this. Right now other things are higher priority for us.

[colindotfun]

👀

[dubem-design]

👀

[shmuli9]

👀

[SiavoshZarrasvand]

No. We will put this on our roadmap when we are ready to look into this. Right now other things are higher priority for us.

Any update on this? Been over two years.
For me personally, I've stopped using Prisma due to this.