graphql-python/graphene-sqlalchemy

Graphene SQLAlchemy integration

Version 3.0 is in beta stage. Please read #348 to learn about progress and changes in upcoming beta releases.

---

Graphene-SQLAlchemy

A SQLAlchemy integration for Graphene.

Installation

For installing Graphene, just run this command in your shell.

pip install --pre "graphene-sqlalchemy"

Examples

Here is a simple SQLAlchemy model:

from sqlalchemy import Column, Integer, String

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class UserModel(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    last_name = Column(String)

To create a GraphQL schema for it, you simply have to write the following:

import graphene
from graphene_sqlalchemy import SQLAlchemyObjectType

class User(SQLAlchemyObjectType):
    class Meta:
        model = UserModel
        # use `only_fields` to only expose specific fields ie "name"
        # only_fields = ("name",)
        # use `exclude_fields` to exclude specific fields ie "last_name"
        # exclude_fields = ("last_name",)

class Query(graphene.ObjectType):
    users = graphene.List(User)

    def resolve_users(self, info):
        query = User.get_query(info)  # SQLAlchemy query
        return query.all()

schema = graphene.Schema(query=Query)

We need a database session first:

from sqlalchemy import (create_engine)
from sqlalchemy.orm import (scoped_session, sessionmaker)

engine = create_engine('sqlite:///database.sqlite3', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))
# We will need this for querying, Graphene extracts the session from the base.
# Alternatively it can be provided in the GraphQLResolveInfo.context dictionary under context["session"]
Base.query = db_session.query_property()

Then you can simply query the schema:

query = '''
    query {
      users {
        name,
        lastName
      }
    }
'''
result = schema.execute(query, context_value={'session': db_session})

You may also subclass SQLAlchemyObjectType by providing abstract = True in your subclasses Meta:

from graphene_sqlalchemy import SQLAlchemyObjectType

class ActiveSQLAlchemyObjectType(SQLAlchemyObjectType):
    class Meta:
        abstract = True

    @classmethod
    def get_node(cls, info, id):
        return cls.get_query(info).filter(
            and_(cls._meta.model.deleted_at==None,
                 cls._meta.model.id==id)
            ).first()

class User(ActiveSQLAlchemyObjectType):
    class Meta:
        model = UserModel

class Query(graphene.ObjectType):
    users = graphene.List(User)

    def resolve_users(self, info):
        query = User.get_query(info)  # SQLAlchemy query
        return query.all()

schema = graphene.Schema(query=Query)

Full Examples

To learn more check out the following examples:

• Flask SQLAlchemy example
• Nameko SQLAlchemy example

Contributing

See CONTRIBUTING.md

graphene-sqlalchemy's Issues

SQLAlchemyObjectType{Meta} should be more friendly to subclassing implementations.

Currently, SQLAlchemyObjectTypeMeta has the following bits that complicate attempts at subclassing it to extend functionality:

1. This code is the only way to suppress all of the 'metaclass magic':

        if not is_base_type(bases, SQLAlchemyObjectTypeMeta):
            return type.__new__(cls, name, bases, attrs)

2. It's generally a mess if you want to add your own options to the Meta class. A subclass can do so by deleting them from attrs['Meta'] beforehand and doing setattr(cls._meta, option, value) after calling the superclass, but that's a bit clunky.

The first limitation becomes an issue when doing things like this:

class MyReplacementSQLAlchemyObjectType(SQLAlchemyObjectType, metaclass=MyReplacementMetaclass):

when the class isn't supposed to map to a model but rather it's intending to add functionality for its own subclasses.

A few possible solutions to this could be:

• Suppressing the metaclass magic (i.e. returning type.new) if the new class subclasses AbstractType

• Suppressing the metaclass magic if the class defines __abstract__ = True as an attribute (and deleting the attribute). SQLAlchemy does something like this.

The second issue is something I've seen addressed by having a selectable Options subclass or factory rather than the hardcoded Options() that exists now. (Marshmallow uses something like this). in This would require some architectural changes in graphene as well though. One implementation of a revamped options might simply be something like:

class Options(object):
    setting = "some default value"
    setting2 = "another default value"
  
    def __init__(self, **kwargs):
        invalid = []
        for k, v in kwargs:
            if not hasattr(self, k):
                invalid.append(k)
            else:
                setattr(self, k, v)
    
        if invalid:        
            raise TypeError(
                "Invalid attributes: {}".format(', '.join(sorted(kwargs.keys()))))

class SQLAlchemyOptions(Options):
   model = None
   # ...
   # No __init__ required, though it could be used to perform validation rather than having it at the metaclass level.

Then:

• Options subclasses can simply add class-level variables to add new options (or change defaults of old ones) to those supported.

• Metaclasses can use attrs['OPTIONS_CLASS'] as the class to use instead of Options. If it's undefined (which it will be 99% of the time), they use the first entry in bases to define it instead.

Marshmallow uses an approach similar to this.

How to solve 'utf8' can't decode,because of string:högskolan

if there is a string: högskolan in database,then there well be a error:
{
"errors": [
{
"message": "'utf8' codec can't decode byte 0xf6 in position 34: invalid start byte",
"locations": [
{
"column": 3,
"line": 2
}
]
}
],
"data": {
"allDegreess": null
}
}

Model vs Node in naming in docs and tests

The documentation and example code use: Example and ExampleModel.

https://github.com/graphql-python/graphene-sqlalchemy/blob/08fcfc0b2548cd3519e4b19772acda4495089392/docs/tutorial.rst#schema

from models import db_session, Department as DepartmentModel, Employee as EmployeeModel

class Department(SQLAlchemyObjectType):
    class Meta:
        model = DepartmentModel
        interfaces = (relay.Node, )

The unit tests use the opposite naming convention: ExampleNode and Example.

graphene-sqlalchemy/graphene_sqlalchemy/tests/test_query.py

Line 10 in 08fcfc0

