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?
Accepted Answer
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'
Read more... 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.
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/
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);
});
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')