Native Queries

Introduction

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

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/chinook_pg/native_operations/queries/

2. Create a new SQL file my_subgraph/connector/chinook_pg/native_operations/queries/artist_by_name_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:

   ddn connector plugin --connector my_subgraph/connector/chinook_pg/connector.yaml -- \
     native-operation create --operation-path native_operations/queries/artist_by_name_between.sql --kind query

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 chinook_pg --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 chinook_pg
   
   # update your metadata to track the new native query
   ddn model add chinook_pg "artist_by_name_between"

List Native Operations

To list the existing Native Operations for a specific connector, use the list command. For example:

ddn connector plugin --connector my_subgraph/connector/chinook_pg/connector.yaml -- \
  native-operation list

Delete a Native Query

A Native Query can be deleted with the delete command. For example:

ddn connector plugin --connector my_subgraph/connector/chinook_pg/connector.yaml -- \
  native-operation delete --name artist_by_name_between --kind query

Usage

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

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