Data type for has-many relationship in DB table types?

sql

#1

Has anyone else thought about how to describe the relational database model in PS types? It’s an ambiguous question, so I’ll describe the situation that I’ve come across. I think it’s an interesting one – a question that all programmers across languages share. Perhaps there’s a unique solution that some PureScripters have come up with, so I thought it would be a fun topic to raise.

-- Could use newtypes for these.
type Cart = { id :: UUID, ... }
type CartLineItem = { id :: UUID, cart_id :: UUID, ... }
sumCart :: Tuple Cart (Array CartLineItem) -> Number

Cart and CartLineItem each map to a database table. Has anyone here thought about how to ensure that the the CartLineItems in that Tuple are all the ones related to that Cart – all that Carts “children” and none others?

Option 1:

Seems to me the most straightforward way is to make a new nominal type to represent it, then have a smart constructor which promises to enforce it.

newtype CartAndLineItems = CartAndLineItems (Cart /\ CartLineItem)
mkCartAndLineItems :: SomePredicate -> Effect CartAndLineItems
mkCartAndLineItems = -- do SQL query

Presuming that mkCartAndLineItems is implemented such that it fills that data as we expect, we know that the relationship of the records in CartAndLineItems is as we expect.

Option 2:

Another technique would be to traverse that relationship using some category theory, but this might still require the result to have a unique nominal type for that specific relationship. I’ve only briefly heard some people talk about it (maybe Spivak?), but the idea is that a DB table is a CT object and a foreign key is a morphism. IIRC, the research was about describing a query language, rather than modelling with data types, but perhaps the query description and execution is the key to ensuring the data type’s “children” relationship property.

Following is my exploration of what this might look like.

-- objects
type Cart = Table "cart" ( id :: UUID, ... )
type CartLineItem = Table "cart_line_item" ( id :: UUID, cart_id :: FK UUID "cart", ... )
-- morphism
newtype WithChildren a b = WithChildren (Tuple a (Array b))
withChildren :: forall a b.
  HasRelationship a b =>
  SomeArgs -> WithChildren a b

-- Use type classes to asset `b` has a lookup relation to `a`.
-- Presume the following pseudo-code works. :) 
class HasRelationship a b where
instance hasRelationshipTables ::
  (RowToList a arl, RowToList b brl, HasRelationshipSchema aName bName arl brl) =>
  HasRelationship (Table aName a) (Table bName b) where
class HasRelationshipSchema (aName :: Symbol) (bName :: Symbol) (a :: RowList) (b :: RowList)
instance hasRelationshipSchemas ::
  (HasFK b bFK, IsFKTo aName bFK) =>
  HasRelationshipSchemas aName bName a b where

-- Maybe execute that as a query? I do not know.
queryEntityAndChildren :: forall a b.
  WithChildren a b -> WithChildrenResult a b
queryEntityAndChildren q = -- Convert to SQL and query...

-- Maybe use like this?
let x :: WithChildrenResult Cart CartLineItem
    x = queryEntityAndChildren (WithChildren Cart CartLineItem)

Option 3:

Maye dependent types is exactly what I’d be needing?
Or perhaps something more realistic for PureScript like LiquidHaskell?

Other options surely exist. :slight_smile: It would be fun to hear them.


#2

Phantom types?

-- The `cart` type parameter identifies the specific cart at the type-level.
-- Note that the type does not have to actually represent any ID - it can be always instantiated to Unit, for example, like ST does with its "thread" parameter.
newtype CartId cart = CartId UUID

type Cart cart = { id :: CartId cart, ... }
type LineItem cart = { id :: UUID, cart_id :: CartId cart, ... }

-- For fetching cart we need an existential to express that we don't know the cart ID ahead of time
fetchCart :: SomePredicate -> Exists Cart

-- We can fetch line items separately, and they will have the association to the cart mentioned in their type.
fetchLineItems ::  forall cart. Cart cart -> Array (LineItem cart)

-- Here the type signature enforces that we only pass in matching cart items
sumCart :: forall cart. Cart cart -> Array (LineItem cart) -> Number

It’s not clear though how to scale this to a larger number of parameters.