SQLAlchemy best practices with sessions for REST API

Question

I created a rest API with openapi generator that contains all the requests necessary for selecting, inserting, and updating my SQL database.

I use from my database generation and manipulation SQLAlchemy and I'm not sure how to use the session to interact with the database in this context.

My project looks like this:

DB
| openapi_server (generated)
| __init__.py
| request.py
| database.py

In database.py I keep my database structure.

In request.py I have all the functions that need to be processed on every request(to interact with the database).

My way of handling this situation is: I create a session variable at the beginning of each function and after the operation is complete I close it.

Any other methods that are more scalable and easy to maintain or which are the best practices?

ReST literally means that there is no server side session. All relevant data is transmitted by the client on every request. — Klaus D., Feb 20 '21 at 11:11
Do these links answer your question? [If REST applications are supposed to be stateless, how do you manage sessions?](https://stackoverflow.com/q/3105296/113116), [Proper Session Management with REST API](https://stackoverflow.com/q/47330913/113116), [Do sessions really violate RESTfulness?](https://stackoverflow.com/q/6068113/113116) — Helen, Feb 20 '21 at 12:08
My session variable is from the SQLAlchemy ORM and allows me to connect and communicate with the database, it's not the application session. That's why I create a session in every function. My question is how to manage this variable more efficiently in my project structure or if there are some good practices. — vladxjohn, Feb 20 '21 at 15:51

score 0 · Accepted Answer · answered Feb 20 '21 at 15:46

My understanding is that the sqlalchemy session is different from the client session in that the client session stores information about authorization & permissions whereas the sqlalchemy session is a gil-bound transaction state which associates your code / machine to an external database.

Assuming you're not utilizing multithreading or parallel processing, a single sqlalchemy session shared between your application would be appropriate. In the case where your users have different levels of database permissions, I would establish those rules in your application authorization, rather than the database user-permission schema. (That should be reserved for system-users.)

Bear in mind, multiple sqlalchemy sessions are appropriate in many scenarios and there are advantages for creating and closing sessions on the fly. But there are also potential downsides, such as write collisions (2 processes try to write the same record) and so on. In these more fine grained cases, I'd suggest a queuing process as a central orchestrator.

For implementation:
I usually create a file create_session.py which has a function to create a new db session with the appropriate DB URI. I then call that function in the main __init__.py like so: session = create_session() --> importing that session throughout the application is done by importing session from the main module ex: from database import session.

In cases where you need to create new / multiple sessions, do so with:

# Getting the path right here isn't always straightforward tbh
# basically, import the function from the module directly
from create_session import create_session
def do_something():
    # Always create your session in a method
    # otherwise your db will open many unnecessary connections 
    my_session = create_session()
    
    print('Done')
    # Close the session when you're done
    my_session.close()

SQLAlchemy best practices with sessions for REST API

1 Answers1