How to: Sequential db.batch with pg-promise

Question

I cannot figure out how to execute a batch call of generated queries sequentially.

I am trying to truncate every table in the DB. My code:

db.any(`
  SELECT table_name
  FROM information_schema.tables
  WHERE table_schema='public'
  AND table_type='BASE TABLE';
`)
.then(res => res.map(item => item.table_name)) // To get only an array with the names
.then(tables => tables.map(tableName => db.none(`TRUNCATE TABLE ${tableName} CASCADE`))) // ES6 template strings, because the table name must be bare here (no quotes)
.then(queries => db.tx(t => t.batch(queries)))

I get deadlock detected errors. It's clear why I am getting deadlocks: The queries cascade and try to truncate the same table as another query. That's why I need to call the queries synchronously. I can't figure out the way to do it. I tried using db.sequence(), but I was getting the same errors. What is the proper way of sequential execution of generated queries with pg-promise? Thanks a lot.

Answer 1

Syntax supported by pg-promise is very flexible. Below is just one such syntax, which is the easiest to use for your case, and the most modern one:

await db.tx(async t => {
    const tables = await t.map(`
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = $1
        AND table_type = $2
    `, ['public', 'BASE TABLE'], a => a.table_name);

    for (let i = 0; i < tables.length; i++) {
        await t.none('TRUNCATE TABLE $1:name CASCADE', tables[i]);
    }
});

// ES6 template strings, because the table name must be bare here (no quotes)

that is wrong, names must be in double quotes, which we provide with SQL Names filter.

Also see from here:

Never use the reserved ${} syntax inside ES6 template strings, as those have no knowledge of how to format values for PostgreSQL.