Native Queries

Introduction

Native Queries allow you to run custom SQL queries on your Oracle database. These allow you to run queries that are not supported by Hasura's GraphQL engine as virtual tables in Oracle.

Native queries can also be added directly to the configuration.json, but it is deprecated now in favour of the following.

Structure

A Native Query is a single SQL statement that returns results and can take arguments. The SQL structure of a Native Query is specified in the Native Operation syntax page.

Create a Native Query

To create a new Native Query, create a new SQL file inside the connector configuration directory, then use the ddn CLI to add it to the connector configuration. For example:

1. Create a new directory structure under the connector configuration:

   mkdir -p my_subgraph/connector/oracle-connector/native_operations/queries/

2. Create a new SQL file my_subgraph/connector/oracle-connector/native_operations/queries/artist_by_id_between.sql with the following content:

   SELECT *
   FROM "Artist"
   WHERE "Name" LIKE {{name}}
     AND "ArtistId" > {{lower_bound}}
     AND "ArtistId" < {{upper_bound}}

3. Create a new entry in the connector configuration:

   NOTE: The operation-path provided should be relative to the configuration directory. It means that for the below case, the SQL file is expected to be present at <ddn-project-root>/my_subgraph/connector/oracle-connector/native-operations/queries/artist_by_id_between.sql

   ddn connector plugin --connector my_subgraph/connector/oracle-connector/connector.yaml -- \
     native-queries create --operation-path native_operations/queries/artist_by_id_between.sql --name artist_by_id_between

4. If your data connector is running, run the following command to update your metadata to track the new native query:

   ddn connector-link update oracle_connector --add-all-resources

   If you get a connection refused error, it means that your data connector is not running. You can either start the data connector and try again, or, run the following commands to update your metadata to track the new native query:

   # start the connector and introspect your DB
   ddn connector introspect oracle_connector
   
   # update your metadata to track the new native query
   ddn model add oracle_connector "artist_by_id_between"

Updating a Native Query

A Native Query can be updated with the create command and setting the --overwrite option. For example:

ddn connector plugin --connector my_subgraph/connector/oracle_connector/connector.yaml -- \
  native-queries create --name artist_by_id_between --operation-path native_operations/queries/artist_by_id_between.sql  --overwrite

Usage

With the example above, you can then use the query in your GraphQL API like this:

query {
  artist_by_id_between(args: {name: "Black", lower_bound: 10, upper_bound: 50}) {
    ArtistId
    Name
  }
}