MySQL with Node.js

I've just started getting into Node.js. I come from a PHP background, so I'm fairly used to using MySQL for all my database needs.

How can I use MySQL with Node.js?

Check out the node.js module list

• node-mysql — A node.js module implementing the MySQL protocol
• node-mysql2 — Yet another pure JS async driver. Pipelining, prepared statements.
• node-mysql-libmysqlclient — MySQL asynchronous bindings based on libmysqlclient

node-mysql looks simple enough:

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret',
});

connection.connect(function(err) {
  // connected! (unless `err` is set)
});

Queries:

var post  = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function(err, result) {
  // Neat!
});
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'

node-mysql is probably one of the best modules out there used for working with MySQL database which is actively maintained and well documented.

Since this is an old thread just adding an update:

##To install the MySQL node.js driver:

If you run just npm install mysql, you need to be in the same directory that your run your server. I would advise to do it as in one of the following examples:

###For global installation:

npm install -g mysql

###For local installation:

1- Add it to your package.json in the dependencies:

"dependencies": {
    "mysql": "~2.3.2",
     ...

2- run npm install

---

Note that for connections to happen you will also need to be running the mysql server (which is node independent)

To install MySQL server:

There are a bunch of tutorials out there that explain this, and it is a bit dependent on operative system. Just go to google and search for how to install mysql server [Ubuntu|MacOSX|Windows]. But in a sentence: you have to go to http://www.mysql.com/downloads/ and install it.

Here is production code which may help you.

Package.json

{
  "name": "node-mysql",
  "version": "0.0.1",
  "dependencies": {
    "express": "^4.10.6",
    "mysql": "^2.5.4"
  }
}

Here is Server file.

var express   =    require("express");
var mysql     =    require('mysql');
var app       =    express();

var pool      =    mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'address_book',
    debug    :  false
});

function handle_database(req,res) {
    
    pool.getConnection(function(err,connection){
        if (err) {
          connection.release();
          res.json({"code" : 100, "status" : "Error in connection database"});
          return;
        }   

        console.log('connected as id ' + connection.threadId);
        
        connection.query("select * from user",function(err,rows){
            connection.release();
            if(!err) {
                res.json(rows);
            }           
        });

        connection.on('error', function(err) {      
              res.json({"code" : 100, "status" : "Error in connection database"});
              return;     
        });
  });
}

app.get("/",function(req,res){-
        handle_database(req,res);
});

app.listen(3000);

Reference : https://codeforgeek.com/2015/01/nodejs-mysql-tutorial/

KnexJs can be used as an SQL query builder in both Node.JS and the browser. I find it easy to use. Let try it - Knex.js

$ npm install knex --save
# Then add one of the following (adding a --save) flag:
$ npm install pg
$ npm install sqlite3
$ npm install mysql
$ npm install mysql2
$ npm install mariasql
$ npm install strong-oracle
$ npm install oracle
$ npm install mssql


var knex = require('knex')({
  client: 'mysql',
  connection: {
    host : '127.0.0.1',
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  }
});

You can use it like this

knex.select('*').from('users')

or

knex('users').where({
  first_name: 'Test',
  last_name:  'User'
}).select('id')

Imo, you should try MySQL Connector/Node.js which is the official Node.js driver for MySQL. See ref-1 and ref-2 for detailed explanation. I have tried mysqljs/mysql which is available here, but I don't find detailed documentation on classes, methods, properties of this library.

So I switched to the standard MySQL Connector/Node.js with X DevAPI, since it is an asynchronous Promise-based client library and provides good documentation. Take a look at the following code snippet :

const mysqlx = require('@mysql/xdevapi');
const rows = [];

mysqlx.getSession('mysqlx://localhost:33060')
.then(session => {
    const table = session.getSchema('testSchema').getTable('testTable');

    // The criteria is defined through the expression.
    return table.update().where('name = "bar"').set('age', 50)
        .execute()
        .then(() => {
            return table.select().orderBy('name ASC')
                .execute(row => rows.push(row));
        });
})
.then(() => {
    console.log(rows);
});

You can also try out a newer effort known as Node.js DB that aims to provide a common framework for several database engines. It is built with C++ so performance is guaranteed.

Specifically you could use its db-mysql driver for Node.js MySQL support.

connect the mysql database by installing a library. here, picked the stable and easy to use node-mysql module.

npm install [email protected]

var http = require('http'),
   mysql = require('mysql');
     
var sqlInfo = {
   host: 'localhost',
   user: 'root',
   password: 'urpass',
   database: 'dbname'
}
client = mysql.createConnection(sqlInfo);

client.connect();

For NodeJS mysql connecting and querying example

You can skip the ORM, builders, etc. and simplify your DB/SQL management using sqler and sqler-mdb.

-- create this file at: db/mdb/setup/create.database.sql
CREATE DATABASE IF NOT EXISTS sqlermysql

const conf = {
  "univ": {
    "db": {
      "mdb": {
        "host": "localhost",
        "username":"admin",
        "password": "mysqlpassword"
      }
    }
  },
  "db": {
    "dialects": {
      "mdb": "sqler-mdb"
    },
    "connections": [
      {
        "id": "mdb",
        "name": "mdb",
        "dir": "db/mdb",
        "service": "MySQL",
        "dialect": "mdb",
        "pool": {},
        "driverOptions": {
          "connection": {
            "multipleStatements": true
          }
        }
      }
    ]
  }
};

// create/initialize manager
const manager = new Manager(conf);
await manager.init();

// .sql file path is path to db function
const result = await manager.db.mdb.setup.create.database();

console.log('Result:', result);

// after we're done using the manager we should close it
process.on('SIGINT', async function sigintDB() {
  await manager.close();
  console.log('Manager has been closed');
});