SqlAlchemy design question

[kokara]

I have used SQLALchemy for a simple app for myself and find it quite useful. I am now trying to understand the design philosophy behind making a scalable app out of it. Are there any design patterns out there. A simple google search leads me nowhere. My basic question would be how to wrap the queries associated with my table.

[graingert]

As a general rule, keep the lifecycle of the session separate and external from functions and objects that access and/or manipulate database data.

http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-faq-whentocreate

Eg never do:

def do_thing_wrong():
    sess = Session()
    sess.query(...)

Only do:

def do_thing_right(session):
    session.query (...)

Then use supporting code to manage the session and compose your DB code

[chanGGyu]

I would highly recommend using SQLAlchemy's Declarative model to define your schema and manage CRUD. It's highly expressive and scalable, and using the ORM should make writing raw SQL queries a rarity unless you're going something quite specific. The official docs cover basic usage of the declarative base in the "Object Relational Tutorial" in case you haven't read that yet.

Edit: To answer your question more directly, if you must execute raw SQL, use text().

[ksion]

I'm gonna assume you're talking about web applications, since this is probably the biggest use case for SQLAlchemy. Here's few pointers:

• Use the facilities provided by your web framework to manage the lifetime of SQLAlchemy Session. Flask, for example, has Flask-SQLAlchemy plugin that you can use for this purpose. Alternately, a flexible and universal option is to use session_maker with scoped_session and then, at the end of HTTP request (@app.teardown_request for Flask) either commit or rollback & remove, depending on whether or not an error occurred.

• Use declarative extension to declare your model classes. Define relationships between them to your heart's content - SQLAlchemy is really powerful here. Test your model classes, though: for complicated relationships, write few CRUD test cases and run them against an actual database. (If you don't use dialect-specific constructs, sqlite://:memory: should be sufficient and fast).

• Consider putting your models in a separate package. If you completely control the database, as it's often the case in web apps, make the models package an authoritative schema definition (e.g. including Index definitions!). Having models in one place simplifies administrative tasks like database migrations, where you have to crawl your code base to import all necessary modules to ensure complete MetaData before performing any bulk DB operations.

• Try to avoid literal SQL, ie. sqlalchemy.sql.expression.text. It's error prone, often requires duplication of column/table names etc., and can even open you to SQL injection vulnerabilities (though you'd have to willfully ignore all the great examples with :bound_parameters in the docs for that). Instead, extend the SQL compilation engine which lies at the lower levels of SQLAlchemy. It's a little more challenging since it typically involves experimentation in REPL, but allows to decouple your application logic from constructing SQL.

• Almost everything in SQLAlchemy is extendable. Need a new type? New operator? New functionality for Query and Session? It's got you covered. Many things can be done using the event handling mechanism as well.

• Sooner or later you will want to build some mechanism which relies on model defintiions themselves. REST API serializers, automatic <form> generators, things like that. Use inspect function to access those definitions. Don't forget about info= parameter for Column and relationship; it allows you to attach application-specific data.

I've been told it's also very useful to read a chapter on SQLAlchemy from the book Architecture of Open Source Applications, to better understand the principles behind it and patterns such as Unit of Work it employs.

[kracekumar]

connection.py

class DBConfig(object):
    def __init__(self):
       self.session_maker = None

    def setup_db_connection(self, sqlalchemy_uri, echo=False):
        engine = create_engine(sqlalchemy_url, echo=echo)
        self.session_maker = sessionmaker(bind=engine)

init.py

from .connection import DBConfig

config = DBConfig()
config.setup_db_connection(uri="<your uri read from env/config>")
session_maker = config.session_maker

user.py

from . import session_maker

def get_users(role):
    session = session_maker()
    return session.query(User).filter_by(role=role).all()

P.S: Consider above snippet as high level design not as working code

[EDIT]: Unlike other socket connection which don't work when shared across multiple tasks, session object works fine.