Ship faster using SQL Server stored procedures with Hasura GraphQL Engine

GraphQL has revolutionized the way APIs are designed and consumed. Many application developers love its flexibility and declarative nature and these are top reasons why GraphQL has  garnered widespread adoption.

Now, imagine combining the power of GraphQL with the performance and security of SQL Server stored procedures…

In this blog, we'll explore the benefits of adding support for SQL Server stored procedures to the Hasura GraphQL Engine, along with some compelling use cases.

SQL Server stored procedures are precompiled database objects that encapsulate business logic and complex data operations. They are very popular with Microsoft SQL Server database administrators, who often use them as the sole point of access to the database from an application. As the database admins are often the SQL experts on the team, this allows them to manage performance, security, and releases in the environment in which they’re most familiar.

Use cases for SQL Server stored procedures:

• Data validation and sanitization: Stored procedures can validate and sanitize incoming data before persisting it in the database, ensuring data integrity and security.
• Complex data manipulation: For complex data transformations or calculations, stored procedures offer a performant way to process data within the database.
• Atomic operations: Stored procedures can group multiple queries into a single transaction, ensuring data consistency and integrity.
• Integration with legacy systems: When dealing with legacy systems, stored procedures can act as a bridge to connect modern applications with older databases.
• Security and access control: By implementing business logic in stored procedures, developers can control data access and protect sensitive information.

Hasura has recently announced the ability to make native queries, which offer complete flexibility when reading data from the database. In doing so, we also introduced logical models, which are a totally freeform way of specifying the structure of the returned data from the database, which has the potential to reach much wider than native queries.

Today, we’d like to announce another feature built on top of logical models: Invoking read-only stored procedures in SQL Server, directly from Hasura GraphQL Engine.

Accessing stored procedures from Hasura allows you to:

• Leverage existing logic: For applications that interact with an already established database containing stored procedures, Hasura's integration allows developers to utilize existing logic without rewriting it, saving valuable development time.
• Improve performance: SQL Server stored procedures are optimized for execution, leading to faster response times and reduced server load. Hasura's support for stored procedures allows developers to tap into this performance advantage while utilizing GraphQL's efficient data retrieval capabilities.
• Control access to data: With GraphQL, app developers can fine-tune queries to request precisely the data they need. When paired with stored procedures, administrators can ensure that the application only has access to the data it needs, and no more.
• Simplify business logic: By exposing SQL Server stored procedures in Hasura's GraphQL schema, developers can abstract complex business logic from application code. This separation enhances code maintainability, reduces duplication, and streamlines development workflows.

Let’s imagine we’re running an online shop, backed by SQL Server. We have a set of products that we want to feature on the homepage, but only for a certain amount of time. We could encode this logic in our various frontends, but this would lead to duplication of work, so we’ve elected to create a stored procedure, managed by our database administrators.

We’ll create two tables:

CREATE TABLE product (
  id int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  name varchar(255) NOT NULL
);

CREATE TABLE product_feature (
  product_id int NOT NULL,
  region varchar(255) NOT NULL,
  featured_start datetime2,
  featured_end datetime2,
  CONSTRAINT fk_product_region_product_id FOREIGN KEY (product_id)
    REFERENCES product (id) ON DELETE CASCADE ON UPDATE CASCADE
);

Here, our product_feature table identifies each region associated with a product, as well as the start and end timestamps we use to decide when to feature that product. (We tie this to a region so we can feature products in different places at different times.)

So our stored procedure might look like this:

CREATE PROCEDURE featured_products
  @region varchar(255),
  @current_time datetime2
AS
  SELECT DISTINCT product.*
  FROM product
  JOIN product_region ON product.id = product_region.product_id
  WHERE region = @region
	AND @current_time BETWEEN featured_start AND featured_end;

This shows us all products to be featured for the given region, at the specified time.

We can now track this stored procedure in Hasura GraphQL Engine. Using the Console, we can navigate to the Native Queries section of the Data tab, and add a logical model corresponding to the structure returned by this procedure:

We then track a Stored Procedure specifying the arguments:

We can then query it just as you would a table. You can even add extra clauses to filter or sort the stored procedure results.

The synergy between SQL Server stored procedures and Hasura GraphQL Engine presents app developers with a powerful toolkit to build high-performance, secure, and scalable applications. By leveraging the benefits of stored procedures, such as improved performance, fine-grained data access, and simplified business logic, developers can optimize their application's data layer and streamline development processes.

From data validation to complex data manipulation and real-time capabilities, the combination of SQL Server stored procedures and GraphQL empowers developers to create robust applications that meet the demands of today's dynamic digital landscape. Whether it's a new application or an existing one seeking optimization, Hasura's support for stored procedures opens up a lot of possibilities for app developers.

To help you get started, we've prepared detailed documentation, guides, and examples:

• https://hasura.io/docs/latest/schema/ms-sql-server/logical-models/stored-procedures/
• https://hasura.io/docs/latest/schema/ms-sql-server/logical-models/index/

We can't wait to see the amazing applications you'll build using Hasura and Native Queries. Get started today by signing up for Hasura Cloud and connecting to one of the supported databases.

If you have any questions or need assistance, feel free to reach out to our team on Discord or GitHub.