Do you put database IDs in your records?

Learn
1

I’m using Elm on the frontend and ASP.NET in F# on the backend. They work pretty well together, and it’s been quite nice to create the same data structures (i.e. records) in both languages that both support unions and records.

Our application lets you create and edit some data we call markups, and we have the same data structure:

type alias Markup = 
  { description : String
  , notes : String
  -- etc
  , id : Maybe Int
  }

and in F#

type Markup = 
  { Description: string
    Notes: string
    // etc
    id: int option }

My question is whether we should store the database ID of the markup inside the record, as we are today, or whether we should store it separately?

The current reason for the optionality is that we have a create API endpoint and an update API endpoint, and both use the same JSON payload, so when we create a markup, we don’t yet have an ID as it hasn’t been stored in the database, but when we update one we pass the ID of the markup to update, and the backend has to awkwardly unpack the id from the int option (in F#, you can “just do this” in a way that will fail, similar to Haskell’s fromJust, but it is nonetheless awkward.

Is there a best practice here?

Edit: added some clarifying language

2

Maybe the question needs more context to get a proper answer (from me at least). My takes are:

If it is an ID, it should not be optional.

If the ID is needed by the application logic, then yes, it must be part of the record.
Warning in that case, prefer ID that can not be guessed like UUID, rather than sequential integers.

3

String is often a good datatype for database ids, even if they are Ints. They could potentially be large 64-bit ints, which are not strictly allowed in Json. Making them Strings is a good way to indicate that this id is an opaque value which has no meaning.

2 Likes
4

Guessability is not a concern for us, in fact I’m okay surfacing it to users. Here’s what we’d like

https://www.our-domain.com/markups/new
https://www.our-domain.com/markups/1
https://www.our-domain.com/markups/2
etc.

The ID is just a database-generated one (serial in Postgres). So, when you create it, you get an ID back. And when you update it, you pass in an ID. Typical CRUD stuff.

The main question is whether you store that ID inside the record for the markup, or whether you store it alongside it. Maybe and option make it easy to handle the case where your ID isn’t there, but it’s awkward when you really “should” have it, for example, for an update.

@rupert Good note on data types! I’ll be sure to include that.

5

Why is the id optional? Is it that in /markups/new you have

myMarkup : Markup
myMarkup = 
  { description = "some text"
  , notes = "other text"
  , id = Nothing
  }

and then in /markups/55 you have

myMarkup : Markup
myMarkup = 
  { description = "some text"
  , notes = "other text"
  , id = Just 55
  }
1 Like
6

Correct, it’s an entity that the user has drafted locally but has not yet saved.

7

Ah! Then I would have

type alias Markup = 
  { description : String
  , notes : String
  -- etc
  , id : Int
  }


type alias MarkupDraft = 
  { description : String
  , notes : String
  -- etc
  }

because these are 2 distinct things. They look and are quite similar but they’re not the same.

3 Likes
8

Interesting - I hadn’t thought of a separate type entirely. I get the trap of “similar, but not the same”, but personally, it feels a little cumbersome to have an entirely separate type for something based on whether it’s been saved or not. Maybe this is a generalizable pattern? What do you think of this?

type alias DbEntity a
  { data : a
  , id : Int
  -- maybe more metadata
  }

type alias Markup = 
  { description : String
  , notes : String
  -- etc
  }

type alias Model =
  { markups : List DbEntity Markup
  , draft : Markup
  }

viewMarkup : Markup -> Html Msg

view model = 
  div [] (List.map (\entity -> viewMarkup entity.data) model.markups)

This sort of feels like Remote Data but for database stuff.

9

The comparison to Remote Data is interesting! I haven’t tried that approach so I can’t say for certain, but it seems reasonable. I have gone the route of 2 different but similar data structures and that does tend to work.

I think generally with Elm I find I care a lot less about writing more code because it’s all so easy to refactor that the quantity doesn’t really get in the way. But I would be curious to hear if you like this Remote Data inspired approach. I could definitely see it working.

I would also be curious if it works with something like

type Id a = Id Int
type alias DbEntity a
  { data : a
  , id : Id a
  -- maybe more metadata
  }

type alias Markup = 
  { description : String
  , notes : String
  }

such that you don’t accidentally send a Id Markup when you meant to send an Id User or vice versa.

1 Like
10

I tend to avoid having ids in entity records because I often have collections of entities and I don’t want the entity record to have a redundant (and possibly inconsistent) copy of the dictionary key.

Another option option in this vein, though, is something like

type alias Markup = 
  { description : String
  , notes : String
  }


type alias Identified id entity = 
  { entity
  | id : id
  }
  
dbMarkup : Identified Int Markup
dbMarkup =
  { id = 3
  , description = "foo"
  , notes = "bar"
  }
2 Likes
11

Just wanted to make a clarifying comment. Even though IDs are appearing in the URL, and you are correct, in this case, these IDs can get out in the wild, the issue with guessable sequences is it creates an attack vector for brute force attacks to cycle through IDs that are outside of a user’s data domain.

So the general recommendation is, unless you need the IDs to be sequential, it is best to use cryptographically secure IDs. :slight_smile:

12

Non-sequential IDs are also recommended for performance reasons in case your application ever starts to scale past the limits of a single machine.

1 Like