from .models import Article, Base, Editor, Reporter

from .models import Article, Base, Editor, Reporter

    class ArticleNode(SQLAlchemyObjectType):

        class Meta:
            model = Article
            interfaces = (Node, )

Which is "correct"?

Make a Relay ConnectionField optimized to work with Query

Graphene has the graphene.relay.ConnectionField, which slices a list/iterable to build the edges inside the Relay connection.
But it doesn't play well with DB queries. It calls len() with Query doesn't have.

graphene-sqlalchemy should have a implementation of the ConnectionField that is aware of the sqlalchemy's Query and use it to slice directly in the DB.

For now, I'm using this workaround:

class RelayConnectionField(graphene.relay.ConnectionField):
    @classmethod
    def resolve_connection(cls, connection_type, args, resolved):
        if isinstance(resolved, Query):
            len_ = resolved.count()
            connection = connection_from_list_slice(
                resolved,
                args,
                connection_type=connection_type,
                edge_type=connection_type.Edge,
                pageinfo_type=PageInfo,
                slice_start=0,
                list_length=len_,
                list_slice_length=len_,
            )
            connection.iterable = resolved
            return connection
        else:
            return super().resolve_connection(connection_type, args, resolved)

Support for Custom Column Types?

I don't see anything in the documentation and a brief look through the source code didn't reveal anything immediately. I have a custom GUID type (as documented in the sqlalchemy docs here: http://docs.sqlalchemy.org/en/latest/core/custom_types.html#backend-agnostic-guid-type) and when I try to use the SQLAlchemyObjectType with the model metaclass, it doesn't know how to convert type GUID. Is there a way to handle these custom column types? Do I need to just subclass SQLAlchemyObjectType and make it understand what that is?

Thanks for any help.

Schema creation code broken in Flask-SQLAlchemy website tutorial

Summary

The schema creation code in the website tutorial does not work. Changing it to match examples/flask_sqlalchemy fixes the problem.

Details

In the tutorial on the website, the code to create the schema looks like this:

schema = graphene.Schema()

class Department(SQLAlchemyObjectType):
    # ...

class Employee(SQLAlchemyObjectType):
    # ...

class Query(graphene.ObjectType):
    node = relay.Node.Field()
    all_employees = SQLAlchemyConnectionField(Employee)

schema.query = Query

When the example query is submitted, this results in an error:

{
  "errors": [
    {
      "message": "'NoneType' object has no attribute 'fields'"
    }
  ],
  "data": null
}

The code in examples/flask_sqlalchemy is different and works; it defines the schema like this:

schema = graphene.Schema(query=Query, types=[Department, Employee, Role])

and if you change the website tutorial code to match, the problem goes away:

schema = graphene.Schema(query=Query, types=[Department, Employee])

This also works:

schema = graphene.Schema(query=Query)

how to Join with get_query

Just trying to filter on a field from a foreign table.

This doesn't seem to work

def resolve_articles(self, args, context, info):
        query = Article.get_query(context)
        authorName = args.get('authorName')
        return query.join(Author).filter(Author.name == authorName)

Subclassing Connection in 2.0

Hi, in 2.0dev version I can't see the way to subclass relay.Connection. Because SQLAlchemyObjectType doesn't have a Connection attribute, the only way I see is to pass connection via the Meta class. Otherwise the connection is automatically created with default Connection class. But I can't specify the subclassed connection in Meta, because it needs a node attribute and I get circular reference. E. g.

class UserConnection(graphene.relay.Connection):
    class Meta:
        node = User

class User(SQLAlchemyObjectType):
    class Meta:
        model = UserModel
        interfaces = (relay.Node, )
        connection = UserConnection

So I don't understand what's the point of connection parameter in SQLAlchemyObjectType Meta class? To make it work, I have changed SQLAlchemyObjectType.__init_subclass_with_meta__ and introduced connection_type parameter. Now I can make an abstract subclassed Connection and pass it to Meta:

class SQLAlchemyObjectType(ObjectType):
    @classmethod
    def __init_subclass_with_meta__(cls, model=None, registry=None, skip_registry=False,
                                    only_fields=(), exclude_fields=(), connection=None, connection_type=None,
                                    use_connection=None, interfaces=(), id=None, **options):

        ...

        if use_connection and not connection:
            # We create the connection automatically
            connection = (connection_type or Connection).create_type('{}Connection'.format(cls.__name__), node=cls) 

        ...

class UserConnection(graphene.relay.Connection):
    class Meta:
        abstract = True


class User(SQLAlchemyObjectType):
    class Meta:
        model = UserModel
        interfaces = (relay.Node, )
        connection_type = UserConnection

Maybe I don't understand something and there is an easier way to make it work?

[BUG] No conversion for PostgreSQL INET Type

Still working this one out, but I believe the error I am receiving is due to a lack of conversion mechanisms in support of the PostgreSQL INET type (see below).

from sqlalchemy.dialects.postgresql import INET
... = Column(INET, ...)

Changing Column type to String does stop the error from occurring.. so sadly yes this is the issue. Fortunately it doesn't appear I need to modify by database, just the model. It would be great to get better type support.

I imagine we just need a means to translate INET into a string and view it as a scalar. Running type() against this field in the terminal returns the INET value as a string, so the mechanism already appears to be in place, it just needs to be supported in graphene_sqla

Traceback (most recent call last):
  File "run.wsgi", line 1, in <module>
    from app import create_app
  File "/srv/www/edge/server/app/__init__.py", line 5, in <module>
    from app.schema import schema
  File "/srv/www/edge/server/app/schema.py", line 7, in <module>
    from app.types import Viewer
  File "/srv/www/edge/server/app/types/__init__.py", line 10, in <module>
    from .watch import Watch
  File "/srv/www/edge/server/app/types/watch.py", line 11, in <module>
    class Watch(SQLAlchemyObjectType):
  File "/srv/www/edge/server/venv/lib/python3.5/site-packages/graphene_sqlalchemy/types.py", line 104, in __new__
    construct_fields(options),
  File "/srv/www/edge/server/venv/lib/python3.5/site-packages/graphene_sqlalchemy/types.py", line 36, in construct_fields
    converted_column = convert_sqlalchemy_column(column, options.registry)
  File "/srv/www/edge/server/venv/lib/python3.5/site-packages/graphene_sqlalchemy/converter.py", line 82, in convert_sqlalchemy_column
    return convert_sqlalchemy_type(getattr(column, 'type', None), column, registry)
  File "/srv/www/edge/server/venv/lib/python3.5/site-packages/singledispatch.py", line 210, in wrapper
    return dispatch(args[0].__class__)(*args, **kw)
  File "/srv/www/edge/server/venv/lib/python3.5/site-packages/graphene_sqlalchemy/converter.py", line 88, in convert_sqlalchemy_type
    "Don't know how to convert the SQLAlchemy field %s (%s)" % (column, column.__class__))
