sql

Expressjs and Knexjs sql query builder

Hello readers. In this tutorial, we will understand the knexjs sql builder library in expressjs application and perform some crud operations.

1. Introduction

knex sql builder library for javascript that provides an easy interface to access a database and perform all the related operations. It supports multiple database engines like Mysql, Postgresql, SQLite, Oracle, and many more. It supports features like –

  • Transactions
  • Connection pooling
  • Streaming queries
  • Both a promise and callback api
  • A thorough test suite

1.1 Setting up Node.js

To set up Node.js on windows you will need to download the installer from this link. Click on the installer (also include the NPM package manager) for your platform and run the installer to start with the Node.js setup wizard. Follow the wizard steps and click on Finish when it is done. If everything goes well you can navigate to the command prompt to verify if the installation was successful as shown in Fig. 1.

Fig. 1: Verifying node and npm installation

2. Expressjs and Knexjs sql query builder

To set up the application, we will need to navigate to a path where our project will reside and I will be using Visual Studio Code as my preferred IDE. Let a take a quick peek at the project structure.

Fig. 2: Project structure

2.1 Pre-requirement – Postgres Setup

Usually, setting up the database is a tedious step but with technological advancements, this process has become simple with the help of Docker. Readers can watch the video available at this link to understand the Docker installation on Windows OS. Open the terminal and trigger the following commands to get the postgresql up and running on the local machine.

Docker commands

-- command to run postgres on docker --

-- remember to change the password --
docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres

-- command to stop the Postgres docker container --
docker stop postgres

-- command to remove the Postgres docker container --
docker rm postgres

Remember to enter the password of your choice. If everything goes well the postgresql database server would be up and running on a port number – 5433 and you can connect with the pgAdmin gui tool for connecting to the server.

Fig. 3. Postgres on Docker

2.1.1 Setting up the mock data

To play with the application we need to create mock data. Once the connection with postgresql is made I will be using the below sql script to create the required data.

users.sql

drop database if exists "learning";

create database "learning";

DROP TABLE IF EXISTS "users";

CREATE TABLE "users" (
  id SERIAL PRIMARY KEY,
  name varchar(255) default NULL,
  email varchar(255) default NULL,
  age integer NULL
);

INSERT INTO users (name,email,age) values ('Uma Montoya','rutrum.justo@hotmail.edu',35);
INSERT INTO users (name,email,age) values ('Burton Tran','urna.nec.luctus@outlook.couk',37);
INSERT INTO users (name,email,age) values ('Catherine Allen','risus.nulla.eget@google.org',24);
INSERT INTO users (name,email,age) values ('Willow Hoffman','facilisis@protonmail.edu',36);
INSERT INTO users (name,email,age) values ('Marny Bishop','consectetuer@protonmail.org',40);
INSERT INTO users (name,email,age) values ('Jane doe','jandoe@protonmail.org',27);

select * from "users";

If everything works fine the sql select statement will show the data.

Fig. 4: Mock data in a table

2.2 Setting up project dependencies

Navigate to the project directory and run npm init -y to create a package.json file. This file holds the metadata relevant to the project and is used for managing the project dependencies, script, version, etc. Replace the generated file with the code given below –

package.json

{
  "name": "expressjs-knex",
  "version": "1.0.0",
  "description": "understanding knex sql builder in express js",
  "main": "index.js",
  "scripts": {
    "dev": "nodemon index.js",
    "start": "node index.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "nodemon",
    "pg",
    "postgresql",
    "knex",
    "sql builder",
    "express",
    "sql"
  ],
  "author": "geek",
  "license": "MIT",
  "dependencies": {
    "config": "^3.3.7",
    "express": "^4.18.1",
    "knex": "^2.1.0",
    "pg": "^8.7.3"
  },
  "devDependencies": {
    "nodemon": "^2.0.16"
  }
}

Once the file is replaced trigger the below npm command in the terminal window to download the different packages required for this tutorial.

Downloading dependencies

npm install

2.3 Setting up the config

Create a file named default.json in the config directory. The file will hold the database and application settings. You’re free to change the details as per your needs.

default.json

