nodejs sql code example
Example 1: mssql query node js
const sql = require('mssql')
const query=`select * from table`;
config = {
database: "dbname",
// can keep these option required with latest version of mssql npm package
options: {
enableArithAbort: true,
encrypt: true,
rowCollectionOnRequestCompletion: false,
},
parseJSON: true,
password: "password",
port: port, // if youe database running on difrent port then 1433
server: "server ip or name",
user: "usenme",
};
sql.on('error', err => {
// ... error handler
})
sql.connect(config).then(pool => {
return pool.request()
.query(query)
}).then(result => {
console.log(result.recordset)
}).catch(err => {
console.log(err)
});
Example 2: how to manage a db connection in javascript
//put these lines in a seperate file
const mysql = require('mysql2');
const connection = mysql.createPool({
host: "localhost",
user: "",
password: "",
database: ""
// here you can set connection limits and so on
});
module.exports = connection;
//put these on destination page
const connection = require('../util/connection');
async function getAll() {
const sql = "SELECT * FROM tableName";
const [rows] = await connection.promise().query(sql);
return rows;
}
exports.getAll = getAll;
Example 3: use nodejs mysql
var mysql = require('mysql');var connection = mysql.createConnection({ host : 'localhost', user : 'me', password : 'secret', database : 'my_db'}); connection.connect(); connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) { if (error) throw error; console.log('The solution is: ', results[0].solution);}); connection.end();
Example 4: js read mysql database
// Use https://github.com/JackChilds/jsmysqldb
// Then once it's setup you can just do this:
// Config that will be sent to server
const config = {
"table" : "customers",
"limit" : "50",
"offset" : "0"
};
// Use the _POST_REQUEST function to send data to the server and get a response
_POST_REQUEST("link-to-server/database.php", 'config=' + JSON.stringify(config), (response) => {
// Convert the response into an array of rows:
var data = JSON.parse(response);
// Now do what you want with data
// data[row index][column name]
});