What is the difference between MySQL & MySQL2 considering NodeJS

Question

I have read the following:

What the difference between mysql and mysql2 gem

So far I have only used MongoDB with NodeJS and I want to be able to learn MySQL for any of my relational database needs. While researching MySQL & NodeJS I have found repositories for MySQL2 and it has nothing related to the MySQL website. I'm guessing that there have been API's created that make it faster for developing with languages like NodeJS & Ruby. From a NodeJS standpoint, I'm guessing that I still run the regular MySQL database on my server, but I need to interact with it using these new API's. Like:

https://github.com/sidorares/node-mysql2/blob/master/README.md

I have seen a site where they do performance benchmarks and NodeJS & MySQL come in very low for performance and NodeJS & MySQL2 very high.

Source for this info: php-nodejs-mysql-and-mongo

Image from that post:

Do I simply use the regular MySQL database on my server and use this mysql2 API or is there a different implementation of MySQL that works with this API?

Lallage

Answer 1

This is just 2 different APIs written by regular people. Difference is in syntax of commands and maybe in performance, just install both, make your own tests for your goals and choose one you think is more suitable for you.

Here is a comparison by NPMCompare:

Hobie

OK, so I'm assuming the 2 apis' I need to have installed are: felixge/node-mysql and sidorares/node-mysql2 Plus I need to have MySQL running with a database to connect to from NodeJS – Lallage

I understand now. felixge/node-mysql and sidorares/node-mysql2 are both api's in order to interact with MySQL while using NodeJS. Similar to the driver or mongodb module I use to interact with MongoDB. I can use either one of them, but I should do my own testing for my needs and choose from that which to use in production. I think that is what is said here. – Lallage

felixge/node-mysql and sidorares/node-mysql2 are almost compatible by code, the only difference is in require part. You can read felixge/node-mysql documentation but run examples using sidorares/node-mysql2 – Selinaselinda

Just a side note, from my experience flexge/node-mysql doesn't work well with JSON type (JSON_OBJECT, JSON_ARRAY..etc) very well. In fact, you need to parse the query result to really work with it. Instead, sidorares/node-mysql2 seems to natively parse it when the corresponding column is JSON type, which save lot of times. – Easy

Answer 2

Extract from https://www.npmjs.com/package/mysql2

"MySQL2 is mostly API compatible with mysqljs and supports majority of features. MySQL2 also offers these additional features

• Faster / Better Performance
• Prepared Statements
• MySQL Binary Log Protocol
• MySQL Server
• Extended support for Encoding and Collation
• Promise Wrapper
• Compression
• SSL and Authentication Switch
• Custom Streams
• Pooling"

Only for the first two features is better: Faster and Secure

Ancel

Pooling is also better --> faster as well. But the normal mysql package has pooling also. – Styrax

mysql2 has improvements are not trivial. mysql2 has a promisified API built in. You can try and roll your own, but it will cost you a lot of development time. Prepared statement support is also my main reason for moving over to mysql2. Its basically a requirement today due to SQL injection hacks. – Obligatory

You can use promises (async/await) with mysql library also. Just pool.query = util.promisify(pool.query) and now you can result = await db.query() til your hearts content. – Polyvalent

Then what is the difference between mysqljs and mysql2? – Posting

Answer 3

I had a lot of problems implementing npm i mysql dependency in my project. First off, if you are following MVC architecture mysql becomes tedious when it comes to extract and send data between server and client. npm i mysql2 simplifies this process, like executing a query is as easy as this

for mysql dependency connection.query(sql,(err,res)=>{*some fn here*}) returns all the rows including the success outcomes of a query. Whereas mysql2 dependency connection.execute(sql) returns onl;y the results from the query and not the rows.

Strathspey

Answer 4

The other difference is, mysql npm module still doesn't support caching_sha2_password authentication introduced in MySQL 8, while mysql2 npm module does. This is why i migrated to mysql2.

Fluviatile

Answer 5

If you look at the source code used for mysql and mysql2 in the graph he is using the mysql api for both of them. The only difference is that the one labeled mysql he is closing the connection each time. So the takeaway is to not close the connection each time. mysql2 api is supposed to be faster, but I haven't seen any data for that.

There is the "mysql2" code that was used for the graph data. Notice the api is still mysql not mysql2:

var sys = require('sys'),
http = require('http'),
mysql = require('mysql')
client = null;

client = mysql.createClient({
          user: 'root',
          password: '',
});
client.query('USE mongo');

http.createServer(function(req, res) {
        client.query(
                  'SELECT * FROM basic WHERE id = '+Math.floor(Math.random()*100000),
                  function selectCb(err, results, fields) {
                    if (err) {
                      res.writeHead(200, {'Content-Type': 'text/html'});
                          res.write('Bad');
                          res.end();
                      throw err;
                    }

                    res.writeHead(200, {'Content-Type': 'text/html'});
                        res.write('Gooood');
                        res.end();
                  }
                );
}).listen(8080);

Champion

lol @ client.query('USE mongo'); – Harmonium

Answer 6

Why can't those who worked on mysql2 improved on the initial mysql......I'm just curious because it sounds funny having something like bootstrap2 or tailwindcss2 rather than contribute and unify to make a good community.

Icarian

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Kubis