Exception: Don't know how to convert the SQLAlchemy field watches.id_orig_h (<class 'sqlalchemy.sql.schema.Column'>)

I guess while were at it, I imagine I will have the same error when using CIDR types as well from
from sqlalchemy.dialects.postgresql import CIDR

How to query by argument?

I was wondering if querying a schema via specific arguments is supposed to work out of the box or if anything special must be done to make it work?

In case of the flask example, I was expecting the following to be a valid query:

{
    role(roleId: 2) {
        roleId
        name
    }
}

But I only get

Unknown argument "roleId" on field "role" of type "Query".

So how would I need to extend the example so that I could search employees by their name or retrieve a role via the ID? Or is my query just wrong?

get_query method assumes query is an attribute, fails when its a callable

The get_query methods from both SQLAlchemyConnectionField and SQLAlchemyObjectType
assume that the Base model has an attribute query but if this is a property it will fail.

I have made this changes to fix it in SQLAlchemyConnectionField:

class SQLAlchemyConnectionField(Connection):
    @classmethod
    def get_query(cls, model, info, **kwargs):
        q = get_query(model, info.context)
        return q() if callable(q) else q

But is probably better to do it in the utils module:

def get_query(model, context):
    query = getattr(model, 'query', None)
    if not query:
        session = get_session(context)
        if not session:
            raise Exception('A query in the model Base or a session in the schema is required for querying.\n'
                            'Read more http://graphene-python.org/docs/sqlalchemy/tips/#querying')
        query = session.query(model)
    else:
        query = query() if callable(query) else query
return query

Import error after updating graphene-sqlalchemy (and graphene) to 2.0.dev

Simply importing SQLAlchemyObjectType causes a failure:

In [1]: from graphene_sqlalchemy import SQLAlchemyObjectType
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-1-a0d050cc2bfb> in <module>()
----> 1 from graphene_sqlalchemy import SQLAlchemyObjectType

