Bulk insert via GraphQL

[ayush3797]

Objective:

To support multiple records insertion in a single table via using a single graphql mutation object.

Example

mutation{
    createMultiple_Book(items: {
       [ 
          { title: "Book 1", publisher_id: 1234},
          { title: "Book 2", publisher_id: 2345},
          ...
       ]    
    }){
           <selection_set>            
    }
}

### Tasks
- [ ] https://github.com/Azure/data-api-builder/issues/1804
- [ ] https://github.com/Azure/data-api-builder/issues/1805
- [ ] https://github.com/Azure/data-api-builder/issues/1806

[ayush3797]

1. How do we handle failures (Exceptions/Database policies) for batch inserts?

• Suppose 10 records were to be inserted. We got an exception while inserting the 6th record. Shall we rollback the first 5 inserts as well?
  Question for @severussundar -> With the current transaction scopes that we have, what will be the behavior?
• For point operations, we used to throw an AuthZ error when no record was returned due to db policy failure. But here, there might be other records that satisfied the db policy and thus were inserted successfully. Won't make sense to throw an AuthZ failure for the entire request. Instead, should log some info and return the response.
• In the mutation, some records might have excluded fields in the body while other records might not. Do we want to fail the mutation entirely or just drop the records which have excluded fields?

2. Name of the batch insert mutation?

• We have mutations like createBook, createPublisher which are point mutations. Its highly likely that we would be extending the functionality of these mutations only to support nested inserts as well. However, for batch inserts the names of the point mutations would not make sense. We may name the batch insert mutations like:
• createMultiple_Book, createMultiple_Publisher
• createMany_Book, createMany_Publisher

or some sort of that.

[AbhijithGanesh]

This is interesting, I am curious if this would involve new feature building

[severussundar]

Adding my ideas for this feature:

Abstract:
At the moment, all the mutation operations supported by DAB are point operations ---> they act on a single item. If users want to perform operations on multiple items (for example, insert n records into an entity), they will have to make n point operation requests.

This issue proposes a feature to support inserting multiple items of the same type as a single mutation operation.
Example: Inserting 10 records to the Books table.

This feature is inapplicable for inserting multiple items of different types ~ inserting records into different tables
For example: Inserting 1 record into the Books table and 3 records into Reviews table. Requests involving multiple types (or entities) are outside the scope of this feature.

Schema:

New mutation operation type will have to be created when generating the schema.

• Name of the mutation operation: createBookMany. (Just a proposal. Please feel free to suggest alternate names)
• Input Type for the operation: [CreateBookInput!]!
• Mutation Response Type: BookConnection!

Example:

mutation{
    createBookMany(items: {
       [ 
          { title: "Book 1", publisher_id: 1234},
          { title: "Book 2", publisher_id: 2345},
          ...
       ]    
    }){
           <selection_set>            
    }
}

Selection Set:

Selection set is written and interpreted the same way as it would be for a list query. Selection set is always written within an items:{} block as data for multiple inserted records are expected to be returned.
Based on the relationships defined in the config file, related entities can be queried.

Example:

mutation{
    createBookMany(items: {
       [           ...
       ]    
    }){
           items{
               id
               title
               publishers{
                  id
                  name
               }              
               authors{
                   items{
                       id
                       name
                   }
               }
           }            
    }
}

The response will be an array of JSONs. Each JSON will correspond to a newly created item. Each Json will be in the same format as specified in the selection set.

Sample Response:

{
      "data":{
          "createBookMany": {
                "items":{ 
                    [
                        {
                             "id": 5001,
                             "title": "Book #1",
                              "publisher_id": 1234,
                              "publishers":{
                                       "id": 1234,
                                        "Name": "Big Company"  
                               },
                              "authors": {
                                      "items": []
                                }
                        },
                        {
                              "id": 5002,
                             "title": "Book #2",
                              "publisher_id": 2345,
                              "publishers":{
                                       "id": 2345,
                                        "Name": "Small Town Publisher"  
                               },
                              "authors": {
                                      "items": []
                                }
                        }
                    ]
                 }       
           }
       }
}

