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\"}

Additional Resources

Package to import oracle database schema : import-oracledb-schema