Generating a GraphQL Schema from a Relational Schema

Use the GET_GRAPHQL_SCHEMA function to obtain the underlying relational schema in the form of a GraphQL schema.

This function takes either or both of the table names and schema as an input and provides a JSON object representing the GraphQL types for the relational tables.

Syntax

DBMS_JSON_DUALITY.GET_GRAPHQL_SCHEMA(
    schema_details in JSON
)
RETURN JSON;

Table 2-2 Inputs to the GET_GRAPHQL_SCHEMA Function

Parameter

Description

schema_details

This parameter is a JSON object and it has two fields: "tableNames" : The value for this field is an array of scalar strings representing the table names for which the GraphQL schema representation is required. If "tableNames" is not specified, GraphQL schema is generated for all tables in the specified relational schema. "schema": You can provide the name of the user/schema/owner of the tables as string. If "schema" is not specified, GraphQL schema representation is generated for the current schema"

Output Schema

The function outputs the GraphQL schema corresponding to the relational schema as a JSON object.

Get the GraphQL type schema for a particular table. This example uses the "TEAM" table defined under the car racing example.

SELECT DBMS_JSON_DUALITY.GET_GRAPHQL_SCHEMA(
    JSON('
        {
            "tableNames": ["TEAM"]
        }
    ')
) AS "GraphQL Schema";

Recollect from the Introduction to the Car Racing Example that the table "TEAM" has three columns : "team_id", "points" and "name". Executing the GET_GRAPHQL_SCHEMA function would produce a JSON object containing these columns as shown below:

GraphQL Schema
--------------------------------------------------------------------------------
{
  "types" :
  [
    {
      "Team" :
      {
	"team_id" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	},
	"points" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	},
	"name" :
	{
	  "type" : "String",
	  "nullable" : false,
	  "quoted" : false
	}
      }
    }
  ]
}


1 row selected.

GraphQL schema could be obtained for a set of tables by specifying multiple table names in the syntax. This example uses the "DRIVER" and "RACE" tables defined under the car racing example.

SELECT DBMS_JSON_DUALITY.GET_GRAPHQL_SCHEMA(
    JSON('
        {
            "tableNames": ["DRIVER", "RACE"]
        }
    	')
) AS "GraphQL Schema";

Executing the GET_GRAPHQL_SCHEMA function would produce a JSON object containing columns from both tables as shown below:

GraphQL Schema
--------------------------------------------------------------------------------
{
  "types" :
  [
    {
      "Driver" :
      {
	"team" :
	{
	  "type" : "Team",
	  "nullable" : false,
	  "quoted" : false
	},
	"team_id" :
	{
	  "type" : "Integer",
	  "nullable" : true,
	  "quoted" : false
	},
	"points" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	},
	"name" :
	{
	  "type" : "String",
	  "nullable" : false,
	  "quoted" : false
	},
	"driver_id" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	}
      }
    },
    {
      "Race" :
      {
	"race_id" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	},
	"race_date" :
	{
	  "type" : "Date",
	  "nullable" : true,
	  "quoted" : false
	},
	"podium" :
	{
	  "type" : "String",
	  "nullable" : true,
	  "quoted" : false
	},
	"name" :
	{
	  "type" : "String",
	  "nullable" : false,
	  "quoted" : false
	},
	"laps" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	}
      }
    }
  ]
}


1 row selected.

You can also specify the underlying schema or user name from which the table representation must be fetched. The following example obtains the GraphQL schema for the table "DRIVER_RACE_MAP" which is created for the current schema".

You can also specify the underlying schema or user name from which the table representation must be fetched. The following example obtains the GraphQL schema for the table "DRIVER_RACE_MAP" which is created for the user "F1".

SELECT DBMS_JSON_DUALITY.GET_GRAPHQL_SCHEMA(
    JSON('
        {
            "tableNames": ["DRIVER_RACE_MAP"],
            "schema": "F1"
        }
    ')
) AS "GraphQL Schema";

This code would return the GraphQL schema corresponding to "DRIVER_RACE_MAP" table from the F1 user as shown below:

GraphQL Schema
--------------------------------------------------------------------------------
{
  "types" :
  [
    {
      "Driver_race_map" :
      {
	"driver" :
	{
	  "type" : "Driver",
	  "nullable" : false,
	  "quoted" : false
	},
	"race" :
	{
	  "type" : "Race",
	  "nullable" : false,
	  "quoted" : false
	},
	"race_id" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	},
	"position" :
	{
	  "type" : "Integer",
	  "nullable" : true,
	  "quoted" : false
	},
	"driver_race_map_id" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	},
	"driver_id" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	}
      }
    }
  ]
}


