MySQL with Node.js

Question

MySQL with Node.js

Question

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?

crawf

2015/11/21

Read less...

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

yojimbo87

2011/04/28

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.

fmsf

2014/07/01

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/

Shaikh Shahid

2016/03/23

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);
});

Lex Soft

2019/01/22

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')

Quy Tang

2017/07/13

mak · Accepted Answer · 2/2/2015 6:53:36 PM

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'