Insert with defaults: nextval doesn't handle newtype-wrapped Id

[ulidtko]

Hi! 👋 Gr8 lib 😂

The facilities around column defaulting though... come across a bit underwhelming.

For sake of example, suppose a table like so:

newtype ClientId = ClientId Int64
  deriving newtype Show
  deriving newtype (DBType, DBEq)

-- | HKD model of an entry in the @client@ table.
data EntryClient f = EntryClient
  { clientId  :: Column f ClientId
  , name      :: Column f Text
  , domain    :: Column f Text
  , last_seen :: Column f UTCTime
  }
  deriving stock Generic
  deriving anyclass Rel8able

deriving instance Show (EntryClient Result)

clientTable :: TableSchema (EntryClient Name)
clientTable = TableSchema
  { name = "client"
  , columns = namesFromLabels { clientId = "id" }
  }

Pretty bland & by-the-book, right?..

But then, it seems Insert can't be written without unsafeDefault:

upsertClient :: Text -> Text -> Insert (Query (Expr ClientId))
upsertClient (litExpr -> name') (litExpr -> domain')
  = Insert
    { into = clientTable
    , rows = values
      [ EntryClient
        { name = name'
        , domain = domain'
        , clientId = unsafeDefault
        --, clientId = nextval "client_id_seq" <&> ClientId -- nope! Expr isn't Functor
        , last_seen = Rel8.Expr.Time.now
        }
      ]
    -- ...

Ideally, as a user I'd love to skip writing out columns with DEFAULT altogether, in the spirit of raw SQL:

  INSERT INTO client (name, domain) VALUES (%s, %s)
  ON CONFLICT (name, domain) DO UPDATE SET last_seen=NOW()
  RETURNING id

— but it seems this may require something like #216.

Or am I missing a more advanced use of values ?

[ulidtko]

Ohhh... wow OK, interesting. TableSchema is a Functor! 💡

Which means I can indeed narrow down the existing 4-column schema, by projecting out just the 2 columns of interest:

upsertClient' :: Text -> Text -> Insert ()
upsertClient' (lit -> name') (lit -> domain')
  = Insert
    { into = proj <$> clientTable
    , rows = values [(name', domain')]
    , onConflict = DoNothing
    , returning = NoReturning
    }
  where
    proj EntryClient{..} = (name, domain)

So that's a way to reduce rows values to just (name, domain) pairs that I wanted, omitting DEFAULT columns 🤔

Unfortunately, doing this precludes both the IODKU & RETURNING clauses (because last_seen & id columns are no longer "in scope"). Thus if I need them, I must abandon the projection & say clientId = unsafeDefault.

---

@ocharles do you think it's a good idea to extend the Selects names exprs existential on Insert constructor to also contain a projection allowing to skip defaulted columns in rows Query only?.. I mean, maybe this:

data Insert a where
  Insert :: (Selects names exprs, Projecting exprs exprsNonDefault) =>
    { into :: TableSchema names
    , rows :: Query exprsNonDefault
    , onConflict :: OnConflict names
    , returning :: Returning names a
    }
    -> Insert a

Because conceivably, the common use-case for RETURNING asks to fetch a defaulted column (id, timestamp) immediately as part of a data-writing query... so, that column must be in names and therefore in exprs. However, by allowing exprsNonDefault to be "smaller than" exprs, the API might elegantly allow omitting those nextval and unsafeDefault calls.

[JackKelly-Bellroy]

Semi-related: #373 might be an answer for newtype-wrapped ID columns, but I'd want help to convince myself that it's safe.