drizzle-team / drizzle-team/drizzle-orm

[FEATURE]: sqlite CREATE VIRTUAL TABLE and R*Tree extension

Open
#2,046 12 comments 148 reactions 0 assignees View on GitHub
db/sqlite drizzle/kit enhancement
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### Describe what you want

sqlite implements geospatial queries using the R*Tree extension ([https://www.sqlite.org/rtree.html](url)), and full text search using the FTS5 extension ([https://www.sqlite.org/fts5.html](url)). Both extensions are enabled by default on Turso. ([https://docs.turso.tech/extensions](url))

Use of these modules requires the CREATE VIRTUAL TABLE statement. ([https://www.sqlite.org/vtab.html](url))

The schema for an R*Tree virtual table might look like:

`export const suburbSpatialIndex = sqliteRTree('suburbSpatialIndex', 'id', ['minLon', 'maxLon'], ['minLat', 'maxLat'])`

this would create a migration:

`CREATE VIRTUAL TABLE suburbSpatialIndex USING rtree(id, minLon, maxLon, minLat, maxLat);`

Querying the virtual table would be just like querying a regular table:

`db.select().from(suburbSpatialIndex).where(and(lte(suburbSpatialIndex.minLon, 144.5), gte(suburbSpatialIndex.maxLon, 144.5), lte(suburbSpatialIndex.minLat, -37.5), gte(suburbSpatialIndex.maxLat, -37.5)))`

this would generate the sql:

`SELECT * FROM suburbSpatialIndex WHERE minLon <= 144.5 AND maxLon >= 144.5 AND minLat <= -37.5 AND maxLat >= -37.5`

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.