Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Knex.js Query Builder - Queries Cheatsheet
/**
* @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))
})
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment