CHEGG BACKED DEV

Backend development reference

Data persistence uses PostgreSQL. Raw SQL builds fluency for interviews and debugging; Knex adds migrations, seeds, and a query builder your Express services call. Learn both: SQL shows what the database actually runs; Knex shows how the app stays maintainable.

Knex queries

Knex query builder methods mirror SQL CRUD: select rows, insert with returning, update, delete, and aggregate. Use these from service functions—never from router files directly.

4 topic(s) on this page.

How to query with Knex — Basic

SQL / Knex Knex queries
Build SELECT queries with knex('table').where() and return rows to the caller.
Learn: Knex builds parameterized queries—safer than string concatenation. Chain where, orderBy, and first() for one row. Await the query or return the promise to the caller.

Supplementary examples

List and read one

function list() {
  return knex("pastes").select("paste_id", "name").orderBy("created_at", "desc");
}

function read(id) {
  return knex("pastes").where({ paste_id: id }).first();
}

Course example

function listTotalWeightByProduct() {
return knex("products")
.select(
"product_sku",
"product_title",
knex.raw(
"sum(product_weight_in_lbs * product_quantity_in_stock) as total_weight_in_lbs"
)
)
.groupBy("product_title", "product_sku");
}

Additional references & examples

How to insert with Knex — Basic

SQL / Knex Knex queries
Insert one or more records using knex('table').insert() and optional returning().
Learn: insert() accepts an object or array for bulk inserts. Use returning('*') on PostgreSQL to get the inserted row back for your 201 response.

Supplementary examples

Insert with returning

function create(data) {
  return knex("pastes")
    .insert(data)
    .returning("*")
    .then((rows) => rows[0]);
}

Course example

const knex = require("../db/connection");
function create(supplier) {
return knex("suppliers")
.insert(supplier)
.returning("*")
.then((createdRecords) => createdRecords[0]);
}
module.exports = {
create,
};

Additional references & examples

How to update and delete with Knex — Basic

SQL / Knex Knex queries
Change or remove rows using update(), del(), or delete() with filters.
Learn: update(patch) and del() (or delete()) should always include where filters. Check the number of affected rows to distinguish 404 from success.

Supplementary examples

Update and delete by id

function update(id, patch) {
  return knex("pastes").where({ paste_id: id }).update(patch);
}

function remove(id) {
  return knex("pastes").where({ paste_id: id }).del();
}

Course example

function read(supplier_id) {
return knex("suppliers").select("*").where({ supplier_id }).first();
}
function update(updatedSupplier) {
return knex("suppliers")
.select("*")
.where({ supplier_id: updatedSupplier.supplier_id })
.update(updatedSupplier, "*");
}

Additional references & examples

How to aggregate data with Knex — Intermediate

SQL / Knex Knex queries
Count, group, or summarize rows using query builder aggregates and JavaScript post-processing.
Learn: Aggregates answer summary questions: how many rows, totals, averages. Use count(), sum(), or raw selects with GROUP BY; post-process in JavaScript when shaping nested API responses.

Supplementary examples

Count pastes per user

return knex("pastes")
  .select("user_id")
  .count("* as paste_count")
  .groupBy("user_id");

Course example

function listOutOfStockCount() {
return knex("products")
.select("product_quantity_in_stock as out_of_stock")
.count("product_id")
.where({ product_quantity_in_stock: 0 })
.groupBy("out_of_stock");
}

Additional references & examples