CODEWITHSUNDEEP
javascriptmysqlnode.jsexpress
Joe Vrolijk
Joe Vrolijk

Reputation: 19

How to use db.query from a .js file

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?

Upvotes: 0

Views: 1479

Answers (1)

Durmuş
Durmuş

Reputation: 76

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

Upvotes: 2

Related Questions