Cloudinary and Postgresql - Persisting and Retrieving Images Using Nodejs
Tags: node, postgres, cloudinary, tutorial
Published: 2020-04-28 07:44:44

In a [previous article](https://dev.to/ebereplenty/image-upload-to-cloudinary-with-nodejs-and-dotenv-4fen), we saw how we can upload images to Cloudinary using nodejs. It is however not so useful as we are not able to easily retrieve it especially when we want to use it in a project. In this article, we will be looking at how we can persist and retrieve images using postgres.

## Table of Content
[Create Database and Table](#1)
[Create APIs](#2)
[Persist Image](#3)
[Retrieve Image](#4)
[Conclusion](#5)

If you have not used postgres before, I suggest you start [here](https://dev.to/ogwurujohnson/-persisting-a-node-api-with-postgresql-without-the-help-of-orms-like-sequelize-5dc5). We are not using any ORM library so we will write our own queries.

**At this point, I want to say a big Congratulations for getting up to this stage.**

![Alt Text](https://media.giphy.com/media/1wmcC8Gygz9MOrCul9/giphy.gif)

The next stage is to create database and a table. If you want to skip it to [creating APIs](#2), clone this [repo](https://github.com/EBEREGIT/server-tutorial/tree/create-database-connetion-and-table). Let's Proceed.

<a name="1"></a>
## Create Database and Table

1. So we want to start by cloning the [previous project](https://github.com/EBEREGIT/server-tutorial/tree/cloudinary-upload) if you don't already have it [here](https://github.com/EBEREGIT/server-tutorial/tree/cloudinary-upload).

2. In your **pgAdmin**
  * Create a database and name it: ``tutorial``
  * Create a table and name it: ``tutorial``
  * Create a Login/Group Role and name it: ``tutorial``. **(Do not forget to give it all privileges)**

3. Back in your project directory, install the [node-postgres](https://node-postgres.com/) (``npm install pg --save``) and [make-runnnable](https://www.npmjs.com/package/make-runnable) (``npm install make-runnable --save``) packages

4. In ``package.json`` file, replace the content of the ``"scripts"`` with ``"create": "node ./services/dbConnect createTables"``. This will be used to execute the ``dbConnect`` file we are about to create.

5. Create a ``services/dbConnect`` file to contain the following code

```javascript

const pg = require("pg");

const config = {
  user: "tutorial",
  database: "tutorial",
  password: "tutorial",
  port: 5432,
  max: 10, // max number of clients in the pool
  idleTimeoutMillis: 30000,
};

const pool = new pg.Pool(config);

pool.on("connect", () => {
  console.log("connected to the Database");
});

const createTables = () => {
  const imageTable = `CREATE TABLE IF NOT EXISTS
    images(
      id SERIAL PRIMARY KEY,
      title VARCHAR(128) NOT NULL,
      cloudinary_id VARCHAR(128) NOT NULL,
      image_url VARCHAR(128) NOT NULL
    )`;
  pool
    .query(imageTable)
    .then((res) => {
      console.log(res);
      pool.end();
    })
    .catch((err) => {
      console.log(err);
      pool.end();
    });
};

pool.on("remove", () => {
  console.log("client removed");
  process.exit(0);
});

//export pool and createTables to be accessible  from an where within the application
module.exports = {
  createTables,
  pool,
};

require("make-runnable");

```

* Now we are all set to create the table in our database. If you are ready, let's rock and roll!

* Execute the following code in your terminal

```javascript

  npm run create

```
* If the image below is your result, then you are good to go

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/fjk5ty113j5p7kaxveqc.JPG)

* Check Your **pgAdmin**, and you should have your table seated properly in your database like in the image below

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/sj7gy6d86vm7ay3d8a74.JPG)

Wow!!! It's been a long road! It's time to unite Nodejs, Postgres and Cloudinary!

![Alt Text](https://images.unsplash.com/photo-1582578598774-a377d4b32223?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=751&q=80)

<a name="2"></a>
## Create 2 APIs

<a name="3"></a>
### API 1: Persist Image
* Require the ``dbConnect.js`` file on the top of the ``app.js`` file like so:

```javascript
  const db = require('services/dbConnect.js');
```

* In the ``app.js`` file, make a new API *(persist-image)* with the following code:

```javascript

// persist image
app.post("/persist-image", (request, response) => {
  // collected image from a user
  const data = {
    title: request.body.title,
    image: request.body.image,
  }

  // upload image here
  cloudinary.uploader.upload(data.image)
  .then().catch((error) => {
    response.status(500).send({
      message: "failure",
      error,
    });
  });
})

```

* Replace the ``then`` block with the following code:

```javascript

.then((image) => {
    db.pool.connect((err, client) => {
      // inset query to run if the upload to cloudinary is successful
      const insertQuery = 'INSERT INTO images (title, cloudinary_id, image_url) 
         VALUES($1,$2,$3) RETURNING *';
      const values = [data.title, image.public_id, image.secure_url];
    })
  })

```
*If you have gone through the prerequisite to this tutorial, then you should know where we are getting ``result.public_id`` and ``result.secure_url``. If you didn't follow, please go through this [tutorial](https://dev.to/ebereplenty/image-upload-to-cloudinary-with-nodejs-and-dotenv-4fen)*

* Still in the ``then`` block, add the following code under the **query** we created

```javascript

// execute query
client.query(insertQuery, values)
      .then((result) => {
        result = result.rows[0];

        // send success response
        response.status(201).send({
          status: "success",
          data: {
            message: "Image Uploaded Successfully",
            title: result.title,
            cloudinary_id: result.cloudinary_id,
            image_url: result.image_url,
          },
        })
      }).catch((e) => {
        response.status(500).send({
          message: "failure",
          e,
        });
      })

```
* So our ``persist-image`` API now looks like this:

```javascript

// persist image
app.post("/persist-image", (request, response) => {
  // collected image from a user
  const data = {
    title: request.body.title,
    image: request.body.image
  }

  // upload image here
  cloudinary.uploader.upload(data.image)
  .then((image) => {
    db.pool.connect((err, client) => {
      // inset query to run if the upload to cloudinary is successful
      const insertQuery = 'INSERT INTO images (title, cloudinary_id, image_url) 
         VALUES($1,$2,$3) RETURNING *';
      const values = [data.title, image.public_id, image.secure_url];

      // execute query
      client.query(insertQuery, values)
      .then((result) => {
        result = result.rows[0];

        // send success response
        response.status(201).send({
          status: "success",
          data: {
            message: "Image Uploaded Successfully",
            title: result.title,
            cloudinary_id: result.cloudinary_id,
            image_url: result.image_url,
          },
        })
      }).catch((e) => {
        response.status(500).send({
          message: "failure",
          e,
        });
      })
    })  
  }).catch((error) => {
    response.status(500).send({
      message: "failure",
      error,
    });
  });
});

```
**Now let's test out all our hard works**

* Open your *postman* and test out your API like the image below. Mine was successful. Hope yours had no errors too?

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/euxsoyb821v85uv5rhml.JPG)

* Open your *cloudinary* console/dashboard and check your ``media Library``. Your new Image should be seating there comfortably like mine below:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/dxy5fl8fodqpn89oltzh.JPG)

* And Now to the main reason why we are here, check the ``images`` table in your **pgAdmin**. Mine is what you see below

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ypv6vcrocsgp13owq5dv.JPG)

Uhlala!!! We made it this far! Please take a break if you need one. I will be here waiting when you return.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/g50o3rufchz16psqmqpm.jpg)

If you are ready, then let's retrieve the image we persisted a moment ago


<a name="4"></a>
### API 2: Retrieve Image
* Start with this code 

```javascript

app.get("/retrieve-image/:cloudinary_id", (request, response) => {

});

```

* Next, we will need to collect a unique ID from the user to retrieve a particular image. So add ``const { id } = request.params;`` to the code above like so:

```javascript

app.get("/retrieve-image/:cloudinary_id", (request, response) => {
  // data from user
  const { cloudinary_id } = request.params;

});

```

* Add the following below the code above

```javascript

db.pool.connect((err, client) => {
      // query to find image
    const query = "SELECT * FROM images WHERE cloudinary_id = $1";
    const value = [cloudinary_id];
    });

```

* Under the query, execute the query with the following code

```javascript

// execute query
    client
      .query(query, value)
      .then((output) => {
        response.status(200).send({
          status: "success",
          data: {
            id: output.rows[0].cloudinary_id,
            title: output.rows[0].title,
            url: output.rows[0].image_url,
          },
        });
      })
      .catch((error) => {
        response.status(401).send({
          status: "failure",
          data: {
            message: "could not retrieve record!",
            error,
          },
        });
      });

```

Now our ``retrieve-image`` API looks like this:

```javascript

app.get("/retrieve-image/:cloudinary_id", (request, response) => {
  // data from user
  const { cloudinary_id } = request.params;
  
  db.pool.connect((err, client) => {
    // query to find image
    const query = "SELECT * FROM images WHERE cloudinary_id = $1";
    const value = [cloudinary_id];

    // execute query
    client
      .query(query, value)
      .then((output) => {
        response.status(200).send({
          status: "success",
          data: {
            id: output.rows[0].cloudinary_id,
            title: output.rows[0].title,
            url: output.rows[0].image_url,
          },
        });
      })
      .catch((error) => {
        response.status(401).send({
          status: "failure",
          data: {
            message: "could not retrieve record!",
            error,
          },
        });
      });
  });
});

```

**Let's see how well we did**
* In your postman, copy the "cloudinary_id" and add it to the URL like in the image below

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/9bmutxbmitgznqnnwmny.JPG)

YEEESSS!!! We can also retrieve our image!

If you are here, then you deserve a round of applause and a standing ovation for your industriousness.

![Alt Text](https://media.giphy.com/media/tODygE8KCqBzy/giphy.gif)

Congratulations! You just reached a great milestone.

<a name="5"></a>
### Conclusion
It has been a beautiful session with you as we started out by creating a database and table in our **pgAdmin** after which we proceeded to create an API to upload our image to cloudinary and create a record of it in our database. Finally, we created an API to retrieve our record from our database.

All codes can be found [here](https://github.com/EBEREGIT/server-tutorial/tree/create-APIs)

{% github EBEREGIT/server-tutorial %}

Congratulations once more...

Up Next, we will be looking at [Updating and deleting images from cloudinary using postgres and nodejs](https://dev.to/ebereplenty/cloudinary-and-postgresql-deleting-and-updating-images-using-nodejs-l8d). We will also clean up our code base.

If you have questions, comments or suggestions, please drop them in the comment section.

You can also follow and message me on social media platforms. 

**[Twitter](https://twitter.com/eberetwit) | [LinkedIn](https://www.linkedin.com/in/samson-ebere-njoku-profile/) | [Github](https://github.com/EBEREGIT)**

Thank You For Your Time.