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

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!