Knex as query builder alternative in Express - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is Knex in the context of Express applications?

Knex is a flexible SQL query builder for Node.js that helps you write database queries using JavaScript instead of raw SQL strings. It works well with Express to manage database operations cleanly.

beginner

How does Knex improve writing database queries compared to raw SQL?

Knex lets you build queries step-by-step using JavaScript methods, which reduces errors, improves readability, and helps avoid SQL injection by automatically escaping inputs.

beginner

Show a simple example of selecting all rows from a 'users' table using Knex.

knex('users').select('*').then(rows => { console.log(rows); }); // This code fetches all users and logs them.

intermediate

What are some benefits of using Knex over an ORM in Express?

Knex offers more control over SQL queries, is lighter weight, and lets you write raw SQL when needed. It’s good when you want flexibility without the complexity of a full ORM.

intermediate

How does Knex handle different database types?

Knex supports multiple databases like PostgreSQL, MySQL, SQLite, and MSSQL by using different client configurations, so you can switch databases without changing your query code much.

What does Knex primarily help you do in an Express app?

A Build SQL queries using JavaScript methods

B Create HTML templates

C Manage user sessions

D Handle HTTP routing

Knex is a query builder that helps write SQL queries in JavaScript.

Which of these is a benefit of using Knex over raw SQL strings?

A Manages user authentication

B Automatically creates database tables

C Automatically escapes inputs to prevent SQL injection

D Generates frontend UI components

Knex escapes inputs to help prevent SQL injection attacks.

How do you select all rows from a 'products' table using Knex?

A knex('products').select('*')

B Both A and B

C knex.query('SELECT * FROM products')

D knex.select('*').from('products')

Both knex('products').select('*') and knex.select('*').from('products') work.

Knex supports which of the following databases?

A PostgreSQL

B MySQL

C SQLite

D All of the above

Knex supports PostgreSQL, MySQL, SQLite, and more.

Compared to an ORM, Knex is best described as:

A A lightweight query builder with more control

B A full database migration tool

C A frontend framework

D A user authentication library

Knex is a lightweight query builder offering more control than ORMs.

Explain how Knex helps you write database queries in an Express app and why it might be preferred over raw SQL.

Think about how writing queries in code can be safer and clearer.

Describe the advantages of using Knex when working with multiple database types in your Express project.

Consider how Knex handles different SQL dialects.

Practice

1. What is the main advantage of using Knex as a query builder in an Express app?

easy

A. It allows writing database queries using JavaScript instead of raw SQL.

B. It automatically creates database tables without any code.

C. It replaces Express middleware for handling requests.

D. It compiles JavaScript into SQL code for faster execution.

Solution

1. Step 1: Understand Knex's purpose

   Knex is designed to let developers write database queries in JavaScript instead of SQL.

2. Step 2: Compare options

   Options B, C, and D describe features Knex does not provide. It does not create tables automatically, replace Express middleware, or compile JS into SQL.

3. Final Answer:

   It allows writing database queries using JavaScript instead of raw SQL. -> Option A

4. Quick Check:

   Knex = JS query builder [OK]

Hint: Knex lets you write queries in JS, not raw SQL [OK]

Common Mistakes:

• Thinking Knex creates tables automatically
• Confusing Knex with Express middleware
• Believing Knex compiles JS to SQL code

2. Which of the following is the correct way to select all rows from a table named users using Knex?

easy

A. knex.tables('users').select('*')

B. knex.from('users').selects()

C. knex.query('SELECT * FROM users')

D. knex.select('*').from('users')

Solution

1. Step 1: Review Knex select syntax

   The common pattern is knex.select('*').from('tableName') to get all rows.

2. Step 2: Check each option

   knex.select('*').from('users') matches the correct syntax. knex.from('users').selects() uses invalid selects() method. knex.tables('users').select('*') uses knex.tables which is not standard Knex syntax. knex.query('SELECT * FROM users') uses raw SQL string which is not the Knex query builder method.

3. Final Answer:

   knex.select('*').from('users') -> Option D

4. Quick Check:

   Correct select syntax = knex.select('*').from('users') [OK]

Hint: Use knex.select('*').from('table') to get all rows [OK]

Common Mistakes:

• Using knex.tables() instead of knex.select().from()
• Using selects() instead of select()
• Trying to pass raw SQL string to knex

3. What will the following Knex query return?

knex('products').where('price', '>', 100).select('id', 'name')

medium

A. All products with price less than or equal to 100, showing id and name.

B. All products with price greater than 100, showing all columns.

C. All products with price greater than 100, showing id and name.

D. Syntax error due to incorrect method chaining.

Solution

1. Step 1: Analyze the where clause

   The query filters products where price is greater than 100.

2. Step 2: Analyze the select clause

   It selects only the 'id' and 'name' columns to return.

3. Final Answer:

   All products with price greater than 100, showing id and name. -> Option C

4. Quick Check:

   where('price', '>', 100) + select('id', 'name') = All products with price greater than 100, showing id and name. [OK]

Hint: where filters rows; select chooses columns [OK]

Common Mistakes:

• Confusing > with < in where clause
• Assuming all columns are returned
• Thinking method chaining causes syntax error

4. Identify the error in this Knex query:

knex('orders').where('status' = 'pending').select()

medium

A. Missing table name in knex call.

B. Using '=' instead of ',' inside where method.

C. select() requires column names as arguments.

D. where method cannot be chained after knex call.

Solution

1. Step 1: Check where method syntax

   The where method expects arguments separated by commas, e.g., where('status', 'pending').

2. Step 2: Identify the error

   The query uses '=' inside where which is invalid syntax in JavaScript function calls.

3. Final Answer:

   Using '=' instead of ',' inside where method. -> Option B

4. Quick Check:

   where('status' = 'pending') is invalid [OK]

Hint: Use commas, not '=' inside where() arguments [OK]

Common Mistakes:

• Using '=' instead of ',' in method arguments
• Thinking select() must have columns
• Believing where cannot be chained

5. You want to update the email of a user with id = 5 using Knex. Which query correctly performs this update?

hard

A. knex('users').where('id', 5).update({ email: '[e-mail]' })

B. knex('users').set('email', '[e-mail]').where('id', 5)

C. knex.update('users').set('email', '[e-mail]').where('id', 5)

D. knex('users').update({ email: '[e-mail]' }).where('id' = 5)

Solution

1. Step 1: Recall correct update syntax

   Knex updates usually chain where() before update() to specify which rows to change.

2. Step 2: Evaluate each option

   knex('users').where('id', 5).update({ email: '[e-mail]' }) correctly chains where('id', 5) before update({ email: ... }). knex('users').update({ email: '[e-mail]' }).where('id' = 5) uses '=' instead of ',' causing syntax error. knex('users').set('email', '[e-mail]').where('id', 5) uses invalid set() method. knex.update('users').set('email', '[e-mail]').where('id', 5) uses invalid syntax.

3. Final Answer:

   knex('users').where('id', 5).update({ email: '[e-mail]' }) -> Option A

4. Quick Check:

   where() before update() is correct pattern [OK]

Hint: Chain where() before update() to target rows [OK]

Common Mistakes:

• Using '=' instead of ',' in where arguments
• Using non-existent set() method
• Using invalid knex.update('users') syntax