Creating a strong and well-structured backend is very important to aiding database management systems in programming. As a developer, you may need help writing raw SQL queries and manually handling database migrations and transactions. Knex.js helps you easily create complex queries to select, insert, update and delete data from a database.
In this article, you will learn how to set up a development environment for using PostgreSQL, configure Knex with PostgreSQL, and build a RESTful API using Node.js, Knex, and PostgreSQL. You will design a to-do list by the end of this article to implement what you are learning.
Overview of Knex
Knex.js is a versatile SQL query builder primarily used with Node.js. It is designed for flexibility, portability, and ease of use across various databases, such as PostgreSQL, CockroachDB, MSSQL, MySQL, MariaDB, SQLite3, Better-SQLite3, Oracle, and Amazon Redshift.
PostgreSQL is a popular relational database system widely used in modern web applications and other software systems.
Setting up your Development Environment
Installing PostgreSQL is necessary if you want to use it for development. Navigate to the PostgreSQL website and select your operating system to download PostgreSQL. Alternatively, you can use PostgreSQL on the cloud with the help of platforms like Neon.tech and ElephantSQL. Both of them offer PostgreSQL as a service.
After setting up PostgreSQL on your machine, proceed to create a folder for our todo by running the following commands:
mkdir knex-todo-tutorial
Next, navigate into the project directory using cd by running the following commands:
cd knex-todo-tutorial
While in the project directory, run the following commands to initialise npm in your project directory:
npm init -y
The -y flag initialises npm with all the default parameters.
To use Knex with PostgreSQL, you need to install some dependencies. Run the following commands to install them in your project:
npm install -g knex
npm install pg express dotenv
Note that installing Knex globally is important; otherwise, it might not initialise.
Next, create a .env file in your project’s root directory and store your database credentials.
# URI
DATABASE_URI = YOUR_DATABASE_URI
# credentials
DATABASE_NAME = YOUR_DATABASE_NAME
DATABASE_PASSWORD = YOUR_DATABASE_PASSWORD
Replace the various placeholders in the code snippet above with the actual values.
Setting up your Express Server
In this tutorial, we will use a simple to-do list API to demonstrate the use of Knex in combination with PostgreSQL with Node.js to build a Node.js application.
First, create an index.js file in your project’s root directory and add the code block to it:
const express = require("express");
const app = express();
const port = 3000;
app.use(express.json());
app.listen(port, () => {
console.log(`App listening on port:${port}`);
});
The code block above initialises your express server and listens on port 3000. It uses express.json() middleware to parse incoming JSON requests.
Configuring Knex with PostgreSQL
To use Knex in your application, you will have to initialise it first and configure it with the database driver you wish to use. In this case, we are using PostgreSQL.
Run the following commands to initialise Knex in your application:
knex init
The command above creates a knexfile.js, which contains configuration settings to connect to your database, such as a database type, host, port, username, password, and other configuration options. You then have to configure it based on your development environment.
The knexfile.js command generated will have sqlite3 as its development database by default. To use PostgreSQL, replace your current knexfile.js with the code block below:
// Update with your config settings.
require("dotenv").config();
/**
* @type { Object.<string, import("knex").Knex.Config> }
*/
module.exports = {
development: {
client: "pg",
connection: process.env.DATABASE_URI,
migrations: {
directory: "./db/migrations",
}
}
};
The code block above configures Knex to use Postgres as its database client. It also specifies the database connection with environmental variables and the file path where your migration files will be stored.
Next, create a db folder in your project directory by running the command below:
mkdir db
Create a db.js file in your db folder and import knex and your knexFile.js file in this manner:
const knex = require("knex");
const knexFile = require("../knexfile.js");
Next, set up your environment variable using the code block below:
//db/db.js
const environment = process.env.NODE_ENV || "development";
module.exports = knex(knexFile[environment]);
The code block above sets your environment variable to either the NODE_ENV or development This lets you specify different configurations for different environments, such as development, production, or testing.
The module.exports statement exports a configured Knex.js instance using the configuration settings from knexFile[environment]. This instance can create database tables, insert data, run queries, and perform other database-related operations in JavaScript code.
Creating Migration Files
Migration files are scripts or programs you can use to manage the changes made in a database schema, such as adding new tables, modifying existing ones, or adjusting column types, without losing data or disrupting ongoing operations. They are used to automate the transfer of data from one database to another.
Altering the schema of a database can be complex and make the database prone to errors. By using migration files, you can define the changes you want to make in a migration file instead of manually modifying the database schema. When you run the migration file using Knex, it automatically applies the changes to the database schema, ensuring that the changes are made consistently and correctly.
To create a migration file, run the command below:
knex migrate:make todo
Create a "todo" migration file in the path specified by the knexfile.js file (db/migrations) using the command above.
Note that you can replace the “todo” argument with your preferred migration name.
Next, open your migration file and replace the up function with the code block below:
exports.up = function (knex) {
//Create a table called "todo" with the following columns: id, title, content, created_at, updated_at
return knex.schema.createTable("todo", (table) => {
table.increments("id").primary(); //id column with auto-incrementing primary key
table.string("title").notNullable(); //title column with type string
table.text("content").notNullable(); //content column with type text
table.timestamps(true, true); //created_at and updated_at columns with type timestamp
});
};
The code block above, when executed, creates a todo table in your PostgreSQL database with the tables specified above.
Next, replace the down function with the code block below:
exports.down = function (knex) {
// Drop the "todo" table if it exists
return knex.schema.dropTableIfExists("todo");
};
The todo table in your PostgreSQL database is dropped when the code block above is executed. This is the opposite of what the up function does.
Run the code block below on your terminal to run migrations.
knex migrate:latest
The command above goes through all your migration files and runs the up function.
To undo the migrations, run the command below:
knex migrate:rollback
The command above goes through all your migration files and runs the down function.
Creating CRUD Endpoints
Create a routes folder in the root directory of your project and a todo.js file for better code organisation.
In your todo.js file, import Express, your Knex configuration, and set up the Express Router. Here’s how to do it:
const express = require("express");
const db = require("../db/db.js");
const router = express.Router();
In your todo.js file, you can add CRUD endpoints to interact with your database. The tutorial will feature queries that get all the tasks, get a task(s) based on a condition, update a task, and delete a task from the database.
Getting All Tasks
To get all the task instances on your to-do list from your database, use the code block below in your blog.js file.
router.get("/todo", async (req, res) => {
try {
const tasks = await db.select("*").from(todo);
res.send({ msg: tasks });
} catch (error) {
res.status(500).json({ error: error.message });
}
});
The code block above returns all the tasks in your database.
Getting Tasks Conditionally
To get the tasks on your database based on certain conditions, use the code block below to achieve that:
router.get("/todo/:id", async (req, res) => {
const { id } = req.params;
try {
const task = await db(todo).where({ id });
if (task.length !== 0) {
res.send({ msg: task });
} else {
res.status(400).json({ msg: "task not found" });
}
} catch (error) {
res.status(500).json({ error: error.message });
}
});
The code block above returns the task based on the “id” given.
Adding a new Task to your Database
To add a new task to your database, use the code block below to achieve that:
router.post("/todo", async (req, res) => {
const { title, content } = req.body;
try {
const task = await db("todo").insert({ title, content });
res.status(201).send(task);
} catch (error) {
res.status(500).json({ error: error.message });
}
});
The code block above adds a new task to your database.
Updating an Existing Task
To update an existing task on the database, use the code block below:
router.put("/todo/:id", async (req, res) => {
const { id } = req.params;
const { title, content} = req.body;
try {
const task = await db("todo")
.where({ id })
.update({ title, content }, ["id", "title", "content"]);
if (task.length !== 0) {
res.status(201).send(task);
} else {
res.status(404).json({ error: "task not found" });
}
} catch (error) {
res.status(500).json({ error: error.message });
}
});
The code block above updates an existing task based on a given id.
Deleting a Task
To delete a task from the database, use the code block below:
router.delete("/todo/:id", async (req, res) => {
const { id } = req.params;
try {
const task = await db("todo").where({ id }).del();
if (task) {
res.status(204).send();
} else {
res.status(404).json({ error: "Task not found" });
}
} catch (error) {
res.status(500).json({ error: error.message });
}
});
The code block above deletes a task from your database.
Finally, export your router by adding the line of code below to your todo.js
module.exports = router
Testing Your Application
Navigate to your index.js, import your router and add it as a middleware in this manner:
//index.js
const todoRouter = require("./routes/todo.js");
app.use(todoRouter);
Then, start up your application by running the command below:
node index.js
To test your application, you can use tools such as Postman to make HTTP requests to your API and verify that it returns the expected results.
Conclusion
In this article, you learned how to use Knex.js with PostgreSQL to build a Node.js API. You also learned how to configure Knex to use PostgreSQL as its database client, connect to a PostgreSQL database locally and remotely, and make queries using Knex.
With the knowledge gained in this article, you can now build strong and reliable Node.js applications that leverage the power of PostgreSQL and the simplicity of Knex.js.
Top comments (0)