Где хранить команды SQL для выполнения

Ответ 1

Я предпочитаю ставить каждый более крупный запрос в один файл. Таким образом, вы можете выделить синтаксис и легко загрузиться при запуске сервера. Чтобы структурировать это, у меня обычно есть одна папка для всех запросов и внутри этой папки для каждой модели.

# 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')
};

Ответ 2

Поместите запрос в процедуру базы данных и процедуру вызова в коде, когда это необходимо.

create procedure sp_query()
select * from table1;

Ответ 3

Я предлагаю вам хранить ваши запросы в файлах .sql вдали от вашего js-кода. Это отделит проблемы и сделает код и запросы более читаемыми. У вас должны быть разные каталоги с вложенной структурой на основе вашего бизнеса.

например:

queries
├── global.sql
├── products
│   └── select.sql
└── users
    └── select.sql

Теперь вам просто нужно потребовать все эти файлы при запуске приложения. Вы можете сделать это вручную или использовать некоторую логику. В приведенном ниже коде будут прочитаны все файлы (синхронизация) и создать объект с той же иерархией, что и папка выше

var glob = require('glob')
var _  = require('lodash')
var fs = require('fs')

// directory containing all queries (in nested folders)
var queriesDirectory = 'queries'

// get all sql files in dir and sub dirs
var files = glob.sync(queriesDirectory + '/**/*.sql', {})

// create object to store all queries
var queries = {}

