Building an API for Uploading Images to Google Cloud Storage and Storing Survey Data in PostgreSQL using node

In this post, we’ll create a robust Node.js API for uploading images to Google Cloud Storage while storing associated survey data in PostgreSQL. This setup is ideal for applications requiring managing media uploads and structured metadata. In modern applications, it's common to work with multimedia data, especially images, along with associated metadata that needs to be stored in a database

Tech Stack

• Node.js: The runtime environment for our application.
• PostgreSQL: The relational database for storing survey details.
• Google Cloud Storage (GCS): A scalable object storage service for uploading and storing images.
• Express.js: The framework for building our API.
• Multer: A middleware for handling multipart/form-data (file uploads).

Prerequisites

To follow along, you’ll need:

1. A Google Cloud Platform (GCP) project with a Cloud Storage bucket.
2. PostgreSQL installed and configured with a sample database.
3. Basic knowledge of Node.js and SQL.

Project Structure

This is the file structure of our project:

my-survey-app/
├── config/
│   ├── dbConfig.js
│   ├── Gcs.js
├── controllers/
│   ├── surveyController.js
├── routes/
│   ├── surveyRoutes.js
├── app.js
├── package.json

Step 1: Set Up Google Cloud Storage (GCS)

First, configure GCS for image uploads. Here’s how:

• Create a bucket in your GCP console.
• Download the service account JSON key and set the GOOGLE_APPLICATION_CREDENTIALS environment variable. In config/Gcs.js:

require('dotenv').config();
const { Storage } = require('@google-cloud/storage');

const storage = new Storage({
  keyFilename: process.env.GOOGLE_APPLICATION_CREDENTIALS
});
const bucketName = process.env.GCP_BUCKET_NAME; 
const bucket = storage.bucket(bucketName);

module.exports = { bucket };

Step 2: Set Up PostgreSQL Database Connection

In config/dbConfig.js:

require('dotenv').config();
const { Pool } = require('pg');

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_DATABASE,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
});

pool.connect()
  .then(() => {
    console.log('Database connected successfully');
  })
  .catch((err) => {
    console.error('Error connecting to the database:', err.message);
  });

module.exports = pool;

Step 3: Creating the Survey API

Here’s the API that handles survey data and uploads images:
In controllers/surveyController.js:

const pool = require('../config/dbConfig');
const { bucket } = require('../config/Gcs');
const { PassThrough } = require('stream');

const InsertProjectSurveyDetails = async (req, res) => {
    const { project_id, user_id, account_id, survey_details } = req.body;
    const imageLists = req.files;

    // Parsing the survey details JSON
    let parsedSurveyDetails;
    try {
        parsedSurveyDetails = JSON.parse(survey_details);
    } catch (error) {
        return res.status(400).json({ Message: 'Invalid JSON format for survey_details' });
    }

    if (!project_id || !user_id || !account_id || !Array.isArray(parsedSurveyDetails) || parsedSurveyDetails.length === 0) {
        return res.status(400).json({ Message: 'Invalid input data' });
    }

    // Validating each survey detail for required fields
    for (const detail of parsedSurveyDetails) {
        const { latitude, longitude, tag, image_id } = detail;
        if (latitude == null || longitude == null || !image_id) {
            return res.status(400).json({ Message: 'Missing latitude, longitude, or image_id in one of the survey details' });
        }
    }

    const insertQuery = `
        INSERT INTO project_survey_details (project_id, user_id, account_id, latitude, longitude, tag, image_name)
        VALUES ($1, $2, $3, $4, $5, $6, $7)
    `;

    const client = await pool.connect();

    try {
        await client.query('BEGIN');

        // Iterate each survey detail and handle image upload and database insertion
        for (const detail of parsedSurveyDetails) {
            const { latitude, longitude, tag, image_id } = detail;

            const imageFile = imageLists.find(img => img.originalname.includes(image_id));
            if (!imageFile) {
                await client.query('ROLLBACK');
                return res.status(400).json({ Message: 'Image not found for provided image_id' });
            }

            const { originalname: imageName, buffer, stream } = imageFile;
            const imageDestination = bucket.file(`Project-Survey-Images/${imageName}`);
            const imageStream = imageDestination.createWriteStream();

            // Handle Buffer or Stream case for image upload
            const uploadStream = stream ? stream : new PassThrough();
            if (buffer) {
                uploadStream.end(buffer);
            } else if (stream) {
                stream.pipe(uploadStream);
            } else {
                await client.query('ROLLBACK');
                return res.status(400).json({ Message: 'No valid stream or buffer found for image' });
            }
            // Upload images to Bucket and handle error
            await new Promise((resolve, reject) => {
                imageStream.on('error', (err) => {
                    reject(err);
                });

                imageStream.on('finish', () => {
                    resolve();
                });

                uploadStream.pipe(imageStream);
            });

            // Construct image's url 
            const url = `https://storage.googleapis.com/${bucket.name}/Project-Survey-Images/${imageName}`;
            await client.query(insertQuery, [project_id, user_id, account_id, latitude, longitude, tag, imageName]);
        }

        await client.query('COMMIT');
        res.status(201).json({ Message: 'Survey details inserted successfully' });

    } catch (error) {
        await client.query('ROLLBACK');
        console.error('Error Inserting survey details:', error);
        res.status(500).json({ Message: 'Error Inserting survey details' });
    } finally {
        client.release();
    }
};

