How to use db.query from a .js file

Question

Document Structure:

|-public/
  |-js/
   |-shop.js
|-views/
|-routes/
|app.js

I have defined my sql connection in my app.js

const mysql = require('mysql');

const db = mysql.createConnection({
    host: '127.0.0.1',
    user: 'root',
    password: 'password',
    database: 'pfis'
});

db.connect((err) => {
    if (err) {
        throw err;
    }
    console.log('Connected to database');
});

global.db = db;

All sql queries under app.js work fine!

My problem is that i have a shop.js file (see directory structure) which needs to insert some sql (stored procedure) once they clicked on a button element on my page. And i can't figure out how to achieve this.

example shop.js (which is not working!):

function purchaseClicked() {
    var stoel = prompt("Enter your chairnumber: ");
    alert('Someone is on this way with the ATM-machine');
    var cartItems = document.getElementsByClassName('cart-items')[0];
    while (cartItems.hasChildNodes()) {
    var itemTitle = document.getElementsByClassName('cart-item-title')[0].innerHTML;
    var itemQuantity = document.getElementsByClassName('cart-quantity-input')[0].value;

        db.query("Call test1_insert(" + itemTitle + ", " + itemQuantity + ", " + stoel + ");",
            function (error, results, fields) {
                if (error) {
                    alert("Something went wrong, try again!");
                }
                alert("Looks like all good!");
            });




        cartItems.removeChild(cartItems.firstChild);
    }
    updateCartTotal();
}

I have tried to add the same db connection code from app.js (see above snippet) in the shop.js file but it doesnt like that either.

Who can help me how to execute SQL from a "outside" .js file?

Note: I'd recommend using Sequelize over the raw MySQL driver. This gives you a consistent interface to many databases, not just one. — tadman
– tadman, Commented Jun 11, 2019 at 15:49
You can't run MySQL queries in the client. Additionally you should never expose your database to users directly. Allowing users to run arbitrary queries is how your system gets compromised. Allowing random people to probe your database server is how you get compromised. Write an API layer. — tadman
– tadman, Commented Jun 11, 2019 at 15:50

Durmuş · Accepted Answer · 2019-06-12 09:04:10Z

2

I use Sequelize for this. Db file like this :

var sequelize = new Sequelize(mysqlDatabase, mysqlUser,mysqlPassword, {
    host: mysqlHost,
    dialect: 'mysql',
    pool: {
        max: 1000,
        min: 0,
        idle: 10000
    },
    //logging: logger.info
    logging: false
});

var db = {};
db.Shop = sequelize.import(__dirname + '/models/Shop.js');
module.exports = db;

After creating db file you can reach shop like this:

const db = require('path/to/sequelize/file');
db.Shop.create(data);

answered Jun 12, 2019 at 9:04

Durmuş

764 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joe Vrolijk Over a year ago

Thanks so much for this!!

Collectives™ on Stack Overflow

How to use db.query from a .js file

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related