Creating data with Knex in a REST API

Using Knex to insert data into a database table

In previous articles we have seen how to get both a collection of records and one single record as a response to a GET API request.
We used Knex, a popular query builder, to connect to a SQLite database and retrieve the records sent back by route handlers in our API.

Now we want to look at creating new records from an API request.
Creating new data involves using the POST HTTP verb, so we need a router.post() handler for the request:

router.post('/', (req, res) => {

  // handle the route here

});

To create a new record we need its data, which is passed to our API in the request body. We need to first extract data from the body:

const postData = req.body;

Once we have the postData, we use the insert() method of Knex to pass data to the database.
insert() will do the same thing as the INSERT statement in plain SQL, and add a new record.

This method will return an array that contains the IDs of the new records created.
Since we are creating one new record, our array will contain one element, but if we created multiple records instead, by passing an array of objects to insert(), we would get back all the IDs of the records created.

We can save this returned array in a variable and display it back to the client.
Here's the code for the post route handler:

router.post('/', async (req, res) => {
  const postData = req.body;

  try {
    const ids = await db('posts').insert(postData);
    res.status(201).json(ids);
  } catch (err) {
    res.status(500).json({message: "Error creating new post", error: err})
  }

});

Note that we are using async/await syntax because we are using promises.

If we now make a test post, the new record gets inserted correctly into the database.
We can test that the feature works as expected by making a POST request with Insomnia and passing a new record object in the body:

As you can see in the screenshot, we get a 201 status code, meaning new record created, and we get back an array with the ID or the new record.

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.