In our previous post, we explained how to automatically generate Word documents using Node.js, pulling data from a JSON array.

This post takes that concept one step further and uses data from an Oracle database to generate Word documents. This example will create documents for each IAL in IFS but you can use this method to create documents for anything in your database - you just need to change the SQL query and the substitution tags.

The Word document template has been adjusted to include the tags we need: view_name, view_desc, view_code, currently_assigned_permissions, currently_assigned_users and last_compile_date and has been formatted properly. We'll use the source code from the previous post as a starting point but remove the JSON array and for loop.

To connect to an Oracle database from Node.js, we need one additional dependency: oracledb. This package does require some additional steps regarding the Oracle client libraries. We already have the correct Oracle client setup but if you don't, you can find all steps required to do this in the oracledb package documentation.

To install the oracledb dependency, execute the following command in your terminal:

And include the package at the top of the code of the index.js file:

We like to keep database connection details in a separate file so it's quicker to jump in and change them. To do this, create a new file named dbconfig.js and export an object with user, password and connectString keys.

For the connectString in the example above, we're using the net service name from my tnsnames.ora file but there are many different types of connection strings you can use. For a full list of valid connection strings, please see the oracledb package documentation.

Include the connection information by requiring it after the other dependencies in index.js

At this stage, it's a good time to prepare the SQL statement that we want to execute. For this example, this query gets the IAL name, code, description, last compile time and a list of users and roles that have permission to view it.

After the error handling functions, create an asynchronous function named run to create the connection to the Oracle DB and pass in an option to return the results as an object.

Next, define the SQL statement that needs to be executed, execute the command and store the results in the result variable.

From here, generating the actual Word documents using this data is essentially the same as in the previous post: loop through the rows array in the result variable, set the substitution data and output the file.

The only differences to the previous post are this data has line breaks in it (when displaying the assigned users/roles), so an extra parameter is passed into the Docxtemplater function:

And the keys in the result array objects don't match exactly with the document so they need to be manually assigned:

Apart from those 2 parts, the rest of the code is the same:

Finish off by catching any connection errors and if everything goes well, close the connection to the database.

Lastly, call the run function.

To execute the code, run the same command from the previous post in your terminal window.

One document per IAL will now be created. An example of this can be found below: