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.

SQL / Knex

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.

Select a function type from the sidebar, or browse topics below.

Knex joins (1)Knex migrations (1)Knex queries (4)Migrations / seeds (2)Raw SQL (5)

How to create tables with SQL — Basic

SQL / Knex Raw SQL
Define schemas with CREATE TABLE for columns, types, and constraints.
Learn: Tables define your domain model in PostgreSQL. Choose types deliberately (TEXT vs VARCHAR, TIMESTAMPTZ for times). Primary keys and foreign keys document relationships and enable referential integrity.

Supplementary examples

Users and pastes tables

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username TEXT NOT NULL UNIQUE
);

CREATE TABLE pastes (
  paste_id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(user_id),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Course example

-- First, remove the table if it exists
DROP TABLE IF EXISTS customers;
-- Create the table anew
CREATE TABLE customers (
customer_id INTEGER primary key generated by default as identity,
customer_name varchar NOT NULL,
phone VARCHAR(30)
);
-- Insert some test data
-- using a multi-row insert statement here
INSERT INTO customers (customer_name, phone)
VALUES
('Sam', '555-1234'),
('Ham', '555-4321'),
('Ram', '555-5678');

Additional references & examples

How to insert data with SQL — Basic

SQL / Knex Raw SQL
Add rows with INSERT INTO for seeds, fixtures, or manual data setup.
Learn: INSERT adds rows; RETURNING is PostgreSQL’s way to get generated IDs and columns back in one round trip—handy for APIs that respond with the created record.

Supplementary examples

Insert with RETURNING

INSERT INTO pastes (user_id, name, tags)
VALUES (1, 'My paste', ARRAY['node', 'sql'])
RETURNING *;

Course example

TRUNCATE
employees,
departments,
projects,
employees_projects,
customers,
orders
RESTART IDENTITY;
-- Insert some data into the employees table
INSERT INTO employees
(first_name, last_name, phone, title, salary, hire_date)
VALUES
('Jane', 'Doe','5551234', 'CEO', 80000, '12/07/1999'),
('Wright', 'Palmer', '5554321', 'Manager', 60000, '01/06/2001'),
('Jim', 'Doe', '5555678', 'Accountant', 50000, '11/06/2015'),
('Toby', 'Bestley', '5558765', 'Associate', 35000, '09/07/2008') ,
('Meredith', 'Hartford', '5559876', 'Associate', 30000, '02/17/2014'),
('Tom', 'Flenderson', '5558769', 'Associate', 32000, '11/23/2013'),
('Bently', 'Singh', '5554326', 'Manager', 60000, '06/06/2005'),
('Winnie', 'Lim', '5554527', 'Manager', 62000, '10/24/2003'),
('Ruda', 'Bross', '5554428', 'Manager', 66000, '11/06/2004');
-- Insert four projects into the projects table
INSERT INTO projects
(project_name, budget, start_date)
VALUES
('Build Database', 20000, '3/4/2020'),
('Plan christmas party', 500, '11/20/2020'),
('Remove old stock', 1000, '4/6/2020'),
('Watch paint dry', 3000, '2/11/2020');
-- Insert some customers into the customers table
INSERT INTO customers
(first_name, last_name, phone, email, street, city, zip_code)
VALUES
('Kacie', 'Mckee','5555234', 'kacie.mckee@gmail.com', '61 Shadow Brook Court', 'Melrose, MA', '02176'),
('Moses', 'Mcghee', '5554651', 'moses.mcghee@gmail.com', '570 Old York St.', 'Upland, CA', '91784'),
('Jerome', 'Aguilar', '5555699', 'jerome.aguilar@yahoo.com', '68 Victoria Road', 'Hoboken, NJ', '07030'),
('Ainsley', 'Bonner', '5558564', 'ainsley.bonner@hotmail.com', '60 Winchester Road', 'Dundalk, MD', '21222') ,
('Delilah', 'Bateman', '5523124', 'delilah.bateman@gmail.com', '482 E. Oxford St.', 'Thibodaux, LA', '70301');

Additional references & examples

How to query data with SQL — Basic

SQL / Knex Raw SQL
Retrieve rows using SELECT with WHERE, ordering, and limits as needed.
Learn: SELECT is the foundation of read APIs. Filter with WHERE, sort with ORDER BY, and paginate with LIMIT/OFFSET. Select only columns you need to keep responses small.

Supplementary examples

Filtered, ordered query

SELECT paste_id, name, created_at
FROM pastes
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 10;

Course example

{
"data": {
"product_id": 1,
"product_sku": "XLH4P7t3er",
"product_title": "Vanilla Scented Candle",
"product_description": "Vanilla-scented candle, perfect for your living room.",
"product_price": "48.25",
"product_quantity_in_stock": 0,
"product_weight_in_lbs": "2.30",
"supplier_id": 1,
"created_at": "2020-12-01T20:37:09.550Z",
"updated_at": "2020-12-01T20:37:09.550Z",
"category_id": 2,
"category_name": "candles",
"category_description": "A category for gift candles, including both scented and non-scented candles, for the home."
}
}

Additional references & examples

How to update and delete with SQL — Basic

SQL / Knex Raw SQL
Modify or remove rows using UPDATE and DELETE with appropriate WHERE clauses.
Learn: Always include WHERE on UPDATE and DELETE unless you intend to touch every row. Check `rowCount` or RETURNING to confirm something actually changed—return 404 when no row matched.

Supplementary examples

Update and delete by id

UPDATE pastes SET name = 'Renamed' WHERE paste_id = 3;
DELETE FROM pastes WHERE paste_id = 3;

Course example

UPDATE employees
SET job_title = 'Chemical Engineer II', city = 'Lawrenceville'
WHERE job_title = 'Chemical Engineer' AND city = 'Trenton';

Additional references & examples

  • UPDATE

    Modify existing rows with WHERE clauses.

  • DELETE

    Remove rows safely with filters.

How to join tables with SQL — Intermediate

SQL / Knex Raw SQL
Combine related tables with JOIN to return denormalized result sets.
Learn: JOINs combine tables in one query. INNER JOIN keeps rows that match both sides; LEFT JOIN keeps all left rows even when the right side is missing—common for optional relationships.

Supplementary examples

Pastes with usernames

SELECT pastes.paste_id, pastes.name, users.username
FROM pastes
INNER JOIN users ON pastes.user_id = users.user_id;

Course example

SELECT *
FROM departments
JOIN employees
ON departments.manager = employees.employee_id;

Additional references & examples

How to configure Knex — Basic

SQL / Knex Migrations / seeds
Set up knexfile and database connection settings for PostgreSQL or other drivers.
Learn: Knex needs a knexfile describing the client (pg), connection string, and migration directory. The same config is used for queries, migrations, and seeds.

Supplementary examples

knexfile.js excerpt

module.exports = {
  development: {
    client: "pg",
    connection: process.env.DATABASE_URL,
    migrations: { directory: "./db/migrations" },
    seeds: { directory: "./db/seeds" },
  },
};

Shared connection module

const knex = require("knex");
const config = require("../knexfile");

module.exports = knex(config[process.env.NODE_ENV || "development"]);

Course example

// Update with your config settings
module.exports = {
development: {
client: "sqlite3",
connection: {
filename: "./dev.sqlite3",
},
},
test: {
client: "postgresql",
pool: { min: 1, max: 5 },
connection: {
database: "db_test",
user: "username_test",
password: "password_test",
},
},
staging: {
client: "postgresql",
connection: {
database: "db_staging",
user: "username_staging",
password: "password_staging",
},
pool: {
min: 2,
max: 10,
},
migrations: {
tableName: "knex_migrations",
},
},
production: {
client: "postgresql",
connection: {
database: "db_production",
user: "username_production",
password: "password_production",
},
pool: {
min: 2,
max: 10,
},
migrations: {
tableName: "knex_migrations",
},
},
};

Additional references & examples

How to write Knex migrations — Intermediate

SQL / Knex Knex migrations
Use exports.up and exports.down to version schema changes in migration files.
Learn: Migrations version schema changes in code reviewable steps. `up` applies; `down` rolls back. Run migrations in CI and production so every environment shares the same table definitions.

Supplementary examples

Create table migration

exports.up = function (knex) {
  return knex.schema.createTable("pastes", (table) => {
    table.increments("paste_id").primary();
    table.integer("user_id").notNullable();
    table.text("name").notNullable();
    table.timestamps(true, true);
  });
};

exports.down = function (knex) {
  return knex.schema.dropTable("pastes");
};

Course example

exports.up = function (knex) {
return knex.schema.createTable("suppliers", (table) => {
table.increments("supplier_id").primary(); // Sets supplier_id as the primary key
table.string("supplier_name");
table.string("supplier_address_line_1");
table.string("supplier_address_line_2");
table.string("supplier_city");
table.string("supplier_state");
table.string("supplier_zip");
table.string("supplier_phone");
table.string("supplier_email");
table.text("supplier_notes");
table.string("supplier_type_of_goods");
table.timestamps(true, true); // Adds created_at and updated_at columns
});
};

Additional references & examples

How to seed data with Knex — Basic

SQL / Knex Migrations / seeds
Populate tables with initial or test data using Knex seed scripts.
Learn: Seeds load repeatable test or demo data after migrations. Keep seeds idempotent where possible (delete then insert, or use upsert patterns) so `knex seed:run` is safe locally.

Supplementary examples

Seed function

exports.seed = async function (knex) {
  await knex("users").del();
  await knex("users").insert([
    { username: "demo" },
    { username: "tester" },
  ]);
};

Course example

exports.seed = function (knex) {
// Deletes ALL existing entries
return knex("table_name")
.del()
.then(function () {
// Inserts seed entries
return knex("table_name").insert([
{ id: 1, colName: "rowValue1" },
{ id: 2, colName: "rowValue2" },
{ id: 3, colName: "rowValue3" },
]);
});
};

Additional references & examples

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 join tables with Knex — Intermediate

SQL / Knex Knex joins
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

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