Knex as query builder alternative in Express - Mini Project: Build & Apply

Using Knex as a Query Builder Alternative in Express

📖 Scenario: You are building a simple Express server that needs to fetch user data from a database. Instead of writing raw SQL queries, you want to use Knex as a query builder to make your code cleaner and easier to maintain.

🎯 Goal: Build an Express route that uses Knex to query a users table and return users with an age greater than 25.

📋 What You'll Learn

Create a Knex configuration object for a SQLite3 database

Set a minimum age threshold variable

Write a Knex query to select users older than the threshold

Create an Express GET route that returns the filtered users as JSON

💡 Why This Matters

🌍 Real World

Knex is often used in Node.js backend projects to write database queries in a safer and more readable way than raw SQL. This helps developers avoid syntax errors and SQL injection risks.

💼 Career

Many companies use Knex or similar query builders in their backend code. Knowing how to use Knex with Express is a valuable skill for backend developers working with relational databases.

1

Set up Knex configuration

Create a constant called knex that requires the knex module and configure it with client 'sqlite3' and connection filename ':memory:'.

Express

// Your code here

Hint

Use require('knex')({ client: 'sqlite3', connection: { filename: ':memory:' } }) to create the Knex instance.

2

Create age threshold variable

Create a constant called minAge and set it to the number 25.

Express

const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: ':memory:'
  },
  useNullAsDefault: true
});

// Your code here

Hint

Just write const minAge = 25; below the Knex setup.

3

Write Knex query to select users older than minAge

Create an async function called getUsersAboveMinAge that returns the result of a Knex query selecting all columns from the users table where the age column is greater than minAge.

Express

const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: ':memory:'
  },
  useNullAsDefault: true
});

const minAge = 25;

// Your code here

Hint

Use knex('users').select('*').where('age', '>', minAge) inside the async function.

4

Create Express route to return filtered users

Require express, create an app with express(), and add a GET route at '/users' that calls getUsersAboveMinAge and sends the result as JSON. Export the app with module.exports = app.

Express

const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: ':memory:'
  },
  useNullAsDefault: true
});

const minAge = 25;

async function getUsersAboveMinAge() {
  return await knex('users').select('*').where('age', '>', minAge);
}

// Your code here

Hint

Use express() to create the app, then add a GET route at /users that calls getUsersAboveMinAge() and sends JSON response.

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