How to join tables with Knex — Intermediate
Use join() or related query builder methods to combine tables in one query.
Learn: Knex join methods mirror SQL JOINs. Select columns from both tables or use aliases when names collide. Prefer joins in the service layer, not in controllers.
Supplementary examples
innerJoin with selected columns
return knex("pastes")
.innerJoin("users", "pastes.user_id", "users.user_id")
.select(
"pastes.paste_id",
"pastes.name",
"users.username"
);Course example
// At the top of the file:
const mapProperties = require("../utils/map-properties");
const addCategory = mapProperties({
category_id: "category.category_id",
category_name: "category.category_name",
category_description: "category.category_description",
});
// Then modify the `read()` function
function read(product_id) {
return knex("products as p")
.join("products_categories as pc", "p.product_id", "pc.product_id")
.join("categories as c", "pc.category_id", "c.category_id")
.select("p.*", "c.*")
.where({ "p.product_id": product_id })
.first()
.then(addCategory);
}Additional references & examples
- Knex.js documentation
Query builder, migrations, seeds, and connection configuration.
- Knex join methods
innerJoin, leftJoin, and related APIs.