Ecto.Schema resolution proposal

[redrabbit]

Hi, I've been using Absinthe and Absinthe.Relay intensively for a private project and wrote a few helper functions in order to resolve GraphQL queries into Ecto queries.

I've put the code here: https://gist.github.com/redrabbit/be3528a4e4479886acbe648a693e65c0

I don't know if Absinthe.Ecto is going to be implemented in a similar manner. If there is interest, I can write a PR and add some tests to it.

Basically, it uses Ecto.Schema and Absinthe.Resolution to resolve pretty much every kind of GraphQL query I'd came across so far.

There are two distinct functions, build_query/3 and batch_query/5.

By default, build_query/3 automatically resolves :join, :preload and :select clauses. It tries to be as performant as possible and selects only required fields.

Setup

Let's say we have schemas representing a music library:

schema "artists" do
  field :name, :string
  has_many :albums, Album
  has_many :tracks, Track
  timestamps
end

schema "albums" do
  field :title, :string
  field :release_date, Ecto.Date
  belongs_to :artist, Artist
  many_to_many :genres, Genre, join_through: "albums_genres"
  has_many :tracks, Track
  timestamps
end

schema "genres" do
  field :name, :string
  many_to_many :albums, Album, join_through: "albums_genres"
  timestamps
end

schema "tracks" do
  field :title, :string
  field :track_nr, :integer
  field :duration, :integer
  field :popularity, :integer
  belongs_to :artist, Artist
  belongs_to :album, Album
  timestamps
end

And the following matching GraphQL types:

object :artist do
  field :id, :id
  field :name, :string
  field :albums, list_of(:album)
  field :tracks, list_of(:track) do
    arg :top, :integer, default_value: 10
    resolve &resolve_artist_top_tracks/3
  end
end

object :album do
  field :id, :id
  field :title, :string
  field :release_date, :date
  field :artist, :artist
  field :tracks, list_of(:track)
  field :genres, list_of(:genre)
end

object :genre do
  field :id, :id
  field :name, :string
  field :albums, list_of(:album)
end

object :track do
  field :id, :id
  field :title, :string
  field :artist, :artist
  field :album, :album
  field :track_nr, :integer
  field :duration, :integer
  field :popularity, :integer
end

Usage

Following GraphQL query:

{
  artists {
    name
    albums {
      title
      tracks {
        title
      }
      genres {
        name
      }
    }
  }
}

Returns a single Ecto.Query:

#Ecto.Query<from a0 in Artist,
           join: a1 in assoc(a0, :albums),
           join: g in assoc(a1, :genres),
           join: t in assoc(a1, :tracks),
         select: [:id, :name, {:albums, [:id, :title, {:genres, [:id, :name]}, {:tracks, [:id, :title]}]}],
        preload: [albums: {a1, [genres: g, tracks: t]}]>

It handles :belongs_to, :has_one, :has_many and :many_to_many associations and will try to join and preload as much as possible. It also support resolving cyclic graphs.

Writing resolvers is straight forward:

 def resolve_artists(_args, info) do
  query = build_query(Artist, info, &order_by(&1, [asc: :name]))
  {:ok, Repo.all(query)}
end

def resolve_artist_by_id(%{id: id}, info) do
  id = String.to_integer(id)
  query = build_query(Artist, info, &where(&1, [id: ^id]))
  case Repo.one(query) do
    nil ->
      {:error, "Cannot find artist with id #{id}."}
    artist ->
      {:ok, artist}
  end
end

In order to support batching and avoid N+1 queries, batch_query/5 provides similar functionalities and resolve the given associations automatically. For example:

  object :artist do
    field :id, :id
    field :name, :string
    field :albums, list_of(:album)
    field :tracks, list_of(:track) do
      arg :top, :integer, default_value: 10
      resolve fn artist, args, info ->
        batch_query(Track, artist, info, &Repo.all/1, fn query ->
            query
            |> order_by([desc: :popularity])
            |> limit(^min(Map.get(args, :top, 10), 100))
        end)
      end
    end
  end

