Using a cursor with pg-promise

Question

I'm struggling to find an example of using a cursor with pg-promise. node-postgres supports its pg-cursor extension. Is there a way to use that extension with pg-promise? I'm attempting to implement an asynchronous generator (to support for-await-of). pg-query-stream doesn't seem to be appropriate for this use case (I need "pull", rather than "push").

As an example, I use SQLite for my unit tests and my (abridged) generator looks something like this...

async function* () {

    const stmt = await db.prepare(...);

    try {

        while (true) {

            const record = await stmt.get();

            if (isUndefined(record)) {

                break;
            }

            yield value;
        }
    }
    finally {

        stmt.finalize();
    }
}

Using pg-cursor, the assignment to stmt would become something like client.query(new Cursor(...)), stmt.get would become stmt.read(1) and stmt.finalize would become stmt.close.

Thanks

Answer 1

Following the original examples, we can modify them for use with pg-promise:

const pgp = require('pg-promise')(/* initialization options */);
const db = pgp(/* connection details */);

const Cursor = require('pg-cursor');

const c = await db.connect(); // manually managed connection

const text = 'SELECT * FROM my_large_table WHERE something > $1';
const values = [10];

const cursor = c.client.query(new Cursor(text, values));

cursor.read(100, (err, rows) => {
  cursor.close(() => {
    c.done(); // releasing connection
  });
  // or you can just do: cursor.close(c.done);
});

Since pg-promise doesn't support pg-cursor explicitly, one has to manually acquire the connection object and use it directly, as shown in the example above.

pg-query-stream doesn't seem to be appropriate for this use case (I need pull, rather than push).

Actually, in the context of these libraries, both streams and cursors are only for pulling data. So it would be ok for you to use streaming also.