Flask Recipe – RESTful CRUD using sqlalchemy
RESTful apps are a thing these days. When your application’s userbase gets quite large and the client could vary from a laptop to an android device to an iOS device, it pays to keep the backend code separate and use the server only for making RESTful calls using HTTP methods that pertain to basic OLTP transactions: SELECT, INSERT, UPDATE and DELETE.
Popular third-party apps like Firebase essentially provide you this same thing – A REST based front to a database that could be accessed online using simple HTTP methods. But in this tutorial, we will learn how to create such a backend ourselves using Python’s
flask framework and
sqlalchemy, a light-weight but powerful ORM library that can access ANY database using its flexible sql expression language.
Rather than using firebase, if you develop your own implementation of your back-end, not only will it help you learn and become a better programmer, but also give you a flexible solution that you can scale and change as per your needs. Its also much cheaper to host your own solution on Amazon EC2 (or Lambda) compared to other costlier alternatives.
Contrary to popular thinking, its not very difficult to create a database agnostic backend such as the one represented in the above diagram. With a minimal and powerful web framework such as
flask, combined with the power of
sqlalchemy, you can get up and running within minutes. In fact, I’ve developed a prototype version called Tiddly that essentially does the same thing as above using just 172 lines of Python code. You can refer to that github repository for reference as we proceed through this tutorial, or directly start using it. But make sure you install the following dependencies before running it:
The first step towards creating the app is creating your database models. Once you’ve done the brainstorming and decided what tables and fields you are going to need, you can create a
models.py source file with something like this:
I’m using sqlite database for example here, but you can use any one of your choice. A
user table is a pretty basic one in almost every app as it is used for authentication. Apart from that, I’ve also created a
dual table just to play around with.
After that, create the second file
app.py that contains our application code. Define the following import statements along with your models as they will come very handy:
Now, the only thing that remains to be done is the plumbing the
HTTP methods to their respective database operations. You can either create a separate view function for each one or use a single one for all of them. In this example, I’m using a single function for simplicity.
I’ve used a non-standard HTTP method,
FETCH for the
SELECT action. That’s because if you use the
GET method, you aren’t allowed to actually post data (as in actual posting, don’t confuse with
POST method) as per the HTTP specification. The other methods, viz
DELETE are self-apparent and they stand for
DELETE actions respectively.
As you can see, the app makes good use of the sql expression language of sqlalchemy to dynamically query any kind of data, not only using the usual
where clause, but also using ordering and pagination (limit/offset) parameters:
The front-end sends whatever it needs to the back-end using JSON format and the result is also in JSON. For example, the following JSON when posted to
/user endpoint using
FETCH method, returns the record from user table where
name field matches
admin and orders the results by email in descending order.
offset clauses to the same can help the front-end with pagination.
Its also pretty trivial to implement user authentication with this design. I haven’t done it in this example for simplicity, but you can find it in the github code.
All code in this tutorial and on github is
MIT licensed and free to use. So, enjoy coding, build your own RESTful CRUD app, and let me know how it goes via comments below!