Building a REST API using NodeJS, ExpressJS, and CockroachDB
Tags: restapi, node, cockroachdb, express
Published: 2023-09-15 11:56:14

In today's technology-driven world, there is a high demand for scalable and high-performance web applications. As a result, developers are constantly exploring new technologies and frameworks to simplify building robust and user-friendly applications.

With [Node.js](https://nodejs.org/en), [Express](https://expressjs.com/), and [CockroachDB](https://www.cockroachlabs.com/), developers have a powerful combination for backend development, offering a seamless and adaptable environment for creating high-performing REST APIs.

In this tutorial, we will build a robust REST API to store and manage a book's essential details, such as the title, author, ISBN, and page count.


## Prerequisites 
This article assumes that you have a basic knowledge of the following: 
- [NodeJS](https://nodejs.org/en)
- [Express](https://expressjs.com/)
- [SQL](https://docs.oracle.com/cd/E12151_01/index.html)

**TL;DR**: The code for this tutorial is on [GitHub](https://github.com/backendbro/node-express-cockroachdb-api). 


## Project Setup
First, create the following files and folders: 

    book-api
     ┣ controller
     ┃ ┗ Book.js
     ┣ database
     ┃ ┗ DB.js
     ┣ routes
     ┃ ┗ Book.js
     ┣ schema
     ┃ ┗ BookSchema.js
     ┣ config.env
     ┗ server.js

Below is a visual illustration of the files and folders needed for this project. 

![project-file-and-folders](https://paper-attachments.dropboxusercontent.com/s_7DF15D909CDD70E893AC300C2AAADBCA4D2B3D2522D62EE445C03FF0A660A54C_1691586098342_17.PNG)

To continue, run the following command in the terminal to initialize the working directory.

```
npm init
```
Fill in the necessary information when prompted by this command, as shown below, then press ENTER.


![The_initilization_of_a_node_directory](https://paper-attachments.dropboxusercontent.com/s_7DF15D909CDD70E893AC300C2AAADBCA4D2B3D2522D62EE445C03FF0A660A54C_1691586808373_18.PNG)


Once completed, a `package.json` file will be generated. This file contains vital information and scripts that run and tests the application. 
Update the `package.json` file with the following script: 

```

"scripts": {
    "start": "nodemon server.js"
}

```


**Visual illustration below:**
![package-json](https://paper-attachments.dropboxusercontent.com/s_932BF3714B30F54A55B42E1919E6FF9C9A6FA101F3D9500995880C65C4E89FEB_1691233754967_15.PNG)



## Installing Dependencies
Run the following command to install the necessary dependencies.
```
npm install  express sequelize sequelize-cockroachdb dotenv
```

Quick note:

- **[Express](https://expressjs.com/)**:  Express is a NodeJS framework used to create web applications and REST APIs.
- **[Sequelize](https://sequelize.org/)**: Sequelize is an ORM widely used by NodeJS developers to manage relational databases. This eliminates the need for writing raw SQL. 
- **[Sequelize-cockroachdb](https://www.npmjs.com/package/sequelize-cockroachdb)**: This ORM is specific to CockroachDB. 
- **[Dotenv](https://www.npmjs.com/package/dotenv)**: Dotenv loads data from the `config.env`  file into the NodeJS environment. By so doing, one can access those data using `process.env`. Dotenv makes an application more maintainable and more secure.

Lastly, install the `nodemon` dependency. 
```
npm install nodemon -g
```
 
- **[Nodemon](https://www.npmjs.com/package/nodemon)**: Nodemon automatically restarts a server whenever one saves a file. I omitted Nodemon in the first command because of the `-g` flag. The flag instructs NodeJS to install Nodemon as a global dependency. By so doing, one does not have to install nodemon ever again. 

> If you only need to use Nodemon once, you can omit the `-g` flag.


## Creating a CockroachDB Cluster. 

**What is CockroachDB?**
CockroachDB is a distributed SQL database management system. CockroachDB is designed to handle transactional workloads with low latency and high throughput. CockroachDB's high availability and fault tolerance ensures it can operate even if a node, zone, or region fails. 

CockroachDB offers the scalability of NoSQL databases and the relational model of SQL databases, making it ideal for web applications that require flexibility and reliability. Its high compatibility makes it easy to port PostgreSQL applications to CockroachDB with minimal code changes.

Additionally, CockroachDB offers a high level of data consistency. CockroachDB is used by companies like — [Comcast](https://corporate.comcast.com/), [Digital Ocean](https://www.digitalocean.com/), and [Bose](https://www.bose.com/home).

Moving on, Follow these simple steps to set up a CockroachDB cluster.

1. Create a [CockroachDB ](cockroachlabs.cloud)account or sign in if you already have one.


2. After signing in, click Create Cluster.
![cockroachdb-signin-page](https://paper-attachments.dropboxusercontent.com/s_A7569BFF41097619026F9F5261A05379FCDB873934374FB7848E48B14D753391_1691301744725_7.png)



3. Select a preferred plan and click Create Cluster.
![cockroachdb-price-plan](https://paper-attachments.dropboxusercontent.com/s_932BF3714B30F54A55B42E1919E6FF9C9A6FA101F3D9500995880C65C4E89FEB_1690905401220_1+2.PNG)




4. The CockroachDB cluster is currently being processed. Wait patiently while it completes.
![cockroachdb-loading-screen](https://paper-attachments.dropboxusercontent.com/s_932BF3714B30F54A55B42E1919E6FF9C9A6FA101F3D9500995880C65C4E89FEB_1690906072845_3+3.PNG)



5. Create an SQL user and password. These credentials will be necessary to connect to the newly created cluster from the application. 
![cockroachdb-sql-user](https://paper-attachments.dropboxusercontent.com/s_932BF3714B30F54A55B42E1919E6FF9C9A6FA101F3D9500995880C65C4E89FEB_1691236841878_tempsnip2.png)



6. Safely store the credentials and click Next.
![cockroachdb-credentials](https://paper-attachments.dropboxusercontent.com/s_932BF3714B30F54A55B42E1919E6FF9C9A6FA101F3D9500995880C65C4E89FEB_1691236877970_tempsnip3.png)



7. Voila! We have successfully created a CockroachDB cluster.
![cockroachdb-cluster](https://paper-attachments.dropboxusercontent.com/s_932BF3714B30F54A55B42E1919E6FF9C9A6FA101F3D9500995880C65C4E89FEB_1691236895118_tempsnip4.png)




### Retrieving the CockroachDB Connection String.
This section will demonstrate the steps to retrieve the connection string.

1. Log in to your [CockroachDB ](https://cockroachlabs.cloud/) account. 

2. Click the Connect button in the top right corner. 
![cockroachdb-cluster](https://paper-attachments.dropboxusercontent.com/s_932BF3714B30F54A55B42E1919E6FF9C9A6FA101F3D9500995880C65C4E89FEB_1690975941733_7.PNG)



3. Scroll down and copy the connection string. 
![cockroachdb-connection-string](https://paper-attachments.dropboxusercontent.com/s_932BF3714B30F54A55B42E1919E6FF9C9A6FA101F3D9500995880C65C4E89FEB_1691236378862_tempsnip.png)


## Building the app components 

Open the `config.env` file and set the following environment variables.

```
PORT = 8000
connectionString = postgresql://backendbro:<ENTER-SQL-USER-PASSWORD>@stone-muskox-9285.8nj.cockroachlabs.cloud:26257/backendbro?sslmode=verify-full
```

>  Make sure to paste your connection string. 

Copy and paste the code below into the `server.js` file:
```
const express = require('express')
const dotenv = require('dotenv')

dotenv.config({path:"./config.env"})
const app = express()

app.use(express.json())
app.use(express.urlencoded({extended:false}))

const PORT = process.env.PORT 
const server = app.listen(PORT, () => {
    console.log(`Server started on 127.0.0.1:${PORT}`)
})

process.on("unhandledRejection", (err) => {
    console.log(err.message)
    server.close(() => process.exit())
})
```

In the code snippet above: 

- We imported some of the dependencies installed earlier.
- We loaded the environment variables stored in `config.env` into `process.env`.
- Next, we instantiated the Express app. 
- The following two lines of code are Express middlewares that enable us to retrieve the body of an HTTP request.
- Afterward, we set our server to listen for connections on `127.0.0.1` and the designated port number.
- Finally, we requested Node to shut down the server if a promise rejection is not handled.

To ensure everything is set up correctly, use the following command to run the application.

```
npm run start
```
**Create a CockroachDB Connection**
Next, open the `DB.js` file to establish a connection with the CockroachDB cluster.
```
const Sequelize = require('sequelize-cockroachdb')
const dotenv = require('dotenv')
    
dotenv.config({path:"./config.env"})
const sequelize = new Sequelize(process.env.connectionString, {logging:false})
    
module.exports = sequelize
```
In the code snippet above:

- We imported the required dependencies and loaded the environment variables into the `process.env` 
- Next, we established a connection to our database. 
- Finally, we exported the database connection.

**Creating the Book Inventory Schema.** 
Navigate to the `BookSchema.js` file and make the necessary updates with the code provided below:
```
const Sequelize = require('sequelize-cockroachdb')
const sequelize = require('../database/DB.js')
    
    const BookInventory = sequelize.define("BookInventory", {
        id:{
            type:Sequelize.DataTypes.INTEGER,
            autoIncrement:true,
            primaryKey:true
        },
        name:{
            type:Sequelize.DataTypes.TEXT,
            allowNull: false,
            unique: true
        },
        author:{
            type:Sequelize.DataTypes.TEXT,
            allowNull: false,
        },
        isbn:{
            type:Sequelize.DataTypes.INTEGER,
            allowNull: false,
            unique: true
        },
        pageCount:{
            type:Sequelize.DataTypes.INTEGER,
            allowNull:false
        }
   })
    
module.exports = BookInventory;
```
In the code snippet above: 

- We used the `define` method to create a Schema, which enables us to store the specified information of a book.
- Lastly, we exported the newly created Schema for later use.


> Sequelize automatically manages timestamps. 

 **Creating the Controllers.**
In this subsection, we'll write code to handle the requests and responses of the application. Navigate to the `controller` folder and update the `Book.js` file with the code below:

To begin, import the Schema we created in the previous subsection.

    const BookInventory = require('../schema/BookSchema.js')

**CREATE BOOK:**
```
    const createBook = async (req,res) => {    
        let book = await BookInventory.sync({force: false})
        book = await BookInventory.create(req.body)
                    .catch(err => res.status(500).json({message: err.message}))
        
        res.status(201).json({data: book})
    }
```
In the code snippet above: 

- The `BookInventory.sync` method creates a new table based on the model's schema. By setting `force:false`, sequelize avoids deleting the table and only update it.
- The `BookInventory.create` method allows us to insert our book data into the database.
- Finally, we return the response. 


**TO GET ALL BOOKS**
```
    const getAllBooks = async (req,res) => {
        const books = await BookInventory.findAll()
                     .catch(err => res.status(500).json({message: err.message}))
                     
        res.status(200).json({length:books.length, data:books})
     }
```
In the code snippet above: 

- We retrieve and return all the books that are stored in the database.


**TO GET SINGLE BOOK**
   ```
 const getSingleBook = async (req,res) => {
        const {id} = req.params 
        
        const book = await BookInventory.findOne({ where: {id} })
                    .catch(err => res.status(500).json({response: err.message})) 
        
        res.status(200).json({data:book})
    }
```
In the code snippet above: 

- We destructured the `id` passed into the request parameter.
-  We queried the database for any entry that match the specified ID and returned the response.


**TO UPDATE A BOOK** 
```
    const updateBook = async (req,res) => {
        const {id} = req.params
        let book = await BookInventory.findOne({ where: {id} })
        .catch(err => res.status(500).json({message: err.message}))     
        
        if(!book) {
            return res.status(404).json({message:`No book found with ID: ${id}`})
        }
        
        book = await book.update(req.body)
        res.status(200).json({data:book})
    }
```
In the code snippet above: 

- We updated the details of a book that matches the specified ID.


**TO DELETE A SINGLE BOOK**
```
    const deleteSingleBook = async (req,res) => {
        const {id} = req.params 
        await BookInventory.destroy({ where: {id}})
                    .catch(err => res.status(500).json({message: err.message}))
        
        res.status(202).json({data:"Book deleted"})
    }
```
In the code snippet above: 

- We deleted the book that matches the specified ID.


**TO DELETE ALL BOOKS**
```
    const deleteAllBooks = async (req,res) => {
        await BookInventory.destroy({ where: {} })
                .catch(err => res.status(500).json({message: err.message}))
        
        res.status(202).json({data:"Books deleted"})
    }
```
In the code snippet above:

- We deleted all books stored in the database. 

Finally, export the controllers. 
```
    module.exports = {
        createBook, 
        getAllBooks,
        getSingleBook,
        updateBook,
        deleteSingleBook,
        deleteAllBooks
    } 
```

**Creating the Routes**
Let’s create the endpoints to send requests and receive a response. Navigate to the routes folder and open the `Book.js` file. Update the file with the following code: 

```
    const router = require('express').Router()
    const {
        createBook, 
        getAllBooks,
        getSingleBook,
        updateBook,
        deleteSingleBook,
        deleteAllBooks
    } = require('../controller/Book.js')
    
    router.post('/create', createBook)
    router.get('/getAllBooks', getAllBooks)
    router.get("/getSingleBook/:id", getSingleBook)
    router.put('/updateBook/:id', updateBook)
    router.delete('/deleteSingleBook/:id', deleteSingleBook)
    router.delete('/deleteAllBooks', deleteAllBooks)
    
    module.exports = router 
```
In the code snippet above: 

- We created a new router object to handle requests. 
- Next, we imported the controllers and associated them with a specified method and URL.
- Lastly, we exported the router object. 


**Rounding up**
Return to the `server.js` file and import the router object. 

```
const express = require('express')
 const dotenv = require('dotenv')
    
    dotenv.config({path:"./config.env"})
    const app = express()
    
    app.use(express.json())
    app.use(express.urlencoded({extended:false}))
    
    // router object
    const bookRoute = require('./routes/Book.js')
    app.use('/api/v1', bookRoute)
    
    const PORT = process.env.PORT 
    const server = app.listen(PORT, () => {
        console.log(`Server started on 127.0.0.1:${PORT}`)
    })
    
    process.on("unhandledRejection", (err) => {
        console.log(err.message)
        server.close(() => process.exit())
    })
```
    

## Conclusion

In conclusion,  building a REST API with Node.js, Express, and CockroachDB provides developers with an effective solution for developing robust and scalable web applications. 


## Resources
- [CockroachDB Documentation ](https://www.cockroachlabs.com/docs/stable)
- [CockroachDB: The Definitive Guide by Guy Harrison, Jesse Seldess, Ben Darnell](https://www.oreilly.com/library/view/cockroachdb-the-definitive/9781098100230/)
- [Sequelize Documentation](https://sequelize.org/docs/v6/getting-started/)