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

Question

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

Yes, you can do File I/O in node.js (https://nodejs.org/api/fs.html) and Yes, you can extract your SQL from there and execute them afterwards. — Bogdan Păun, Mar 16 '15 at 16:54

score 3 · Answer 1 · answered Dec 23 '16 at 19:19

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.

score 1 · Accepted Answer · answered Mar 16 '15 at 17:25

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.

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

2 Answers2