In this video, the small sample application is extended to use MySQL. Learn which database is the fastest.
- [Instructor] After we create the schema from MySQL we are now ready to extend our test application in MaxCoin. For that I already opened the MaxCoin project and the file test.js in it and first we have to install MySQL driver and we will use MySQL 2 for that, so I will simply type npm install --save mysql2. Next we will require this const mysql equals require mysql2.
Now I scroll all the way down so that we have everything concerning MySQL in one place and here down here after line 89 I add the definition of the connection at const connection equals and the rest is driver function mysql.createConnection and this takes an object this argument and this contains the whole database configuration, so add the host.
This shall be localhost, the port is 3406, the user is root, the password is the password we set before when creating a container so it's mypassword and the database is maxcoin.
Next, let's connect to MySQL, so I add connection.connect. This will establish the database connection. Again we have callback here with a potential error, so I add if (err) throw error. I also want to time this again, so I add console.time('mysql') and if everything went fine here we can let user know console.log('Successfully connected to mysql') and let's also close this connection then, so add connection.end.
Let's try this out real quick. Node test.js and we see here right at the start successfully connected to mysql. Next we have to implement a function that actually does the insert statement into a MySQL. For that I will add a new function right before we create this connection object, add function insertMySQL and this takes a connection some data and a callback and this now is very similar to what we did for Redis, so I will first define an empty array const values and next I will prepare a SQL statement, const sql equals INSERT INTO coinvalues and this are the field's valuedate and coinvalue and we want to pass in the values that we will now mark here with a question mark because what we actually want to do is we want to execute a box statement.
This means we want to insert all values at once into the database with one command because doing so provides the best performance. So, next we have to fill this value array, so I run through the past data object.keys(data) and for each key I have a callback and within this callback we will push values.push and here we will again add square brackets because we create the nested array here and we create the tuple here of key and data(key).
This means the first element is the date and the second element is the value for this given date. Now all that is left to do is we have to execute this query, so I add connection.query and we want to execute the SQL statement that we defined before and then we pass in another array and this array actually contains now again these values and we have a callback here.
So whenever the data you want to insert into MySQL is not too large, so the created array would exceed some memory limits of your system it's really always best practice to perform bulk inserts because it has the best performance. Now I scroll back down here to our connect function we created before and of course we now have to implement the logic that fetches from the API and then inserts into MySQL. For that I will now call fetchFromAPI and I get an error, pass some data back, error handling again, I copy, paste it simply from here and then let's insert what we just got from the API, so I will run insertMySQL and we pass in the connection, data.bpi and I will get back the result from the insert statement and this is either an error or results and fields.
Callback error handling again and we want to let the user know that we inserted some values, so I add console.log and in back ticks Successfully inserted $ curly brackets results.affectedRows documents into MySQL and now everything that is left to do is we want to now query the database to find the maximum value and this is now a little bit similar to what we did with MongoDB, so I add connection.query and we SELECT * from coinvalues and we want to order the result by coinvalue descending.
This means that the highest coin value will be on top of the result and we will limit the result to one row starting with the first result marked with zero. Again we have callback here and that's again either error or results and fields.
So, again error checking, copy, paste it over from here and now I add the result, so let the user know, console.log and in back ticks MySQL: The one month max value is $ curly brackets results zero .coinvalue, so from the result that we get that's an array and we take the first and actually only element and the coinvalue column from that and we add then and it was reached on $ curly brackets results square brackets zero .valuedate.
Now also make sure that we end the connection then here inside the callback and not anywhere else because then the connection would end before we actually have done all our database work, so add this connection.end here and now before we end this connection, we also want to time this whole execution, so add console.timeEnd mysql and now let's try this out real quick, so run another test.js and we see that we successfully inserted 31 documents into MySQL, we get the one month's max value from MySQL here.
We see that due to the lengths of this coinvalue field we have a different precision here but the value and the number value is the same and let's also see the timings and interestingly as you see here MySQL now takes around 86 milliseconds, MongoDB 99 milliseconds and Redis is 100 milliseconds. That's interesting because actually in this scenario and I tried it several times MySQL is faster and of course, this really always depends on your use case but just because MySQL is the more or less oldest technology it doesn't mean that it's the slowest for every use case.
- Basics of relational and nonrelational databases
- Preparing your environment
- When to use document databases
- Inserting data into MongoDB
- Basic insert operations for MongoDB
- Querying data from MongoDB with Node.js
- Using key-value stores
- Using relational databases with Node.js
- Adding MySQL and Sequelize to a project