Querying databases with Knex.js
Tags: javascript, webdev
Published: 2020-04-03 15:05:49

---
title: Querying databases with Knex.js
published: true
date: 2020-03-13 18:30:31 UTC
tags: javascript,webdev
canonical_url: https://blog.logrocket.com/querying-databases-with-knex-js/
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/tskzr880jzpc1i0hv8va.jpeg
---
**Written by [Avanthika Meenakshi](https://blog.logrocket.com/author/avanthikameenakshi/)**✏️

## Intro to Knex

Knex.js is a “batteries-included” query builder for PostgreSQL, MySQL, SQLite3, Oracle, Amazon Redshift, and many other database drivers. We simply install the Knex library and the appropriate driver to query the database. Primarily made for Node.js, Knex supports both Node-style callbacks and promises. In this article, we’ll look into querying a Postgres database with Knex.

## Connecting Knex with Postgres

To use Knex and Postgres, we will have to get Knex and the pg driver installed in the app first.

```jsx
npm install pg knex --save
```

After the dependencies are installed, the next step is establishing the connection to pg. This is done more or less the same as how we traditionally make pg connection in Node apps. We specify the connection parameters for Postgres and point Knex to connect to the pg client.

```jsx
const db = require("knex")({
  client: "pg",
  connection: {
    host: "localhost",
    user: "postgres",
    password: "",
    database: "knex-test"
  }
});

app.set("db", db);
```

Once the connection is established, we’re going to seed data without writing any queries with Knex and [faker.js](https://github.com/marak/Faker.js/). I’m using Postman and pgAdmin to test the APIs we’re building.

[![LogRocket Free Trial Banner](https://i0.wp.com/blog.logrocket.com/wp-content/uploads/2017/03/f760c-1gpjapknnuyhu8esa3z0jga.png?resize=1200%2C280&ssl=1)](https://logrocket.com/signup/)

## Seeding dump data

Knex provides a lot of [built-in helpers](http://knexjs.org/#Schema-Building) to build our table schema. I’ve used the built-in `createTable` function to create a table called `users` if the table doesn’t exist. Faker.js is helping us seed dummy data into the table. I’m using the built-in `.insert` method to insert data into the users table we just created. All this happens when we hit the route `http:localhost:3000/seed`.

```jsx
router.get("/seed", function(req, res, next) {
  const db = req.app.get('db');
  db.schema.hasTable("users").then(function(exists) {
    if (!exists) {
      db.schema
        .createTable("users", function(table) {
          table.increments("id").primary();
          table.string("name");
          table.string("email");
        })
        .then(function() {
          const recordsLength = Array.from(Array(100).keys());
          const records = recordsLength.map(rec => ({
            name: faker.name.findName(),
            email: faker.internet.email()
          }));
          db("users")
            .insert(records)
            .then(() => {
              res.send("Seeded data");
            });
        });
    } else {
      res.send("Table exists - Seeded data");
    }
  });
});
```

If you have pgAdmin installed, you can quickly have a look at the seeded data. When all goes right, you’ll see the response “Seeded data” on your browser or Postman interface. When you try to re-hit the route, it will respond back with “Table exists – Seeded data.”

![Screenshot Of Our Seeded Data In A Table](https://i1.wp.com/blog.logrocket.com/wp-content/uploads/2020/03/seeded-data.png?resize=730%2C313&ssl=1)

## Querying, inserting and removing data

### Fetching all data

Knex makes querying really simple. I’m going to write a service that can fetch, update, delete, and create users. With Knex, querying for data from the Postgres database is as simple as:

```jsx
getAllUsers(db) {
    return db
      .select("*")
      .from("users")
      .then(rows => rows);
 }
```

I’m going to create a `get` route to fetch all the users in from the database. When this endpoint is pinged from Postman, Knex builds the query for the data we are requesting and returns the data.

```jsx
router
  .route("/")
  .get(function(req, res) {
    const db = req.app.get("db");
    UsersService.getAllUsers(db).then(data => {
      res.send(data);
    });
  })
```

### Inserting data

Inserting a new user into the database is simple as well. I’m going to create a `.post` method to the route we’ve already created and update the `usersService.js` with a query to insert data into the pg database.

```jsx
const UsersService = {
  insertUser(db, newUser) {
    return db
      .insert(newUser)
      .into("users")
      .returning("*")
      .then(rows => {
        return rows[0];
      });
  }
};

module.exports = UsersService;
```

The updated route looks like:

```jsx
router
  .route("/")
  .get(...)
  .post(function(req, res) {
    const db = req.app.get("db");
    UsersService.insertUser(db, req.body).then(data => {
      res.send(data);
    });
  });
```

### Updating, deleting, and fetching particular data

I’m going to group the routes for updating, deleting, and fetching by user ID together.

```jsx
router
  .route("/:id")
  .get(function(req, res) {
    const db = req.app.get("db");
    UsersService.getById(db, req.params.id).then(data => {
      res.send(data);
    });
  })
  .patch(function(req, res) {
    const db = req.app.get("db");
    UsersService.updateUser(db, req.params.id, req.body).then(() => {
      res.status(204).end();
    });
  })
  .delete(function(req, res) {
    const db = req.app.get("db");
    UsersService.deleteUser(db, req.params.id).then(data => {
      res.status(204).end();
    });
  });
```

Querying for specific information from Knex is pretty straightforward. In this example, we’re using a combination of `select`, `update`, and `delete` with a `where `clause to pick users by ID and to modify their information. The `usersService.js` looks like:

```jsx
const UsersService = {
  getById(db, id) {
    return db
      .from("users")
      .select("*")
      .where("id", id)
      .first();
  },
  deleteUser(db, id) {
    return db("users")
      .where({ id })
      .delete();
  },
  updateUser(db, id, userFields) {
    return db("users")
      .where({ id })
      .update(userFields);
  }
};

module.exports = UsersService;
```

On hitting the GET, PATCH, or DELETE route with the appropriate user ID, we will be able to see, modify, or delete the user information from the database.

The complete version of [`usersService.js`](https://github.com/AvanthikaMeenakshi/knex-tutorial/blob/master/module/usersService.js) and the [routes](https://github.com/AvanthikaMeenakshi/knex-tutorial/blob/master/routes/index.js) are pushed into [this repository](https://github.com/AvanthikaMeenakshi/knex-tutorial) on GitHub.

## Pros and cons of Knex.js

##### Pros:

1. Helps us write fewer SQL queries: Knex knows which database system you’re connecting to and will change the SQL it writes to match
2. Feels more like programming with JavaScript
3. Migrations and seeding are much simpler

##### Cons:

1. You should still take time to learn db manipulations at a deeper level
2. Knex requires a learning curve. It is a library itself, and the developer should know their way inside out to use Knex
3. If you’re a beginner trying to build APIs, it is better to choose the hard path than the happy path. Writing queries yourself will make you more proficient and comfortable with databases than using a query builder like Knex

* * * 
## Plug: [LogRocket](https://logrocket.com/signup/), a DVR for web apps
 
![LogRocket Dashboard Free Trial Banner](https://i2.wp.com/blog.logrocket.com/wp-content/uploads/2017/03/1d0cd-1s_rmyo6nbrasp-xtvbaxfg.png?resize=1200%2C677&ssl=1)
 
[LogRocket](https://logrocket.com/signup/) is a frontend logging tool that lets you replay problems as if they happened in your own browser. Instead of guessing why errors happen, or asking users for screenshots and log dumps, LogRocket lets you replay the session to quickly understand what went wrong. It works perfectly with any app, regardless of framework, and has plugins to log additional context from Redux, Vuex, and @ngrx/store.
 
In addition to logging Redux actions and state, LogRocket records console logs, JavaScript errors, stacktraces, network requests/responses with headers + bodies, browser metadata, and custom logs. It also instruments the DOM to record the HTML and CSS on the page, recreating pixel-perfect videos of even the most complex single-page apps.
 
[Try it for free](https://logrocket.com/signup/).
* * *

The post [Querying databases with Knex.js](https://blog.logrocket.com/querying-databases-with-knex-js/) appeared first on [LogRocket Blog](https://blog.logrocket.com).