node.js - postgres - pg-promise

node-postgres: cómo ejecutar la consulta "WHERE col IN(<lista de valores dinámicos>)"?

Question

Estoy tratando de ejecutar una consulta como esta:

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

El problema es que la lista de identificadores contra los que quiero filtrar no es constante y debe ser diferente en cada ejecución. También necesitaría escapar de los identificadores, ya que pueden provenir de fuentes no confiables, aunque en realidad podría escapar de cualquier cosa que se presente en una consulta, independientemente de la confiabilidad de la fuente.

node-postgres parece funcionar exclusivamente con parámetros enlazados: client.query(''SELECT * FROM table WHERE id = $1'', [ id ]) ; esto funcionará si tengo un número conocido de valores ( client.query(''SELECT * FROM table WHERE id IN ($1, $2, $3)'', [ id1, id2, id3 ]) ), pero no funcionará con una matriz directamente: client.query(''SELECT * FROM table WHERE id IN ($1)'', [ arrayOfIds ]) , ya que no parece haber ningún manejo especial de los parámetros de la matriz.

Crear la plantilla de consulta dinámicamente de acuerdo con el número de elementos en la matriz y expandir la matriz de ids en la matriz de parámetros de consulta (que en mi caso real también contiene otros parámetros además de la lista de ids) parece irrazonablemente oneroso. La codificación de la lista de identificadores en la plantilla de consulta tampoco parece viable, ya que node-postgres no proporciona ningún método de escape de valores.

Esto parece ser un caso de uso muy común, así que supongo que en realidad estoy pasando por alto algo, y no que no sea posible usar el operador común de IN (values) SQL con node-postgres.

Si alguien ha resuelto este problema de una manera más elegante que las que mencioné anteriormente, o si realmente me estoy perdiendo algo acerca de node-postgres, por favor ayude.

Answer 1

Al usar pg-promise , esto funciona bien a través del filtro CSV (valores separados por comas):

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

Y para abordar la inquietud sobre varios tipos de datos :csv modificador :csv serializa la matriz en csv, al tiempo que convierte todos los valores en su formato PostgreSQL adecuado, de acuerdo con su tipo de JavaScript, incluso admitiendo el Formato de tipo personalizado .

Y si tiene valores de tipo mixto como este: const values = [1, ''two'', null, true] , aún obtendrá el SQL escapado correctamente:

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

ACTUALIZAR

A partir de v7.5.1, pg-promise comenzó a admitir :list como un alias intercambiable para el filtro: :csv :

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

Answer 2

Hemos visto esta pregunta antes en la lista de problemas de github. La forma correcta es generar dinámicamente su lista de parámetros basados ​​en la matriz. Algo como esto:

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 esa forma obtendrás el escape parametrizado de postgres.

Answer 3

La idea en general:

var invals = [1,2,3,4], cols = [...fields];
var setvs = vs => vs.map(v=> ''$''+ (values.push(v))  ).join();

var values = [];
var text = ''SELECT ''+ setvs(cols) +'' FROM table WHERE id IN ('' + setvs(invals) +'')'';

Answer 4

La mejor solución que he encontrado ha sido utilizar la función ANY con la coerción de matriz de Postgres. Esto le permite hacer coincidir una columna con una matriz arbitraria de valores como si hubiera escrito col IN (v1, v2, v3) . Este es el enfoque en la respuesta de pero pero aquí muestro que el rendimiento de ANY es el mismo que IN .

Consulta

Su consulta debe verse como:

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

Ese bit al final que dice $1::int[] se puede cambiar para que coincida con el tipo de su columna "id". Por ejemplo, si el tipo de sus ID es uuid , escribiría $1::uuid[] para forzar el argumento a una matriz de UUID. Vea aquí para la lista de tipos de datos Postgres .

Esto es más sencillo que escribir código para construir una cadena de consulta y es seguro contra inyecciones de SQL.

Ejemplo

Con node-postgres, un ejemplo completo de JavaScript se ve así:

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

Actuación

Una de las mejores maneras de entender el rendimiento de una consulta SQL es observar cómo la base de datos la procesa. La tabla de muestra tiene aproximadamente 400 filas y una clave principal llamada "id" de text de tipo.

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

En ambos casos, Postgres reportó el mismo plan de consulta:

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[]))

Es posible que vea un plan de consulta diferente según el tamaño de su tabla, donde hay un índice y su consulta. Pero para consultas como las anteriores, ANY e IN se procesan de la misma manera.

Answer 5

Otra posible solución es usar la función UNNEST siguiente manera:

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

He utilizado esto en un procedimiento almacenado y funciona bien. Creo que debería funcionar también desde el código de nodo-pg.

Puedes leer acerca de la función UNNEST here .

Answer 6

Otra posible solución es, por ejemplo, la API REST en NODE JS:

var name = req.body;//Body is a objetc that has properties for example provinces
var databaseRB = "DATABASENAME"
var conStringRB = "postgres://"+username+":"+password+"@"+host+"/"+databaseRB; 

var filter_query = "SELECT row_to_json(fc) FROM ( SELECT ''FeatureCollection'' As type, array_to_json(array_agg(f)) As features FROM (SELECT ''Feature'' As type, ST_AsGeoJSON(lg.geom)::json As geometry, row_to_json((parameters) As properties FROM radiobases As lg WHERE lg.parameter= ANY($1) )As f) As fc";

var client = new pg.Client(conStringRB);
client.connect();
var query = client.query(new Query(filter_query,[name.provinces]));
query.on("row", function (row, result) {
  result.addRow(row);
});
query.on("end", function (result) {
 var data = result.rows[0].row_to_json
   res.json({
     title: "Express API",
     jsonData: data
     });
});

Tenga en cuenta que se puede utilizar cualquier tipo de matriz

Answer 7

Parece que puedes haber estado cerca basado en tu comentario a la answer @ ebohlman. Puede usar WHERE id = ANY($1::int[]) . PostgreSQL convert la matriz al tipo al que se convert el parámetro en $1::int[] . Así que aquí hay un ejemplo artificial que funciona para mí:

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 }