Edit

Share via

Multiple mutations in GraphQL for Data API builder

  • Article

Data API builder (DAB) supports combining multiple mutation operations together into a single transaction. Multiple mutations support scenarios where you need to create multiple items belonging to the same entity or create multiple items belonging to a related entity. This guide walks through a sample scenario using a multiple mutation operation.

Prerequisites

  • Existing SQL server and database.
  • Data API builder CLI. Install the CLI
  • A database client (SQL Server Management Studio, Azure Data Studio, etc.)

Create tables

Start by creating two basic tables to represent books and their respective chapters. Books have a one-to-many relationship with their corresponding chapters.

  1. Connect to the SQL database using your preferred client or tool.

  2. Create a table named Books with id, title, year, and pages columns.

    DROP TABLE IF EXISTS dbo.Books;

CREATE TABLE dbo.Books
(
    id int NOT NULL PRIMARY KEY,
    title nvarchar(1000) NOT NULL,
    [year] int null,
    [pages] int null
)
GO

  3. Create another table named Chapters with id, name, and pages columns. Create a book_id column with a foreign key relationship to the id column of the Books table.

    DROP TABLE IF EXISTS dbo.Chapters;

CREATE TABLE dbo.Chapters
(
    id int NOT NULL PRIMARY KEY,
    [name] nvarchar(1000) NOT NULL,
    [pages] int null,
    book_id int NOT NULL,
    FOREIGN KEY (book_id) REFERENCES dbo.Books(id)
)
GO

  4. Validate that your tables are created with some common SQL queries.

    SELECT * FROM dbo.Books

    SELECT * FROM dbo.Chapters

    Note

    At this point, the queries should not return any data.

Build configuration file

Now, use the DAB CLI to create a configuration file, entities, and entity relationships.

  1. Open a terminal

  2. Store your SQL database connection string as a shell variable named SQL_CONNECTION_STRING.

    SQL_CONNECTION_STRING="<your-sql-connection-string>"

    $SQL_CONNECTION_STRING="<your-sql-connection-string>"

  3. Run dab init specifying the following properties.

    Value
    database-type mssql
    graphql.multiple-create.enabled true
    host-mode development
    connection-string Use the SQL_CONNECTION_STRING shell variable created in the previous step. 
    dab init --database-type "mssql" --graphql.multiple-create.enabled true --host-mode "development" --connection-string $SQL_CONNECTION_STRING

  4. Run dab add to add a Book entity specifying the following properties.

    Value
    source dbo.Books
    permissions anonymous:* 
    dab add Book --source "dbo.Books" --permissions "anonymous:*"

  5. Run dab add again to add a Chapter entity now specifying the following properties.

    Value
    source dbo.Chapters
    permissions anonymous:* 
    dab add Chapter --source "dbo.Chapters" --permissions "anonymous:*"

  6. Run dab update to create the Book to Chapter relationship specifying the following properties.

    Value
    relationship chapters
    cardinality many 
    dab update Book --relationship chapters --target.entity Chapter --cardinality many

  7. Finally, run dab update one last time to create the Chapter to Book relationship specifying the following properties.

    Value
    relationship book
    cardinality one 
    dab update Chapter --relationship book --target.entity Book --cardinality one

Execute multiple create mutation

To wrap up things, use the DAB CLI to run the API and test the GraphQL endpoint using Banana Cake Pop.

  1. Start the runtime engine using the current configuration.

    dab start

  2. Navigate to the /graphql relative endpoint for your running application. This endpoint opens the Banana Cake Pop interface.

    Note

    By default, this would be https://localhost:5001/graphql.

  3. Author a GraphQL mutation to create three distinct rows across two tables in your database. This mutation uses both the Book and Chapter entities in a single "multiple create" operation. Use the following properties for the mutation.

    Entity type ID Name Pages Year
    Book 1 Introduction to Data API builder 200 2024
    Chapter 2 Configuration files 150
    Chapter 3 Running 50 
    mutation {
  createBook(
    item: {
      id: 1
      title: "Introduction to Data API builder"
      pages: 200
      year: 2024
      chapters: [
        {
            id: 2
            name: "Configuration files", pages: 150 
        }
        {
            id: 3
            name: "Running", pages: 50
        }
      ]
    }
  ) {
    id
    title
    pages
    year
    chapters {
      items {
        name
        pages
      }
    }
  }
}

  4. Observe the output from the mutation. The mutation created related data for both entity types.

    {
  "data": {
    "createBook": {
      "id": 1,
      "title": "Introduction to Data API builder",
      "pages": 200,
      "year": 2024,
      "chapters": {
        "items": [
          {
            "name": "Configuration files",
            "pages": 150
          },
          {
            "name": "Running",
            "pages": 50
          }
        ]
      }
    }
  }
}

  5. Use a GraphQL query to retrieve all books in your database including their related chapters.

    query {
  books {
    items {
      pages
      title
      year
      chapters {
        items {
          name
          pages
        }
      }
    }
  }
}

    Note

    At this point, the query should return the single book with two chapters.

  6. Observe the output from this query with an array of books including their nested array of chapters.

    {
  "data": {
    "books": {
      "items": [
        {
          "pages": 200,
          "title": "Introduction to Data API builder",
          "year": 2024,
          "chapters": {
            "items": [
              {
                "name": "Configuration files",
                "pages": 150
              },
              {
                "name": "Running",
                "pages": 50
              }
            ]
          }
        }
      ]
    }
  }
}

  7. Connect to the SQL database again using your preferred client or tool.

  8. Validate that your data was successfully created using a SQL query.

    SELECT 
    c.name AS chapterName,
    c.pages AS chapterPages,
    b.title AS bookName,
    b.year AS releaseYear
FROM dbo.Chapters c
LEFT JOIN dbo.Books b ON b.id = c.book_id

    Note

    This query should return two chapter records.

Related content