How to Create a Node API With Knex and PostgreSQL
Published: 2024-07-02 15:15:37

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](https://knexjs.org/) 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](https://www.postgresql.org/) 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](https://www.postgresql.org/download/) and select your operating system to download PostgreSQL. Alternatively, you can use PostgreSQL on the cloud with the help of platforms like [Neon.tech](http://Neon.tech) and [ElephantSQL](https://www.elephantsql.com/). 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:

```bash
mkdir knex-todo-tutorial
```

Next, navigate into the project directory using `cd` by running the following commands:

```bash
cd knex-todo-tutorial
```

While in the project directory, run the following commands to initialise npm in your project directory:

```bash
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:

```bash
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.

```bash
# 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:

```jsx
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:

```bash
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:

```jsx
// 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:

```bash
mkdir db
```

Create a `db.js` file in your `db` folder and import `knex` and your `knexFile.js` file in this manner:

```jsx
const knex = require("knex");
const knexFile = require("../knexfile.js");
```

Next, set up your `environment` variable using the code block below:

```jsx
//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:

```jsx
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:

```jsx
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:

```jsx
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.

```jsx
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:

```jsx
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:

```jsx
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.

```jsx
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:

```jsx
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:

```jsx
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:

```jsx
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:

```jsx
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` 

```jsx
module.exports = router
```

## Testing Your Application

Navigate to your `index.js`, import your `router` and add it as a middleware in this manner:

```jsx
//index.js
const todoRouter = require("./routes/todo.js");

app.use(todoRouter);
```

Then, start up your application by running the command below:

```jsx
node index.js
```

To test your application, you can use tools such as [Postman](https://www.postman.com/) 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.