Querying databases with Knex.js

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.

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.

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. I’m using Postman and pgAdmin to test the APIs we’re building.

Seeding dump data

Knex provides a lot of built-in helpers 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.

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.”

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:

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.

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.

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:

router.route('/')
  .get(function(req, res, next) {
    const db = req.app.get("db");
    UsersService.getAllUsers(db).then(data => {
      res.send(data);
    });
  })
  .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.

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:

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 and the routes are pushed into this repository 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

Are you adding new JS libraries to build new features or improve performance? What if they’re doing the opposite?

There’s no doubt that frontends are getting more complex. As you add new JavaScript libraries and other dependencies to your app, you’ll need more visibility to ensure your users don’t run into unknown issues.

---

Over 200k developers use LogRocket to create better digital experiences

Learn more →

---

LogRocket is a frontend application monitoring solution that lets you replay JavaScript errors as if they happened in your own browser so you can react to bugs more effectively.

LogRocket works perfectly with any app, regardless of framework, and has plugins to log additional context from Redux, Vuex, and @ngrx/store. Instead of guessing why problems happen, you can aggregate and report on what state your application was in when an issue occurred. LogRocket also monitors your app’s performance, reporting metrics like client CPU load, client memory usage, and more.

Build confidently — start monitoring for free.