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.

We made a custom demo for .
No really. Click here to check it out.

Click here to see the full demo with network requests

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(...)
  .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.

Plug: LogRocket, a DVR for web apps

LogRocket is a frontend application monitoring solution 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.

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