Tracking node-oracledb 4.0 Development

[cjbj]

Updates:

Node-oracledb 4.0 has been released: https://blogs.oracle.com/opal/oracle-db-named-objects-and-advanced-queuing-support-new-in-node-oracledb-40

---

Node-oracledb 4.0-dev is a 'work in progress' and subject to change:

• There is a prebuilt binary package at https://github.com/oracle/node-oracledb/releases/tag/v4.0.0-dev-20190623 with commits up to 2019-06-23 (i.e. with the initial named object query & bind support) Alternatively, you can compile from source code as shown in the next point
• Install the development branch by running npm install oracle/node-oracledb#dev-4.0 See here for general steps about compiling.
• View the CHANGELOG
• The documentation is here

You need Node.js 8.16, 10.16, or 12 (or later in each version) to use this version of node-oracledb.

---

It's time to start thinking about node-oracledb 4.

We just pushed out node-oracledb 3.1.2 with a few bug fixes yesterday and want to do some bigger tasks for the next release.

The task list had quite a few things ticked off over last year: SODA; Privileged Connections; Array DML; Native EBR; Native events; Password Change; Installer improvements; CQN; Heterogeneous Connection Pool; Session tagging and state fixup; Pool draining; Call timeouts; Incremental improvements and stabilization. I have to thank the team and contributors for this. There have been other ongoing efforts we've been involved in too, from other languages, through making Instant Client RPMs available without clickthrough, to the Oracle 18c XE release (Windows 18c XE was released today, by the way, so you're no longer restricted to using it on Linux). Overall it was a busy year.

There's a lot still on the task list for future node-oracledb releases but I want to keep focus and not bite off too much for 4.0. What I would like to see is PL/SQL and SQL object binding support; this is a big task but we can hope! A major release would also be the place to remove the NAN layer in the implementation and use N-API - @anthony-tuininga has started looking at this. If N-API works functionally for node-oracledb, then we'd need to verify that it is stable before making a final decision about using it for 4.0.

Looking at timing, there are no guarantees. I think node-oracledb 4 will support only Node 8 and higher, based on the near EOL of Node 6, and also the status of N-API in Node 6. So, plan to upgrade Node to something newer if you want to use node-oracledb 4.

Reminder: everything is subject to change. And, if you have a pressing need for a feature, you can do it yourself!

[tuanvayn]

n-api is sound good

[cjbj]

@tuancongtuyn any particular reason?

[tuanvayn]

I think need to make the lib easier to use. I lost many time to learn how to config oracledb

[cjbj]

@tuancongtuyn node-oracledb will still use a binary that needs Oracle Client. There would be a small advantage when upgrading Node.js since one node-oracledb binary (that uses N-API) will not not need recompiling. Note that node-oracledb 3.1 partially does this (for a predetermined set of Node.js versions) with it's multi-binary install changes.

[cjbj]

An update on the N-API work, which is the foundation refactoring before we begin any new user-visible features: it's progressing well but still not complete or fully tested. If we decide it's OK and we use N-API in node-oracledb 4, you will need to be using Node.js 8.12 or later.

Testing did uncover a major performance issue in the Node.js N-API implementation affecting strings. It has a simple yet-to-be-merged fix, which I hope will land in Node.js 8, 10 and 12. What this means is that to get comparable performance with node-oracledb 3, you would have to use node-oracledb 4 with Node.js 12 or with latest Node.js 8.x and 10.x patch releases (assuming the fix is back ported).

Stay tuned. Comments welcome.

[cjbj]

I pushed a development branch https://github.com/oracle/node-oracledb/tree/dev-4.0 that you can install with npm install oracle/node-oracledb#dev-4.0. This has the N-API changes that may or may not be incorporated into any eventual node-oracledb 4.0, depending on the Node.js N-API performance fix getting into Node.js 8 & 10 (it is already merged to Node.js master).

[tuanvayn]