module.exports = { InsertProjectSurveyDetails };

Note:

When dealing with image uploads in Node.js, images can be provided either in buffer form (loaded fully in memory) or stream form (data flows in chunks). Our API handles both formats using the PassThrough stream:

• Buffer Handling: If the image is in buffer format (like in the screenshot below), it is converted into a readable stream using PassThrough.
• Stream Handling: If the image is already a stream, it is directly piped through PassThrough. This flexibility ensures that the image data is processed consistently, regardless of its original format.

Tip: Before implementing this approach, check whether your image contains buffer data or a stream. The API automatically handles both based on the provided content, ensuring robust and efficient processing.

Step 4: Setting Up Routes

In routes/surveyRoutes.js:

const express = require('express');
const multer = require('multer');
const { InsertProjectSurveyDetails } = require('../controllers/surveyController');

const upload = multer(); 
const router = express.Router();

router.post('/survey', upload.array('files'), InsertProjectSurveyDetails);

module.exports = router;

Step 5: Integrating Everything in app.js

const express = require('express');
const surveyRoutes = require('./routes/surveyRoutes');

const app = express();

app.use(express.json());
app.use('/api', surveyRoutes);

const PORT = process.env.PORT || 4000;
app.listen(PORT, () => {
    console.log(`Server is running on port ${PORT}`);
});

Handling Edge Cases

1. Validating Survey Data: Ensure all required fields like latitude, longitude, and image_id are present in each survey detail.
2. Image Upload Handling: Handle cases where the image data might be missing, or the buffer or stream is invalid.
3. Database Transactions: Use PostgreSQL transactions to ensure atomicity. If an error occurs while processing any detail, roll back the transaction.

How the API Works

When sending a request to the API, you provide a list of survey points in a survey_details array. Each survey point contains the following information:

• latitude & longitude: The geographical coordinates where the survey was conducted.
• tag: A label that helps you identify the survey point (like "Survey Point A").
• image_id: A unique identifier used to find the correct image file. The image files are also sent along with the request. Each image’s filename contains this image_id, so we can easily match it with the correct survey point. For example, if the image filename is "66789_survey.jpg", the image_id for that survey point would be 66789.

Why Use image_id?

The image_id is important because it ensures each survey point is linked with the correct image. By extracting the image_id from the image filename, we can precisely match it with the latitude, longitude, and tag you provided. This makes it easier to perform analytics or other operations based on the exact location and the associated images.

How to test the API using Postman

1. Set the request method to POST
2. Under the Body tab, select form-data.
3. Add the following fields:
   • project_id: (text) e.g., 4
   • user_id: (text) e.g., 25
   • account_id: (text) e.g., 2
   • survey_details: (text) The JSON data is shown below:

[
    {
        "latitude": 37.7749,
        "longitude": -122.4194,
        "tag": "Survey Point A",
        "image_id": "66789"
    },
    {
        "latitude": 37.7750,
        "longitude": -122.4195,
        "tag": "Survey Point B",
        "image_id": "66790"
    }
]

Under Files, add the images:
* files: Upload multiple files where each file’s name includes the image_id (e.g., 66789_survey.jpg, 66790_survey.jpg).

Example Postman Request and Response Output

Final Notes

• The API actually handles several search points. You can send as many survey points as necessary by adding multiple items to the survey_details structure.
• Each image is uploaded to Google Cloud Storage and the remaining information is stored in the database, making it easier to analyze locations and tags later.

Comments

[Prabhat kumar Mishra]

Great work absolutely amazing 👍