/Users/mojochao/.virtualenvs/graphene-research/lib/python2.7/site-packages/graphene_sqlalchemy/__init__.py in <module>()
----> 1 from .types import (
      2     SQLAlchemyObjectType,
      3 )
      4 from .fields import (
      5     SQLAlchemyConnectionField

/Users/allengooch/.virtualenvs/graphene-research/lib/python2.7/site-packages/graphene_sqlalchemy/types.py in <module>()
     10 from graphene.types.utils import yank_fields_from_attrs
     11
---> 12 from .converter import (convert_sqlalchemy_column,
     13                         convert_sqlalchemy_composite,
     14                         convert_sqlalchemy_relationship,

/Users/mojochao/.virtualenvs/graphene-research/lib/python2.7/site-packages/graphene_sqlalchemy/converter.py in <module>()
    102 @convert_sqlalchemy_type.register(postgresql.ENUM)
    103 @convert_sqlalchemy_type.register(postgresql.UUID)
--> 104 @convert_sqlalchemy_type.register(TSVectorType)
    105 def convert_column_to_string(type, column, registry=None):
    106     return String(description=get_column_doc(column),

NameError: name 'TSVectorType' is not defined

My environment is Python 2.7.13 with the following packages installed into a clean virtualenv:

appnope (0.1.0)
backports.shutil-get-terminal-size (1.0.0)
click (6.7)
decorator (4.1.2)
enum34 (1.1.6)
Flask (0.12.2)
Flask-GraphQL (1.4.1)
Flask-SQLAlchemy (2.2)
graphene (2.0.dev20170802065539)
graphene-sqlalchemy (2.0.dev2017073101)
graphql-core (2.0.dev20170801051721)
graphql-relay (0.4.5)
ipdb (0.10.3)
ipython (5.4.1)
ipython-genutils (0.2.0)
iso8601 (0.1.12)
itsdangerous (0.24)
Jinja2 (2.9.6)
MarkupSafe (1.0)
pathlib2 (2.3.0)
pexpect (4.2.1)
pickleshare (0.7.4)
pip (9.0.1)
promise (2.1.dev0)
prompt-toolkit (1.0.15)
ptyprocess (0.5.2)
Pygments (2.2.0)
scandir (1.5)
setuptools (36.3.0)
simplegeneric (0.8.1)
singledispatch (3.4.0.3)
six (1.10.0)
SQLAlchemy (1.1.13)
traitlets (4.3.2)
typing (3.6.2)
wcwidth (0.1.7)
Werkzeug (0.12.2)
wheel (0.29.0)

resolver outside of the class isn't working in SQLAlchemyConnectionField

I've been trying to get this to work for some time and haven't been able to figure it out. I am trying to use a resolver outside of a class using an SQLAlchemyConnectionField. The resolver is called when it is in the class however not when it is outside of the class. I have modified the flask example code to demonstrate this:

class Employee(SQLAlchemyObjectType):

    class Meta:
        model = EmployeeModel
        interfaces = (relay.Node, )

    favourite_roles = SQLAlchemyConnectionField(Role)

    def resolve_favourite_roles(self, args, context, info):
        # everyone wants to be a manager
        return Role.get_query(Role).filter_by(name='manager')

The above code works as expected. for the query:

{allEmployees {
  edges {
    node {
      name
      favouriteRoles {
        edges{
          node {
          	name
          }
        }
      }
    }
  }
}}

It returns:

{
  "data": {
    "allEmployees": {
      "edges": [
        {
          "node": {
            "name": "Peter",
            "favouriteRoles": {
              "edges": [
                {
                  "node": {
                    "name": "manager"
                  }
                }
              ]
            }
          }
        },
        {
          "node": {
            "name": "Roy",
            "favouriteRoles": {
              "edges": [
                {
                  "node": {
                    "name": "manager"
                  }
                }
              ]
            }
          }
        },
        {
          "node": {
            "name": "Tracy",
            "favouriteRoles": {
              "edges": [
                {
                  "node": {
                    "name": "manager"
                  }
                }
              ]
            }
          }
        }
      ]
    }
  }
}

However when I have this code:

def resolve_favourite_roles(root, args, context, info):
    # everyone wants to be a manager
    return Role.get_query(Role).filter_by(name='manager')

class Employee(SQLAlchemyObjectType):

    class Meta:
        model = EmployeeModel
        interfaces = (relay.Node, )

    favourite_roles = SQLAlchemyConnectionField(Role, resolver=resolve_favourite_roles)

This is the result.

{
  "data": {
    "allEmployees": {
      "edges": [
        {
          "node": {
            "name": "Peter",
            "favouriteRoles": {
              "edges": [
                {
                  "node": {
                    "name": "manager"
                  }
                },
                {
                  "node": {
                    "name": "engineer"
                  }
                }
              ]
            }
          }
        },
        {
          "node": {
            "name": "Roy",
            "favouriteRoles": {
              "edges": [
                {
                  "node": {
                    "name": "manager"
                  }
                },
                {
                  "node": {
                    "name": "engineer"
                  }
                }
              ]
            }
          }
        },
        {
          "node": {
            "name": "Tracy",
            "favouriteRoles": {
              "edges": [
                {
                  "node": {
                    "name": "manager"
                  }
                },
                {
                  "node": {
                    "name": "engineer"
                  }
                }
              ]
            }
          }
        }
      ]
    }
  }
}

The resolver function is not being called when it is outside of the class.

Any help is appreciated.

License

Hi. Can you specify the license for this project?
Thanks.

Non-ORM Usage Examples

It would be useful to see a usage example that did not use the SQLAlchemy ORM layer.

Does `graphene-sqlalchemy` generate connections based on relations?

I have these two models:

class Organization(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    created = db.Column(db.TIMESTAMP, default=db.func.now())
    modified = db.Column(db.TIMESTAMP, default=db.func.now(), onupdate=db.func.now())
    
    # Attributes
    name = db.Column(db.String(200), nullable=False)
    logo = db.Column(db.TEXT, default='')
    video = db.Column(db.TEXT, default='')
    about = db.Column(db.TEXT, default='')

class Project(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    created = db.Column(db.TIMESTAMP, default=db.func.now())
    modified = db.Column(db.TIMESTAMP, default=db.func.now(), onupdate=db.func.now())

    # Attributes
    name = db.Column(db.String(200), nullable=False)
    start_date = db.Column(db.TIMESTAMP, nullable=True)
    end_date = db.Column(db.TIMESTAMP, nullable=True)

    # Relations
    organization_id = db.Column(db.Integer, db.ForeignKey('organization.id'), nullable=False)
    organization = db.relationship('Organization', foreign_keys=[organization_id],
                                   cascade="all, delete-orphan", single_parent=True,)

And this schema:

import Organization as OrganizationModel
import Project as ProjectModel

class Project(SQLAlchemyObjectType):
    class Meta:
        model = ProjectModel
        interfaces = (relay. Node, )

class Organization(SQLAlchemyObjectType):
    class Meta:
        model = OrganizationModel
        interfaces = (relay.Node, )


class Query(graphene.ObjectType):
    node = relay.Node.Field()

    all_organizations = SQLAlchemyConnectionField(Organization)
    all_projects = SQLAlchemyConnectionField(Project)


    @staticmethod
    def resolve_organization(self, args, context, info):
        query = Organization.get_query(context)
        return query.filter_by(**args)

    @staticmethod
    def resolve_project(self, args, context, info):
        query = Project.get_query(context)
        return query.filter_by(**args)


schema = graphene.Schema(query=Query, types=[Organization, Project])

I would then expect to be able to write a query to access projects from allOrganizations, but I seem unable to do get it to work. If I do the connection manually like this (with the same model):

import Organization as OrganizationModel
import Project as ProjectModel
class Project(SQLAlchemyObjectType):
    class Meta:
        model = ProjectModel
        interfaces = (relay. Node, )


class OrganizationProjectConnection(graphene.Connection):
    class Meta:
        node = Project


class Organization(SQLAlchemyObjectType):
    class Meta:
        model = OrganizationModel
        interfaces = (relay.Node, )

    projects = SQLAlchemyConnectionField(OrganizationProjectConnection)


class Query(graphene.ObjectType):
    node = relay.Node.Field()

    all_organizations = SQLAlchemyConnectionField(Organization)
    all_projects = SQLAlchemyConnectionField(Project)


    @staticmethod
    def resolve_organization(self, args, context, info):
        query = Organization.get_query(context)
        return query.filter_by(**args)

    @staticmethod
    def resolve_project(self, args, context, info):
        query = Project.get_query(context)
        return query.filter_by(**args)


schema = graphene.Schema(query=Query, types=[Organization, Project])

it works as I expect. Is this a feature not yet implemented, or do I have to encourage the framework to make the connection somehow?

One method for creating a Union type. Is there a better way?

So after some extreme hacking, I figured out how to create a Union type that I'll share below. I'd like to get some input from the authors if this is a fragile solution, and whether there is a better approach.

Here's what I've done (take special note of this: StreamEventOT._meta.model = StreamEvent):

import graphene
from graphene import relay
from graphene_sqlalchemy import (
    SQLAlchemyConnectionField,
    SQLAlchemyObjectType,
)
import spark.graphql._prepare

def connection_for_type(_type):
    class Connection(graphene.Connection):
        total_count = graphene.Int()

        class Meta:
            name = '{}Connection'.format(_type._meta.name)
            node = _type

        def resolve_total_count(self, args, context, info):
            return context['session'].query(_type._meta.model).count()

    return Connection

class UserOT(SQLAlchemyObjectType):
    class Meta:
        model = User
        interfaces = (relay.Node,)
UserOT.Connection = connection_for_type(UserOT)


class AlertOT(SQLAlchemyObjectType):
    class Meta:
        model = Alert
        interfaces = (relay.Node,)
AlertOT.Connection = connection_for_type(AlertOT)


class MessageOT(SQLAlchemyObjectType):
    class Meta:
        model = Message
        interfaces = (relay.Node,)
MessageOT.Connection = connection_for_type(MessageOT)


class TaskOT(SQLAlchemyObjectType):
    class Meta:
        model = Task
        interfaces = (relay.Node,)
TaskOT.Connection = connection_for_type(TaskOT)


class StreamEventOT(graphene.Union):
    class Meta:
        types = (AlertOT, MessageOT, TaskOT)

StreamEventOT.Connection = connection_for_type(StreamEventOT)
StreamEventOT._meta.model = StreamEvent

class Query(graphene.ObjectType):
    all_users = SQLAlchemyConnectionField(UserOT)
    all_stream_events = SQLAlchemyConnectionField(StreamEventOT.Connection)
    node = relay.Node.Field()


schema = graphene.Schema(
    query=Query,
    types=[
        UserOT,
        StreamEventOT,
        AlertOT,
        MessageOT,
        TaskOT,
    ]
)

[Keywords for anyone searching for polymorphism, or joined table inheritance]

passing Arguments to the SQLAlchemyConnectionField in 2.0 doesn't work

I'm trying to upgrade my Application to 2.0 and i have a connection field i'm passing arguments to
but that doesn't seem to work with the new version to anymore. I followed the upgrade instructions
but the argument doesn't get passed to my resolve method.

Here is the code that worked in the old version:

user_content = SQLAlchemyConnectionField(Content, tagURLName=graphene.String())

def resolve_user_content(self, args, context, info):
    sess = context['session']
    tagURLName = args.get('tagURLName')

Following the upgrade instructions i changed the code to:

user_content = SQLAlchemyConnectionField(Content, tagURLName=graphene.String())

def resolve_user_content(self, info, tagURLName):
    context = info.context
    sess = context['session']

But the argument doesn't get passed through...

An error occurred while resolving field Root.userContent
Traceback (most recent call last):
File "/var/local/lr-venv/lib/python3.5/site-packages/graphql/execution/executor.py", line 196, in resolve_or_error
return executor.execute(resolve_fn, source, info, **args)
File "/var/local/lr-venv/lib/python3.5/site-packages/graphql/execution/executors/sync.py", line 7, in execute
return fn(*args, **kwargs)
File "/var/local/lr-venv/lib/python3.5/site-packages/graphene_sqlalchemy/fields.py", line 34, in connection_resolver
iterable = resolver(root, info, **args)
TypeError: resolve_user_content() missing 1 required positional argument: 'tagURLName'
Traceback (most recent call last):
File "/var/local/lr-venv/lib/python3.5/site-packages/graphql/execution/executor.py", line 215, in complete_value_catching_error
exe_context, return_type, field_asts, info, result)
File "/var/local/lr-venv/lib/python3.5/site-packages/graphql/execution/executor.py", line 268, in complete_value
raise GraphQLLocatedError(field_asts, original_error=result)
graphql.error.located_error.GraphQLLocatedError: resolve_user_content() missing 1 required positional argument: 'tagURLName'

It works fine for all the other Fields. Is there something special about the SQLAlchemyConnectionField i need to take into account?

Detecting mutations

I want to ensure that queries submitted via GET never attempt to mutate. When a query comes in, how can I detect whether it includes a mutation? Is there a clean, unambiguous way?

I apologize if this is an inappropriate place for such questions. I don't see links to a forum or anything. I appreciate your time.

Auto generated mutations given model

Is there anything on the roadmap to create a class similar to SQLAlchemyObjectType but for mutations that would take a model in its Input and automatically create a graphene.Mutation subclass that has all the fields in it? The reason I ask is that I am thinking about brewing my own and if its not on the roadmap I'd be happy to contribute unless there is a good reason why this is bad. I think I can create a SQLAlchemyMutationMeta and SQLAlchemyMutation class to take the model from the Input class, fetch the fields in it and pass appropriate attrs to MutationMeta.

How to have edges/totalCount on a relationship field?

Hey,

I'd like to have edges/node/totalCount support on a relationship field.

E.g. if looking at the flask_alchemy example app, I'd like to have query such as this (changes are in bold):

{
  allEmployees {
    edges {
      node {
        id,
        name,
        department {
          totalCount
          edges {
              id,
              name
           }
        },
        role {
          totalCount
          edges {
            id,
            name
           }
        }
      }
    }
  }
}

Can this be done?

Thank you

Support custom TypeDecorator types

Not sure exactly why this doesn't work as is.
Looking into it.
Otherwise accessing impl somehow might resolve it.

Handle connections in 2.0

I upgrading to 2.0 of graphene and related packages.

Without any changes I get
Exception: ConnectionField's now need a explicit ConnectionType for Nodes.

So I added the connection explicitly like the upgrade guide say DepartureConnection

class DepartureSchema(SQLAlchemyObjectType):
    """
    A departure
    """
    class Meta:
        model = Departure
        interfaces = (graphene.Node, relay.Node)


class DepartureConnection(graphene.Connection):
    class Meta:
        node = DepartureSchema


class Query(graphene.ObjectType):
    departures = relay.ConnectionField(DepartureConnection)

But now I get

graphql.error.located_error.GraphQLLocatedError: Resolved value from the connection field have to be iterable or instance of DepartureConnection. Received "None"

So I'm probably missing something. I think the documentation/example with relay needs to be adjusted for 2.0. If I just knew what is needed in 2.0 I could do a PR

Thanks for these great libraries!

Anything on the roadmap for a relay SQLAlchemyClientIDMutation class?

I have been using this library for a project and its great so far, however it seems there should also be a class for relay ClientIDMutations. Is this on the roadmap?

Hide certain fields in a model

Is there a simple way to flag certain fields in a model as "private" or otherwise hidden? There are some fields that, authenticated or not, I never want passed via GraphQL query.

How to run raw SELECT for a Base field?

Hello,

I have a class extending Base that has one field that's quite complex to retrieve - I want to run a complex SELECT (one that has a JOIN on another table where ID is a string that has to be converted to int and is back JOIN-ed to the original table using that ID). It seems like its simply impossible to retrieve the value for this field using only relationship and keep it only one field (and not a structure with multiple { }).

Is it possible to run a raw SQL to retrieve a field value?

Thanks

Multliple Database Recursive Lookup

I have 2 databases that cannot be combined.

There's a field that needs to be resolved using the other database. The other database is also an SQLAlchemyObjectType.

Here's an example that's a bit contrived that i'd like to accomplish but not sure how to automatically provide a definition for the db1 resolver that would use the automatic schema from DB1Table.

What i'd like to see is the schema for graphql that looks like what is below the code.

code

db1_engine = create_engine('sqlite:///database1.sqlite3')
db2_engine = create_engine('sqlite:///database2.sqlite3')

db1_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=db1_engine))
db2_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=db2_engine))

