GraphQL + Django: resolve queries using raw PostgreSQL query
<p><strong>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)?</strong></p>

<p>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 <a href="http://graphene-python.org/docs/django/tutorial/" rel="nofollow">Graphene Django tutorial</a> and then hit a road block when I realized the model I created was an amalgam of several tables.</p>

<p>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.</p>

<p>My approach since has been to avoid models and use the simpler method demonstrated in Steven Luscher's talk: <a href="https://youtu.be/UBGzsb2UkeY?t=4m30s" rel="nofollow">Zero to GraphQL in 30 Minutes</a>.</p>

<blockquote>
  <p><strong>TLDR;</strong></p>
  
  <p>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).</p>
</blockquote>

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

<pre><code>localhost:8000/api?query={orderItems{date,uuid,orderId}}
</code></pre>

<p>returns:</p>

<pre><code>{ "data":{ "orderItems":[ {"date":null, "uuid":null, "orderId":null }, ... ] } }
</code></pre>

<blockquote>
  <p>project/main/<strong>app/schema.py</strong></p>
</blockquote>

<pre class="lang-python prettyprint-override"><code>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;
    """)
</code></pre>

<p>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.</p>

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

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

<p>Here are some more references:</p>

<blockquote>
  <p>project/main/<strong>schema.py</strong></p>
</blockquote>

<pre class="lang-python prettyprint-override"><code>import graphene

from project.app.schema import QueryType AppQuery

class Query(AppQuery):
    pass

schema = graphene.Schema(
    query=Query, name='Pathfinder Schema'
)
</code></pre>

<blockquote>
  <p><strong>file tree</strong></p>
</blockquote>

<pre><code>|-- project
    |-- manage.py
    |-- main
        |-- app
            |-- models.py
            |-- schema.py
        |-- schema.py
        |-- settings.py
        |-- urls.py
</code></pre>

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

<blockquote>
  <p>project/main/<strong>app/schema.py</strong></p>
</blockquote>

<pre class="lang-python prettyprint-override"><code>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()
</code></pre>

python django postgresql graphql graphene-python