turso geospatial and fuzzy search using extensions
There's an option to create a database with some of the extensions enabled that are kinda barebones. I don't know how to use those to begin with much less through the ORM. https://github.com/nalgeon/sqlean/blob/main/docs/fuzzy.md
GitHub
sqlean/docs/fuzzy.md at main · nalgeon/sqlean
The ultimate set of SQLite extensions. Contribute to nalgeon/sqlean development by creating an account on GitHub.
3 Replies
Hi, I also have an application which needs Geospatial and fuzzy search.
In my app we store cities and search through them via name and to find out which is the nearest city to a users location. I think we can combine our knowledge to find a good solution.
I'm just guessing but I would assume the steps are:
1) https://orm.drizzle.team/docs/get-started-sqlite#better-sqlite3 - you create your db connection with better-sqlite3
2) https://github.com/nalgeon/sqlean/blob/main/docs/install.md#install-nodejs - here sqlean have docs on how to load extension with better-sqlite3
3) then you just use
sql "magic" operator to do direct queries like select soundex('hello');
or something like that@janat08 @FleetAdmiralJakob 🗕 🗗 🗙 For the geospatial queries you can read the SQLite help page on R*Tree which is included on Turso databases by default:
https://www.sqlite.org/rtree.html
This extension uses VIRTUAL TABLE, which Drizzle doesn't currently support in the schema. However you can manually modify the SQL generated by drizzle migrate to create virtual tables with this extension. You then interact with the virtual table like a regular table.
Please leave a comment on this feature request if you would like to see it implemented: https://github.com/drizzle-team/drizzle-orm/issues/2046
For the SQLean Fuzzy, you will need to run:
turso db create [database-name] --enable-extensions
Then as @Huge Letters mentioned at point 3 use the magic sql operator.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...