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 bee something like client.query(new Cursor(...)), stmt.get would bee stmt.read(1) and stmt.finalize would bee stmt.close.

Thanks

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 bee something like client.query(new Cursor(...)), stmt.get would bee stmt.read(1) and stmt.finalize would bee stmt.close.

Thanks

• javascript
• pg-promise
• database-cursor

• Cursor examples for the base driver are here, so you just have to use manual connect to access Client and use it in the same way as in the examples. – vitaly-t Commented Jun 5, 2018 at 11:09

1 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.