In the above object, the :albums field is resolved automatically within the query using a :join. The :tracks field will require a 2nd query (using where: [artist_id: id] or where: a1.artist_id in ^ids).

Resulting in executing two SQL queries for the following GraphQL:

{
  artists {
    name
    tracks(top:5) {
      title,
      duration
    }
    albums {
      title
      tracks {
        title
      }
      genres {
        name
      }
    }
  }
}

2016-11-26 02:57:28.093 [debug] QUERY OK source="artists" db=2.1ms
SELECT a0."id", a0."name", a1."id", a1."title", t2."id", t2."title", g3."id", g3."name" FROM "artists" AS a0 INNER JOIN "albums" AS a1 ON a1."artist_id" = a0."id" INNER JOIN "tracks" AS t2 ON t2."album_id" = a1."id" INNER JOIN "albums_genres" AS a4 ON a4."album_id" = a1."id" INNER JOIN "genres" AS g3 ON a4."genre_id" = g3."id" ORDER BY a0."name" []
2016-11-26 02:57:28.095 [debug] QUERY OK source="tracks" db=1.6ms
SELECT t0."artist_id", t0."id", t0."title", t0."duration" FROM "tracks" AS t0 WHERE (t0."artist_id" = $1) ORDER BY t0."popularity" DESC LIMIT $2 [1, 5]

Customization

You can customize your queries using the optional transform parameter both functions provide.

For example, to fetch albums sorted by :release_date and album tracks sorted by :track_nr, one can write two new resolver functions:

def resolve_artist_albums(artist, _args, info) do
  batch_query(Album, artist, info, &order_by(&1, [desc: :release_date]))
end

def resolve_album_tracks(album, _args, info) do
  batch_query(Track, album, info, &order_by(&1, [asc: :track_nr]))
end

And update the schema types like this:

object :artist do
  field :albums, list_of(:album), do: resolve &resolve_artist_albums/3
end

object :album do
  field :tracks, list_of(:track), do: resolve &resolve_album_tracks/3  
end

[bruce]

Thanks for this -- very thorough, great work! I love that you dug into field selection as well. @benwilson512 and I will give this a better look-over soon. My guess is that we'll push you to PR, with some feedback.

[redrabbit]

As I already wrote the code in a very generic way for my own needs, I thought it my help this project as well. Even if it just brings some ideas.

There a certainly a few concerns which might be relevant thought.

1. Obviously, it uses Ecto.Schema to resolve fields. This means that the GraphQL objects must use the exact same field names as well. It would be nice if we could alias fields in order to differ from the original Ecto field names. Maybe this is something we can build on top of this.

2. The batch_query/5 function resolves the association field using :has_one/:has_many (see here). For example, if we want to batch over the artist's albums or tracks, it currently requires the Artist schema to provide following fields: has_many(:albums, Album) and has_many(:tracks, Track). It might be a better solution to use :belongs_to to resolve those associations. Note that the batch_query/5 function also provide a way to resolve the assoc manually (see here), passing the 2nd as a tuple:

def resolve_artist_albums(artist, _args, info) do
  # this:
  batch_query(Album, artist, info, &order_by(&1, [desc: :release_date]))
  # equals that:
  batch_query(Album, {:artist_id, artist.id}, info, &order_by(&1, [desc: :release_date]))
end

I'm note sure how 1. is relevant. Maybe it's ok to use the same Ecto and Absinthe schema and field names. I can imagine Absinthe.Ecto providing a very high-level integration will Ecto:

defmodule MyApp.GraphQL.Schema.Types do
  use Absinthe.Ecto.Schema.Notation

  alias MyApp.Ecto.Schema.{Artist, Album, Track}

  # create an object from the given Ecto.Schema
  object :artist, ecto_schema: Artist

  # same but exporting only specific fields
  object :artist, ecto_schema: Artist, only: [:id, :name, :albums]

  # same but using except instead of only
  object :artist, ecto_schema: Artist, except: [:tracks]

  # same but allows to define specific resolvers
  object :artist, ecto_schema: Artist, except: [:tracks] do
    # defines resolver for :albums
    field :albums, do: resolve &resolve_artist_albums/3
    # other fields are resolved automatically like in the above examples 
  end
