在哪里存储要执行的SQL命令 [英] Where to store SQL commands for execution

问题描述

由于内联mysql查询，我们面临代码质量问题.使用自写的mysql查询确实会使代码混乱，并且还会增加代码库等.

We face code quality issues because of inline mysql queries. Having self-written mysql queries really clutters the code and also increases code base etc.

我们的代码充满了诸如此类的东西

Our code is cluttered with stuff like

/* beautify ignore:start */
/* jshint ignore:start */
var sql = "SELECT *"
+" ,DATE_ADD(sc.created_at,INTERVAL 14 DAY) AS duedate"
+" ,distance_mail(?,?,lat,lon) as distance,count(pks.skill_id) c1"
+" ,count(ps.profile_id) c2"
+" FROM TABLE sc"
+" JOIN "
+" PACKAGE_V psc on sc.id = psc.s_id "
+" JOIN "
+" PACKAGE_SKILL pks on pks.package_id = psc.package_id  "
+" LEFT JOIN PROFILE_SKILL ps on ps.skill_id = pks.skill_id and         ps.profile_id = ?"
+" WHERE sc.type in "
+" ('a',"
+" 'b',"
+" 'c' ,"
+" 'd',"
+" 'e',"
+" 'f',"
+" 'g',"
+" 'h')"
+" AND sc.status = 'open'"
+" AND sc.crowd_type = ?"
+" AND sc.created_at < DATE_SUB(NOW(),INTERVAL 10 MINUTE) "
+" AND sc.created_at > DATE_SUB(NOW(),INTERVAL 14 DAY)"
+" AND distance_mail(?, ?,lat,lon) < 500"
+" GROUP BY sc.id"
+" HAVING c1 = c2 "
+" ORDER BY distance;";
/* jshint ignore:end */
/* beautify ignore:end */

我不得不稍微模糊一下代码.

I had to blur the code a little bit.

如您所见，在您的代码中重复执行此操作是不可读的.另外，由于atm不能使用ES6，这要归功于多行字符串，这至少会使字符串更漂亮.

As you can see, having this repeatedly in your code is just unreadable. Also because atm we can not go to ES6, which would at least pretty the string a little bit thanks to multi-line strings.

现在的问题是，是否有一种方法可以将SQL过程存储在一个地方?作为附加信息，我们使用节点(〜0.12)并表示要公开API，并访问MySQL数据库.

The question now is, is there a way to store that SQL procedures in one place? As additional information, we use node (~0.12) and express to expose an API, accessing a MySQL db.

我已经考虑过使用JSON，这将导致更大的混乱.另外，这甚至是不可能的，因为JSON的字符集有点严格，并且JSON可能也不会喜欢具有多行字符串.

I already thought about, using a JSON, which will result in an even bigger mess. Plus it may not even be possible since the charset for JSON is a little bit strict and the JSON will probably not like having multi line strings too.

然后，我想到了将SQL存储在文件中并在启动节点应用程序时加载的想法.目前，这是将SQL查询放在一个位置并将其提供给其余节点模块的最佳方法. 这里的问题是，使用一个文件?每个查询使用一个文件?每个数据库表使用一个文件吗?

Then I came up with the idea to store the SQL in a file and load at startup of the node app. This is at the moment my best shot to get the SQL queries at ONE place and offering them to the rest of the node modules. Question here is, use ONE file? Use one file per query? Use one file per database table?

感谢您的帮助，我无法成为世界上第一个解决此问题的人，所以也许有人有一个可行的解决方案！

Any help is appreciated, I can not be the first on the planet solving this so maybe someone has a working, nice solution!

PS:我尝试使用像squel这样的库，但这并没有真正的帮助，因为您可以看到我们的查询很复杂.主要是关于将我们的查询放入查询中心".

PS: I tried using libs like squel but that does not really help, since our queries are complex as you can see. It is mainly about getting OUR queries into a "query central".

推荐答案

我更喜欢将每个较大的查询都放在一个文件中.这样，您可以突出显示语法，并且很容易在服务器启动时加载.为此，我通常为所有查询提供一个文件夹，为每个模型提供一个文件夹.

I prefer putting every bigger query in one file. This way you can have syntax highlighting and it's easy to load on server start. To structure this, i usually have one folder for all queries and inside that one folder for each model.

# queries/mymodel/select.mymodel.sql
SELECT * FROM mymodel;

// in mymodel.js
const fs = require('fs');
const queries = {
  select: fs.readFileSync(__dirname + '/queries/mymodel/select.mymodel.sql', 'utf8')
};

这篇关于在哪里存储要执行的SQL命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！