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

foreign we will be inserting a image blob object inside our mySQL database so this we will be doing it inside node.js and JavaScript so basically you can see that this is my exam control panel here so you need to have this software installed on your machine right here and inside this software we have this PHP my admin interface where you can create your mySQL database you can create tables you can create these databases so inside my database blob you can see that this is the table that I have created here and it contains these two Fields out there let me just show you from scratch foreign so we have two columns out there first is the ID field which is the primary key of the table and secondly we are storing The Blob object data so specifically we are storing the image file blob object this can be video file PDF file any file that you want to store it inside my SQL and if I show you the code here and inside this node.js application so you can see we are simply inserting this profile dot jpg image you can see that we are inserting this blob image inside the mySQL database and also after the successful insertion we are also reading it this blob object which is stored inside this table so now let me show you you can see that currently no entry is there and if I run this pH node.j script here you will now see a record will be added node index.js so you can see it is printed out this buffer and blob data insert it and if I refresh it you will now see this blob data is successfully inserted in the mySQL database through the node.js code that we have written and if I click this you can see it is clickable if I click this blob object it will download this as an bin file so basically blob basically it's a binary data file which contains your garbage code such as binary code it is not readable by the human eye so it is understandable by machines computers so if you want to read this blob object we have successfully read this blob object and you can see we have stored this as output.png this is the same image file that we inserted you can see profile.jpg and you can even read it and display it inside the browser itself but we are just saving it as in local directory we'll see output.png so we will be doing both the operations we will be inserting The Blob object and also we will be reading it after that so let's start this tutorial guys so for this tutorial I have given a full blog post on my tutorial which is webninjadeveloper.com so this is my blog website so this is I have written a complete step-by-step blog post where I have explained each instruction in detail here alongside with all the source code is also given so let's start this application so first of all guys what we need to do is that we need to initialize a empty package.json file and PM in a dash file and after this you need to install this module which is my sequel so basically this is actually a driver module which allows you to connect to mySQL database so if you go to npmgs.com you will see this module that I'm talking about it is my sequel so this is a module I'm talking about is it just a node.js driver which lets you connect to mySQL database it's a very popular module line 48 000 weekly downloads so after this guys what we need to do we inside your index.js we will write the code from scratch so the very first thing we will do you need to store the image file whichever file you need to insert into the database so I'm just inserting this image profile.jpg and also guys you need to first of all create your new database let me create a new database of blob DB and I will create this database and inside this database I need to create a table so for creating this table I will write just a simple SQL statement create table and followed by your table name which we can say that blob or we can simply say bind data you can call this table anything and inside this parentheses you will provide your column names so first we will be inserting the ID which will be an integer not null and also it will be Auto increment you can see that we have Fields out there and secondly we will have the data property which will hold the blob data for us so this will be of type block and this will be prime D key of the table will be the ID field you can see that so just close it and here guys we need to put the engine property so what will be the engine engine is equal to innodb and default care set is equal to UTF eight mb4 so basically this is the table that we have created here so let me just close it sorry we have put wrong slash here you can see this is our table so it contains two Fields out there first is the primary key which is not null it is type integer Auto increment and it solves the primary key of the table and secondly we have got the data feed which is of type blob so as I now click this go button our table will be created if I see you will see the structure of the table it contains two Fields out there first is the ID which is a primary key and secondly we have this blob data object which will actually hold the blob file for us it can be video file image file PDF file text file any file of your choice but specifically here we will be inserting the image file so after doing this process guys now we can connect to the database using node.js we will import a file system module and also we will be importing the MySQL module that we have installed here and right here we just need to create the connection so we will use this create pool method and inside this we need to pass some information so first option will be connection limit so this will be concurrent connections I will put 10 here host name is localhost for mySQL database port number on which port number it runs so you can exactly tell by port number 3306 so this is a port number so you can just provide this port number then we have this username property user property sorry so this will be root and then we have the password property for this password is empty no password is there and then we need to provide the database property here so whichever database you have created mine is blobdb and lastly we have time zone H it is UTC so after you provide all these details guys now we just need to write a simple function here basically which is called as query format so basically whenever you insert any sort of execute any query such as insert any sort of information in the table or reading from the table this function will be executed so it will have the query and the values inside this if no values are provided then we will simply not execute the query like this this is mandatory you need to write this function and then we will have a simple uh this expression regular expression here and basically this is required this is just a logic to execute the query here it is little bit complicated but you need to write this so what I can do is that I can simply copy this method here so you can go to the blog post this method is available so you can simply paste it here you can see that this is actual method which will get called whenever you basically make the insert statement or select statement so this is required so after doing this what we need to do is that basically our input file is stored inside profile.jpg this is a file we need to insert into the database so we will provide the path here of the input file which will be profile.jpg and also the output file name which will get automatically created whenever we read read The Blob object from the database so we will save it as output.png in the same directory so route right right here guys which contains security methods basically here we will insert the blob object here insert into followed by table name so this will be bind data and here we will provide the data here so the data here will be simply here we will provide the values so basically here we need to convert this into binary data so like this we will pick the placeholder variable like this data so so you can see that you after that you need to put a comma here so this also takes a callback function as well so if any sort of error take place or not so here we need to pass our data object here and whichever data we need to insert error and response so basically guys what we are doing inside this insert insert statement we are inserting into this table name that we have created and then we are inserting The Blob data if you see we are inserting this blob data if you closely notice here inside this query brackets we are inserting this data here like this and this data is coming here because of let's suppose you can see that this data is coming because we need to now make a custom function basically this image file that you see right here profile.jpg we need to first of all convert this image file into buffer data so that we can insert into the database as blob object for that we need to first of all construct a custom function which is read image file this will be a custom function we will pass the path here input file here like this and here we can even console log the data here so now we just need to make this function so we will make this function function read image file and it will receive the file as an argument here so we are passing this input file path here you will see profile.jpg so right here guys we need to convert this image into we need to first of all read the binary data and convert to this buffer object so we will construct bitmap and we will read this using file system module of node shares so we will simply call this method read file synchronize and we will pass file here so what it will do guys basically now we just need to convert this into buffer so we will construct a buffer variable new buffer and here we will convert this image to buffer here and you simply need to return this buffer here from this function that's it so this is three lines of code here we are first of all reading the binary data and converting into buffer like this so if you now execute your basically if I comment this line here so now what you will see guys basically if I execute the node script here so it will convert this image to this buffer here you will see this is binary buffer data which is not readable through the human eye but it is understandable through the computer machine so we will be inserting this buffer data to the as in Blob object inside our mySQL database so now we simply need to insert this so we are just writing we are passing this data buffer object here like this inside the table so after doing this we will simply here we will check for any sort of error if any sort of error takes place we will throw this error like this if no error take place we will read this buffer data like this you will see we will get the response which we have received here you will see response we will get the first entry and now we need to get the blob object so we will construct a data variable so row dot data because you can see data is basically the column name that you have this is a data column name so you need to write the same column name here also you can see here also we are accessing this blob data here and we can just console log a simple message block data read so after getting the blob data now we simply need to convert this blob into buffer so now here we can construct a new object of buffer new buffer and here we will pass the blob data and this is binary data as you all know inside second argument we need to provide that and then we need to write this image file we in order to write inside your local file system you will use this method write file synchronize and based upon this buffer data that you received right here we will construct the image file so we will simply pass the path here where we need to save the file and the buffer data that's it and this will save this file guys so we can simply after that we can simply console login message new file create it and you can also pass the path as well so output file and now basically if you see guys basically and if I execute this node.jscript here so what did you will see in the left hand side if I show you the application so nothing is present right here as I execute the script here and you will see output.png file will be created it is saying cannot read properties of undefined rating data index.js 38 line uh row dot data let me see guys I think table name is bind data this is correct I think there is some kind of if I show you this browse you will now see the blob is successfully inserted you will see that the blob object is successfully inserted here you will see this is a blob object that we constructed so there is some kind of error taking place whenever we try to read it okay okay sorry if we haven't executed the select query that was the problem so here you can see that we do need to create this we just after this we can simply say a console log message that blob data inserted so after this insertion we just need to also execute the select statement which will actually get the data for us so pool query again we will select star from the table name which is bind data and here this will return the error and the response sorry so here you just need to do this process all this process that you see yeah so I will copy this process so basically inside this select statement all this data will be fetched from the database as you all know this select statement so now you can see our result will be there you can see Zero row data all that stuff and now if you see if I execute this so you will see output.png first of all the blob data inserted and then blob data was read from the database and depending upon that our output.png file was constructed so you can even display this image in the browser itself guys you just need to have a simple HTML file in here you can also display that blob file in the browser itself so I can just make another video on that topic but still you can insert blob data inside mySQL database using node shares and also read it so this is a complete tutorial and if you want the full source code regarding this tutorial so you can go to the web ninja developer.com so here I have given all the source code step by step instruction is given so the link is given in the description of this live stream so thank you very much and please hit that like button subscribe the channel and I will be

Channel: Coding Shiksha

Views: 9,500

Keywords: coding shiksha, node upload image to mysql database

Id: 1gPCKcGHiaE

Length: 18min 59sec (1139 seconds)

Published: Tue Nov 15 2022