Query a database with Knex
Tags: knex, express, backend
Published: 2020-02-28 15:15:30

---
title: Query a database with Knex
published: true
description: How to retrieve data from two database tables with Knex
tags: knex, express, backend
cover_image: https://ferrariwebdevelopment.s3.us-east-2.amazonaws.com/assets/20191230-join-tables1.jpeg
---

# How to retrieve data from two database tables with Knex

In a previous article we have seen how to write `SQL` statements that can retrieve data from different database tables, provided related records in these tables are connected by what is called a foreign key.

Let's see how we can use this concept to retrieve data from two tables with Knex.

Imagine our application has a database with two tables: `users` and `posts`. In our application, users create posts, and when a post is created it's saved in the `posts` table. One attribute of the `posts` table is `user_id`, which is set to the `id` of the user that created the post.

Here's how those related tables are structured:

```
Table users:

id          username
----------  ----------
1           lao_tzu
2           socrates
3           seneca
```

```
Table posts:

id      contents              user_id
------  --------------------  -------
1       Let your workings re  1
6       Things arise and she  1
7       Beware of the barren  2
10      Trusting everyone is  3
```

As you can see, each post has a `user_id` column which is the foreign key for the user that created that post.

In our Express application, we provide an API that consumes this data.Our goal for now is to retrieve all posts by a particular user, using this endpoint:

```
GET /api/users/:id/posts
```

where `:id` is the user id we are interested in.

### User router

In our application, we have already defined a `UserRouter` bound to `/api/users`, in the `server.js` file:

```js
// server.js

const UserRouter = require('./users/user-router.js');
server.use('/api/users', UserRouter);
```

so, what we need to do in the user router is create an endpoint to handle `/:id/posts` with a `GET` method.
Let's start stubbing out the route handler method:

```js
router.get('/:id/posts', async (req, res) => {
})
```

Inside the route handler, the first thing to do is to extract the `id` from the parameters (we have already seen similar code before, refer to previous articles for more details):

```js
const { id } = req.params;
```

We then call the database object, defined in our router as `db`, passing the table '`posts`' to it. This will generate a `SQL` statement similar to `SELECT * FROM posts`. We assign the posts to a variable `posts`:

```js
const posts = await db('posts')
```

The above code returns all the posts, but we only want posts from a particular user, so we need to limit the posts returned.We do that by using a where clause in our query. We pass an object to where that looks like this:

```js
{user_id: id}
```

The object specifies the column we are interested in (`user_id`) and the value we want for that column, contained in the `id` variable set previously.Finally, in our response, we send the found posts back to the caller, along with a `200` OK status.
This is our code so far:

```js
const posts = await db('posts').where({user_id: id})
res.status(200).json(posts)
```

### Handling errors

Of course, we need to handle the case where an error occurs, so in the `catch` block we send back an error message if the posts cannot be found:

```js
res.status(500).json({message: "can't get posts"})
```

This is the full code for the route handler:

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

  try {
    const posts = await db('posts').where({user_id: id})
    res.status(200).json(posts)
  } catch (err) {
    res.status(500).json({message: "can't get posts"})
  }
})
```

If we now make a request to the `API` with *Insomnia*, or some other `REST` client, we get back all the posts for a particular user:

![Posts returned](https://dev-to-uploads.s3.amazonaws.com/i/y09oxjk4lkcg217uwlwu.png)

This works well, and the response includes the user id. But we would like to include the actual *user name* in the response instead of just the id.How do we achieve that?

If we want the user name, we need to learn how to join both posts and users table together. We'll see how to do that in the next article.

---

*I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.*

*You can receive articles like this in your inbox by [subscribing to my newsletter](https://cesare.substack.com).*