_.each(files, function(file){
    // 1. read file text
    var queryText = fs.readFileSync(__dirname + '/' + file, 'utf8')

    // 2. store into object
    // create regex for directory name
    var directoryNameReg = new RegExp("^" + queriesDirectory + "/")

    // get the property path to set in the final object, eg: model.queryName
    var queryPath = file
        // remove directory name
        .replace(directoryNameReg,'')
        // remove extension
        .replace(/\.sql/,'')
        // replace '/' with '.'
        .replace(/\//g, '.')

    //  use lodash to set the nested properties
    _.set(queries, queryPath, queryText)
})

// final object with all queries according to nested folder structure
console.log(queries)

выход журнала

{
    global: '-- global query if needed\n',
    products: {
        select: 'select * from products\n'
    },

    users: {
        select: 'select * from users\n'
    }
}

чтобы вы могли получить доступ ко всем запросам, таким как queries.users.select

Ответ 4

Я родом из разных платформ, поэтому я не уверен, что это то, что вы ищете. как и ваше приложение, у нас было много шаблонных запросов, и нам не нравится, когда он жестко закодирован в приложении.

Мы создали таблицу в MySQL, позволяющую сохранить Template_Name (уникальный), Template_SQL.

Затем мы написали небольшую функцию в нашем приложении, которая возвращает шаблон SQL. что-то вроде этого:

SQL = fn_get_template_sql(Template_name);

Затем мы обрабатываем SQL как-то так: Псевдо:

if SQL is not empty
    SQL = replace all parameters// use escape mysql strings from your parameter
    execute the SQL

или вы можете прочитать SQL, создать соединение и добавить параметры, используя ваш самый безопасный способ.

Это позволяет редактировать запрос шаблона где и когда угодно. Вы можете создать таблицу аудита таблицы шаблонов, в которой будут записаны все предыдущие изменения, чтобы вернуться к предыдущему шаблону, если это необходимо. Вы можете расширить таблицу и зафиксировать, кто и когда был последним отредактированным SQL.

с точки зрения производительности, это будет работать "на лету", плюс вам не нужно читать какие-либо файлы или перезагружать сервер, когда вы зависите от процесса запуска сервера при добавлении новых шаблонов.

Ответ 5

Вы можете создать совершенно новый модуль npm, чтобы принять модуль пользовательских запросов и разместить там все свои сложные запросы.

Затем вы можете классифицировать все свои запросы по ресурсам и по действиям. Например, структура dir может быть:

/index.js -> it will bootstrap all the resources
/queries
/queries/sc (random name)
/queries/psc (random name)
/queries/complex (random name)

Следующий запрос может находиться под каталогом /query/complex в собственном файле, и файл будет иметь описательное имя (пусть предполагается retrieveDistance)

// You can define some placeholders within this var because possibly you would like to be a bit configurable and reuseable in different parts of your code.
/* 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 */

module.exports = sql;

Верхний уровень index.js будет экспортировать объект со всеми сложными запросами. Примером может быть:

var sc = require('./queries/sc');
var psc = require('./queries/psc');
var complex = require('./queries/complex');

// Quite important because you want to ensure that no one will touch the queries outside of
// the scope of this module. Be careful, because the Object.freeze is freezing only the top
// level elements of the object and it is not recursively freezing the nested objects.
var queries = Object.freeze({
  sc: sc,
  psc: psc,
  complex: complex
});

module.exports = queries;

Наконец, в главном коде вы можете использовать такой модуль:

var cq = require('custom-queries');
var retrieveDistanceQuery = cq.complex.retrieveDistance;
// @todo: replace the placeholders if they exist

Выполняя что-то подобное, вы переместите весь шум конкатенации строк в другое место, которое вы ожидаете, и вы сможете легко найти в одном месте все ваши сложные запросы.

Ответ 6

Есть несколько вещей, которые вы хотите сделать. Во-первых, вы хотите хранить многострочные строки без ES6. Вы можете использовать toString функции.

var getComment = function(fx) {
        var str = fx.toString();
        return str.substring(str.indexOf('/*') + 2, str.indexOf('*/'));
      },
      queryA = function() {
        /* 
            select blah
              from tableA
             where whatever = condition
        */
      }

    console.log(getComment(queryA));

Ответ 7

Это, несомненно, вопрос в миллион долларов, и я думаю, что правильное решение всегда зависит от случая.

Вот мои мысли. Надежда могла бы помочь:

Один простой трюк (который, по сути, я читал, что он на удивление более эффективен, чем объединение строк с "+" ) заключается в том, чтобы использовать массивы строк для каждой строки и присоединяться к ним.

Он по-прежнему остается беспорядочным, но, по крайней мере, для меня, немного яснее (особенно при использовании, как и я, "\n" в качестве разделителя вместо пробелов, чтобы сделать результирующие строки более читабельными при распечатке для отладки).

Пример:

var sql = [
    "select foo.bar",
    "from baz",
    "join foo on (",
    "  foo.bazId = baz.id",
    ")", // I always leave the last comma to avoid errors on possible query grow.
].join("\n"); // or .join(" ") if you prefer.

В качестве подсказки я использую этот синтаксис в своей собственной SQL "build" library. Он может не работать в слишком сложных запросах, но если у вас есть случаи, когда параметры могут меняться, очень полезно избегать (также субоптимальных) "коалесцирующих" беспорядок, полностью удаляя ненужные части запроса. Он также находится на GitHub (и это не слишком сложный код), поэтому вы можете продлить его, если считаете его полезным.

Если вы предпочитаете отдельные файлы:

О наличии одного или нескольких файлов, имеющих несколько файлов, менее эффективны с точки зрения эффективности чтения (больше файлов открываются/закрываются накладные расходы и более жесткое кэширование уровня ОС). Но, если вы загружаете все их за один раз при запуске, это на самом деле едва заметная разница.

Итак, единственный недостаток (для меня) заключается в том, что слишком сложно иметь "глобальный взгляд" на вашу коллекцию запросов. Даже если у вас очень много запросов, я думаю, что лучше сочетать оба подхода. То есть: связанные с группой запросы в одном файле, поэтому у вас есть один файл для каждого модуля, подмодель или любые выбранные вами критерии.

Конечно: отдельный файл приведет к относительно "огромному" файлу, также с которым трудно справиться "сначала". Но я (вряд ли) использую фальцовку на основе маркера vim (foldmethod = marker), которая очень полезна для обработки этих файлов.

Конечно: если вы еще не используете vim (поистине?), у вас не было бы этого варианта, но, конечно, в вашем редакторе есть еще одна альтернатива. Если нет, вы всегда можете использовать сложение синтаксиса и что-то вроде функции (my_tag) {"в качестве маркеров.

Например:

---(Query 1)---------------------/*{{{*/
select foo from bar;
---------------------------------/*}}}*/

---(Query 2)---------------------/*{{{*/
select foo.baz 
from foo
join bar using (foobar)
---------------------------------/*}}}*/

... когда свернуто, я вижу это как:

+--  3 línies: ---(Query 1)------------------------------------------------

+--  5 línies: ---(Query 2)------------------------------------------------

Что, используя правильно выбранные метки, гораздо удобнее управлять, и с точки зрения синтаксического анализа нетрудно разобрать все запросы на разделение файлов этими разделительными строками и использовать метки в качестве ключей для индексации запросов.

Грязный пример:

#!/usr/bin/env node
"use strict";

var Fs = require("fs");

var src = Fs.readFileSync("./test.sql");

var queries = {};


var label = false;

String(src).split("\n").map(function(row){
    var m = row.match(/^-+\((.*?)\)-+[/*{]*$/);
    if (m) return queries[label = m[1].replace(" ", "_").toLowerCase()] = "";
    if(row.match(/^-+[/*}]*$/)) return label = false;
    if (label) queries[label] += row+"\n";
});

console.log(queries);
// { query_1: 'select foo from bar;\n',
//   query_2: 'select foo.baz \nfrom foo\njoin bar using (foobar)\n' }

console.log(queries["query_1"]);
// select foo from bar;

console.log(queries["query_2"]);
// select foo.baz
// from foo
// join bar using (foobar)

Наконец (идея), если вы приложите столько усилий, не будет плохой идеей добавить некоторый логический знак вместе с каждой меткой запроса, указав, будет ли этот запрос использоваться часто или только изредка. Затем вы можете использовать эту информацию для подготовки этих инструкций при запуске приложения или только тогда, когда они будут использоваться более одного раза.

Ответ 8

Можете ли вы создать представление, в котором этот запрос.

Затем выберите из представления

Я не вижу никаких параметров в запросе, поэтому я предполагаю, что создание вида возможно.

Ответ 9

Создайте процедуры хранения для всех запросов и замените var sql = "SELECT..." для вызова процедур, таких как var sql = "CALL usp_get_packages".

Это лучшее для производительности и без изменений зависимости от приложения. В зависимости от количества запросов может быть огромная задача, но для каждого аспекта (ремонтопригодность, производительность, зависимости и т.д.) - лучшее решение.

Ответ 10

Другой подход с отдельными файлами с использованием шаблонов строк ES6.

Конечно, это не отвечает на исходный вопрос, потому что он требует ES6, но уже есть принятый ответ, который я не собираюсь заменять. Я просто подумал, что это интересно с точки зрения обсуждения вариантов хранения и управления запросами.

// myQuery.sql.js
"use strict";

var p = module.parent;
var someVar = p ? '$1' : ':someVar'; // Comments if needed...
var someOtherVar = p ? '$2' : ':someOtherVar';

module.exports = `
[email protected]@[email protected]@
    select foo from bar
    where x = ${someVar} and y = ${someOtherVar}
[email protected]@/[email protected]@
`;

module.parent || console.log(module.exports);
// (or simply "p || console.log(module.exports);")

Преимущества этого подхода заключаются в следующем:

• Очень читабельна, даже небольшие накладные расходы javascript.

• Параметры помещаются как читаемые имена переменных вместо глупых "$ 1, $2" и т.д.... и явно объявлены в верхней части файла, поэтому просто проверить, в каком порядке они должны быть предоставлены.

• Может потребоваться как myQuery = require("path/to/myQuery.sql.js") получить правильную строку запроса с позиционными параметрами $1, $2 и т.д. в указанном порядке.

• Но также можно напрямую выполнить с node path/to/myQuery.sql.js получением действительного SQL для выполнения в sql-интерпретаторе

  • Таким образом, вы можете избежать беспорядка копирования и повторения запроса и замены параметров (или значений) каждый раз из среды тестирования запросов на код приложения: просто используйте тот же файл.

  • Примечание: Я использовал синтаксис PostgreSQL для имен переменных. Но с другими базами данных, если они разные, их довольно легко адаптировать.

Пример:

(
    echo "\set someVar 3"
    echo "\set someOtherVar 'foo'"
    node path/to/myQuery.sql.js
) | psql dbName

ПРИМЕЧАНИЕ: '@@sql @@' и '@@/sql @@' (или подобные) ярлыки полностью необязательны, но очень полезны для правильной подсветки синтаксиса, по крайней мере в Vim

Фактически, я на самом деле не пишу ниже (...) | psql... код непосредственно на консоль, а просто (в буфере vim):

echo "\set someVar 3"
echo "\set someOtherVar 'foo'"
node path/to/myQuery.sql.js

... столько раз, сколько условия теста Я хочу проверить и выполнить их, визуально выбрав желаемый блок и набрав :!bash | psql ...

Ответ 11

Я опаздываю на вечеринку, но если вы хотите хранить связанные запросы в одном файле, YAML подходит, потому что он обрабатывает произвольные пробелы лучше, чем в любом другом формате сериализации данных, и у него есть другие приятные функции, такие как комментарии:

someQuery: |-
  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
    -- ...

# Here a comment explaining the following query
someOtherQuery: |-
  SELECT 1;

Таким образом, используя такой модуль, как js-yaml, вы можете легко загрузить все запросы в объект при запуске и получить доступ к ним через разумное имя:

const fs = require('fs');
const jsyaml = require('js-yaml');
export default jsyaml.load(fs.readFileSync('queries.yml'));

Вот фрагмент этого действия в действии (используя строку шаблона вместо файла):

const yml =
`someQuery: |-
  SELECT *
    FROM TABLE sc;
someOtherQuery: |-
  SELECT 1;`;

const queries = jsyaml.load(yml);
console.dir(queries);
console.log(queries.someQuery);

<script src="https://unpkg.com/[email protected]/dist/js-yaml.min.js"></script>

Ответ 12

Вызов процедуры в коде после помещения запроса в процедуру db. @paval уже ответил вы также можете обратиться здесь.

        создать процедуру sp_query()
         выберите * из таблицы1;