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
1 2 3 4 5 |
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
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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.
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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
1 2 3 4 5 6 7 8 9 10 |
// 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; } |