The mysql module for nodejs can sometimes result in code such as
var sql = " SELECT p.id, p.title, p.description"
" FROM posts as p"
" INNER JOIN issues AS i ON i.id = p.issue AND i.is_live = 1 AND p.is_live = 1"
" WHERE MATCH(p.title) AGAINST( ? IN NATURAL LANGUAGE MODE)"
" OR MATCH(p.description) AGAINST( ? IN NATURAL LANGUAGE MODE)"
" ORDER BY ID DESC LIMIT ? OFFSET ?" ;
this.conn.queryAsync(sql, [toSearch, toSearch, limit, offset])
.then((res) => {...})
.catch((err) => {...})This is a module that tries to mask this problem by introducing an easier and eye-friendly interface in order to perform mySQL queries.
Connecting to the database is very similar to the mysql package. For more information on the configuration passes as argument to the mynodesql constructor visit the mysql package's github.
const ns = require('mynodesql');
const mynodesql = new ns.mynodeSQL({
host : 'localhost',
user : 'root',
password : '',
database : 'node_sql',
multipleStatements: true
}).connect();SELECT queries are pretty simple to make. Some example are presented bellow
var sql = "SELECT u.id, u.name, u.surname FROM users as u WHERE u.id > 3";
this.conn.queryAsync(sql, [])
.then((res) => {...})
.catch((err) => {...})Using mynodesql:
mynodesql.select(['u.id', 'u.name', 'u.surname'])
.from('users as u').where('id','3')
.execute()
.then((res) => {...})
.catch((err) => {...})One can even make subqueries
var sql = "SELECT u.name ,( SELECT COUNT(*) FROM users WHERE name = 'John' ) AS johnCount FROM users as u ";
this.conn.queryAsync(sql, [])
.then((res) => {...})
.catch((err) => {...})Using mynodesql:
mynodesql.select(['u.name', 'u.surname'])
.sBlockStart()
.select([{'func' : 'COUNT', 'value' : '*'}])
.from('users')
.where('name', 'John')
.sBlockEnd('johnCount')
.from('users as u')var sql = "INSERT INTO users (name,surname) VALUES ('Fotis','Bokos')";
this.conn.queryAsync(sql, [])
.then((res) => {...})
.catch((err) => {...})Using mynodesql:
mynodesql.insert('users', {
'name' : 'Fotis',
'surname' : 'Bokos',
}).execute()
.then((res) => {...})
.catch((err) => {...})var sql = "UPDATE users SET name = 'George',surname = 'Doe' WHERE name = 'John'";
this.conn.queryAsync(sql, [])
.then((res) => {...})
.catch((err) => {...})Using mynodesql:
mynodesql.update('users', {
'name' : 'George',
'surname' : 'Doe',
}).where('name','John')
.execute()
.then((res) => {...})
.catch((err) => {...})mynodesql also allows the use of JOIN along with UPDATE quries
mynodesql.update('pets as p')
.join('users as u', 'u.name = \'John\)
.set('p.owner', 'u.id', true)
.execute()
.then((res) => {...})
.catch((err) => {...})