1 row selected.

To obtain the GraphQL schema for the entire relational schema, just specify the "schema" argument in the input:

SELECT DBMS_JSON_DUALITY.GET_GRAPHQL_SCHEMA(
    JSON('
        {
            "schema": "F1"
        }
    ')
) AS "GraphQL Schema";

Executing the above code would return the schema corresponding to all the tables specified in F1 as shown below:

GraphQL Schema
--------------------------------------------------------------------------------
{
  "types" :
  [
    {
      "Driver_race_map" :
      {
	"driver" :
	{
	  "type" : "Driver",
	  "nullable" : false,
	  "quoted" : false
	},
	"race" :
	{
	  "type" : "Race",
	  "nullable" : false,
	  "quoted" : false
	},
	"race_id" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	},
	"position" :
	{
	  "type" : "Integer",
	  "nullable" : true,
	  "quoted" : false
	},
	"driver_race_map_id" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	},
	"driver_id" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	}
      }
    },
    {
      "Driver" :
      {
	"team" :
	{
	  "type" : "Team",
	  "nullable" : false,
	  "quoted" : false
	},
	"team_id" :
	{
	  "type" : "Integer",
	  "nullable" : true,
	  "quoted" : false
	},
	"points" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	},
	"name" :
	{
	  "type" : "String",
	  "nullable" : false,
	  "quoted" : false
	},
	"driver_id" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	},
	"driver_race_map" :
	{
	  "type" : "[Driver_race_map]",
	  "nullable" : false,
	  "quoted" : false
	}
      }
    },
    {
      "Team" :
      {
	"team_id" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	},
	"points" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	},
	"name" :
	{
	  "type" : "String",
	  "nullable" : false,
	  "quoted" : false
	},
	"driver" :
	{
	  "type" : "[Driver]",
	  "nullable" : false,
	  "quoted" : false
	}
      }
    },
    {
      "Race" :
      {
	"race_id" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	},
	"race_date" :
	{
	  "type" : "Date",
	  "nullable" : true,
	  "quoted" : false
	},
	"podium" :
	{
	  "type" : "String",
	  "nullable" : true,
	  "quoted" : false
	},
	"name" :
	{
	  "type" : "String",
	  "nullable" : false,
	  "quoted" : false
	},
	"laps" :
	{
	  "type" : "Integer",
	  "nullable" : false,
	  "quoted" : false
	},
	"driver_race_map" :
	{
	  "type" : "[Driver_race_map]",
	  "nullable" : false,
	  "quoted" : false
	}
      }
    }
  ]
}


1 row selected.

Starting in 23.26.1, the output of DBMS_JSON_DUALITY.GET_GRAPHQL_SCHEMA includes additional column metadata that can be used for documentation and client-side schema generation.

• Column comments (added using COMMENT ON COLUMN) appear as a "description" field for the corresponding column.
• Column annotations (added using the ANNOTATIONS clause) appear as an "annotations" array of name/value pairs.

The following example adds comments and annotations to columns in the TEAM table and then retrieves the GraphQL schema representation.

COMMENT ON COLUMN team.name IS 'Official team name';
COMMENT ON COLUMN team.points IS 'Constructor points total';

ALTER TABLE team MODIFY (
    name ANNOTATIONS (
        Display_Label 'Team Name',
        Classification 'Public'
    ),
    points ANNOTATIONS (
        Display_Label 'Team Points',
        Classification 'Public'
    )
);

SELECT DBMS_JSON_DUALITY.GET_GRAPHQL_SCHEMA(
    JSON('
        {
            "tableNames": ["TEAM"]
        }
    ')
) AS "GraphQL Types representation";

Executing the above code returns the GraphQL schema for TEAM, with the additional metadata included for the annotated/commented columns:

{
  "types" :
  [
    {
      "Team" :
      {
        "team_id" :
        {
          "type" : "Integer",
          "nullable" : false,
          "quoted" : false
        },
        "points" :
        {
          "type" : "Integer",
          "nullable" : false,
          "quoted" : false,
          "description" : "Constructor points total",
          "annotations" :
          [
            { "name" : "DISPLAY_LABEL", "value" : "Team Points" },
            { "name" : "CLASSIFICATION", "value" : "Public" }
          ]
        },
        "name" :
        {
          "type" : "String",
          "nullable" : false,
          "quoted" : false,
          "description" : "Official team name",
          "annotations" :
          [
            { "name" : "DISPLAY_LABEL", "value" : "Team Name" },
            { "name" : "CLASSIFICATION", "value" : "Public" }
          ]
        }
      }
    }
  ]
}