DEV Community

Cover image for Cloudinary and Postgresql - Persisting and Retrieving Images Using Nodejs
NJOKU SAMSON EBERE
NJOKU SAMSON EBERE

Posted on • Updated on

Cloudinary and Postgresql - Persisting and Retrieving Images Using Nodejs

#node #postgres #cloudinary #tutorial

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.

Alt Text

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");
Enter fullscreen mode Exit fullscreen mode

  • 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
Enter fullscreen mode Exit fullscreen mode
  • If the image below is your result, then you are good to go

Alt Text

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

Alt Text

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

Alt Text

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');
Enter fullscreen mode Exit fullscreen mode
  • 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,
    });
  });
})
Enter fullscreen mode Exit fullscreen mode
  • 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];
    })
  })
Enter fullscreen mode Exit fullscreen mode

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,
        });
      })
Enter fullscreen mode Exit fullscreen mode
  • 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,
    });
  });
});
Enter fullscreen mode Exit fullscreen mode

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

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

Alt Text

  • 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

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

Alt Text

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) => {

});
Enter fullscreen mode Exit fullscreen mode
  • 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;

});
Enter fullscreen mode Exit fullscreen mode
  • 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];
    });
Enter fullscreen mode Exit fullscreen mode
  • 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,
          },
        });
      });
Enter fullscreen mode Exit fullscreen mode

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,
          },
        });
      });
  });
});
Enter fullscreen mode Exit fullscreen mode

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

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

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

GitHub logo 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

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.

Top comments (1)

Collapse
 
okuro3499 profile image
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/+254 719 240756 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

Read next

railsstudent profile image

Data retrieval with the experimental resource and rxResource functions in Angular 19

Connie Leung -

frederickollinger profile image

Deploy Postgresql with Helm

Frederick Ollinger -

moh_moh701 profile image

Mastering C# Fundamentals: Multidimensional Arrays

mohamed Tayel -

josephciullo profile image

Supercharge Your Web Animations: Optimize requestAnimationFrame Like a Pro

Giuseppe Ciullo -