Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Share On Stumbleupon
Contact us
Hide Buttons

Writing multiline sql queries in javascript using knex

I have been using knex lately to inter­face with a post­gres data­base. Knex pro­vides you with a num­ber of ways to write queries in a shor­hand form. For examm­ple, if you want to select a user from the users table with an id of 1, you can sim­ply write some­thing like the following

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

This is extremely use­ful for a major­ity of cases. How­ever an appli­ca­tion of any size is bound to run into a sit­u­a­tion where one just needs to write a raw query to extract data. One such sit­u­a­tion is when you have large queries that involve mul­ti­ple tables. Writ­ing such queries in the ‘knex’ way of doing things is an over­head mainly since you would any­ways need to write the query in raw sql first and then have to trans­late it into the knex syntax.

To speed up devel­op­ment, 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 exam­ple, con­sider a schema where a user can place orders and each order can have mul­ti­ple 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 prob­a­bly 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 edi­tor. How­ever, if you were to directly use this query in javascript, you would have to com­bine them in a sin­gle line. Mainly because its one string and split­ting a string on mul­ti­ple lines in javascript is nei­ther 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 impor­tant rule to keep in mind when writ­ing code.

Code that can­not be eas­ily read should not be written.

The rea­son is sim­ple. We are all human beings, not machines.

Well, I had to find a bet­ter way to do this. Luck­ily I stum­bled across an npm pack­age called mul­ti­line 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 com­ment that is spec­i­fied as the func­tion body is what mul­ti­line con­verts into a proper string.
Also note that I used a place­holder ? in the query so that I can pass the user id as an argu­ment to the query. We will use this later.

The only prob­lem is that now you have all these extra new­line char­ac­ters and tab char­ac­ters in your query string as a side effect of hav­ing a query on mul­ti­ple lines. Thats much eas­ier to handle.

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

I replace the new­line char­ac­ters with an empty string, effec­tively tak­ing them out of the query. Then, I replace all the tab char­ac­ters 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 com­ments what you think about this approach, of if you’ve stum­bled across a bet­ter way to do the same.


Ryan Sukale

Ryan is a UX engineer living in San Francisco, California.