In this post we’ll explore what is GraphQL and when it makes sense to use it. We’ll map an existing SQL database (SQLite) with an Object Relational Mapping (ORM) system – SQLAlchemy in Python, and finally unify both concepts by using the ORM bindings inside GraphQL queries and start up the web server with Flask. If you want to get the final code, it’s hosted on GitHub. Give it a star, if you found it useful.
GraphQL is not a replacement for REST, it’s more of an extension, both use the HTTP protocol. It has quite a few benefits:
It’s not all perfect, thus some considerations:
Since we are going to use SQLite, for the SQL database and Python 3 with PIP. We can install them via sudo apt-get install sqlite python3 python3-pip
for debian-based distributions or sudo yum install sqlite python3 python3-pip
for RHEL ones. I’ve done all the setup on a Ubuntu WSL2 distribution, so I can confirm that it works fine on WSL in Windows. Once we have those up and running, we’ll need to install a few python packages that we’ll use to setup a web server (Flask) and have cleaner bindings between GraphQL and SQLAlchemy (Graphene, Graphene_SQLAlchemy), so run python3 -m pip install flask flask_graphql sqlalchemy graphene graphene_sqlalchemy
, if you don’t have admin privileges for the machine, append --user
to the command.
A small note on folder layout, as per the GitHub repo, in order to not mix SQLAlchemy bits with GraphQL one and confuse you what belongs where, I’ve put all GraphQL-related files in a single folder.
I’ve created a small sample school database with a few records, with the below relationships:
The primary design choice was to have a few tables with primary keys and some foreign key relationships, to make the example closer to reality. You can either directly download the database from here, or recreate the SQLite database by running:
sqlite3 schooldb.sqlite3
and when you are presented with the SQLite prompt paste the contents from this file, press Enter and then press Ctrl + D to exit.
We’ll start with the table definitions. We import the data types from the SQLAlchemy module. On their website the list of basic types is quite extensive. Since you now know the table columns from above, we are replicating them manually below. One interesting piece is defining a relationship, which I’ve done here for Teachers and Classes. This is used for defining logical relationship, so when I fetch a teacher object, they will have an list called classes list, to address the case where one teacher can teach multiple classes. You can define cascade relationship, for example when deleting a teacher record to delete all class records respectively and vice-versa. This is where the power of object relational mapping comes into play. Chances are your SQL database already supports these features, but this adds an extra layer of safety when it comes to deletion queries. In the below screenshot, you can see how I load the earlier created school database, then initialize a declarative_base(), meaning I will define the tables manually. SQLAlchemy also offers automapper, which should be good enough for basic tables, but can’t confirm on how well it works on complicated databases.
The SQLAlchemy no primary key error is quite popular. SQLAlchemy always needs a primary key in a table, even if it doesn’t have such. The reason is, you always need some kind of unique row identifier for deletion purposes. In our example, I’ve deliberately created the grades table, which has this problem, so we can work around it, following the suggestions from here and here.
Define the types where inside the class Meta, you pass the earlier defined table definitions. The magic here is actually done by SQLAlchemyObjectType. The fields classes below are needed only for the editing method called mutation. If you are only going to allow read-only, you can skip the AddStudentFields and StudentFields classes.
So far we have the type definitions on database side and on query side, so the next step is to query the database and return query types.
the most essential part is when adding your request types at the top, the actual resolve functions to be of the style resolve_<request_name> so the data is picked by the Default Resolver, more details on this you can read here. The argument types are passed as is and you can directly put them in the filter method for the SQLAlchemy object. It’s in the above screenshot where the magic happens. You query in JSON format SQL database. You still need to define how the database search is actually done.
The UPDATE/INSERT queries are represented with the mutation keyword before the curly brace that starts the query.
The definition is similar to the read queries, since we defined the StudentFields we directly pass them as to what to parse and how to store in the database respectively. No magic here, just more mappings. Once done, you can return an object of what you’ve added, I like to add a status, in case it fails, similar to an HTTP 200 response, above, I return just True, but you can do .rollback() for example if your SQL transaction failed and so on.
If you’ve worked with Flask before, there isn’t anything particularly different setup-wise about it. Since there’s a module that makes hosting the interactive query console a one-liner. Import the GraphQLView and add an endpoint to the Flask application. This will host the GraphQL endpoint, but until you set graphiql to True, you won’t get a UI.
If this worked correctly, you should be able to reach http://localhost:5000 and get the below view:
In the repo with all files, I’ve added a text file with sample queries you can try out as soon as you spawn the server locally.
I hope you’ve found this tutorial useful. Share with others, who you’ve always wanted to do the jump from SQL to GraphQL. Until next time!
We live in an unprecedented era of customizability of physical goods and services. One problem…
My next instalment on distributed systems after Elasticsearch and Redis is Apache Kafka. The intended audience,…
On my quest to make shorter videos on programming fundamentals, I'm kicking this initiative with…
One of the most interesting datasets I have is my own Apple Health data, which I've…
After a month and half of making, in this 3-part series I go over the…
People often ask me why do I blog and how much time do I spend…