CODEWITHSUNDEEP
node.jsexpressnode-mysql
Bravo
Bravo

Reputation: 1139

Can I store SQL statements in a separate file in node js?

I would like to store all my SQL statements in a separate (.txt maybe?) file. This will make my code look clean and more readable.

Example:

router.get('/', function (req, res, next) {

var sql = // get SQL from a file

connection.query(sql, function (err, rows, fields) {
    var row;

    if (!err) {
        row = rows[0];
    }

    res.render('index', { ... });
})

});

P.S. I don't want to store the SQL statements in a .js file.

Thank you in advance

Upvotes: 4

Views: 4883

Answers (2)

deathangel908
deathangel908

Reputation: 9709

You surely can do that but remember that work with files cost "too much time"

I don't agree with Gepser. File content should be loaded once at server start and being stored in ram (object I mean) while runtime.

I would separate queries into some logical modules and then into tables or at least just tables:

json file:

{
  "usersTable": {
    "selectAll": "select * from user",
    "selectSome": "select * from users where id > :id",
    "insert": "insert into users values (:name, :second_name, )"
  },
  "messagesTable": {
    "selectAll": ""
  }
}

key-value file:

usersTable.selectAll=select * from user
userTable.selectSome=select * from users where id > :id
userTable.insert=insert into users values (:name, :second_name, )

messagesTable.selectAll=
  • If the project is huge you dont want to store every single query in one file. At least with key-value approach. Separate files by directories that contain module name.
  • Use named parameters instead of positional. Otherwise it's ambiguous what parameter is when you don't see the query.
  • Load file with sqls at application initialization. And use object at runtime.

Upvotes: 3

Gepser Hoil
Gepser Hoil

Reputation: 4236

You surely can do that but remember that work with files cost "too much time". What you probably need is a layer that do that queries.

For example you could do something like:

  myDBLayer.getNumber(function(id, country, name){
    //Do stuff
  });

But you have to define the getNumber function and do a function for every query you want. Off course you can receive parameters and be a kind of abstract with that but I think that is more efficient that just read some file with all queries.

Upvotes: 1

Related Questions