Cloudinary and Postgresql - Persisting and Retrieving Images Using Nodejs

In a previous article, 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
Create APIs
Persist Image
Retrieve Image
Conclusion

If you have not used postgres before, I suggest you start here. 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.

The next stage is to create database and a table. If you want to skip it to creating APIs, clone this repo. Let's Proceed.

Create Database and Table

1. So we want to start by cloning the previous project if you don't already have it here.

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 (npm install pg --save) and make-runnnable (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
   

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
  

  npm run create

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

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

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

Create 2 APIs

API 1: Persist Image

• Require the dbConnect.js file on the top of the app.js file like so:

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

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

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

.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

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

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

// 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?

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

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

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

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

API 2: Retrieve Image

• Start with this code

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:

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

});

• Add the following below the code above

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

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

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

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.

Congratulations! You just reached a great milestone.

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

EBEREGIT / server-tutorial

This is a tutorial was to teach how to create a simple, secure and robust nodejs server but we have expanded our scope to cloudinary and postgres

Server-tutorial

This is a tutorial was to teach how to create a simple, secure and robust nodejs server but we have expanded our scope to cloudinary and postgres

Full details on how to build out this server is found here.

Full details on how to upload images to cloudinary using nodejs is found here.

Full details on how to persist and retrieve images to cloudinary using nodejs and postgres is found here.

Full details on how to delete and update images to cloudinary using nodejs and postgres is found here.

Full details on Nodejs Code Structure Optimization With Express Routing is found here.

Dependences

• Express
• Cloudinary
• Node
• NPM
• DotENV
• Nodemon
• Node Postgres

SETTING UP

• Fork this repository
• Clone the repositury to your machine
• Open up a terminal
• Navigate into the project directory
• Run npm install to install all needed dependencies
• Run nodemon index to spin…

View on GitHub

Congratulations once more...

Up Next, we will be looking at Updating and deleting images from cloudinary using postgres and nodejs. 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 | LinkedIn | Github

Thank You For Your Time.

Comments

[Gideon Okuro]

I used this method on my local server it works but when i deploy to heroku i get this error {
"message": "failure",
"error": {
"error": {
"errno": -2,
"code": "ENOENT",
"syscall": "open",
"path": "images/[phone] 20180211_213438.jpg"
}
}
}
I also got this error in local server when the path to the file being uploaded was wrong. How do I solve this for the deployed server