Многорядная вставка с pg-обещанием

Question

Я хотел бы вставить несколько строк с одним INSERT запрос, например:

INSERT INTO tmp(col_a,col_b) VALUES('a1','b1'),('a2','b2')...

Есть ли способ сделать это легко, предпочтительно для массива объектов, подобных этим:

[{col_a:'a1',col_b:'b1'},{col_a:'a2',col_b:'b2'}]

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

До сих пор я мог сделать это только для одного объекта:

INSERT INTO tmp(col_a,col_b) VALUES(${col_a},${col_b})

В качестве дополнительного вопроса: используются ли вставки ${} нотация защищена от SQL-инъекций?

Answer 1

Я автор pg-обещания.

В более старых версиях библиотеки это было рассмотрено на упрощенных примерах в статье Performance Boost, которая по-прежнему важна для написания высокопроизводительных приложений баз данных.

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

const pgp = require('pg-promise')({
    /* initialization options */
    capSQL: true // capitalize all generated SQL
});
const db = pgp(/*connection*/);

// our set of columns, to be created only once, and then shared/reused,
// to let it cache up its formatting templates for high performance:
const cs = new pgp.helpers.ColumnSet(['col_a', 'col_b'], {table: 'tmp'});

// data input values:
const values = [{col_a: 'a1', col_b: 'b1'}, {col_a: 'a2', col_b: 'b2'}];

// generating a multi-row insert query:
const query = pgp.helpers.insert(values, cs);
//=> INSERT INTO "tmp"("col_a","col_b") VALUES('a1','b1'),('a2','b2')

// executing the query:
db.none(query)
    .then(data => {
        // success;
    })
    .catch(error => {
        // error;
    });

Смотрите API: ColumnSet, вставьте.

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

И вы можете использовать тот же подход для генерации любого из следующих запросов:

• один ряд INSERT
• многорядные INSERT
• один ряд UPDATE
• многорядные UPDATE

Защищены ли вставки с использованием нотации ${} от SQL-инъекций?

Да, но не один. Если вы вставляете имена схем / таблиц / столбцов динамически, важно использовать имена SQL, которые в сочетании защитят ваш код от внедрения SQL.

---

Смежный вопрос: многострочные обновления PostgreSQL в Node.js

---

дополнительные услуги

Q: как получить id каждой новой записи одновременно?

A: просто добавив RETURNING id на ваш запрос и выполняя его методом many:

const query = pgp.helpers.insert(values, cs) + 'RETURNING id';

db.many(query)
    .then(data => {
        // data = [{id: 1}, {id: 2}, ...]
    })
    .catch(error => {
        // error;
    });

или, что еще лучше, получите id-ы и преобразуйте результат в массив целых чисел, используя метод map:

db.map(query, [], a => +a.id)
    .then(data => {
        // data = [1, 2, ...]
    })
    .catch(error => {
        // error;
    });

Чтобы понять, почему мы использовали + смотрите здесь: pg-обещание возвращает целые числа в виде строк.

UPDATE-1

Для вставки огромного количества записей, см. Импорт данных.

ОБНОВЛЕНИЕ-2

Используя v8.2.1 и новее, вы можете заключить статическую генерацию запроса в функцию, чтобы ее можно было генерировать в методе запроса, чтобы отклонить при сбое генерации запроса:

// generating a multi-row insert query inside a function:
const query = () => pgp.helpers.insert(values, cs);
//=> INSERT INTO "tmp"("col_a","col_b") VALUES('a1','b1'),('a2','b2')

// executing the query as a function that generates the query:
db.none(query)
    .then(data => {
        // success;
    })
    .catch(error => {
        // error;
        // will get here, even if the query generation fails
    });

Answer 2

Попробуйте https://github.com/datalanche/node-pg-format - например,

      var format = require('pg-format');

var myNestedArray = [['a', 1], ['b', 2]];
var sql = format('INSERT INTO t (name, age) VALUES %L', myNestedArray); 
console.log(sql); // INSERT INTO t (name, age) VALUES ('a', '1'), ('b', '2')

аналогично работает с массивом объектов.

Answer 3

      CREATE TABLE "user"
(
    id         BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    first_name VARCHAR(255),
    last_name  VARCHAR(255),
    email      VARCHAR(255),
    password   VARCHAR(60),
    role       VARCHAR(255),
    enabled    BOOLEAN                                 NOT NULL DEFAULT FALSE,
    CONSTRAINT pk_user PRIMARY KEY (id)
);

      INSERT INTO "user" (id,
                    first_name,
                    last_name,
                    email,
                    password,
                    role,
                    enabled)
VALUES (generate_series(1, 50),
       substr(gen_random_uuid()::text, 1, 10),
        substr(gen_random_uuid()::text, 1, 10),
        substr(gen_random_uuid()::text, 2, 5 )
            || '@' ||
        substr(gen_random_uuid()::text, 2, 5)
            || '.com',
        substr(gen_random_uuid()::text, 1, 10),
        (array['ADMIN', 'MANAGER', 'USER'])[floor(random() * 3 + 1)],
        (array[true, false])[floor(random() * 2 + 1)]
       );