Update to array records with new v3 `setColumn` query

[Evelios]

I'm looking to add a new value to a column value of uuid[] and I'm running into scoping issues. I'm not able to get the current values of that column when trying to add a new value to it. This stems from the add to LINQ style column update from #40. I'd be happy to update the documentation like discussed in #50 and this use case as well when this is figured out. I wasn't really able to find any complex updates from other user searching the internet with the new syntax.

CREATE TABLE Users (
    id uuid NOT NULL PRIMARY KEY,
    items uuid[] NOT NULL DEFUALT ARRAY[]::uuid[] )

type User = 
    { Id: Guid
      Items: Guid list }

let userTable = table<User>

let addItem (conn: IDbConnection) (userId: Guid) (itemId: Guid) =
    update {
        for u in userTable do
            setColumn u.Items (List.Cons(itemId, u.Items)
            where (id = userId)
    }

error FS0039: The value or constructor 'u' is not defined

The equivalent SQL command I'm trying to emulate is

UPDATE users
SET items = array_append(items, @item)
WHERE id = @id

[JordanMarr]

Are you saying this is something you could do with the old anonymous record updates that you can't do with the new setColumn feature? If so, how would you have done it before?

[Evelios]

I haven't done it with the old version. I just started to pick up this library recently using v3. If this wasn't a feature that was supported before then I guess this isn't actually an issue but a new use case

[JordanMarr]

Yes, it sounds like this will involve some custom handling for PostgreSQL array types. Do you know what this would look like via Dapper or vanilla ADO.NET? That would be a good place to start.

[Dzoukr]

I tried to simulate this locally and had no success. I think this is the case where you should fallback to a good old custom query. 😃

[Evelios]

Sorry, I have no experience with Dapper on it's own or with ADO.NET. I'm just learning databases for the first time. I'm alright just writing vanilla SQL for this. You can close this issue if this isn't something you want to pursue, thanks.

[JordanMarr]

I think it might be as easy as this:

open Dapper

let addItem (conn: IDbConnection) (user: User) (item: Guid) =
    conn.Execute(
        """
        UPDATE users
        SET items = array_append(@items, @item)
        WHERE id = @id")
        """, {| items = user.Items; item = item; id = user.Id |})

open Dapper is key here because Dapper adds its own extension methods (like Execute) on top of the connection.