@cjbj Did you test performance? And how is it improve than the older?

[cjbj]

@tuancongtuyn It's too early to say.

[cjbj]

@tuancongtuyn Node 11.12 just got announced and is the first release (so far) to have the N-API performance fix: do some testing and let us know how you find the speed. There is still a memory leak that we know of, but this may not affect you.

[tuanvayn]

@tuancongtuyn Node 11.12 just got announced and is the first release (so far) to have the N-API performance fix: do some testing and let us know how you find the speed. There is still a memory leak that we know of, but this may not affect you.

Do you want your library can integrate to bookselfjs in future?

[sosoba]

My proposals for v4:

1. New fetch type "ISO string" for DATE and 3 kinds of TIMESTAMP database types.
2. New fetch type BigInt for NUMBER database.

[anthony-tuininga]

Thanks for the suggestions! Here are my initial comments:

The different TIMESTAMP database types are already supported -- at least on fetch. Binding is a different story which we are still considering the best approach to take. Bigint is still experimental in Node.js; once it is no longer experimental we will definitely consider implementing it. We have also considered adding support for fetching numbers as strings and calling a function to transform the string into whatever you care to return instead -- but that's still in the thought process so far.

[cjbj]

I just pushed some more updates to the dev-4.0 branch that had accumulated from the team while I was vacationing (yes, thank you, it was great). Check the CHANGELOG. Notably:

• Added SODA bulk insert methods sodaCollection.insertMany() and sodaCollection.insertManyAndGet(). These are usable with Oracle Client 18.5.
• Added connection.currentSchema as requested in Enhancement: add new property to execute(): Options to specify schema #884
• Corrected the type of sodaCollection.metaData to be Object.

A little progress on the SQL & PL/SQL object binding project happened while I was away, but @anthony-tuininga has progressed pretty far with Advanced Queuing (AQ) support and Implict Results support. None of these are on a GitHub branch yet.

Other FYIs:

• Oracle Instant Client 19.3 for Linux was just released. It connects to Oracle DB 11.2.0.4 or later. I'll blog on it soon.
• @morgiyan is tracking SODA features in Tracking: Known issues with SODA #1088

[cjbj]

Oracle Advanced Queue support for RAW queues just landed on the dev-4.0 branch. See #362 (comment).

[charleaux]

Any idea when SODA will be ready for use in production?

[anthony-tuininga]

See #1088 for details. Max (who opened that issue) can answer all of your questions about SODA. :-)

[cjbj]

I just merged support for Implicit Results to the dev-4.0 branch, see https://github.com/oracle/node-oracledb/blob/dev-4.0/doc/api.md#implicitresults

The dev-4.0 doc notes the SODA status change. I'll update the 3.1 production doc soon.

[cjbj]

I just merged a small change from the team to the dev-4.0 branch that gives the CQN subscription registration id when calling conn.subscribe(), see #1075

[cjbj]

An update on node-oracledb 4.0 progress: work is happening steadily on the object binding/fetching project. It's still missing some functionality, and documentation hasn't started. Bedding it down will still take some time but I know people are eager for the feature, so I'm thinking we'll aim to merge code to the dev-4.0 branch in a relatively rough state when some basic demos work to my satisfaction. Maybe a week or two for that, depending on what other work interrupts us.

[cjbj]

I've just pushed a few more updates to the dev-4.0 branch.

• We've introduced new constants OUT_FORMAT_ARRAY and OUT_FORMAT_OBJECT to deprecate ARRAY and OBJECT for outFormat values. The latter will still work but with the upcoming Oracle object binding support, there is scope for confusion about whether OBJECT is a type or not (it's not).

• the numeric values for node-oracledb Type Constants and Oracle Database Type Constants have changed. This will make it easier for us to simplify things if/when we make changes to bind type usage. Make sure your code uses the constants instead of their values!

