Knex.js Query Builder - Queries Cheatsheet

knexjs.js

/**
 * @Desc : All of my data are seeded using this testing database(https://github.com/datacharmer/test_db) just import it to your local database server 
 */
const knex = require("knex")
const db = knex(require("./knexfile")["development"])
const { bgCyan, bgRed, redBright, bold, underline } = require("colorette") // To add colors for stdout text

db.from("departments").select("*").first().then(res => {
    console.log(bgCyan("Basic select:"))
    console.log(res)
}).catch(err => {
    console.log(bgRed(err))
})

db.from("employees").select("*").groupBy("gender").limit(10)
    .then(res => {
        console.log(bgCyan("Group by:"))
        console.log(res)
    }).catch(err => {
        console.log(bgRed(err))
    })

db.from("employees").select("*").where('emp_no', 499999)
    .then(res => {
        console.log(bgCyan("Where condition:"))
        console.log(res)
    }).catch(err => {
        console.log(bgRed(err))
    })

db.from("employees").select("*").havingNotNull("hire_date").limit(2)
    .then(res => {
        console.log(bgCyan("Only select rows without a null values in your desired columns:"))
        console.log(res)
    }).catch(err => {
        console.log(bgRed(err))
    })

db.select("*").from("employees").orderBy("emp_no", "desc").limit(2)
    .then(res => {
        console.log(bgCyan("Only brings the last 2 records (emp_no = id):"))
        console.log(res)
    }).catch(err => {
        console.log(bgRed(err))
    })

db.select("*", { id: 'emp_no' }).from("employees").orderBy("id", "desc").limit(2)
    .then(res => {
        console.log(bgCyan("Only brings the last 2 records (emp_no = id)"))
        console.log(res)
    }).catch(err => {
        console.log(bgRed(err))
    })

db.column("first_name", "last_name").select().from("employees").first()
    .then(res => {
        console.log(bgCyan("Select certain columns:"))
        console.log(res)
    }).catch(err => {
        console.log(bgRed(err))
    })

db.select("*").from("employees").innerJoin("dept_emp", "dept_emp.emp_no", "employees.emp_no")
    .innerJoin("departments", "departments.dept_no", "dept_emp.dept_no").first()
    .then(res => {
        console.log(bgCyan("Inner joins between 3 tables :"))
        console.log(res)
    }).catch(err => {
        console.log(bgRed(err))
    })


db.select("*", { userrr: 'users.id' }).from("users").innerJoin("tasks", "tasks.user_id", "users.id")
    .then(res => {
        console.log(bgCyan("Change columns names in a select query :"))
        console.log(res)
    }).catch(err => {
        console.log(bgRed(err))
    })