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

Need a 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

Need a 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

Need a 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

Need a hint?

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