Object support in our internal builds is looking good, but we are still trying to streamline behaviors between the mapping of Object types to a plain old JS objects, and seeing if we can avoid a layer. Overall I won't push the current code just yet since it may well confuse you if we change it.

[cjbj]

I've merged the current state of the named Oracle type query and binding support to the dev-4.0 branch. Yay! Please hammer it and test all your favorite types (dates, numbers, lobs!) and and give us feedback so we can bed it in.

Doc is:

• https://github.com/oracle/node-oracledb/blob/dev-4.0/doc/api.md#objects
• https://github.com/oracle/node-oracledb/blob/dev-4.0/doc/api.md#dbobjectclass
• https://github.com/oracle/node-oracledb/blob/dev-4.0/doc/api.md#getdbobjectclass

There are a few examples:

• https://github.com/oracle/node-oracledb/blob/dev-4.0/examples/selectobject.js
• https://github.com/oracle/node-oracledb/blob/dev-4.0/examples/selectgeometry.js
• https://github.com/oracle/node-oracledb/blob/dev-4.0/examples/selectvarray.js
• https://github.com/oracle/node-oracledb/blob/dev-4.0/examples/aqobject.js
• https://github.com/oracle/node-oracledb/blob/dev-4.0/examples/plsqlrecord.js

Install as normal: npm install oracle/node-oracledb#dev-4.0 See the first post in the issue for links about building.

[cjbj]

To make it easier for you to play with node-oracledb 4.0-dev, I've uploaded a package with prebuilt binaries for macOS, Windows x64 and Linux x64 to https://github.com/oracle/node-oracledb/releases/tag/v4.0.0-dev-20190623

If you have network access, you can install with

npm install https://github.com/oracle/node-oracledb/releases/download/v4.0.0-dev-20190623/oracledb-4.0.0-dev.tgz

If not, then manually download oracledb-4.0.0-dev.tgz and run npm install oracledb-4.0.0-dev.tgz

[mijohansen]

You actually have to use the prebuilt binary for macOS if you want to use yarn.
yarn add https://github.com/oracle/node-oracledb/releases/download/v4.0.0-dev-20190623/oracledb-4.0.0-dev.tgz

[cjbj]

@mijohansen thanks for sharing.

Let us know what you find with the features.

One heads up: the new connection.queue() method will be renamed connection.getQueue() prior to production.

[Volterra90]

Hi! I tried to use the new feature with the DBObject class, because I have to call a PL/SQL which returns a complex type.

My problem is that inside that DBObject there are more DBObjects, and I can't find a way to let con.execute "out parameter" know the typing. So my result is a Proxy object which I don't know how to handle. I'll paste the code here so you can check that. I tried other solutions, but I really can't modify the PL/SQL, I'm not allowed to do that, and it has to be Node.js project...

const oracledb = require('oracledb')

const getAgencyData = async () => {
    try {
        const pool = oracledb.getPool()
        const con = await pool.getConnection()
        const [a, b, c, d] = 
            await Promise.all([con.getDbObjectClass('a'), 
            con.getDbObjectClass('b'), con.getDbObjectClass('c'), 
            con.getDbObjectClass('d')])
        const resultQuery = await con.execute(`BEGIN :result:= plcCall(:parameter); END;`, {
            result: {
                dir: oracledb.BIND_OUT, 
                type: a,
//TODO Is there a way to type dbObjects b, c an d inside result so I can get them?
            },
            parameter: 'parameter',
        })
       return resultQuery.outBinds
//TODO Fields b, c and d inside resultQuery.outBinds are Proxys.
    } catch (e) {
        return {
            error: `PLERROR`
        }
    }
}

