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 obvious and trivial to load all its fields. to reduce traffic and used memory it is useful to use a projection. In SQL that means that you select the interested fields instead 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 fewer 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 nesting or load nested 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 popular in Java or PHP.

Independent of the framework or language, it is important to know, what data is getting loaded, to make the code work efficient and be reliable with providing the necessary nested 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 control, to load only necessary objects.

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