knexReview.md

knex Review

<style> span { color: green; } </style>

---

What is a query builder?

A software library that allows the use of objects and methods to write SQL queries in a database agnostic way.

---

What are 2 reasons to use a query builder?

---

What are 2 reasons to use a query builder?

• Avoid long concatenated strings in code.

• Build complex SQL statements programmatically

• Automatically quotes table names and columns to prevent conflict with SQL reserved words and special characters.

• Automatically escapes parameters to reduce risk of SQL injection attacks

• Provides DB abstraction, simplifying migration to different DB platforms

---

What command is used to initialize knex?

knex init

---

What file does knex init create?

knexfile.js

---

What does knexfile.js contain?

Connection strings for various environments

---

What is the command to create a new migration named create_students?

knex migrate:make create_students

---

What folder/files are created when the migration command is called?

migrations folder and CURRENTDATETIME_create_students.js

---

In a knex migration, how would you create an ID column that auto increments?

table.increments()

---

In a knex migration, how would you create a text column called name?

table.string('name')

---

How do you specify that a column can not be NULL using Knex?

---

How do you specify that a column can not be NULL using Knex?

knex.schema.createTable('address', function(table) {
  t.increments().primary();
  table.string('city',50).notNullable();
  table.string('state',2).notNullable();
  table.integer('zip',5).unsigned().notNullable();
});

---

What command is used to run the latest migration?

knex migrate:latest

---

Write the following SQL statement in knex:

SELECT * FROM students;

knex('students').select();

---

Write the following SQL statement in knex:

SELECT * FROM students WHERE id=5; LIMIT 1

knex('students').select() .where({id:5}).limit(1);

---

Write the following SQL statement in knex:

SELECT * FROM todos
WHERE id IN ('1', '2', '3')
OR user_id IN ('4', '5', '6');

knex('todos').whereIn('id', ['1', '2', '3']).orWhereIn('user_id', ['4', '5', '6']);

---

Write the following SQL statement in knex:

DELETE * FROM students;

knex('students').del();

---

Write the following SQL statement in knex:

UPDATE "students" SET "score" = "score" + 10 WHERE id=1;

knex('students').where({id:1}) .increment("score",10);

---

Write the following SQL statement in knex:

SELECT * FROM "students"
LEFT OUTER JOIN "todos" ON "students"."id" = "todos"."student_id";

knex('students').leftOuterJoin('todos', 'students.id', 'todos.student_id');

---

fin