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?

Answer 1

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' 

Answer 2

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

Answer 3

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.

Answer 4

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/

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

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