Under the Hood of Flask-SQLAlchemy
--
Learn how to recreate this popular Flask Extension yourself with just a few lines of code
Have you ever wondered how the Flask-SQLAlchemy extension works under the hood? How hard would it be to set up SQLAlchemy yourself? Today we’re going to dive deep into how to set up the SQLAlchemy ORM for use with a Flask app. At the end you’ll have your very own version of Flask-SQLAlchemy to use!
The purpose of SQLAlchemy is to provide a means for your Flask app to communicate with a database. While the Flask and SQLAlchemy documentation certainly got my head spinning for a few days, hopefully I’ll be able to break it down into bit-sized pieces that you’ll gobble up easily. To explain how the various parts of SQLAlchemy work, I’m going to use the analogy of a Flask App talking to it’s good friend the database on the phone…
- The sessionmaker
SQLAlchemy’s sessionmaker object is the phone Flask uses to make calls to the database — it’s the ‘call factory’ if you will. When you initialize the sessionmaker you pass it the configuration for the calls you want it to make so every time the sessionmaker makes a call it’ll look and act the same. For example you can set the ‘bind’ to use when creating sessions. The bind essentially tells the sessionmaker what phone contact to call when Flask tells it to ‘start a call” and it will always only make calls to that contact.
- The engine
Before you can tell your sessionmaker ‘phone’ which contact to call, you have to create a contact in said phone. Setting up a SQLAlchemy engine is like creating this new contact. When you run sqlalchemy.create_engine(‘database_location’) you pass in the location string of the database that you want your app to talk to.
Once you’ve created an engine, you can then set the sessionmaker bind equal to your engine and it will always make calls to that engine.
- The session
In our example of a phone call, the SQLAlchemy session object is the call itself. Flask uses the sessionmaker object to make the call/session and then it uses it to query the database, add new rows, update rows, delete rows, etc.
- The scoped_session
Imagine you live in a house with several landline phones. You’re using the phone in the kitchen and then all the sudden your Mom decides to pick up the phone in her bedroom. Her phone is connected to the same call as your phone. Once everyone has hung up, the call ends and someone would have to start a new call. The landline is SQLAlchemy’s scoped_session object. The scoped_session object makes sure that every phone in the house has access to that same call and that when everyone has pressed “end call” the call is disconnected. When a phone tries to make a call again, it will create a new call as opposed to continuing the previous one.
In the landline phone example the lifespan of the call was dependent on when the people in the house pressed ‘start call’ and ‘end call.’ In the world of web apps this is akin to when a web server makes a request against your web app. When the request begins, the scoped_session object should use the sessionmaker object to create a session that is accessible by all modules in your Flask web app. The Flask app uses the session to make any necessary queries or updates to the database.Then the request ends and the scoped_session object removes the current session. When a new request comes, it’ll create a new session to handle that request.
Hopefully that analogy gave you a good sense of how things work when you use SQLAlchemy to create a connection to your database. Now let’s put it into code.
1.To start let’s create an empty class called “SQLAlchemy.” By setting up the SQLAlchemy connection as a class it’ll be easier to import the various pieces to different parts of our Flask app.
2. Let’s create our __init__ function. The only thing we need to do here is tell the SQLAlchemy object how we’ll be defining the tables in our database. We’ll be using the declarative extension, which allows us to define tables and models in one go, similar to how you do when using the normal Flask-SQLAlchemy extension. We’ll assign this to self.Base to pass into our tables when setting up the database. More on the declarative extension here.
3. Now let’s create the function where all the magic happens — the init_app function. This is the function we’ll run to connect a flask app.
I’ve attempted to add comments to the code that explain what it’s doing but let me go into a deeper explanation line by line now.
- Line 4: We initialize the engine using SQLAlchemy’s create_engine function and pass in the location of our database (which is set in the app’s config under the variable “DATABASE”).
- Line 6: We create the sessionmaker and set a few default settings to use when generating sessions (such as setting bind=self.engine in line 9 so sessions all access the correct database).
- Line 13: We initialize the scoped_session registry by passing in self.sessionmaker so that the registry knows how to create sessions. Initially the registry is empty, but when our app gets a request and calls self.session the scoped_session registry will create and store a session to associate with the request.
- Line 15:We set the scoped_session’s scopefunc to the flask function that identifies the current app’s call stack. The app sets up and tears down the call stack with each request, so it’s a fitting object to tie our session lifespans to. Learn more about scopefuncs.
- Line 18: We set self.Base.query to session.query_property(). If you’ve used Flask-SQLAlchemy before, you know that you can run a query directly against a table versus against db.session. IE if you had a table named user you could run User.query… instead of db.session.query(User)…
- Line 20: This line creates any defined models and creates them in the database if they don’t already exist. More on this later.
4. Make sure that the scoped_session object removes the current session after each request. To do this we’ll write a function “remove_session” that calls self.session.remove() and add that to our app’s list of teardown request functions (functions that run at the end of each request).
Notice we added app.teardown_request(self.remove_session) to the end of our init_app() function.
10. Now let’s define our database tables using the self.Base declarative extension…Here’s a short example of how to create a table:
Notice how we imported db from your_application. This is assuming you set db = SQLAlchemy() in your application’s __init__.py file. Make sure to pass db.Base into each of your table classes so SQLAlchemy can understand them. You’ll also need to import the various table structures from SQLAlchemy (such as Column, Integer, String, etc).
11. Set up your flask app factory and use the SQLAlchemy ORM! Here’s an example of an simple __init__.py file I created using the FLask_SQLAlchemy object we created in this tutorial:
Note that first I defined db = SQLAlchemy(), second I imported my table classes from the module where I defined them, then I ran db.init_app(app). You must do these things in this order otherwise you will find that your database tables aren’t created properly. db=SQLAlchemy() creates db.Base which we need to define our tables. If you do db.init_app(app) before you import your table classes, SQLAlchemy won’t know about them and it won’t create them in your database.
That’s how it’s done! Here’s a brief overview of how to use the SQLAlchemy session object to run queries and add things to your database.
I never like to create something without testing it to make sure it works first…let’s create some tests to make sure our our Flask-SQLAlchemy object will work properly. The SQLAlchemy docs say that in the context of a web application there are two things that need to be implemented:
- Create a single scoped_session registry when the web application first starts and make sure that this registry is accessible across the application.
- Make sure that scoped_session.remove() is called after each request ends.
Let’s make sure our module is following these two rules. If you’re interested in following along, for the tests I’ve created a simple Flask app that creates an app that ‘registers’ users to a database. You can find the full app and tests on github.
For ease of testing I’ve changed the in the init_app function from the above example self.session = sqlalchemy.orm.scoped_session(self.sessionmaker, scopefunc=flask._app_ctx_stack.__ident_func__) to self.session = init_scoped_session() , init_scoped_session being a function that returns the original code (creating a scoped_session object). This way I can tie a decorator to the function that will record many times the function is called.
1a. Create a single scoped_session registry when the web application first starts
1b. Make sure that this scoped_session registry is accessible across the application
2. Make sure that scoped_session.remove() is called after each request ends.
Both tests passed with flying colors!
If you’re like me you’re probably still a bit mystified about how everything works. Here, I’ll go through some of the questions that were running through my head and the answers I found.
1.First, why do we create a new session with each request and remove it at the request end? Why don’t we just create one single session for the lifespan of the app?
For web apps, tying the lifespan of a session to the lifespan of a request is par for the course. A request meaning when one of your app’s routes is called (for example a “GET” or “POST” request to your app). This page has a nice diagram of how the flow of the SQLAlchemy bind should work when following this pattern.
2. The SQLAlchemy docs say that when the scoped_session object is called it will always return the same session until the scope ends and scoped_session.remove is called. How does this work?
Let me show two tests that I ran to prove this behavior:
Note that you have to explicitly call the scoped_session object (db.session) in order for this to work. By calling db.session() you’re passing the session object itself to session1 and session2. If you just ran session1 = db.session you would be passing the scoped_session object itself and the second test (that asserts that session1 and session2 are different in different requests) would fail because the scoped_session object itself does not change between requests.
Here’s an example:
3. With Session = scoped_session(sessionmaker), is calling Session() and Session the same thing? I.e. in your code example why can you call db.session instead of having to call db.session = db.session() like in these examples in the SQLAlchemy docs?
It’s a lovely thing called implicit method access. I recommend reading about it here. I explain it a bit in the question above as well.
4. Do I need to store anything in flask’s g object?
No. If you’re like me, you may have first tried writing some heavy handed code like this: It’s not necessary. You’re overthinking things. The scoped_session object handles all that.
5. Did you publish your project on PyPi?
Yes in fact I did. You can install it with pip by calling pip install flask-sqlalchemy-bind. Learn more about it here. I may publish an article on how I uploaded it to PyPi and the process I went through for that. But maybe not because I’m a bit blogged out after this post.
Hopefully that answered all of your questions in a semi-concise manner. If you have any more (or if you found some errors in my thought patterns) feel free to contact me at klf16@my.fsu.edu.