class Base(Object):
    pass

Base = declarative_base(cls=Base)

class DB1Base(Base):
    __abstract__ = True
    metadata = MetaData(bind=db1_engine)

DB1Base.query = db1_session.query_property()

class DB2Base(Base):
    __abstract__ = True
    metadata = MetaData(bind=db2_engine)

DB2Base.query = db2_session.query_property()

class ModelDB1Table(DB1Base):
    __tablename__ = 'db1table'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)

class ModelDB2Table(DB2Base):
    __tablename__ = 'db2table'
    id = Column(Integer, primary_key=True)
    data = Column(Text(2000), nullable=False)
    created_by_id = Column(Integer, nullable=False)

class SchemaDB1Table(SQLAlchemyObjectType):
    class Meta:
        model = ModelDB1Table
        interfaces = (relay.Node, )

class SchemaDB2Table(SQLAlchemyObjectType):
    class Meta:
        model = ModelDB2Table
        interfaces = (relay.Node, )
    
    def resolve_db1(self, info):
        return db1_session.query(ModelDB1Table).get(self.created_by_id)


class Query(graphene.ObjectType):
    node = relay.Node.Field()
    all_db1_table = SQLAlchemyConnectionField(SchemaDB1Table)
    all_db2_table = SQLAlchemyConnectionField(SchemaDB2Table)

