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.
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.
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.
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.
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.
You can download the full source code of this example here: Expressjs and Knex sql query builder
