Node.js Tutorial to Insert & Read Image BLOB File in MySQL Database Using mysql Library in Javascript

Welcome folks today in this blog post we will be inserting and reading blob data file from mysql database in node.js using mysql library in javascript. All the full source code of the application is shown below.

Get Started

In order to get started you need to initialize an empty directory and inside it we need to create a package.json file for the node.js project by executing the below commands

npm init -y

Now this command will create the package.json file inside the root directory.

And now we need to install the dependencies which are required for this project. For this we will need the mysql dependency as shown below

npm i mysql

As you can see we have installed the mysql driver library for connecting to the mysql database.

Connecting to MySQL Database

Now for creating the database you will need the xammp control panel and then inside it you need to first of all start the software as shown below and then we need to execute the sql code to create the database and the table as shown below

Now we need to create the mysql database which is blob as shown below

Now you need to copy paste the below sql code inside the sql section to create the table as shown below

CREATE TABLE `bindata` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `data` BLOB,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `bindata` (

`id` INT NOT NULL AUTO_INCREMENT,

`data` BLOB,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

As you can see we have created the table and inside it we have two fields which is the id which is the primary key and then we have the data field which is an actually BLOB field.

Node.js Code

Now we will be writing the node.js code to make the connection to the mysql database and insert the blob file as shown below

const fs = require("fs");
const mysql = require("mysql");

1 2	const fs = require("fs"); const mysql = require("mysql");

As you can see we are importing the fs and mysql modules at the very top of the node.js script. fs module is needed for reading and writing files at the local file system and also mysql module is needed for connecting to the mysql database.

pool = mysql.createPool({
  "connectionLimit": 10,
  "host": "localhost",
  "port": 3306,
  "user": "root",
  "password": "",
  "database": "blob",
  "timezone": 'utc',

  queryFormat: function(query, values) {
    if (!values) return query;
    return query.replace(/\:(\w+)/g, function(txt, key) {
      if (values.hasOwnProperty(key)) {
        return this.escape(values[key]);
      }
      return txt;
    }.bind(this));
  }
});

pool = mysql.createPool({

"connectionLimit": 10,

"host": "localhost",

"port": 3306,

"user": "root",

"password": "",

"database": "blob",

"timezone": 'utc',

queryFormat: function(query, values) {

if (!values) return query;

return query.replace(/\:(\w+)/g, function(txt, key) {

if (values.hasOwnProperty(key)) {

return this.escape(values[key]);

}

return txt;

}.bind(this));

}

});

As you can see we have creating the connection by calling the createPool() method of the mysql module. And inside this method we are providing the details of the mysql database such as the hostname, username and password. And also we are providing the database name as well. We are also providing the port number as well. And then we are also writing the queryFormat method inside that we are writing the sql statement to insert and read blob data from the database.

const inputfile = "profile.jpg";
const outputfile = "output.png";

1 2	const inputfile = "profile.jpg"; const outputfile = "output.png";

As you can see we are providing the input image file path. This image file need to be present inside the root directory. Input file will be inserted into the mysql database and output file will be created dynamically after we read the inserted blob image data from the mysql database.

Inserting Blob Image File in MySQL Database

Now we will be inserting the image blob file inside the database using the query() method as shown below

pool.query("INSERT INTO `bindata`(data) VALUES(BINARY(:data))", { data }, function(err, res) {
  if (err) throw err;
  console.log("BLOB data inserted!");
  // Check to read it from DB:
  pool.query("select * from `bindata`", function(err, res) {
    if (err) throw err;
    const row = res[0];
    // Got BLOB data:
    const data = row.data;
    console.log("BLOB data read!");
    // Converted to Buffer:
    const buf = new Buffer(data, "binary");
    // Write new file out:
    fs.writeFileSync(outputfile, buf);
    console.log("New file output:", outputfile);
  });
});

pool.query("INSERT INTO `bindata`(data) VALUES(BINARY(:data))", { data }, function(err, res) {

if (err) throw err;

console.log("BLOB data inserted!");

// Check to read it from DB:

pool.query("select * from `bindata`", function(err, res) {

if (err) throw err;

const row = res[0];

// Got BLOB data:

const data = row.data;

console.log("BLOB data read!");

// Converted to Buffer:

const buf = new Buffer(data, "binary");

// Write new file out:

fs.writeFileSync(outputfile, buf);

console.log("New file output:", outputfile);

});

As you can see we are calling the query() method and inside it we are using the INSERT statement to insert blob data inside the database. After the process of insertion is successful we are then reading the inserted image file and saving it inside the root directory with the name of output.png

As you can see if you check the mysql database you can see the blob image file is inserted inside the table as shown below

Reading Buffer of Image File

Now we can read the buffer data of the image file path inside the node.js and javascript as shown below

// Read buffer of an image file:
const data = readImageFile(inputfile); // `data`'s type is Buffer
console.log(data)

function readImageFile(file) {
  // read binary data from a file:
  const bitmap = fs.readFileSync(file);
  const buf = new Buffer(bitmap);
  return buf;
}

// Read buffer of an image file:

const data = readImageFile(inputfile); // `data`'s type is Buffer

console.log(data)

function readImageFile(file) {

// read binary data from a file:

const bitmap = fs.readFileSync(file);

const buf = new Buffer(bitmap);

return buf;

}