How can I create a virtual table with sqlite?
I am using turso & sqlite. I have enabled the FTS5 extension so I can perform full text search. I want to define a virtual table in my schema so I can query it using drizzle. If I define it as a regular sqliteTable, running db:push from drizzle kit will create the table and break the virtual table. What's the best way to proceed?
9 Replies
If you created the virtual table manually, you might just need to create it in the schema for drizzle to recognize it but don't run db:push
Is there a way I can exclude it from db:push? I don't want to lose the functionality.
There might be a couple ways you could achieve that
You could manually not export it when running the push
And re-export it for regular operation
But that could get messy IMO
how would you create it in the schema? just as a normal sqliteTable? or is there a special virtualtable?
just curious if either of you got a working solution? I'm starting down the road of wanting to search through a turso table and figured drizzle supported that, but seems like you have to hack some stuff in there?
Same over here. Seems like both drizzle and prisma just can't get this sort of things working, real bummer
Maybe you can declare it as an existing view? I don't really know ...
https://orm.drizzle.team/docs/views#declaring-existing-views
Drizzle ORM - Views
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
+1, would also like to do FTS with Turso
I've requested a virtual table feature: https://github.com/drizzle-team/drizzle-orm/issues/2046 Please give the feature request a bump 😜
In the mean time, I've added the virtual table to the schema as a normal table, then in the migration sql file modified it to the sqlite virtual table syntax. (FYI I'm using drizzle kit generate, not push). Works fine as a workaround.
GitHub
[FEATURE]: sqlite CREATE VIRTUAL TABLE and R*Tree extension · Issue...
Describe what you want sqlite implements geospatial queries using the R*Tree extension (https://www.sqlite.org/rtree.html), and full text search using the FTS5 extension (https://www.sqlite.org/fts...