Using SqlHydra in a timeseriesDB (Postgres) with a "WHERE builder" (subnet fun with inet types)

[houstonhaynes]

I've seen your example here and I appreciate the options you present. In my case I'll be spawning actors using Akkling where the query will bring back results from a timeseriesDB table. For a particular client application view there will be a handful (8 to 12) of visuals where each child actor is responsible for one query that's triggered by a parent actor on a schedule to supply updates on data for those visuals.

So far I've been looking at using a list comp to do the assembly and run the query - but as you can see it's already getting a bit complicated with this IPAddress types.

open System.Net
open Akka.Actor
open Npgsql.FSharp
open Npgsql.FSharp.Akkling
open Npgsql.FSharp.SqlModule

// message type for actor
type QueryParams = {
    countryCode: Option<string>
    secondColumn: Option<string>
    thirdColumn: Option<string>
    srcIP: Option<IPAddress>
    dstIP: Option<IPAddress>
    interval: string
}

let queryActor (ctx: Actor<_>) (msg: QueryParams) =
    async {
        let baseQuery = "SELECT UNNEST(string_to_array(col, ';')) AS col_part, COUNT(*) AS count FROM events WHERE event_time >= now() AT TIME ZONE 'UTC' - INTERVAL "

        // Function to convert IP to /24 subnet string, if IP is provided
        let ipToSubnet24 = function
            | Some(ip) -> 
                let bytes = ip.GetAddressBytes()
                Some(sprintf "%d.%d.%d.0/24" bytes.[0] bytes.[1] bytes.[2])
            | None -> None

        let srcIpClause = msg.srcIP |> ipToSubnet24 |> Option.map (sprintf " AND src_ip <<= '%s'::inet")
        let dstIpClause = msg.dstIP |> ipToSubnet24 |> Option.map (sprintf " AND dst_ip <<= '%s'::inet")


        // Build the dynamic WHERE clauses based on the message
        let whereClauses = 
            [ if msg.countryCode.IsSome then Some(sprintf "cc = '%s'" msg.countryCode.Value) else None
              if msg.secondColumn.IsSome then Some(sprintf "sc = '%s'" msg.secondColumn.Value) else None
              if msg.thirdColumn.IsSome then Some(sprintf "tc = '%s'" msg.thirdColumn.Value) else None
              if msg.srcIP.IsSome then Some(sprintf "AND %s" srcIpClause) else None
              if msg.dstIp.IsSome then Some(sprintf "AND %s" dstIpClause) else None ]
            |> List.choose id
            |> String.concat " AND "

        let interval = "'" + msg.interval + "'" 

        let finalQuery = 
            baseQuery + interval + 
            (if not (String.IsNullOrWhiteSpace(whereClauses)) then "" + whereClauses else "") + 
            " GROUP BY col_part"

        let! result = 
            use conn = new NpgsqlConnection("<connection string>")
            conn.OpenAsync() |> Async.AwaitTask
            Sql.query conn finalQuery
            |> Sql.executeAsync<(_, int)> |> Async.AwaitTask
        
        printfn "Query Result: %A" result
    } |> Actor.ofAsyncFunc


// Usage example
let system = ActorSystem.create "MySystem"
let actorRef = system.ActorOf(Actor.props (queryActor), "QueryActor")


actorRef <! { countryCode = Some "USA"; secondColumn = Some "YES"; dstIP = "10.1.128.173" ; interval = "'1 MINUTE'" }

So while the DB is relatively simple - it's just one time series table with a dozen columns in it - there's some nuance to how queries can be built and changed during the lifetime of the actor. I thought that SqlHydra might have a way of making the cognitive burden of this a bit more manageable and wondered if you had thoughts on an alternative approach to using Npgsql.Fsharp. Thanks!

[JordanMarr]

It looks to me like Npgsql.FSharp is the better choice in this case over SqlHydra.

I think SqlHydra is most valuable for basic CRUD queries in large databases with lots of tables and columns to keep track of, thereby eliminating the potential for typos in your many queries. Most of the apps that I work on are composed of 95% of these kinds of CRUD queries.

But for the 5% of highly specialized core domain queries, like this one, I think manually editing the SQL as you are doing is advantageous. The strongly typed bits of SqlHydra will probably just get in the way of what you are trying to do here. Especially considering the fact that you are using specialized Postgres syntax like UNNEST(string_to_array(col, ';')) AS col_part and operators like >>=, and dynamically modifying the where clause.

Granted, you could do these things in SqlHydra by using the kata custom operation to tweak the underlying SqlKata query.
But that would add more complexity than what you are already doing.
I added the kata operation because I think a lot of people will already be happily using SqlHydra for 95% of their app, and they may want to go ahead and use it for the last 5% of domain stuff just for consistency rather than using standard SQL strings, but it's definitely not its strong suit.

Another alternative would be to use the SqlKata library directly, as it offers a very fine-grained way to parameterize SQL queries. (SqlHydra uses it behind the scenes.) But really, I think what you are doing here is probably the least complex way to do it.

It might be worth breaking your function up into another function that generates the SQL query and another one that runs it; then you could slap a few unit tests on it to ensure that the SQL text is generated properly for each scenario.

[houstonhaynes]

Thanks so much for weighing in! I was just wondering if I was missing something. FWIW I was anticipating these actors to be "where the sausage is made" and though I'll likely "module out" the SELECT and WHERE builders it'll still be a bit of a kludge. 🚧