Web Service and Data Formats

Hi guys,

I’m about to create my first web service, and I need validation on how data is managed between the UI that is requesting a particular JSON format, and a relational database which organise the data differently.

To illustrate a little bit, I’m gonna take a simple example of an overly simplified database for an Hotel:

Screenshot 2022-04-20 at 18.18.54

An hotel has some fields, it has rooms and pictures, nothing fancy here.

To unmarshall the SQL response from the Hotel DB, I would create these simple structs…

type Hotel struct {
  HotelId string `json:"hotel_id"`
  Name    string `json:"name"`
  Address string `json:"address"`
  NbRooms int32 `json:"nb_rooms"`
  Pictures []Picture `json:"pictures"`
  Rooms []Room `json:"hotel_rooms"`

type Picture struct {
  Url    string `json:"url"`
  Order  string `json:"order"`
  Description string `json:"description"`

type Room struct {
  RoomNb    string `json:"room_nb"`
  Capacity  string `json:"name"`
  Surface   string `json:"surface"`
  Price    float32 `json:"price"`

… On the other hand, when the UI is performing requests to populate its views, it just wants to only retrieve the necessary data, and in a format that may not always match the structs above.

For example, we can imagine that the UI would want to get the available Hotels (depending on dates, prices etc…) but only some specific information like the name, the address, the number of rooms that’s it. For some reason, the UI doesn’t want to get images of any kind or any more information about an Hotel.

    "hotel_id": "0001",
    "name": "The Nice Hotel",
    "address": "...",
    "nb_rooms": 4,
    "hotel_id": "0001",
    "name": "The Nice Hotel",
    "address": "..." "nb_rooms": 4,

In order to satisfy the UI need, the Hotel API would need other type of structs that would match the UI data requirement:

var hotelList = []HotelListItem{}

type HotelListItem struct {
  HotelId string `json:"hotel_id"`
  Name string `json:"name"`
  Address string `json:"address"`
  NbRooms int32 `json:"nb_rooms"`

This means that the model would be in charge of transforming the struct result of the SELECT request sent to the database, into a struct that would fit the response the UI is waiting for. In other words, the “domain” part of the Book service would not only contain the CRUD methods, but also transformation methods to do this job.

What do you think about these assumptions?

We can imagine that the number of “transform” functions may grow and take a lot of place because of the UI is building hundreds of different views which would make the DTO bigger and bigger … Even if we could imagine several view using the same JSON response format, is there any other strategy to be implemented in this kind of situation?

Here’s a link to a diagram to illustrate a little bit:

If you can advise lectures on the subject, I’ll take them.

Many thanks for your help :slight_smile:


There are techniques that reduces the need for structs and views. Have a look at SQL driver sqlx that not need any structs. There are ways to reduce endpoints in the API. I have created some tests that maybe can give you at least another view. And using AXAJ may reduce flickering if there are a lot of records.

I’ve used a similar approach to what you are describing with XListItem type structs. Here’s where I usually land with this: for simple CRUD type operations like retrieving a single Hotel and its’ associated data, use models directly and just generate the SQL (either in your project or using Gorm or SQLX). For anything even relatively advanced, I usually want to hand-code the SQL anyway. So in your example I would just generate SQL for Hotel, Picture and Room. Using a Gorm type example:

func GetHotel(hotelID string) Hotel {
    var hotel Hotel 
    db.First(&hotel, 1)
    return hotel

But, for HotelListItem I would probably want to write a custom query and scan that directly in to a slice of HotelListItem structs. Also using a Gorm type example:

// GetHotelList gets hotel list items based on criteria. Borrowing from other 
// languages, I've sometimes thought of these list items as a ViewModel if I 
// want to think about them in a more specific UI context. Like `HotelSearchItem`
// and `HotelDetailView` or something along those lines.
func GetHotelList(priceMax int64, capacityMin int32) []HotelListItem {
	result := hotelList = []HotelListItem{}
    h.id as hotel_id,
from hotel h
    join room r on r.hotel_id = h.id
where r.price <= ?
    and r.capacity >= ?`, priceMax, capacityMin).Scan(&result)
	return result


  • Because I generally don’t trust ORMs with too many joins or anything too complicated (not that this is in any way complicated, but usually my queries are more complicated than this). Gorm generates more sane SQL than any other ORM I’ve used, but often times with complicated queries you’re going to be fighting your ORM.
  • Because it makes it easy to hand-tune my non-CRUD queries. This doesn’t matter much in a small app but it matters a lot in large production apps (in my experience at least; your results may vary!).
  • There’s zero magic here and it’s really obvious to any future developer what is going on and how to tweak it. If I wanted to, I could copy/paste that SQL directly into an editor and run it.

Other options include:

  • Create database views / stored procedures for everything and have models that mirror them. You could generate them using volatiletech/sqlboiler.
  • You could also look in to a go GraphQL implementation. I have less experience with this.

Hi @Dean_Davidson,

Thank you for sharing your experience. To give a little more detail, I’m using pgx actually.
From my perspective, the 3 types of structs in this API:

  1. the request structs that receives the data from the JSON payload (unmarshal)
  2. the response structs that receives the SQL responses
  3. Optionally … the structs used to generate the JSON responses to the client. This point was part of my interrogations because sometimes these structs could be one of those from point 2. but I’m not sure if it’s always the case. I don’t want to return all the hotel fields if there’s no need to cause we don’t want to drain the battery of our clients, right :slight_smile:

Just to illustrate a little bit more, there’s a lot of different incoming queries that the API should be able to handle. So, to speed up the implementation, POST and PATCH request contains dynamic definitions of what needs to be added or updated like:

POST /api/hotel
      value:"The Madison Hotel"
     value:"21 E 27th St NYC"

From here I unmarshal the JSON in the struct

type HotelReq struct{
   Fields []FieldValue
type FieldValue struct{
   Field string      `json:"field" binding:"required"`
   Value interface{} `json:"value" binding:"required"`

Then I use a bit of reflection to create an insert or an update string request that I submit to the database client.

INSERT INTO HOTEL(name, address) values ('The Madison Hotel', '21 E 27th St NYC')

same for the updates.

Now I need to work with the select part (and I suppose I need to add some checks like presence of the provided field and special characters … to avoid any SQL injections)(putting aside the auth server but that another story :D). There may be 4 or 5 cases for which the UI wants to retrieve an hotel but with different other info … thinking about it… I’m wondering it it wouldn’t a job to be done by the “service” part of the api (in a controller, service, domain kind-of architecture). for example, the hotel service could ask some date to the hotel domain and optionally (depending on the incoming http request) other info from other domains like a “rooms” domain, or another “restaurant” domain and be in charge of gathering all the data from these domains and create the http response along with the JSON payload. What do you think? From my perspective it should respect the SoC principle. Is-it something we do in real life api projects?

Many thanks.

“We can imagine that the number of “transform” functions may grow and take a lot of place because of the UI is building hundreds of different views which would make the DTO bigger and bigger … Even if we could imagine several view using the same JSON response format, is there any other strategy to be implemented in this kind of situation?”

Sounds like what you really want — but may not realize it — is to implement a GraphQL server.

GraphQL is designed to address pretty-much the exact problem you just mentioned where the client gets to decide what the output data looks like based on the GraphQL query the client provides.

This link I just googled might be able to help you if you are unfamiliar with GraphQL:

Hope this helps.

Yeah - depending on your front-end, I would generally lean towards making my API calls a little generic and the front-end framework can compose multiple calls to generate all the data needed for a given page. Google has a generic design guide with a few examples of when they deviate from standard methods for custom ones:

Not exactly comprehensive, but I think certain things you’ve mentioned might be addressed by this. Read through that API design guide if you want. It has a lot of good info and is platform-agnostic.

1 Like

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