schema = graphene.Schema(query=Query)

desired schema output

{
  SchemaDB1Table {
    id: ID!
    name: String!
  }
  SchemaDB2Table {
    id: ID!
    data: String!    
    created_by_id: Int!
    db1 {
      id: ID!
      name: String!      
    }
  }
}

Problem with simple create mutation.

I'm trying to add a simple create mutation for a SQLAlchemy model and am not having much success. My code looks like:

import graphene
from graphene.relay import Node
from graphene_sqlalchemy import SQLAlchemyConnectionField, SQLAlchemyObjectType

from sm import models  # module containing my SQLAlchemy model classes

def _make_object_type(class_name, model):
    """Factory function dynamically creating SQLAlchemyObjectType subclasses."""
    def _make_inner_meta_type():
        return type('Meta', (), {'model': model, 'interfaces': (Node, )})
    return type(class_name, (SQLAlchemyObjectType, ), {'Meta': _make_inner_meta_type()})

Company = _make_object_type('Company', models.Company)
# ... lots of other SQLAlchemyObjectType types elided

class Query(graphene.ObjectType):
    node = Node.Field()
    company = Node.Field(Company)
    all_companies = SQLAlchemyConnectionField(Company)
# ... lots of other fields elided

class CompanyInput(graphene.InputObjectType):
    name = graphene.String(required=True)
    description = graphene.Int(required=False)

class CreateCompany(graphene.Mutation):
    class Arguments:
        company_data = CompanyInput(required=True)

    # output fields of mutation
    # ok = graphene.Boolean()
    company = graphene.Field(lambda: Company)

    @staticmethod
    def mutate(root, company_data=None):
        company = Company(
            name=company_data.name,
            description=company_data.description
        )
        return CreateCompany(company=company)

class Mutation(graphene.ObjectType):
    create_company = CreateCompany.Field()

schema = graphene.Schema(query=Query, mutation=Mutation)

When I issue the following mutation:

mutation myFirstMutation {
    createCompany(companyData: {name:"Test", description: "A test company"}) {
        company {
            name,
            description
        }
    }
}

In graphiql I don't see any input type in the docs, and a red squiggle under the companyData argument to createCompany. When I execute the query I get an error message of "Unknown argument 'companyData' on field 'createCompany' of type 'Mutation'."

I based my attempt on the docs at http://docs.graphene-python.org/en/latest/types/mutations/, and everything seems correct. Does any one have any idea what I'm doing wrong?

Many thanks in advance!

Examples don't work

Hi,