end

[benwilson512]

This is a seriously impressive amount of work. There's a lot going on here and I'd like the chance to discuss it with you in a slightly more flexible medium. Do you have ScreenHero or Skype? You can find me on SH with my github handle [at] gmail [dot] com, and skype with just the same name as my github handle.

There's some philosophical differences between this approach and the one I had been targeting, but this is a ton of valuable capability, so I want to see where we can go with this.

[tlvenn]

ya great work @redrabbit ! Projecting only selected fields is great but it also has some challenges. There are 2 use cases where we will probably need something to load some fields even though they are not directly required by the graphql query:

• fields that should always be loaded even though they are not requested. One example are fields that are needed to complete authorization.
• fields that are abstract (don't map 1 for 1 to an ecto field) but do need some ecto fields to be resolved. Example would be avatar(size: small) that under the hood would use an ecto avatar_url field and transform it to match the requested size.

@benwilson512 did you have a chance to talk with @redrabbit already ?

[tlvenn]

Both above points could probably be easily solved using the new meta facility.

[redrabbit]

I had the pleasure to have a very interesting discussion with @benwilson512 a few days ago.

I think the proposed code is working quiet well for what it is meant to do, mapping GraphQL queries into Ecto queries if their schemas match exactly. The problem is that currently, it is a all-or-nothing solution.

If your GraphQL schema deviates from it Ecto.Schema counterpart, the functions will not be able to resolve the query correctly.

For example, let's say your Ecto.Schema has a :first_name and :last_name field. It would be nice to have the possibility to define a :name field on the GraphQL object which internally will fetch both first and last name and concat them. Doing so would also provide a way to define aliases, for example when the name of the GraphQL object field derives from the Ecto.Schema field.

@tlvenn, adding :select fields should be quiet easy. If you have a look at the code, you will see:

  def build_query(schema, info, transform \\ nil) do
    # Resolves fields using info,
    # basically, a shortcut for:
    # fields = info.definition.fields
    fields = resolve_fields(info)

    # Resolves select statement using Ecto.Schema and fields,
    # this is the :select statement from Ecto.Query.
    # It also fetches fields required foreign keys
    # in order to resolve associations.
    select = resolve_fields(schema, fields)

    # Resolves associations using Ecto.Schema and fields,
    # this is a bit more complicated than the :select statement above.
    # It handles :has_one, :has_many, :belongs_to and :many_to_many.
    assocs = resolve_assocs(schema, fields)

    # Creates the Ecto.Query using the given Ecto.Schema
    # and the above :select and :assoc statements.
    # The optional transform parameter is a function
    # used to transform/compose the final query.
    new_query(schema, select, assocs, transform)
  end

We could simply pass an extra select parameter for additional select statements...

I really like how Ecto is really explicit in contrast to other ORMs (say Django or ActiveRecord) which do a lot of magic behind the scene. Having Ecto.Schema and Ecto.Changeset decoupled from the database schema itself is also a great thing. I also really like how preloading and joining associations is a task left to the developer. My code has some of this magic stuff going on behind the scene, I think the Absinthe core team will have to find the right amount of things that can be handled automatically without sacrificing flexibility.

I've posted the code in order to bring some ideas to the table. Maybe Absinthe.Ecto will build on top of it or only extract some of it. Maybe none of it will be used and they will take a completely different approach. Maybe Absinthe.Ecto will go the more explicit way and this code should live in a different library though. Personally, I'm ok will all of this 😄.

[tlvenn]

For example, let's say your Ecto.Schema has a :first_name and :last_name field. It would be nice to have the possibility to define a :name field on the GraphQL object which internally will fetch both first and last name and concat them

Ya that's pretty much my point 2 and can easily be solved by adding meta at the field level listing the Ecto schema fields needed to properly resolve this graphql field. Then as you pointed out, it's fairly trivial to update the resolve_fields/2 to introspect requested objects and fields and build a list of additional fields that should be fetched out of the meta.

Thanks again for your contribution @redrabbit , very much appreciated.

[hellomika]

@redrabbit This is great! I've been using it in a project where my Absinthe schema almost perfectly matches my Ecto schema. Better than writing resolution batchers. And 1 query with 4 joins beats 5 queries.

That said I think I've found a bug. Using this query:

query {
  visits {
    id
    patient {
      id
      clients {
        id
      }
    }
    staff {
      id
    }
  }
}

I get this error:

** (Ecto.QueryError) could not find association `staff` on schema App.Patient in query:
from v in App.Visit,
  join: p in assoc(v, :patient),
  join: c in assoc(p, :clients),
  join: s in assoc(p, :staff),
  select: [:id, :patient_id, {:patient, [:id, {:clients, [:id]}]}, :staff_id, {:staff, [:id]}],
  preload: [patient: {p, [clients: c]}, staff: c]

It looks like staff got joined to patient instead of visits. I've been able to replicate it with multiple combinations of different fields, including arguments.

[benwilson512]

What we have here at the moment are two distinct ways of trying to efficiently load database records.

Whole Tree Up Front

This is the approach of the linked code. When resolving a top level field some form of introspection happens on the remaining tree producing a Big Fancy Query (BFQ). This query will effectively try to load the rest of the sub tree from the database in a single query. Resolver functions further down the tree don't really need to do anything since the data is already loaded.

Main Advantage: Load multiple tables worth of data in a single database query

Lazy Batching

This is the current approach of this library. Nothing is done up front. Resolution functions that map to a database association register the desire to load that association when run, and then groups of such associations are executed altogether.

Major Advantage: Extremely flexible.

A new scenario

Here's where things get interesting. My work with Apollo / Relay transport batching along with the recent work on subscriptions has put me in a situation where in each case I want to execute a SET of documents. That is instead of simply having:

{ posts(category: "a") { creator { name } } }

I have:

[
  {"query": "{ posts(category: \"a\") { creator { name } } }", "id": "A"},
  {"query": "{ posts(category: \"b\") { creator { name } } }", "id": "B"},
  {"query": "{ posts(category: \"c\") { creator { name } } }", "id": "C"},
  ...
]

This happens with transport batching when the front end groups together many graphql queries on the front end and then sends them back in one HTTP request. It happens in subscriptions because we track groups of documents that belong to particular subscription fields.

Batching here turns out to be super useful, because not only can you batch the creator lookup inside doc "A", you can batch all lookups for the creator field across ALL documents "A", "B", and so on.

No matter how many documents are there, you'll have just one database query for the users table. I'm not sure how this would be possible with the Whole Tree approach. You could try doing a tree merge but this starts to be an issue when the document shape varies.

Consider a document set:

[
  {"query": "{ posts(category: \"a\") { creator { name } } }", "id": "A"},
  {"query": "{ posts(category: \"b\") { creator { name } } }", "id": "B"},
  {"query": "{ posts(category: \"c\") { title }", "id": "C"},
  ...
]

For some posts we load the creator, for some we do not. If we tree merge we're going to load the creator for ALL posts whether or not the document actually asked us to. Obviously this will get filtered out prior to going to the client but it's database work we don't need to do and data loaded into memory that serves no purpose.

Things to think about:

• Document sets
• Argument handling { user { posts(category: "A") { comments(by: "user:123") { body}}}
• Mixed trees (some nodes at some levels are ecto based, others are not)
• multi-level batching maybe? Batching composes across siblings, but not from parents to children. Would this be valuable? doable? worth it?

[tlvenn]

@benwilson512 I always though that it would be best to leverage both approaches.

There are scenarios where the BFQ will win and others where batching queries is more efficient. The resolver from the batching strategy already check if the data is there already and if it's the case, simply return the data directly.

The missing piece would be a piece of middleware that could flag each query in the document set so the BFQ do not load any association at all for those flagged query and defer the work to the batching resolver.

The other scenario when it would be useful to fallback to the batching strategy is once we deal with the @defer @stream and @live