Writing multiline sql queries in javascript using knex

I have been using knex lately to interface with a postgres database. Knex provides you with a number of ways to write queries in a shorhand form. For exammple, if you want to select a user from the users table with an id of 1, you can simply write something like the following

knex('users').where({
  id: userId
});

This is extremely useful for a majority of cases. However an application of any size is bound to run into a situation where one just needs to write a raw query to extract data. One such situation is when you have large queries that involve multiple tables. Writing such queries in the 'knex' way of doing things is an overhead mainly since you would anyways need to write the query in raw sql first and then have to translate it into the knex syntax. To speed up development, knex lets you directly use a raw sql query. For example,

knex.raw('select * from users, orders on users.id = orders.user_id');

var query = multiline.stripIndent(function () {/*
  select
    t.id, t.tag_text, count(t.id)
    from user_links ul,
    user_link_tags ult,
    tags t
    where ul.content_link_id = ?
    and ult.user_link_id = ul.id
    and t.id = ult.tag_id
    group by t.id,t.tag_text
  */});

query = query.replace(/\n/g, '').replace(/\t/g, ' ');

return knex.raw(query, [+contentLinkId])

For example, consider a schema where a user can place orders and each order can have multiple items. Each such item also has a name. Now, in order to print the names of all the items ordered by a user, along with the id's of the other tables, you'd probably write a query like the following.

select users.id, orders.id, items.id, item.name
    from users,
    orders,
    items
    where users.id = 1
    and order.user_id = users.id
    and items.order_id = order.id;

This works nicely in an sql editor. However, if you were to directly use this query in javascript, you would have to combine them in a single line. Mainly because its one string and splitting a string on multiple lines in javascript is neither fun nor productive. So, your query would look like this

knex("select users.id, orders.id, items.id, item.name from users, orders, items where users.id = 1 and order.user_id = users.id and items.order_id = order.id");

Now that looks HORRIBLE. There is one important rule to keep in mind when writing code.

Code that cannot be easily read should not be written.

The reason is simple. We are all human beings, not machines. Well, I had to find a better way to do this. Luckily I stumbled across an npm package called multiline that really did the job for me. Here's what you'd do.

npm install multiline --save

The next part is pretty simple.

var query = multiline.stripIndent(function () {/*
  select users.id, orders.id, items.id, item.name
      from users,
      orders,
      items
      where users.id = ?
      and order.user_id = users.id
      and items.order_id = order.id
*/});

As you can tell, the comment that is specified as the function body is what multiline converts into a proper string. Also note that I used a placeholder ? in the query so that I can pass the user id as an argument to the query. We will use this later. The only problem is that now you have all these extra newline characters and tab characters in your query string as a side effect of having a query on multiple lines. Thats much easier to handle.

query = query.replace(/\n/g, '').replace(/\t/g, ' ');

I replace the newline characters with an empty string, effectively taking them out of the query. Then, I replace all the tab characters with spaces just so that the words dont stick together. Finally, the query can be run in knex as follows:

var userId = 1;
return knex.raw(query, [userId])
  .then(function (response) {
    return resp.rows;
  });

Let me know in the comments what you think about this approach, of if you've stumbled across a better way to do the same.

knex 1