С тех пор, как я выпустил Slonik (клиент PostgreSQL для Node.js) и написал спорную статью Прекратите использовать Knex.js (tl; dr; построители запросов созданы как строительные блоки для ORM; они не добавляют ценности, когда большинство запросов статичны.), меня много спрашивали - тогда как мне генерировать динамические запросы? Я отвечу на это, приведя пару примеров из реальной жизни.

Все запросы в этой статье - это актуальные запросы, используемые в реальном бизнесе, Applaudience, который в значительной степени полагается на PostgreSQL.

Заявление об ограничении ответственности: (1) Во всех примерах обсуждаются только угрозы SQL-инъекций. Логика авторизации (например, внесение в белый список столбцов, к которым пользователю разрешен доступ) не входит в объем данной статьи. (2) Все утверждения предполагают, что в реализации Slonik нет ошибок.

Статический запрос с динамическими привязками значений

Если логика вашего запроса не меняется в зависимости от ввода пользователя, просто создайте SQL-запрос, используя литерал шаблона с тегами sql, например

sql`
  SELECT c1.country_id
  FROM cinema_movie_name cmn1
  INNER JOIN cinema c1 ON c1.id = cmn1.cinema_id
  WHERE cmn1.id = ${cinemaMovieNameId}
`;

Если вы используете Slonik, можно безопасно передавать значения как заполнители шаблонных литералов. sql будет интерпретировать все маркеры-заполнители и создавать окончательный SQL-запрос. В этом случае единственной динамической частью запроса являются сами привязки значений, поэтому окончательный запрос будет следующим:

SELECT c1.country_id
FROM cinema_movie_name cmn1
INNER JOIN cinema c1 ON c1.id = cmn1.cinema_id
WHERE cmn1.id = $1

Значения запроса и связанные значения будут отправлены в PostgreSQL отдельно: нет риска SQL-инъекции.

Связывание списка значений

Когда входные данные вашего запроса представляют собой список значений (например, при извлечении строк, соответствующих нескольким идентификаторам), вы можете использовать sql.valueList, например

sql`
  SELECT m1.*
  FROM movie m1
  WHERE m1.id IN (${sql.valueList(movieIds)})
`;

Это сгенерирует запрос с динамическим набором привязок значений, т.е. если movieIds равно [1, 2, 3], запрос, отправляемый в PostgreSQL, будет:

SELECT m1.*
FROM movie m1
WHERE m1.id IN ($1, $2, $3)

Однако, несмотря на то, что это распространенный шаблон, я не советую использовать его. Вместо этого используйте sql.array, например

sql`
  SELECT m1.*
  FROM movie m1
  WHERE m1.id = ANY(${sql.array(movieIds, 'int4')})
`;

Это сгенерирует запрос фиксированной длины, который не изменяется в зависимости от его входных данных, т.е.

SELECT m1.*
FROM movie m1
WHERE m1.id = ANY($1::"int4"[])

Читать далее sql.array vs sql.valueList.

Запрос с динамическими столбцами

Если результат вашего запроса относится к столбцам, зависящим от ввода пользователя, используйте sql.identifier для генерации SQL, который идентифицирует эти столбцы, например

(Примечание: не фактический запрос, используемый в бизнесе. См. Следующий абзац.)

sql`
  SELECT m1.id, ${sql.identifier(['m1', movieTableColumnName])}
  FROM movie m1
  WHERE
    m1.id = ${moveId}
`;

Этот запрос создаст запрос, который выбирает ровно 1 динамически определяемый столбец. Риск внедрения SQL отсутствует, т.е. даже если логика, приводящая к генерации movieTableColumnName, была каким-то образом скомпрометирована, худшее, что может случиться, - это то, что злоумышленник сможет вернуть любой столбец под псевдонимом m1 или выполнить запрос с недопустимыми значениями идентификатора столбца (оба несут риск; бизнес-логика выходит за рамки этой статьи).

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