AuthZ requirements:
The role with which the mutation request is executed should have the create action setup. Also, the create action should have access to all the fields necessary for the mutation. If there are database policies defined for the create action, it would be evaluated against each item to be inserted.

Failure due to any reason on the create action - exlcuded fields, policy, etc. on any single item should lead to the rollback of all the inserts (if some inserts has succeeded already), and the mutation operation should be deemed unsuccessful.

For constructing the response, the read action setup will be taken into consideration (which is the current behavior for GraphQL).

• Excluded fields: When read action excludes a certain field but is included in the selection set, we throw an error right now. The same behavior can be retained as is.
• Database Policy: When a certain record does not satisfy a policy defined for the read action, a null/empty response to be returned. No errors to be thrown incase of policy failure.

Database Query Execution:
All the database queries associated with the insert part of the mutation and the fetch part of the mutation for all the items should be executed in a single transaction. So, for the client application, would see that either all the records got inserted successfully or none got inserted.

Other considerations:
Perhaps, we can limit the number of items that can be inserted in a single operation.

[JerryNixon]

This is a very valuable feature. Some thoughts.

createBookBulk instead of createBookMany

This feature is awesome. But we want to communicate to the consumer through our names what is going on behind the scenes. In this case, we want to ensure they understand this is a bulk operation, not a "big" operation. Bulk operations, by the way, are supported in SQL, Postgres and MySQL. Maybe Cosmos, but it's an amazing feature.

Add bulk to permissions: [ create, read, update, delete ]

The reason for this is because bulk operations are not always appropriate. In addition, they automatically disable certain constraints that are fine to do for the right reason and by the right user, but as a developer I would want to constrain which user can do these types of operations.

Important limits: Only one top-level, and nothing nested.

To make bulk operations work, it makes sense to simplify our engineering effort to limit top level operations to one. In addition, nested mutations during a bulk operation are inappropriate. The reason for this is the ancillary logic for nested mutations are, in some ways, impossible during bulk operations.

✅ Yes

mutation{
    createBookBulk(items: {
       [ 
          { title: "Book 1", publisher_id: 1234},
          { title: "Book 2", publisher_id: 2345},
          ...
       ]    
    }){ <selection_set> }
}

❌ No

mutation{
    createBookBulk(items: {
       [ 
          { title: "Book 1", publisher_id: 1234},
          { title: "Book 2", publisher_id: 2345},
          ...
       ]    
    }){ <selection_set> }

    createPublisherBulk(items: {
       [ 
          { name: "Publisher 1"},
          { name: "Publisher 2"},
          ...
       ]    
    }){ <selection_set> }
}

Payload size limits in docs: needed

As you are thinking about this feature, don't think that the consumer will insert 1 or 10 records, instead think that the consumer will be bulk inserting 15,000 records. Today we do not need to really document the internal limit to the GQL payload, but we will need to with this feature as the payload size is not infinite. This documentation will allow the consumer/developer to play their calls and split them as necessary.

Parallel? Yes Atomic? Yes

There are other discussions around transactions and parallel versus serial. The only point I want to make here is that bulk operations are not relevant to those conversations. If we introduce a atomicity flag or not, bulk operations should be defined on their own with separate logic. Bulk operations are automatically in parallel and must be treated as a single operation.

One more thing. It has to be bulk!

Just an aside. This has to be a bulk insert. If I pass in 15,000 records to insert and DAB tries to insert those one by one serially, this whole thing is useless. It would be better for me to have individual insert operations from my client and call DAB 15,000 times in parallel. But we don't want developers to think like this. We want them to have the benefit of bulk operations.

Remember we already support this (we don't need another syntax for the same thing):

mutation {
    createBook( ... )
    createBook( ... )
    createBook( ... )
    createBook( ... )
    createBook( ... )
    createBook( ... )
    createBook( ... )
}

Bulk inserts are awesome!