How to Insert Blob Data in MySQL Using Node js Express

In this tutorial, you will learn how to insert blob data in the MySQL database using node js express js.

Node Express JS Insert Blob Data in MySQL Database

Here are steps to create a table with LONGBLOB data type to store the file’s binary data in MySQL database:

Step 1: Setting Up Node JS Project

First of all, create one directory, open the terminal or cmd, and navigate by running the following command:

cd / your-created-directory

Initialize a new Node.js project by running the following command:

npm init -y

Then run the following command on terminal or cmd to install Express and the MySQL package:

npm install express mysql

Step 2: Set Up the Database Connection

Now, visit your node js express project directory and create an index.js file in the project directory.

Then you need to setup database a connection to the MySQL database. So, open your index.js file in any text editor and add the following code to your index.js file:

const express = require('express');
const mysql = require('mysql');

const app = express();

// Create a MySQL connection
const connection = mysql.createConnection({
  host: 'localhost', // Replace with your MySQL host
  user: 'your_username', // Replace with your MySQL username
  password: 'your_password', // Replace with your MySQL password
  database: 'your_database', // Replace with your MySQL database name
});

// Connect to the MySQL server
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err);
    return;
  }
  console.log('Connected to MySQL!');
});

// Start the Express server
app.listen(3000, () => {
  console.log('Server is running on port 3000');
});

Step 3: Creating a Table for Storing Blob Data

Create a table in the MySQL database to store our Blob data:

// Create a table for Blob data
connection.query(
  `CREATE TABLE IF NOT EXISTS blobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data LONGBLOB
  )`,
  (err) => {
    if (err) {
      console.error('Error creating table:', err);
      return;
    }
    console.log('Table created successfully!');
  }
);

Step 4: Read the File and Insert BLOB Data

Create a route in the index.js file to handle the POST request for inserting the Blob data to MySQL database:

// Route to handle Blob insertion
app.post('/blob', (req, res) => {
  const blobData = req.body.data; // Assuming the Blob data is sent as 'data' field in the request body

  // Insert the Blob data into the database
  connection.query('INSERT INTO blobs SET ?', { data: blobData }, (err, result) => {
    if (err) {
      console.error('Error inserting Blob data:', err);
      res.status(500).json({ error: 'Failed to insert Blob data' });
      return;
    }
    console.log('Blob data inserted successfully!');
    res.json({ message: 'Blob data inserted successfully!' });
  });
});

Step 5: Test the File Upload and BLOB Insertion

To test blob data insertion, you can use Postman or curl to send a POST request with http://localhost:3000/blob url.

Note that:- Make sure to set proper header and content type as per your requirements.

Conclusion

In this tutorial, you learned how to insert Blob data into MySQL using Node.js and Express.

Recommended Tutorials