When working with databases, it is always the question how to load the objects. Now I will give you some thoughts that are important in general and especially when working with nodeJS.

When loading objects of a kind by there id, it is obviouse and trivial to load all its fields. to reduce traffic and used memory it is usefull to use a projection. In SQL that means that you select the interested fields instat of loading the entire rows with *. But when selecting specific fields, you will need a new field selection for all methods where you load the same type of objects. So usually I am loading the whole row, because in development it is much cleaner and you have fiewer different query types. That can later be more easy to optimize using a caching layer.

It becomes more complex, when you need to load related objects. In Bookshelf or sequilize this feature is called neasting or load neasted objects. In other frameworks there are features called lazy loading. When Accessing the property to read the related object, behind the scene the framework will load that new object. These feature is more polular in Java or PHP.

Independent of the framwork or language, it is important to know, what data is geting loaded, to make the code work efficient and be reliable with providing the nessasary neasted objects. As a solution I now follow some naming convention. To get objects of some type, I will call a get-method. something like “var users = userDB.getByName(name)” to get one or more user with that name. This will only load the users direct properties/fields. To load the profilePictures to some user I will implement a fetchProfilePics on the userDB controller. This method will take a list of users, load there profilePictures, extend the users my the profilePics property that will be a list of pic-objects and return the pic-objects.

This process can be automated very easily and it will be clear what objects are loaded and it is good to controll, to load only nessasary objects.

updated

The rule of getting objects with by some information and then fetch reladed objects was very usefull and I saw that I would write a lot of code repeatedly for many types of objects. So I prepared to mysql a libreary, that let you give a schema for a class and it will provide you reasonable prepared get and fetch methods. you can try tmysqlpromisedao.