knex-populate

This tool/plugin/add-on allows you to populate foreign key fields in the JSON response. You can get the same information from a JOIN, however, the amount of code that it takes to put that together could be large, depending on the tables involved any any aliases you want to add. This is an alternative method of doing that for "cleaner" response objects. Mimics the structure of responses from Mongoose/MongoDB and SailsJS/Waterline.

Example:

Without populate:

Post:
{
  id: 1,
  post_title: 'some clever title',
  post_body: 'some interesting content'
}

Comment:
{
  id: 1,
  comment_text: 'hello world',
  post_id: 1
},
{
  id: 2,
  comment_text: 'hello world again',
  post_id: 1
},
{
  id: 2,
  comment_text: 'hello world yet again',
  post_id: 2
}

With populate:

{
  id: 1,
  post_title: 'some clever title',
  post_body: 'some interesting content',
  comments: [{
      id: 1,
      text: 'hello world',
      post_id: 1
    },
    {
      id: 2,
      text: 'hello world again',
      post_id: 1
    }]
}

Getting Started:

npm install --save knex
npm install --save knex-populate

# Then add one of the following (adding a --save) flag:
$ npm install pg
$ npm install sqlite3
$ npm install mysql
$ npm install mysql2
$ npm install mariasql
$ npm install strong-oracle
$ npm install oracle
$ npm install mssql

Definition

knex_populate(knex_instance, main_table)
  .find(object_with_query_params(if any))
  .populate(referenced_table, foreign_key, [ alias ])
  .exec()
  .then(results => res.send(results));

NOTE: You can chain more than one of the populate methods together if you have more than one foreign key that you want to populate!

Usage (in Express):

var knex = require('../db/knex');
var knex_populate = require('knex-populate');
...
router.get('/api/posts', function(req, res, next) {
  knex_populate(knex, 'posts')
    .find()
    .populate('comments', 'post_id', 'comments')
    .limitTo(5)
    .orderBy('post_title', 'asc')
    .exec()
    .then(results => res.send(results));
});

Methods:

| Name | Usage | Required/Optional | Description | Example | |--- |--- |--- |--- |--- | | knex_populate | knex_populate(knex_instance, main_table) | Required | Main method | knex_populate(knex, 'posts') | | find | find(criteriaObj) | Required - first chained method to knex_populate | Takes an argument of an object containing any search criteria. Either this or findById is required | find({name: "David"}) | | findById | findById(id) | Required - first chained method | Queries by a single ID. Either this or find is required | .findById(2) | | limitTo | limitTo(number) | Optional | Takes a number as an argument. Will limit the results to that number of results. | .limitTo(2) | | orderBy | orderBy(columnName, order) | Optional | Takes two arguments. First argument is the column to be ordered by and the second argument is either "asc" or "desc". | .orderBy('post_title', 'asc') | | populate | populate(referenced_table, foreign_key, [ alias ]) | Optional | Takes three arguments. First argument is the table to be referenced regarding the foreign key. Second argument is the foreign key in the referenced table. Third argument is optional, but if you want the populated foreign key to be called something specific, you can do that there. | .populate('comments', 'post_id', 'comments') | | exec | exec() | Required - last chained method | Takes no arguments, but is required to execute the query. | .exec() |