PS. It's actually my first query in GitHub, so I'm sorry if I write it where it shouldn't be.
PS2. Thanks in advance, and sorry if my English is a bit broken :-(

[dmcghan]

@Volterra90 Did you try just specifying the top level type? If so, did you get an error? If so, which was it?

Also, this code here:

 const [a, b, c, d] = 
            await Promise.all([con.getDbObjectClass('a'), 
            con.getDbObjectClass('b'), con.getDbObjectClass('c'), 
            con.getDbObjectClass('d')])

Should probably be written like this:

 const a = await con.getDbObjectClass('a');
 const b = await con.getDbObjectClass('b');
 const c = await con.getDbObjectClass('c');
 const d = await con.getDbObjectClass('d');

The reason is that a connection can only do one thing at a time (even though the original code implies otherwise). With Promise.all, you end up throwing multiple commands from JS land to Libuv's thread pool queue, but they will only be dequeued when the previous operation has completed. I've only seen bad things happen with this at scale. It's better to keep the queueing in the JS code.

[anthony-tuininga]

Can you show the PL/SQL function signature and the signature of the complex type you are trying to pass?

You only need the top-level type. Any types that are part of the top-level type will be included automatically. There is no need to specify any of the sub-level types. For example, if you have a type like the following:

type udt_EmbeddedType is record (
    a number,
    b varchar2(30)
);

type udt_TopLevelType is record (
    a number,
    b udt_EmbeddedType,
    c date
);

function MyFunc(a_Parameter) return udt_TopLevelType;

Then in your JavaScript code you would only need to do the following:

const result = await con.execute("begin :ret := MyFunc(:2); end;", [{ type: "UDT_TOPLEVELTYPE", dir: oracledb.BIND_OUT }, 5])
const rec = result.outBinds[0];
console.log("Rec:", rec);

The output to the console will show the contents of the record, including the embedded record.

Hope that helps!

[Volterra90]

Hi! Thanks for the reply! I've already solved the problem. You were right, it's enough with the top-level type, the rest of it was not necessary so I only define what I've called "a" (which is the top level object) and the rest of it worked perfectly.

The problem had nothing to do with the module. I'm using the Chrome dev tools to inspect my code, and I was seeing the console.log() of the result in the console, which returned me a weird object and I thought it was just wrong. In the end I looked in VSC and the sub-level types were actually there! Not sure why that happens though... Got me stuck for some hours...

Thanks for your attention, I feel it was just a newbie's problem!

[cjbj]

@Volterra90 keep giving us feedback.

I just pushed an update to the dev-4.0 branch which makes the toString behavior of DbObject a little nicer.

This push also had the rename of connection.queue() to connection.getQueue().

It also changes the default of events to true, to give a better out-of-the-box high availability experience for people connecting to FAN-enabled DB services.

[GaryGSC]

FYI: Sometime soon, the maximum value for UV_THREADPOOL_SIZE will increase from 128 to 1024.

[dmcghan]

Thanks, @GaryGSC, that's good to know! Though, 128 is probably high enough for most folks... :)

[cjbj]

Interesting. Luckily it should just be a doc update for node-oracledb.

[cjbj]

You can see I've been making release-time commits like spelling typo fixes. I'm itching to ship. However we're still waiting to get more feedback from you about the objects implementation in the real-world. I know it's summer for you Northern hemisphere people, and this week there is a public holiday in the USA - sounds to me like a perfect time to do some playing with 4.0-dev....

[cjbj]

Let's really wrap up this release.... Start testing node-oracledb 4.0 before the API is final. Install with npm install oracle/node-oracledb#dev-4.0

[Volterra90]

Hi! I've been using the package the last two weeks without any problems. I was really interested in the DBObject class and it worked like wonders, no problems whatsoever.

Also, I just want to say that actually the url I have in my package.json (https://github.com/oracle/node-oracledb/releases/tag/v4.0.0-dev-20190623) returns me a 500 error.

[cjbj]

@Volterra90 thanks for the feedback. Our final testing is underway.

[cjbj]

Node-oracledb 4.0 has been released: https://blogs.oracle.com/opal/oracle-db-named-objects-and-advanced-queuing-support-new-in-node-oracledb-40

Thanks everyone!