You have an Oracle SQL database and want to display it as a graph ? Great ! Let's see how to achieve that with Ogma, a powerful and blazing fast graph visualization library.

Create your Graph Database

You can have a look at our example, which allows you to visualize a sample property graph in minutes with Docker compose.

Oracle provides great tutorials/resources on how to create your graph database:

• Tutorial
• Quick start guide

Add some functions to Oracle Database 23c

If your server version is below 23.2, you will need to add theese two SQL functions

• ORA_SQLGRAPH_TO_JSON
• CUST_SQLGRAPH_JSON

This will allow you to select vertices/edges in your database in JSON format.

sqlplus -s USER/PASSWORD@localhost:1521/SESSION @/path/to/script/sqlgraph-to-json.sql

CREATE OR REPLACE FUNCTION CUST_SQLGRAPH_JSON (
  QUERY VARCHAR2
) RETURN CLOB
  AUTHID CURRENT_USER IS
  INCUR    SYS_REFCURSOR;
  L_CUR    NUMBER;
  RETVALUE CLOB;
BEGIN
  OPEN INCUR FOR QUERY;
  L_CUR := DBMS_SQL.TO_CURSOR_NUMBER(INCUR);
  RETVALUE := ORA_SQLGRAPH_TO_JSON(L_CUR);
  DBMS_SQL.CLOSE_CURSOR(L_CUR);
  RETURN RETVALUE;
END;

Retrieve your nodes/edges from the Databse in NodeJS

First, install the ogma, the oracle connector and ogma-oracle-parser:

npm i oracledb @linkurious/ogma @linkurious/ogma-oracle-parser

Create your connection:

const connectString = host + ":" + port + "/" + service;
oracledb.getConnection({
  user,
  password,
  connectString,
});

Then run your first command:

app.get("/nodes/:type", (req, res) => {
  const query = `select v
          from graph_table (
            openflights_graph
            match (v1 is ${req.params.type})
            columns (
              VERTEX_ID(v1) as v
            )
          )`;
  return conn.execute(query).then((r) => res.json(r));
});

You can see that the result should look like

{
  metadata: {
    name: 'V',
    dbType: [DbType DB_TYPE_JSON],
    nullable: true,
    dbTypeName: 'JSON',
    fetchType: [DbType DB_TYPE_JSON]
  },
  rows: [
    {
      GRAPH_OWNER: 'GRAPHUSER',
      GRAPH_NAME: 'OPENFLIGHTS_GRAPH',
      ELEM_TABLE: 'CITIES',
      KEY_VALUE: { ID: 1 }
    }
  ]
}

Now, we can use the CUST_SQLGRAPH_JSON to retrieve vertives/edges data from the ids we got from the previous request:

import { parseLob } from "@linkurious/ogma-oracle-parser";
...

app.get("/nodes/:type", (req, res) => {
  const query = `select v
          from graph_table (
            openflights_graph
            match (v1 is ${req.params.type})
            columns (
              VERTEX_ID(v1) as v
            )
          )`;
  const pageStart = 0;
  const pageLength = 3200;
  return conn
    .execute<Lob[]>(
      `SELECT CUST_SQLGRAPH_JSON('${query}', ${pageStart}, ${pageLength}) AS COLUMN_ALIAS FROM DUAL`
    )
    .then((result) => {
      const { numResults, nodes, edges } = parseLob(result.rows[0][0]);
      return { nodes, edges };
    });
});

Now, what you get is this:

{
  nodes: [
    {
      id: 'vlabel-id',
      data: {
        ...
      }
    },
    ...
  ],
  edges: [
    {
      id: 'elabel-id',
      source: 'vlabel-id',
      target:'vlabel-id'
      data: {
        ...
      }
    },
    ...
  ]
}

Where vlabel and elabel are the labels you have passed to SQL in your CREATE PROPERTY GRAPH call. -id is the id of your element in the table. And that's it ! You now have retrieved nodes and edges in the Ogma format

The plugin also provides a getRawGraph function that does all the work for you. You can use it like this:

import { getRawGraph } from "@linkurious/ogma-oracle-parser";
...

app.get("/nodes/:type", (req, res) => {
  const query = `select v
          from graph_table (
            openflights_graph
            match (v1 is ${req.params.type})
            columns (
              VERTEX_ID(v1) as v
            )
          )`;
  return getRawGraph(conn, query).then(({ nodes, edges }) => {
    return { nodes, edges };
  });
});

Display your nodes in Ogma

Let' s assume you already have a client side project. Just install Ogma:

npm install @linkurious/ogma

Create your Ogma instance:

import Ogma from "@linkurious/ogma";
import axios from "axios";
const ogma = new Ogma({
  container: "id-of-your-container",
});

axios.get("http://url-to-node-server:port/nodes/VLABEL").then(({ data }) => {
  ogma.setGraph(data);
  return ogma.layouts.force();
});

And you are done !

Customize your nodes/edges ids

By default, the plugin transforms the label:{"ID": id} into label-id. You can customize this behaviour by creating an instance of the OgmaOracleParser class"

import { OgmaOracleParser } from "@linkurious/ogma-oracle-parser";

const { parse, parseLob, getRawGraph } = new OgmaOracleParser({
  SQLIDtoId: (label, id) => `${label}-${id}`,
  SQLIDfromId: (id) => {
    const [label, id] = id.split("-");
    return `${label}:{"ID": ${id}}`;
  },
});

Node and Edge data types

You can type the data of your nodes and edges by passing ND and ED value in the OgmaOracleParser constructor:

type NodeDataType = { name: string; id: number };
type EdgeDataType = { score: number };
const { parse, parseLob, getRawGraph } = new OgmaOracleParser<
  NodeDataType,
  EdgeDataType
>();