- As you may have guessed
I'm now back at my maxcoin sample application
and also in this maxcoin directory on my console
in like for MongoDB and redis
I now also have to install a driver
and I will use mysql2 for that.
There is also regular mysql package
but mysql2 is a little bit more modern.
For instance, it's a puts promises.
So now type mysql, npm install mysql2.
This will pull down the driver
and I can already open my main js file
and they will now bring in the mysql backend
because the file already exists
but we have to implement it still.
So copy this line here for the RedisBackened
and just rename it to MySQLBackened
and I also have to change the pass here
to MySQLBackend
and then I have to create this connection functions
so I will simply copy, runRedis and call it runMySQL
and of course want to have the mySQLBackend here
and for that I have to do new MySQLBackend,
and then in the end I the call, mySQLBackend max.
Also the runRedis command shouldn't now be
of course runMySQL.
Okay, that's it from the main js file.
Next let's head into MySQLBackend js
and there I will now
start implementing all the methods that I need.
First I define a member variable
for the database connection.
So in this case, I call it this.connection
we called this, this client before,
but for mysql this follows modern nomenclature
of the driver.
So I add this connection and it's null
and of course I also have to bring in the
mysql drivers so I add on top,
const mysql = require ("mysql2")
and they are now I add /promise
so that we can use the promise API provided by the driver
and then we will already implement the connect function,
and for that,
I add this.connection = await mysql.createConnection
and this takes now a number of
configuration arguments first host
and the host is localhost
next the port
as it's not the default port and it's 3406
and then the user, and to use this root
and our password, this very safe, mypassword,
and in the end, I also have to tell mysql
which database to use and the database name is
as we created it before maxcoin.
In the end I also want to return this connection
just to make sure then
in the other function that everything went fine.
Also now we'll implement this connect function
very simple, a return, this.connection.end.
Next we will implement the insert function.
This is a little bit more exciting.
So add const data = await this.coinAPI.fetch.
So that's the same as we had before.
We have to fetch the data
from the API and now add const sql,
so this is the sql command that we eventually
want to execute.
And here I add INSERT INTO coinvalues.
So that's the database table that we created before
and I want to fill the columns valuedate and coinvalue,
and then I have to add where the values come from.
And for that, I now simply add a question mark
because mysql will later then interpolate that for me
and the, you will see in a second, how this works.
First I prepared the values array
so at const values equals square brackets,
now we have the array here
and then I will iterate over Object.entries,
again, data.bpi
and for each of them, I get an entry
and for each entry I do a values.push
and there I will now push an array element
with a tuple that contains entry zero and entry one.
So this is the date and the coin value.
And in the end and now this is why
we prepared the sql statement
before I can now simply return this.connection.query
the sql statement, and then again
an array with the values
and this will now actually result in a box statement
so this means it will just shoot all values
at once into the database.
You might also wonder why we have again
an array that we pass in with the array
of values and this values array is indeed an array
of erased wrapped in an array.
This is just how the driver expects it.
It also took me a little while to figure that out.
And then we also have to implement the get max function
and this is again, very similar
to what we did for other databases before
because I now return this.connection.query
and the query is now
SELECT * FROM coinvalues ORDER by coinvalues.
So I'm ordering by the value of the coin,
and I'm doing this in descending order
and I'm just returning one record.
So I'm limited to zero, one.
So this means, give me the first record
starting with the index zero, entered it already.
Now what is missing?
This is almost the most code is not a max function.
And again, I'm lazy I head
over to the RedisBackened and they will
they're just copy everything that we have
in there copied over to the max function.
They will first replace redis
with MySQL so that the console output makes sense.
And here is the last one.
And now I can also search
and replace for this lower case redis.
So I look for redis
and I replace it with mysql
and then I select replace all.
Of course, there are a few nuances that are again different.
Let's call the result of the connect method connection.
Then ask if connection,
then we can output successfully connect to mysql.
And in line 57, we get our insert result
and in line 60,
I want to gain output how many records have been inserted,
and for that I have to now
use insert result on the index zero
and there is this effectedRows property.
Next in line 66,
we call getMax, we get the result back
but this will now be a pretty large object
with a lot of meta data.
So to get the actual data,
I now add const row = result
at array index zero and again, array index zero.
So this is a little bit of a nested array.
And in the end I will now return the result
and of course, now I will return the actual row.
So this was a lot of code.
Let's see if this works now.
So I run npm start
and things are looking good
so I get this result of object back,
which is again this maximum value for Bitcoin.
And if we look at the timings
we also see that mysql is also pretty fast.
So the select operation took around 10 milliseconds here
in our example.
In the end, let's also have a quick look
into mysql workbench to actually see this data,
so in maxcoin tables,
click on coinvalues in there
do a right click and do select rows, limit 1000.
And you see the select statement
and below you see the result
and this is our table with all the data.