How to create an API endpoint query with Knex

Finding one single record with a User model in Knex

In the previous article we started working on a User model that we can use to make calls to the database, instead of making the calls directly in the route handlers.

We have created a user-model.js file that keeps all the database logic in one place, so it's easy to read and modify in the future.

In our user-model.js file we have started by creating a method to retrieve all the users.
We then used this method in the router in this way:

const users = await User.all();

Let's now create a method to find one single user by id. We'll call the method find and it's defined like this:

// user-model.js

const find = id => {
  return db('users').where({id})
}

We then export the method:

module.exports = {
  all,
  find
}

And we use it in the router:

router.get('/:id', async (req, res) => {
  const { id } = req.params;  // extract id from params

  try {
    const user = await User.find(id)

    // ... code that returns the user ...
  } 
});

If we fire up our trusty REST client and test this endpoint, we should get our user back, right?

Let's send a GET request to localhost:4000/api/users/2. This is what we get back:

Well, we do get the user. Sort of.
What we didn't expect is to see the user wrapped inside an array. Why is it so?

The reason is that this Knex call:

db('users').where({id})

returns an array of records. The array only contains one element, because that's the only record that matches the id provided.

The .where() method in Knex always returns an array of records because we could potentially have a where clause that finds multiple records.

In our case, we only want one single user, though, so we need to extract the record from the array somehow.

This is very easy because Knex provides a method called .first() that returns the first record from a query. We can simply attach .first() at the end of the query to return the single user:

db('users').where({id}).first();

If we now test out the endpoint, we will get back a single user object: