1

I have a simple model that has a string field, like this:

final class Record: Model, Content {
    
    @ID(key: .id)
    var id: UUID?

    @Field(key: "name")
    var name: String

    ...
}

And I want to query and filter this name field using a regexp pattern.

My current solution is using .all() to get all rows, then use normal NSRegularExpression way to filter every row in Swift.

However this is REALLY SLOW. On my machine I can do around 400 requests per second using the ORM framework provided .filter() functions. While only 4 request per second with my own regex filter.

I know that sqlite does provide a REGEXP function, and it's significantly faster. But it requires loading an external module to run.

So how can I import this module using FluentSQL or is there any workaround?

Axeler4t0r
  • 11
  • 1
  • SQLite3 supports the REGEXP operator: `WHERE x REGEXP ` so with Fluent you can filter by custom `REGEXP` operator easily. – imike Feb 23 '22 at 19:30
  • @imike Some SQLite3 distribution does not include REGEXP operator by default, like the Ubuntu install which I am using. I installed `sqlite3-pcre` via `apt-get`, then I can use `REGEXP` in command line after `.load /usr/lib/sqlite3/pcre.so`. But I don't know how to do this in Fluent. – Axeler4t0r Feb 24 '22 at 00:48
  • Similar to this `.filter(\.$name, .custom("ILIKE"), "earth")` which is written here https://docs.vapor.codes/4.0/fluent/advanced/ – imike Feb 24 '22 at 13:37

0 Answers0