sql`
  SELECT
    m1.id,
    m1.foreign_comscore_id,
    m1.foreign_imdb_id,
    m1.foreign_metacritic_id
    m1.foreign_rottentomatoes_id,
    m1.foreign_tmdb_id,
    m1.foreign_webedia_id
  FROM movie m1
  WHERE
    m1.id = ${moveId}
`;

Последний действительно возвращает некоторые лишние данные по каждому запросу, но имеет несколько преимуществ:

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

Запрос с несколькими динамическими столбцами

То же, что и выше, но sql.identifierList.

Вложение динамических SQL-запросов

sql тегированные литералы шаблона могут быть вложенными, например

(Примечание: упрощенная версия фактического запроса, используемого в бизнесе.)

const futureEventEventChangeSqlToken = sql`
  SELECT
    ec1.event_id,
    ec1.seat_count,
    ec1.seat_sold_count
  FROM event_change_future_event_view ec1
`;
sql`
  SELECT
    event_id,
    seat_count,
    seat_sold_count
  FROM (
    ${futureEventEventChangeSqlToken}
  ) AS haystack
  WHERE ${paginatedWhereSqlToken}
  ORDER BY ${orderSqlToken}
  LIMIT ${limitSqlToken}
`

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

Внедрение динамических фрагментов SQL

sql.raw используется для внедрения динамических фрагментов SQL, т.е.

sql`
  SELECT ${sql.raw('foo bar baz')}
`

переводится в (неверный) запрос:

SELECT foo bar baz

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

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

Запрос с членами или оператором предиката динамического сравнения

Если оператор предиката сравнения, присутствующий в вашем запросе, является динамическим, используйте sql.comparisonPredicate, например.

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

sql`
  SELECT
    c1.id,
    c1.nid,
    c1.name
  FROM cinema c1
  WHERE
    ${sql.comparisonPredicate(
      sql`c1.name`,
      nameComparisonOperator,
      nameComparisonValue
    )}
`;

nameComparisonOperator может быть такими значениями, как =, >, < и т. Д. Предполагая, что nameComparisonOperator равно '=', тогда результирующий запрос будет:

SELECT
  c1.id,
  c1.nid,
  c1.name
FROM cinema c1
WHERE
  c1.name = $1

Последнее - чрезвычайно редкий вариант использования, который почти полностью зарезервирован для создания инструментов абстракции SQL более высокого уровня (таких как ORM). Это может быть полезно для сценариев «расширенного поиска», однако продолжайте читать, чтобы ознакомиться с альтернативными шаблонами (см. sql.booleanExpression).

Запрос с динамическими членами предложения WHERE

Если наличие элементов предложения WHERE является динамическим, используйте sql.booleanExpression.

const findCinemas = (root, parameters, context) => {
  const booleanExpressions = [
    sql`TRUE`,
  ];
  if (parameters.input.query) {
    const query = parameters.input.query;
    if (query.countryId !== undefined) {
      booleanExpressions.push(
        sql`c2.id = ${query.countryId}`
      );
    }
    if (query.nid !== undefined) {
      booleanExpressions.push(
        sql`c1.nid % ${query.nid}`
      );
    }
    if (query.name !== undefined) {
      booleanExpressions.push(
        sql`c1.name % ${query.name}`
      );
    }
  }
  const whereSqlToken = sql.booleanExpression(
    booleanExpressions,
    'AND'
  );
  return context.pool.any(sql`
    SELECT
      c1.id,
      c1.nid,
      c1.name,
      c2.code_alpha_2 country_code,
      c2.name country_name
    FROM cinema c1
    INNER JOIN country c2 ON c2.id = c1.country_id
    WHERE ${whereSqlToken}
  `);
},

findCinemas - это реализация преобразователя GraphQL. Предложение WHERE запроса строится с использованием комбинации трех возможных логических выражений. Как и в случае со всеми другими методами построения запросов в Slonik, все выражения могут быть вложенными: вы можете иметь другие логические выражения в качестве членов логического выражения или даже выражение SQL, построенное с использованием литерала шаблона с тегами sql.

Резюме

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

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