node-postgres : comment exécuter la requête "WHERE col IN (<liste de valeurs dynamiques>)" ?

Question

J'essaie d'exécuter une requête comme celle-ci :

SELECT * FROM table WHERE id IN (1,2,3,4)

Le problème est que la liste des identifiants sur lesquels je veux filtrer n'est pas constante et doit être différente à chaque exécution. J'aurais également besoin d'échapper les identifiants, car ils pourraient provenir de sources non fiables, bien que j'échappe en fait tout ce qui entre dans une requête, quelle que soit la fiabilité de la source.

node-postgres semble fonctionner exclusivement avec des paramètres liés : client.query('SELECT * FROM table WHERE id = $1', [ id ]) ; cela fonctionnera si j'avais un nombre connu de valeurs ( client.query('SELECT * FROM table WHERE id IN ($1, $2, $3)', [ id1, id2, id3 ]) ), mais ne fonctionnera pas directement avec un tableau : client.query('SELECT * FROM table WHERE id IN ($1)', [ arrayOfIds ]) car il ne semble pas y avoir de traitement spécial des paramètres de tableau.

Construire le modèle de requête dynamiquement en fonction du nombre d'éléments dans le tableau et étendre le tableau des identifiants dans le tableau des paramètres de la requête (qui dans mon cas actuel contient également d'autres paramètres en plus de la liste des identifiants) semble être une charge déraisonnable. Le codage en dur de la liste des identifiants dans le modèle de requête ne semble pas non plus viable, car node-postgres ne fournit aucune méthode d'échappement des valeurs.

Cela semble être un cas d'utilisation très commun, donc je pense que je suis en train de négliger quelque chose, et non pas qu'il n'est pas possible d'utiliser la méthode commune de l IN (values) Opérateur SQL avec node-postgres.

Si quelqu'un a résolu ce problème d'une manière plus élégante que celles que j'ai énumérées ci-dessus, ou si je rate vraiment quelque chose à propos de node-postgres, veuillez m'aider.

Answer 1

Il semble que vous ayez été proche, d'après votre commentaire à celui de @ebohlman. réponse . Vous pouvez utiliser WHERE id = ANY($1::int[]) . PostgreSQL convertir le tableau au type de paramètre dans lequel il a été coulé dans $1::int[] . Voici donc un exemple artificiel qui fonctionne pour moi :

var ids = [1,3,4]; 

var q = client.query('SELECT Id FROM MyTable WHERE Id = ANY($1::int[])',[ids]);

q.on('row', function(row) {
  console.log(row);
})

// outputs: { id: 1 }
//          { id: 3 }
//          { id: 4 }

Answer 2

Nous avons déjà vu cette question sur la liste des problèmes de Github. La bonne méthode consiste à générer dynamiquement votre liste de paramètres en fonction du tableau. Quelque chose comme ceci :

var arr = [1, 2, "hello"];
var params = [];
for(var i = 1; i <= arr.length; i++) {
  params.push('$' + i);
}
var queryText = 'SELECT id FROM my_table WHERE something IN (' + params.join(',') + ')';
client.query(queryText, arr, function(err, cb) {
 ...
});

De cette façon, vous obtenez l'échappement paramétré de postgres.

Answer 3

La meilleure solution que j'ai trouvée a été d'utiliser la fonction ANY avec la coercition des tableaux de Postgres. Cela vous permet de faire correspondre une colonne avec un tableau arbitraire de valeurs comme si vous aviez écrit col IN (v1, v2, v3) . C'est l'approche adoptée dans Réponse de pero mais ici je montre que la performance de ANY est la même chose que IN .

Requête

Votre requête devrait ressembler à ceci :

SELECT * FROM table WHERE id = ANY($1::int[])

Le passage à la fin qui dit $1::int[] peut être modifié pour correspondre au type de votre colonne "id". Par exemple, si le type de vos identifiants est uuid vous écrivez $1::uuid[] pour transformer l'argument en un tableau d'UUIDs. Voir ici pour la liste des types de données Postgres. .

C'est plus simple que d'écrire du code pour construire une chaîne de requête et c'est sans danger pour les injections SQL.

Exemple

Avec node-postgres, un exemple complet en JavaScript ressemble à ceci :

var pg = require('pg');

var client = new pg.Client('postgres://username:password@localhost/database');
client.connect(function(err) {
  if (err) {
    throw err;
  }

  var ids = [23, 65, 73, 99, 102];
  client.query(
    'SELECT * FROM table WHERE id = ANY($1::int[])',
    [ids],  // array of query arguments
    function(err, result) {
      console.log(result.rows);
    }
  );
});

Performance

L'un des meilleurs moyens de comprendre les performances d'une requête SQL est de regarder comment la base de données la traite. La table échantillon comporte environ 400 lignes et une clé primaire appelée "id" de type text .

EXPLAIN SELECT * FROM tests WHERE id = ANY('{"test-a", "test-b"}');
EXPLAIN SELECT * FROM tests WHERE id IN ('test-a', 'test-b');

Dans les deux cas, Postgres a signalé le même plan de requête :

Bitmap Heap Scan on tests  (cost=8.56..14.03 rows=2 width=79)
  Recheck Cond: (id = ANY ('{test-a,test-b}'::text[]))
  ->  Bitmap Index Scan on tests_pkey  (cost=0.00..8.56 rows=2 width=0)
        Index Cond: (id = ANY ('{test-a,test-b}'::text[]))

Le plan de requête peut varier en fonction de la taille de la table, de la présence d'un index et de la requête. Mais pour des requêtes comme celles ci-dessus, ANY y IN sont traitées de la même manière.

Answer 4

Utilisation de pg-promesse cela fonctionne bien via le Filtre CSV (valeurs séparées par des virgules) :

const values = [1, 2, 3, 4];

db.any('SELECT * FROM table WHERE id IN ($1:csv)', [values])
    .then(data => {
        console.log(data);
    })
    .catch(error => {
        console.log(error);
    });

Et pour répondre à la préoccupation concernant les différents types de données, :csv sérialise le tableau en csv, tout en convertissant toutes les valeurs dans leur format PostgreSQL approprié, en fonction de leur type JavaScript, même en supportant l'option Formatage des types personnalisés .

Et si vous avez des valeurs de type mixte comme celle-ci : const values = [1, 'two', null, true] vous obtiendrez toujours le SQL correctement échappé :

SELECT * FROM table WHERE id IN (1, 'two', null, true)

UPDATE

Depuis la version 7.5.1, pg-promesse a commencé à soutenir :list comme un alias interchangeable pour le :csv filtre :

db.any('SELECT * FROM table WHERE id IN ($1:list)', [values])

Answer 5

Une autre solution possible est d'utiliser le UNNEST comme ceci :

 var ids = [23, 65, 73, 99, 102];
 var strs = ['bar', 'tar', 'far']
 client.query(
   'SELECT * FROM table WHERE id IN(SELECT(UNNEST($1))',
    [ids],  // array of query arguments
    function(err, result) {
       console.log(result.rows);
    }
);
client.query(
   'SELECT * FROM table WHERE id IN(SELECT(UNNEST($1))',
    [strs],  // array of query arguments
    function(err, result) {
       console.log(result.rows);
    }
);

Je l'ai utilisé dans une procédure stockée et cela fonctionne bien. Je pense que cela devrait également fonctionner à partir du code du nœud-pg.

Vous pouvez lire sur la fonction UNNEST aquí .