@credenceanalytics/oracledb-to-json-schema NPM

Export of Oracle database object Schema

This Node.js module exports Oracle database object schema in JSON/Script formats.

Introduction

This module provides a utility to retrieve table structures in JSON format & other object DDL in script files based on the database connection object.

Features

Export table JSON schema
Export schema of Oracle object types like view, function etc.

Folder Structure for DB Objects

Below structure will be created by the package, if not available in the provided path.

definition/
   ├── table
   ├── procedure
   ├── view
   ├── function
   ├── trigger
   ├── synonym
   ├── sequence
   ├── index
   ├── constraint
   ├── ref_constraint
   ├── package
   ├── package_body
   ├── type
   └── type_body

Requirements

Node.js
Oracle Database, Client libraries

Installation

npm install @credenceanalytics/oracledb-to-json-schema

Usage

toJSONSchema(dbConnObj, options, objTypeArr, objArr)

dbConnObj - Oracle database connection object
options: Additional feature based option parameters.
- exportDir - Path where the definition folder will be created to export schema. Default is ./
objTypeArr : Oracle object type in array format e.g. "VIEW", "FUNCTION"
objArr : Oracle object name in array format e.g. "TAB1", "TAB2"

const oracledbToJson = require("@credenceanalytics/oracledb-to-json-schema");
const expSchema = new oracledbToJson();
const options = { exportDir: "dbmodel/legacy" };
const objTypeArr = ["TABLE"];
const objArr = ["TAB1", "TAB2"];
await expSchema.toJSONSchema(dbConnObj, options, objTypeArr, objArr);

Features:

Schema export of all objects
```
toJSONSchema(dbConnObj, options);
```

Schema export of selective object types

toJSONSchema(dbConnObj, options, ["TABLE"]);

toJSONSchema(dbConnObj, options, ["VIEW", "FUNCTION"]);

Export of selective object

toJSONSchema(dbConnObj, options, ["TABLE"], ["TAB1", "TAB2"]);

Sample code to export schema:

index.js

const oracledbToJson = require("@credenceanalytics/oracledb-to-json-schema");
const oracledb = require("oracledb");
const expSchema = new oracledbToJson();
const options = { exportDir: "./dbmodel/legacy" };
const dbconfig = {
	username: "",
	password: "",
	host: "",
	database: ""
};
async function jsonSchema() {
	console.time("jsonSchema");
	let dbConnObj;
	try {
		const connectString = `${dbconfig.host}/${dbconfig.database}`;
		dbConnObj = await oracledb.getConnection({ user: dbconfig.username, password: dbconfig.password, connectString });

        // Schema export of all objects
		var resp = await expSchema.toJSONSchema(dbConnObj, options);
        console.log("Result:", resp);
	} catch (error) {
		console.error("Oracle DB execution exception:", error);
		throw error;
	} finally {
		if (dbConnObj) {
			try {
				await dbConnObj.close();
			} catch (error) {
				console.error("Error closing connection:", error);
				throw error;
			}
		}
	}
	console.timeEnd("jsonSchema");
}
jsonSchema();

Package Test Case Run

npm test - <folderdir> <dbusername> <dbpassword> <dbhost> <dbname> <options>
- Sample
```
npm test - dbusername dbpassword dbhost dbname {\"exportDir\":\"test\"}
```