{
  "dev": {
    "db": {
      "host": "endpoint",
      "port": 5432,
      "user": "username",
      "password": "password",
      "db_name": "database_name",
      "table": "table_name",
      "driver": "pg"
    },
    "app_port": 5005
  }
}

2.4 Setting up the knexjs configuration

Create a file named knexconfig.js in the config directory. The file will be responsible to read the details from the configuration.

knex.js

const config = require("config");

module.exports = {
  connection: {
    host: config.get("dev.db.host"),
    user: config.get("dev.db.user"),
    password: config.get("dev.db.password"),
    database: config.get("dev.db.db_name")
  },
  client: config.get("dev.db.driver"),
  debug: false
};

2.5 Creating routing controller

Create a file named routes.js in the routes folder responsible to handle the api endpoints and database interaction with the help of knexjs sql library.

routes.js

const knexConfig = require("../config/knexconfig");
const conn = require("knex")(knexConfig);

const config = require("config");
const table = config.get("dev.db.table");

const index = (req, resp) => {
  return resp.status(200).json({ message: "up" });
};

// crud endpoints

const getAll = async (req, resp) => {
  console.log("getting all users");
  try {
    const users = await conn(table).select("*");
    return resp.status(200).json({ data: users });
  } catch (err) {
    error(err, resp);
  }
};

const findOne = async (req, resp) => {
  let key = parseInt(req.params.id);
  console.log("searching user %s", key);
  try {
    const res = await conn(table).where("id", key).select();
    if (res.length === 0)
      return resp.status(400).json({ message: `${key} not found` });
    else return resp.status(200).json({ data: res });
  } catch (err) {
    error(err, resp);
  }
};

const save = async (req, resp) => {
  console.log("saving user");
  const { name, email, age } = req.body;
  try {
    const res = await conn(table)
      .insert({ name: name, email: email, age: age })
      .returning("id");
    return resp.status(201).json({ message: `${res[0].id} created` });
  } catch (err) {
    error(err, resp);
  }
};

const deleteOne = async (req, resp) => {
  let key = parseInt(req.params.id);
  console.log("deleting user %s", key);
  try {
    const res = await conn(table).where("id", key).select();
    if (res.length === 0)
      return resp.status(400).json({ message: `${key} not found` });
    else {
      await conn(table).where({ id: res[0].id }).del();
      return resp.status(202).json({ message: `${res[0].id} deleted` });
    }
  } catch (err) {
    error(err, resp);
  }
};

const update = async (req, resp) => {
  let key = parseInt(req.params.id);
  console.log("updating user %s", key);
  // db part skipped for brevity. you are free to play around
  return resp.status(204).json({ info: `${id} updated` });
};

// helper method

function error(err, resp) {
  console.log(err);
  return resp.status(503).json({ message: "error occurred" });
}

module.exports = {
  index,
  getAll,
  findOne,
  save,
  deleteOne,
  update
};

2.6 Creating the implementation file

Create a file in the root directory that acts as an entry point for the application. The application endpoints will be exposed on a port number – 5005.

index.js

// knex sql query builder tutorial in express js

const express = require("express");
const config = require("config");

const api = require("./routes/routes");

const app = express();
app.use(express.json());

// app endpoints
app.get("/", api.index);
app.get("/getAll", api.getAll);
app.get("/find/:id", api.findOne);
app.post("/save", api.save);
app.delete("/delete/:id", api.deleteOne);
app.put("/update/:id", api.update);

// driver code
const port = config.get("dev.app_port");
app.listen(port, () => {
  console.log(`service endpoint = http://localhost:${port}`);
});

3. Run the Application

To run the application navigate to the project directory and enter the following command as shown below in the terminal. The application will be started successfully on the port number – 5005.

Run command

$ npm run start

Once the application is started successfully open the postman and hit the different endpoints to play around. You can download the http endpoint details from the Downloads section and export them into the postman tool. That is all for this tutorial and I hope the article served you with whatever you were looking for. Happy Learning and do not forget to share!

4. Summary

In this tutorial, we saw a brief introduction to the knexjs sql builder library and integrated it with the postgresql database. You can download the source code from the Downloads section.

5. Download the Project

This was a tutorial to perform crud operations in an expressjs application via the knex sql library.

Download
You can download the full source code of this example here: Expressjs and Knex sql query builder

Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button