How would you store big.Rat in a database?

I’m working on a recipe app, and I decided big.Rat is far easier to use as an ingredient quantity than floats (what I used before) because I get all the fraction display and methods of representation for “free.” This all works great… until I try to store it in the database (mysql.) Right now I’m using [gorm][1] and it can’t handle this automatically, which is fine I can use the native sql driver or just write raw queries, but I’m not sure how to store this in the database:

type Ingredient struct {
ID                int
RecipeID          int
Qty               big.Rat
Uom               string
Name, Description string
Preparation       string

}

because [big.Rat][2] is actually a struct:

type Rat struct {
    // To make zero values for Rat work w/o initialization,
    // a zero value of b (len(b) == 0) acts like b == 1.
    // a.neg determines the sign of the Rat, b.neg is ignored.
    a, b Int
}

so I suppose I could store it as a struct (it’s own table with a foreign key) or I could just store both integers side by side in the table, or I could store it as a string and scan it in… but any of these solutions means a lot of extra code… basically re-writing gorm and gorilla schema to handle this case. Does anyone have a better suggestion for me?

Thank you!
[1]: https://github.com/jinzhu/gorm
[2]: https://golang.org/src/math/big/rat.go?s=370:570#L6

I’d probably go with that, if you want to use the big.Rat (that’s a rather funny type name by the way, in the context of rodent mascots and stuff). The big.* types are somewhat unwieldy in that they are arbitrarily large. The other alternative would perhaps be to store the Bytes() of a and b as binary blobs but that’s rather unfriendly if you ever want to migrate this database to something else.

Another option (which I would probably go with for a recipe system) is to just store floats, or a pair of regular ints, and do appropriate rounding/“fractionizing” in the presentation layer…

Thank you for the advice, I think I’ll do your last suggestion, store them as floats and present them as fractions. Bummer, the big.Rat package handles everything the way I’d want without me having to write any extra code. OK! I’m off to search github for a javascript float -> fraction library :smile:

If you’re using sqlx, you could possibly write custom marshaling/unmarshalling methods on a type Currency big.Rat.

The marshaling method could just strconv each int within your big.Rat into a string and with a colon separator, and unmarshalling just the reverse. Would still be fast (especially in comparison with the DB connection) and is simple enough.

1 Like

Consider that if you decide to store it as bytes or some kind of string in the DB then you lose all ability to actually run queries on the data. If you’re using it just as a backing store that might be OK.

But how hard would it be to store as float, and then write a minimal marshaler that converts between that and big.Rat?

I’d have it like this:

type Ingredient struct {
ID                int
RecipeID          int
Qty               string
Uom               string
Name, Description string
Preparation       string
```

and store that in a DB native decimal field. When doing calculations I'd use:
`qty, _ := new(big.Rat).SetString(ing.Qty)`
 it adds a bit more boilerplate but when it comes to flexibility and accuracy this approach is IMO the best.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.