Add subscription helpers

[danielrearden]

It would be feasible to automatically publish an event when an insert, update or delete operation takes place, potentially reducing the boilerplate required to set up subscriptions. A PubSub implementation would need to be provided when creating the client instance.

[JeffML]

I can propose an API for this.

[JeffML]

Subscription Thoughts

In the current documentation, it is suggested that the client would subscribe to specific table_action events, like CUSTOMER_CREATED, which is fine-grained interface with stongly typed return values. However, with 100 tables and 3 subscription topics per table, that would suggest that a fully-informed subscriber would need to monitor to 300 separate topics.

For argument's sake, a simpler API would have three subscription topice (create, update, delete) and return a generic column:value array result for all data mutation operations. The values in the array would be strings, meaning that the the client would not have type information unless it otherwise encoded in the array values ,e.g. [{"firstName": ["Jeff", "String"]}, ...]. Though lack of type safety is a big drawback, the API would be much simpler, using mutation operation types (create, update, delete) as the subscription topics, and table name(s) as parameters:

subscription {
  inserts ( tables: ["Customer", "Order", "Invoice"] ){  
    tableName
    columnValues {
      name
      value
    }
  }

  # wildcard option
  inserts ( tables: ["*"] except: ["Invoice", "Order] ) {...} 
}

Similar API would be used for UPDATE and DELETE operation subscription topics.

---

Is there an in-between approach?

The sqlmancer generator could generate an INSERT subscription topic, taking table names as parameters, then return a union type of all possible table-specific INSERT event return types.

schema:

  union InsertResult = Customer | Order | Invoice | ...
  enum TABLES { CUSTOMER, ORDER, INVOICE, ... }  

  subscription {
    insert(tables: TABLES!) : InsertResult!
  }

client:

subscription {
  inserts( tables: [CUSTOMER, ORDER, INVOICE] ) {
    ...on Customer {...}
    ...on Order {...}
    ...on Invoice {...}
}

In this implementation, the union and enum types would be generated by sqlmancer. The client would be responsible for correctly anticipating the InsertResult structure based on what it has subscribed to. So you wind up with type safety and a simple API. The drawback is that all those ...on statements that the client has to support is verbose, but probably can't be helped if the client wants to ensure that the return data is of the type it assumes.

---

@danielrearden, you had mentioned one problem with mutation operations is that some databases only return the id of the created/updated/deleted row. Why not just return the original input to the GraphQL mutation call? For CREATE operations, sqlmancer would throw in the (newly generated?) primary key as well. It may be better to do it that way, rather than rely on secondary query to fetch data that was just inserted, since the row could conceivably change between the mutation and the refetch of the row data to build a return value.

[danielrearden]

@JeffML Thanks for your input. Aggregating events like you've described is very clever, and it also conveniently gets around GraphQL limiting subscriptions to only one root field.

By necessity, Sqlmancer is already pretty opinionated about how queries should look because of how it maps selection sets to database projections. Mutations, on the other hand, are intentionally very flexible -- you can still do anything you want inside your resolver and return data in whatever shape is appropriate for your API. Ideally, I'd like to maintain that same sort of flexibility for subscriptions and avoid the library dictating a particular schema pattern.

If individual create/update/delete operations published events like shown in the documentation (i.e. model_action), these could still be combined into a single AsyncIterator inside the subscribe function to allow the sort of pattern you illustrated. There are libraries out there for merging AsyncIterators, but we could maybe expose a small utility for that as part of this library.

import { mergeAsyncIterators } from 'sqlmancer'

const resolvers = {
  Subscription: {
    inserts: {
      subscribe: () => mergeAsyncIterators([
        pubSub.asyncIterator('USER_CREATED'),
        pubSub.asyncIterator('POST_CREATED'),
        ...
      ])
    }
  }
}

I think adding a publish method to the existing Create/Update/Delete builders would be a good start. If a PubSub instance is passed to createSqlmancerClient, it could then be passed down to any builder instances that are initialized when calling individual model methods like createOne. So the API could be as simple as

Post.createOne({ ... }).publish().execute()

Then on the subscription side of things, something like this could return the appropriate AsyncIterator instance:

Post.subscribe('CREATE')

What do you think?

[JeffML]

A simple implementation of subscriptions is better than nothing, though I feel safe in assuming that what will eventually be desired is a return value that includes the mutation input data on success, and error on failure.

Whether the subscription event returns GraphQL input types or some array of key-value pairs of table column values, I don't know. Feature requests will determine what is needed. I agree that dictating a schema is not desirable, but convenience functions might be useful for helping users build mutation/subscription responses, though it would be too early in the game to attempt to implement those.

Aggregating async operations hadn't occurred to me, but it seems like nice idea for grouping subscription events under one topic, as determined by the client implementor. I suspect the implentation of mergeAsyncIterator() is simple, but I might be surprised.

So it seems the first order of business is to write a mechanism for generating publish methods, as you suggest, so I'll start looking deeper into it.