I followed the flask-sqlalchemy examples here (and the snippets in the project's Readme as well) but it didn't work out of the box. I had the following exception: AttributeError: 'Request' object has no attribute 'get' when trying to request from Graphiql.

After some fiddling with flask-sqlalchemy i solved the bug by passing a context argument to GraphQLView.as_view.

Here is my full url declaration:

app.add_url_rule(                                                                   
    '/graphql',                                                                     
    view_func=GraphQLView.as_view(                                                  
        'graphql',                                                                  
        schema=schema,                                                              
        graphiql=True,                                                         
        context={'session': db_session}                                             
    ),                                                                              
)

I don't know if this is the proper way to fix the problem, but thought it'll be useful to tell you about it.

Support for hybrid_property?

Hello,

I am trying to use hybrid_property as a property on a GraphQL response. However, when I execute the GraphQL request, I am receiving an error stating the property cannot be found.

Are SQlAlchemy hybrid_property attributes currently supported?

Thanks,
G

@hybrid_property
    def document_count(self):
        return len(self.linked_documents)

Can I perform recursive queries?

I have a database with a hierarchical recursive structure. This is what my schema looks like:

@schema.register
class HierarchyChild(SQLAlchemyNode):
    class Meta:
        model = HierarchyModel

@schema.register
class Hierarchy(SQLAlchemyNode):
    class Meta:
        model = HierarchyModel

    children = SQLAlchemyConnectionField(HierarchyChild)

Currently this returns nested results, however, it doesn't pass the parent context to the child node. Thanks in advanced!

Having issues with sqlalchemy_utils JSONType while reading

I am using : https://github.com/kvesteri/sqlalchemy-utils

just so that I dont need to worry if I using Postgres in production or sqlite in dev environment .But from looks like it, I have no issues inserting data in json column, But when I read , seems like I get back a string, Wondering how to I get a python dict or json back when I read the column ?

from sqlalchemy_utils import JSONType
class Test(db.Model):
    __tablename__ = 'test'
    id = Column(Integer, primary_key=True)
    json = Column(JSONType)

What I see

"json": "{\"data\": .....

N + 1 round trip problem

Does this library handle nested models (joins) in a single query from the server to the DB?
For example

user {
  id
  posts {
    id
  }
}

Invalid id format when retrieving node

Im having issues retrieving nodes by id that have composed primary keys.

I was getting an error:

File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 846, in _get_impl
','.join("'%s'" % c for c in mapper.primary_key))
InvalidRequestError: Incorrect number of values in identifier to formulate primary key for query.get(); primary key columns are xxxxxxxx

So I override get_node method to see what I was receiving as id and this it:

ID TYPE: <type 'unicode'>
ID: (1L, '1')

So I had to fix it by turning that unicode id into a list, trying to re-build the primary key of the schema's model, using sqlalchemy mappers:

    @classmethod
    def get_node(cls, info, id):
        id_parts = id.strip('()').split(',')
        parts = []
        pk = cls._meta.model.__mapper__.primary_key
        for n, k in enumerate(pk):
            part = id_parts[n].strip()
            p_type = k.type.python_type
            if p_type in (int, long, float):
                if unicode.isalpha(part[-1]):
                    part = part[:-1]
                parts.append(p_type(part))

            else:
                # remove inner string/unicode repr
                stripped_part = re.sub(r"[u]?'(.*?)'", "\\1", part)
                parts.append(stripped_part)

        return cls.get_query(info).get(parts)

@syrusakbary I'm wondering why I'm getting a unicode when the SQLAlchemyObjectType.resolve_id returns a tuple

`convert_column_to_enum` doesn't work at all

This issue is related with three packages: graphql-core, graphene and graphene-sqlalchemy. Actually there are 2 separated problems related with the Enum type:

1. In convert_column_to_enum, the returned value should be an instance of UnmountedType but not a subclass of UnmountedType.
2. ChoiceType of sqlalchemy_utils coerces the result to Choice type or Enum subclass type (depend on which XxxxTypeImpl it used). But it's more pain in ass since there's no way to override GraphQLEnumType's serialize method like GraphQLScalarType did.

How to filter and limit?

Hey really nice library. Does it support filtering and limit/offset?

Question about querying a single resource with flask sqlalchemy example.

I have followed the example at http://docs.graphene-python.org/projects/sqlalchemy/en/latest/tutorial/#testing-our-graphql-schema and have a question.

I can issue the following query to list all employees and their departments.

{
  allEmployees {
    edges {
      node {
        name
        department {
          name
        }
      }
    }
  }
}

I would like to be able to query a single employee name and their department name by employee id. I can query using the Node interface, but that doesn't allow me to access Employee name field. Am I supposed to "cast" this to the specific Employee type to do that? What I would like is something like:

