Keeping code modular when accessing a database

Question

I've been working on the database for a very basic social networking site: all you can do is register for an account and send/accept friend requests. The server is written in Javascript (NodeJS)

I have a method getUser(username) that accesses the database to get the JSON object representing a particular user given their username, and a method to list the friends of a particular user. From the listFriends(username) function, I want to return an array of the user objects rather than just their usernames and ideally I would like to utilize my own get() function rather than altering the SQL query that listFriends() uses

It might be easier to understand with an example. If I have three tables:

TABLE: UsersName
    username (unique) | firstName | lastName   |
    ------------------|-----------|------------|
    pjfry             | Phillip   | Fry        |
    proff             | Professor | Farnsworth |
    bender            | Bender    | Rodriguez  |

TABLE: UsersProfile (their profile description)
    username (unique) | description            |
    ------------------|------------------------|
    pjfry             | I went to the future   |
    proff             | I am very old          |
    bender            | Destroy all humans     |

TABLE: Friendships (for simplicity assume that if (a,b) is an entry then so is (b,a))
    user1      | user2
    -----------|---------------
    bender     | pjfry
    pjfry      | bender
    pjfry      | proff
    proff      | pjfry

And a function to get the user object:

//the callback accepts the user object
function get (username, callback) {
    db.query(
        'select * from (UsersName n, UsersProfile p) where n.username=p.username and n.username=\'' + username + '\'',
        function (rows) {
            //for simplicity assume that this call always succeeds
            //the user object is altered a bit:
            callback({
                username: rows[0].username,
                name: {
                    first: rows[0].firstName,
                    last: rows[0].lastName,
                    full: rows[0].firstName + ' ' + rows[0].lastName
                },
                profile: {
                    description: rows[0].description
                }
            });
        }
}

And here is the function to list the friends of a given user

//callback accepts the array of friends
function listFriends(username, callback) {
    db.query(
        'select user2 from Friendships where user1=\'' + username + '\'',
        function(rows) {
            //assume this call always succeeds
            callback(rows)
        }
    )
}

The problem here is that listFriends() will just return the array of usernames rather than user objects. How could I modify the listFriends() function so it returns the user objects by utilizing the get() function?

It could be done by modifying the SQL statement in listFriends() but it would be much cleaner to use the get() method so that if the structure of the user object is ever changed, it only needs to be changed in one place.

Answer 1

Trying to keep things as DRY as possible, something like this should fit your requirements:

// Disclaimer: I have not run tested this code myself

//the callback accepts the user object
function get(username, callback) {
  getFriendsWithConditions('n.username=\'' + username + '\'', function(rows){
    // returns only a single object to the input callback
    callback(rows[0]);
  });
}

//callback accepts the array of friends
function listFriends(username, callback) {    
  getFriendsWithConditions('n.username in (select user2 from Friendships where user1=\'' + username + '\')', callback);
}

function getFriendsWithConditions(whereCondition, callback) {
  db.query(
    'select * from (UsersName n, UsersProfile p) where n.username=p.username and (' + whereCondition + ')',
    function(rows) {
      // Using ECMAScript 5 Array.map
      callback(rows.map(rowToUserObject));
    }
  );
}

function rowToUserObject(row)
{
  return {
    username: row.username,
    name: {
      first: row.firstName,
      last: row.lastName,
      full: row.firstName + ' ' + row.lastName
    },
    profile: {
      description: row.description
    }
  };
}

A few things that came to mind as I was writing this:

1. UserName and UserProfile feel like they should only be one table, but for all I know this is a simplified version of your actual database, so I left them as is.

2. I used Array.map as defined in ECMAScript 5 which should be available in Node.

3. The SQL alias names for each table are hardcoded throughout each function, meaning that you would also need to a) set some sort of constant or come of with a convention to keep all aliases consistent throughout all your functions (which can be a pain to maintain as the project grows) or b) look into using an ORM that can do these things for you (and more!) This will help you avoid sweating the details of how to construct queries, leaving you to worry about more important things, like what data you actually need. I'm not terribly familiar with what's available for NodeJS in terms of ORMs, but the official NodeJS wiki should be a good place start.