Native Queries

Introduction

Native queries allow you to run custom SQL queries on your SQL Server database through Hasura's GraphQL API. This enables you to:

• Execute complex SQL queries beyond Hasura's standard GraphQL operations
• Perform custom data aggregations and transformations
• Access advanced SQL Server features

Structure

A native query consists of a single SQL SELECT statement that:

• Returns a result set
• Can accept input arguments
• Must follow the Native Operation syntax guidelines

Configuration

Native queries are defined in the metadata.nativeQueries section of your configuration.json. Here's an example:

{
  "selectArtistByNameBetween": {
    "sql": "SELECT * FROM [dbo].[Artist] WHERE "Name" LIKE '%' + {{name}} + '%' AND "ArtistId" > {{lower_bound}} AND "ArtistId" < {{upper_bound}}",
    "columns": {
      "ArtistId": {
        "name": "ArtistId",
        "type": "int",
        "nullable": "nonNullable",
        "description": null
      },
      "Name": {
        "name": "Name",
        "type": "varchar",
        "nullable": "nullable",
        "description": null,
        "castAs": "varchar(100)"
      }
    },
    "arguments": {
      "lower_bound": {
        "name": "lower_bound",
        "type": "int",
        "nullable": "nonNullable",
        "description": null
      },
      "upper_bound": {
        "name": "upper_bound",
        "type": "varchar",
        "nullable": "nullable",
        "description": null
      },
      "name": {
        "name": "Name",
        "type": "varchar",
        "nullable": "nullable",
        "description": null
      }
    },
    "description": null
  }
}

Connector metadata update

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

ddn connector-link update my_mysql --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 my_mysql

# update your metadata to track the new native query
ddn model add my_mysql "selectArtistByNameBetween"

Usage

Once configured, you can execute the native query through your GraphQL API:

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

The response will include the matching records:

{
  "data": {
    "selectArtistByNameBetween": [
      {
        "ArtistId": 15,
        "Name": "Black Label Society"
      },
      {
        "ArtistId": 27,
        "Name": "Black Sabbath"
      }
    ]
  }
}

Best Practices

1. Use meaningful names for your native queries that describe their purpose
2. Include descriptions for queries, columns, and arguments to improve API documentation
3. Validate and sanitize input arguments to prevent SQL injection
4. Consider performance implications for complex queries
5. Use appropriate column types and cast specifications