ノードを用いたSQLクエリの動的生成js

発売以来Slonik (ノード用のPostgreSQLクライアント)Stop using Knex.js 記事( TL ; DR ;クエリビルダは、ORM用のブロックを構築するように設計されています;クエリの大部分が静的であるときに値を追加しません).私は多くの質問されている-どのように動的なクエリを生成するのですか?私は実際の例のカップルを共有することによって、これに答えます.
この記事のクエリのすべては実際のビジネスで使用されるクエリです.Applaudience どちらheavily relies on PostgreSQL .
免責事項:(1)すべての例はSQLインジェクションの脅威のみを議論します.認可ロジック(例えば、whitesting列ユーザーはアクセスする権限がある)はこの記事の範囲にありません.( 2 )すべての文はslonik実装にバグがないと仮定します.

動的値結合を持つ静的クエリ

クエリロジックがユーザーの入力によって変更されない場合は、SQLクエリを sql tagged template literal , 例えば

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を使っているならばsafe to pass values as template literal placeholders . 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 is [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 無効なカラム識別子値(両方とも危険を運びます;ビジネスロジックはこの記事の範囲にありません)で、エイリアスかExecute質問を実行してください.
ユーザーのクエリに応じてアプリケーションが異なる列を返す必要がある場合、ビジネスロジックのスコープにあるすべての列を選択し、必要なカラムの値を選ぶ必要があります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}
`;

後者はすべてのクエリにいくつかの余分なデータを返しますが、いくつかの利点があります.

これは、SQLの注入のリスクを減らす(どのくらいのコード生成ロジックを信頼し、静的コードは常に動的なコードよりも安全です).

それは1つのエントリだけを生成します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断片が質問の間で共有されるとき、または、意図が単に1つの場所でコード複雑さの集中を減らすことになっているとき、便利です.

動的SQLフラグメントの注入

sql.raw は、動的なSQLフラグメントを注入するために使用されます.

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

(無効な)クエリへの変換

SELECT foo bar baz

前の例とは異なりsql タグ付きテンプレートsql.raw 安全ではない-それはユーザーの入力を使用して動的なSQLを作成することができます.
クエリを生成するための既知のユースケースはありませんsql.raw それは入れ子に縛られていないsql 式(「動的SQLクエリの入れ子」で記述されている)または他のquery building methods . 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.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 は、GraphSQLリゾルバの実装です.クエリの句は、3つの可能なブール式の組み合わせを使用して構築されます.Slonikの他のクエリビルディングメソッドの場合と同様に、すべての式は入れ子にできます.ブール式のメンバー、あるいはsql タグ付きテンプレートリテラル.

概要

これらの例は、すべての一般的な動的なSQLの構築シナリオをカバーし、どのようにSlonikother query building methods provided by Slonik . この記事の主な意図は、slonikがクエリの静的部分をそのまま維持するSQLクエリを構築するための安全な抽象化を提供することを示すことでした.
あなたが私の仕事を評価して、Slonikとmany other of my オープンソースのプロジェクトを継続的に改善し、パトロンになることを考えてください.


最後に、私はあなたがカバーするために私をご希望のユースケースのシナリオを逃した、コメントでそれを言及し、私は喜んでそれを含むでしょう.