GraphQL + Django: resolve queries using raw PostgreSQL query

Question

What is the best way to use GraphQL with Django when using an external database to fetch data from multiple tables (i.e., creating a Django Model to represent the data would not correspond to a single table in my database)?

My approach was to temporarily abandon using Django models since I don't think I fully understand them yet. (I'm completely new to Django as well as GraphQL.) I've set up a simple project with an app with a connected external Postgres DB. I followed all the setup from the Graphene Django tutorial and then hit a road block when I realized the model I created was an amalgam of several tables.

I have a query that sends back the proper columns mapped to the fields in my model, but I don't know how to make this a dynamic connection such that when my API is hit, it queries my database and maps the rows to the model schema I've defined in Django.

My approach since has been to avoid models and use the simpler method demonstrated in Steven Luscher's talk: Zero to GraphQL in 30 Minutes.

TLDR;

The goal is to be able to hit my GraphQL endpoint, use a cursor object from my django.db.connection to get a list of dictionaries that should resolve to a GraphQLList of OrderItemTypes (see below).

The problem is I am getting nulls for every value when I hit the following endpoint with a query:

localhost:8000/api?query={orderItems{date,uuid,orderId}}

returns:

{ "data":{ "orderItems":[ {"date":null, "uuid":null, "orderId":null }, ... ] } }

project/main/app/schema.py

import graphene
from django.db import connection


class OrderItemType(graphene.ObjectType):
    date = graphene.core.types.custom_scalars.DateTime()
    order_id = graphene.ID()
    uuid = graphene.String()

class QueryType(graphene.ObjectType):
    name = 'Query'
    order_items = graphene.List(OrderItemType)

    def resolve_order_items(root, args, info):
        data = get_order_items()

        # data prints out properly in my terminal
        print data
        # data does not resolve properly
        return data


def get_db_dicts(sql, args=None):
    cursor = connection.cursor()
    cursor.execute(sql, args)
    columns = [col[0] for col in cursor.description]
    data = [
        dict(zip(columns, row))
        for row in cursor.fetchall() ]

    cursor.close()
    return data

def get_order_items():
    return get_db_dicts("""
        SELECT j.created_dt AS date, j.order_id, j.uuid
        FROM job AS j
        LIMIT 3;
    """)

In my terminal, I print from QueryType's resolve method and I can see the data successfully comes back from my Postgres connection. However, the GraphQL gives me nulls so it has to be in the resolve method that some mapping is getting screwed up.

[ { 'uuid': u'7584aac3-ab39-4a56-9c78-e3bb1e02dfc1', 'order_id': 25624320, 'date': datetime.datetime(2016, 1, 30, 16, 39, 40, 573400, tzinfo=<UTC>) }, ... ]

How do I properly map my data to the fields I've defined in my OrderItemType?

Here are some more references:

project/main/schema.py

import graphene

from project.app.schema import QueryType AppQuery

class Query(AppQuery):
    pass

schema = graphene.Schema(
    query=Query, name='Pathfinder Schema'
)

file tree

|-- project
    |-- manage.py
    |-- main
        |-- app
            |-- models.py
            |-- schema.py
        |-- schema.py
        |-- settings.py
        |-- urls.py

Foreword

Answer 1

Default resolvers on GraphQL Python / Graphene try to do the resolution of a given field_name in a root object using getattr. So, for example, the default resolver for a field named order_items will be something like:

def resolver(root, args, context, info):
    return getattr(root, 'order_items', None)

Knowing that, when doing a getattr in a dict, the result will be None (for accessing dict items you will have to use __getitem__ / dict[key]).

So, solving your problem could be as easy as change from dicts to storing the content to namedtuples.

import graphene
from django.db import connection
from collections import namedtuple


class OrderItemType(graphene.ObjectType):
    date = graphene.core.types.custom_scalars.DateTime()
    order_id = graphene.ID()
    uuid = graphene.String()

class QueryType(graphene.ObjectType):
    class Meta:
        type_name = 'Query' # This will be name in graphene 1.0

    order_items = graphene.List(OrderItemType)

    def resolve_order_items(root, args, info):
        return get_order_items()    


def get_db_rows(sql, args=None):
    cursor = connection.cursor()
    cursor.execute(sql, args)
    columns = [col[0] for col in cursor.description]
    RowType = namedtuple('Row', columns)
    data = [
        RowType(*row) # Edited by John suggestion fix
        for row in cursor.fetchall() ]

    cursor.close()
    return data

def get_order_items():
    return get_db_rows("""
        SELECT j.created_dt AS date, j.order_id, j.uuid
        FROM job AS j
        LIMIT 3;
    """)

Hope this helps!

Scheller

That was incredibly helpful, thank you for taking the time! One minor edit is the row list should be spread before passed to the namedtuple RowType(*row). In general, is this good practice or is there a better approach to fetching the external data? – Foreword

If you see my second attempt (the other answer posted to this question), I used a resolver for each field using the dict[key] approach you suggested. From a couple of tests I just ran, it seems both methods return the data in the same amount of time. I wonder if the somewhat slow return time from GraphQL is normal (1000 records ~ 3-4 seconds after caching; before cachine, it takes ~6 seconds for the same dataset). – Foreword

If you use the development version, the query will be resolved at least 10x times faster (300ms?). You can install it with pip install graphene-django>=1.0.dev. Hope this helps! – Scheller

Answer 2

Here is temporary workaround, although I'm hoping there is something cleaner to handle the snake_cased fieldnames.

project/main/app/schema.py

from graphene import (
    ObjectType, ID, String, Int, Float, List
)
from graphene.core.types.custom_scalars import DateTime
from django.db import connection

''' Generic resolver to get the field_name from self's _root '''
def rslv(self, args, info):
    return self.get(info.field_name)


class OrderItemType(ObjectType):
    date = DateTime(resolver=rslv)
    order_id = ID()
    uuid = String(resolver=rslv)
    place_id = ID()

    ''' Special resolvers for camel_cased field_names '''
    def resolve_order_id(self, args, info):
    return self.get('order_id')

    def resolve_place_id(self, args, info):
        return self.get('place_id')

class QueryType(ObjectType):
    name = 'Query'
    order_items = List(OrderItemType)

    def resolve_order_items(root, args, info):
        return get_order_items()

Foreword

It should be noted that having all of these resolvers makes GraphQL incredibly slow. – Foreword

Answer 3

One can also just change the default resolver for the graphene.object.

I believe the following would work after restructuring.

from graphene.types.resolver import dict_resolver

class OrderItemType(ObjectType):

    class Meta:
        default_resolver = dict_resolver

    date = DateTime()
    order_id = ID()
    uuid = String()
    place_id = ID()

Even if this method is not directly applicable to the above question, this question is what I found when researching how to do this.

dict_resolver

Selfhood