This post shows how to auto-generate a REST API from a handful of SQLAlchemy models. This means full CRUD (POST, GET, PUT, DELETE) endpoint generation for each SQLAlchemy model defined.
Let’s say our company is building a Single-Page Application for librarians & readers that accomplishes the following 2 goals:
- Show the availability and information of a given book to readers.
- Allow a librarian to perform CRUD operations to update their libary’s inventory.
We’ve been tasked with building a REST API to allow a Single-Page Application to interface with our database.
Environment Setup
- Create project root directory.
mkdir automagic_api - Create and activate virtualenv.
cd automagic_api; # virtualenv env; Python 2.7 python -m venv env source env/bin/activate - Install flask-restless and sqlalchemy
pip install flask-restless sqlalchemy
Your project directory tree should look as follows:
automagic_api/
env/
Further compartmentalize the project and define our models
- Create nested project directory ‘/automagic_api’ to hold Flask-specific files.
mkdir automagic_api cd automagic_api - Create ‘models.py’ within the flask-project directory and define Book and Author..
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy import ForeignKey, Column, Integer, String, Boolean
from sqlalchemy.orm import backref, relationship
from automagic_api import Base
class Author(Base):
@declared_attr
def __tablename__(cls):
# API endpoint will take the form '/api/__tablename__'
return cls.__name__.lower()
id = Column(Integer, primary_key=True)
first_name = Column(String(64))
last_name = Column(String(64))
class Book(Base):
@declared_attr
def __tablename__(cls):
return cls.__name__.lower()
id = Column(Integer, primary_key=True)
title = Column(String(64))
author_id = Column(Integer,
ForeignKey("author.id"), nullable=True)
author = relationship(Author,
backref=backref('books'))
is_available = Column(Boolean)
Your directory structure should now look like this:
automagic_api/
automagic_api/
models.py
env/
Create controllers.py to define endpoints
Below we define our 2 Flask-restless API blueprints: one for Book and one for Author, which when instantiated, construct the CRUD endpoints for each respective model.
from automagic_api import app, s, manager
from automagic_api.models\
import Book, Author
author_api_blueprint = manager.create_api_blueprint(Author,
methods=['GET', 'PATCH', 'POST', 'DELETE'])
book_api_blueprint = manager.create_api_blueprint(Book,
methods=['GET', 'PATCH', 'POST', 'DELETE'])
Your directory should now look like:
automagic_api/
automagic_api/
models.py
controllers.py
env/
Create __init__.py in our Flask-project
Create the file in ‘automagic_api/automagic_api/’. This file will import our models.py, our controllers.py and instantiate our API.
import flask
import flask_restless
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy import *
app = flask.Flask(__name__)
# Create our SQLAlchemy DB engine
engine = create_engine('sqlite:///foobar.db')
Session = sessionmaker(bind=engine, autocommit=False, autoflush=False)
s = scoped_session(Session)
Base = declarative_base()
Base.metadata.bind = engine
# Import all models to add them to Base.metadata
from models import Book, Author
Base.metadata.create_all()
manager = flask_restless.APIManager(app, session=s)
# Register flask-restless blueprints to instantiate CRUD endpoints
from controllers import book_api_blueprint, author_api_blueprint
app.register_blueprint(author_api_blueprint)
app.register_blueprint(book_api_blueprint)
Create run.py
Create run.py in our root directory
from automagic_api import app
app.run(debug=True, host='0.0.0.0', port=5000)
Your final directory structure should look as follows:
automagic_api/
run.py
automagic_api/
__init__.py
models.py
controllers.py
env/
Start and test your API!
In the project root, start the server
env/bin/python run.py
In a python CLI, create a POST request to create a new Book:
>>> import requests
>>> import json
>>> payload = {
'title': "The Eye of the World",
'author': {
'first_name': "Robert",
'last_name': "Jordan"
},
'is_available': True
}
>>> headers = {'content-type': 'application/json'}
>>> r = requests.post("http://localhost:5000/api/book", data=json.dumps(payload),headers=headers)
>>> print r.status_code
201
Go ahead and open up a web browser, and navigate to http://localhost:5000/api/book to see the Book object that you just POSTed to your sqlite database.
Likewise, you can invert this request and check out http://localhost:5000/api/author to see the Author object that was created, and the nested “books” attribute that the author has written.
- Note: URLs for the API are constructed based off of the __tablename__ of the sqlalchemy model, and are prefixed with “/api” (i.e Book -> “/api/book”)
Congratulations, you now have a working boilerplate for a SQLAlchemy-model defined REST API. Now go ahead and add more models and endpoints!
Final Remarks
The reason that I use Flask-restless for day-to-day APIs is it’s abstraction.
- It preserves abstraction between the database’s relational model, and the JSON schema expected as input and output to the API
- It preserves abstraction between SQLAlchemy, and the SQL dialect under-the-hood.
For instance, when I POST the following payload from a client application, I don’t care about the relational model between an Author and a Book, I just care that the JSON Book object contains a nested Author in its schema. By providing the nested Author object WITHOUT an id field, I implicitly CREATE this Author in the database.
{
'title': "The Eye of the World",
'author': {
'first_name': "Robert",
'last_name': "Jordan"
},
'is_available': True
}
Conversely, if I would like to create a book that references a pre-existing author, I would changed my nested Author object to contain the id of the Author who wrote it.
{
'title': "The Eye of the World",
'author': {
'id': 1
},
// or, we could break abstraction and assign an 'author_id':
// 'author_id': 1,
'is_available': True
}
Regarding preservation of SQLAlchemy’s level of abstraction, Flask-restless goes as far as to implement SQLAlchemy’s Query API at the REST API level. Take the following example:
If Robert Jordan wrote 14 books (which he did in a painstakingly long series), and I only wanted to grab all books that Robert Jordan penned which are available in the library I could run the following query:
http://localhost:5000/api/book?q={"filters":[{"name":"is_available","op":"==","val":true},{"name":"author_id","op":"==","val":1}]}
This would return our targeted books, and translates to running the following in SQLAlchemy:
session.query(Book).filter(Book.author_id==1,
Book.is_available == True).all()
- The searchformat API also implements order_by, limit, offset and group_by.
See Flask-restless searchformat
These 2 key features make this tool perfect for Single-Page Applications that need to communicate with an API server.
A huge thanks to the brilliant developers of Flask, Flask-restless and SQLAlchemy. I am but a humble messenger spreading the work of others…