How to execute paginated requests

[Andrija-Markovic]

Oracle DB Version: "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.19.0.0.0" Nodejs Version: 16.4.0 OracleDB.versionString: 6.0.2 OracleDB.oracleClientVersionString: could not get it because I am running in Thin mode process.platform: win32 process.version: v16.4.0 process.arch: x64

package.json dependencies: "oracledb": "^6.0.2", "@types/oracledb": "^5.3.1",

Problem Description

I have an Expressjs app written in TypeScript running on Node. The application connects to an Oracle DB solely to read data from it. This is how the app creates the connection pool:

this.connPool= await OracleDB.createPool({
    user: config.DB_USER,
    password: config.DB_PWD,
    connectionString: config.DB_CONN
});

When a connection is needed the app does:

await this.connPool.getConnection();

And it also releases the connection when no longer needed by doing:

await conn.release();

Now, I would like to know what is the best way to run paginated queries using node-oracledb. I want my app to fetch 10 rows, then I want to apply some business logic to those rows, and, if needed, I want the app to fetch next 10 rows.

Currently, the app executes queries like this:

const result = await conn.execute(query, bindVars, { maxRows: 10 });

Is the best way to append OFFSET ${offset} FETCH FIRST ${fetchSize} ROWS ONLY to the query string and remove { maxRows: 10 } from the query execution line above? Or is there a more optimal way to do this?

I am new to the node-oracledb package so any help would be really appreciated!

[sosoba]

You should use resultSet pattern. See https://github.com/oracle/node-oracledb/blob/main/examples/resultset2.js#L74-L100.

[Andrija-Markovic]

Thank you @sosoba! Really appreciate you including a good code example. I will close this issue now.