{
  employee(id: "someid") {
    name
    department {
      name
    }
}

Is this reasonable or am I "doing it wrong"? What is best practice for accessing a single employee using the Relay connections/nodes/edges paradigm?

Many thanks in advance!

filter how to use？

My ui have a search , how can i use relay?
how do i realize this example by connectfiled or relay?

query combineMovies {
  allMovies(filter: {
    OR: [{
      AND: [{
        releaseDate_gte: "2009"
      }, {
        title_starts_with: "The Dark Knight"
      }]
    }, {
      title: "Inception"
    }]
  }) {
    title
    releaseDate
  }
}

result:
{
  "data": {
    "allMovies": [
      {
        "title": "Inception",
        "releaseDate": "2010-08-28T20:00:00.000Z"
      },
      {
        "title": "The Dark Knight Rises",
        "releaseDate": "2012-07-20T00:00:00.000Z"
      }
    ]
  }
}

Upgrading to graphene 2.0

Based on example from @aminghadersohi on another issue I adapted that solution for my use case. I am using something like this and its working great for now, But am unable to migrate this to Graphene 2.0. After migrating to 2.0 based on upgrade guide, either the relay args and extra args both dont work.

I liked this approach because I can query on any configured field from model, based on how I configure it.

If there is a better approach on how I can query any fields please let me know, I can just migrate to that .

Would it be possible to point me how do I migrate this?

Base = sqlalchemy.ext.declarative.declarative_base()

class ClientRoleStore(Base):
    __tablename__ = "clientrole"
    uuid = Column(CHAR(36), primary_key=True, nullable=False, default=lambda: str(uuid1()))

class ClientRole(SQLAlchemyObjectType):
    class Meta:
        model = ClientRoleStore
        interfaces = (relay.Node,)

all_types = [
    ClientRole,
    # there are other fields but I have kept it simple here
]


query_args = {
    "clientrole": [("name", graphene.String()), ("add", graphene.String())],

}

query_attrs = {'node': relay.Node.Field()}


def resolve(cls, self, args, context, info):
    RELAY_ARGS = ['first', 'last', 'before', 'after']

    query = cls.get_query(context)
    if args:
        for key, value in args.items():
            if key not in RELAY_ARGS:
                query = query.filter(getattr(model, key) == args[key])
    return query.all()


for type_cls in all_types:
    model = type_cls.__dict__['_meta'].model
    name = model.__tablename__

    extra_args = {key[0]: key[1] for key in query_args[name]}

    query_attrs[name + 's'] = SQLAlchemyConnectionField(type_cls, **extra_args)
    query_attrs[name] = relay.Node.Field(type_cls)
    query_attrs['resolve_' + name + 's'] = partial(resolve, type_cls)

Query = type("Query", (graphene.ObjectType,), query_attrs)

Incorrect repo in readme

The existing clone and cd are invalid for the examples/flask_sqlalchemy/README.md file. The README should read:

# Get the example project code
git clone https://github.com/graphql-python/graphene-sqlalchemy.git
cd graphene-sqlalchemy/examples/flask_sqlalchemy

Fragments doesn't seem to work

Graphene does not seem to resolve the other object Type using fragments in the query.

Here is my schema:

class User(db.Model):
    __tablename__ = 'tbl_users'

    id = Column(String(40), primary_key=True)
    username = Column(String(64), index=True, unique=True)
    email = Column(String(64), index=True, unique=True)

    claims = None


class User(SQLAlchemyObjectType):

    class Meta:
        interfaces = (relay.Node,)
        model = UserModel


class UserMeta(SQLAlchemyObjectType):

    class Meta:
        interfaces = (relay.Node,)
        model = UserModel

    claims = json.JSONString()


class Queries(graphene.ObjectType):
    me = graphene.Field(UserInterface)

    def resolve_me(_, args, context, info):
        query = User.get_query(context)
        user = query.get(1)
        user.claims = {'hello': 'world'}
        return user


schema = graphene.Schema(
    query=Queries,
    types=[UserMeta, User]
)

And here is my query:

{
  me {
    ...userData
    ...userMeta
  }
}

fragment userMeta on UserMeta {
  claims
}

fragment userData on User {
  username
  email
}

It will return:

{
  "data": {
    "me": {
      "createdAt": "2017-03-31T05:29:32+00:00",
      "email": "[email protected]"
    }
  }
}

However, if I move claims to the User object type, it works as expected.
Also, even if I only use the UserMeta fragment in the query, it still doesn't resolve it.

Update:
Im sorry, my way was wrong.

Global ids from objects with composite primary keys are not correctly extracted

Due to the way graphene-sqlalchemy constructs global ids, it is not possible to get unique global ids from objects with composite primary keys, typically association objects.

Batching selects from same model

Disregard the issue, discovered DataLoader.

Filtering by JSONString

Hacking around with the example, I have been able to add a JSONB field to the employee class (Replacing SQLite with Postgres 9.6).

Is it possible (or on the road map) to specify a json filter, eg the equivalant in postgres of 'SELECT * from Employee where employee.data ->>'thing' = 'blah', so maybe a schema resembling

  allEmployees {
    edges {
      node {
        name
        data(thing: blah)
    }
  }
}
}

Filter on the backend

Given I have: an authenticated user, a model type that extends SQLAlchemyObjectType, and a SQLAlchemyConnectionField defined in my Query, how can I restrict the returned objects to only rows that have a relationship with this user?

Adding support for Postgres TSTZRANGE

Hello,

I need to be able to convert a postgres TSTZRANGE into a GraphQL type. I noticed that the graphene-django project does support the postgres range type.s

Just curious if it would be a good idea to add the same support to this codebase.

https://github.com/graphql-python/graphene-django/blob/master/graphene_django/converter.py#L193

Thanks
G

Database ID

1. Is there a way to get/map database id so that you can get this value in query?

2. Can we map only selected fields from the database table instead of all by default?

Refactor SQLAlchemyConnectionField to use SQLAlchemyObjectType.get_query()

Right now, SQLAlchemyConnectionField uses the get_query() implementation in graphene_sqlalchemy.utils. The same code is in SQLAlchemyObjectType.

graphene-sqlalchemy/graphene_sqlalchemy/fields.py

Lines 18 to 20 in 1d353f7

	@classmethod
	def get_query(cls, model, info, **args):
	return get_query(model, info.context)

graphene-sqlalchemy/graphene_sqlalchemy/types.py

Lines 146 to 149 in 1d353f7

	@classmethod
	def get_query(cls, info):
	model = cls._meta.model
	return get_query(model, info.context)

This means that if someone wants to update the query for an SQLAlchemyObjectType, e.g. to implement permissions restrictions, they have to subclass not only SQLAlchemyObjectType but also SQLAlchemyConnectionField.

I suggest refactoring SQLAlchemyConnectionField to re-use the get_query() implementation of the SQLAlchemyObjectType it wraps. I'm willing to look into creating a PR if there is interest.

cannot handle PickleType

Hi sweet lib,
Want to commit and dict to sql alchemy, but computer says no...

Order by option is missing

Great job,
Order by option is missing.