Falcon: DB connector?

[ariscn]

What's the Right Way (tm) to set up a DB connector / ORM in the Falcon lifecycle? I'd love to see an example of Peewee/SQLAlchemy/Redis using a Flask-style proxy (or any example that doesn't involve passing the connection around manually like the current docs).

[oscarcp]

There is no Right Way(tm) but you can take this as an example... https://github.com/clione/sikre/blob/master/sikre/db/connector.py It seems that people are getting quite interested in Falcon, so maybe if I have time enough I'll create a hook with this code alongside the OAuth 2 authentication

[MackYoel]

@oscarcp I'm also really interested and would love to see more real examples. Talons project is not very clear, but at least gives us an idea of the panorama. I'll be attentive to this

[bcthomas]

I would also love to see more examples like this.

[Gesias]

I´m using repool connection pool and handle the inject & release with middleware. Works very well thus far.

[MackYoel]

@Gesias Could you give us some example about that? Thanks!

[Gesias]

@MackYoel Sure thing, tried to write it down here but this Markdown syntax is killing me, will put up a public file instead for reference. Also, I´ve implemented the Remodel project also to have access to an ORM when needed. It does have a connection pool built in actually. I have both now because I like to have a bit of control.

[foresmac]

We've been using a pretty simple middleware that works well for us since we do everything with stored procedures and don't use an ORM:

class DatabaseCursor(object):

    def __init__(self):
        self.db = database_connection()

    def process_resource(self, req, resp, resource):
        if resource is not None:
            if self.db.closed:
                self.db = database_connection()
            resource.db = self.db
            resource.cursor = self.db.cursor()

    def process_response(self, req, resp, resource):
        if hasattr(resource, 'cursor'):
            resource.cursor.close()

database_connection() is a pretty simple function that connects to a Postgres db using psycopg2, but it could basically be anything that returns a DBAPI connection object. Then we get a cursor for each resource and close the cursor after the response is generated. This allows us to do self.cursor.callproc() in our resource methods.

[ddxgz]

I've used peewee with Falcon in a small project, in a way like Django. Though it didn't bother me, I'd like to see some more examples.

[synic]

@foresmac: pretty new to falcon, but, from reading the code, I think that middleware might have a few bugs. Since a new responder is not created for every request, your cursor object could be shared between two different requests (and users!).

It also seems to suffer from a race condition where the cursor can be in the process of being closed while another concurrent request tries to access it.

It almost seems like the only way is to assign it to req.context['db'] - since req is one of the only instances created and destroyed in a request cycle.

Am I wrong about this? If so, can someone please explain?

[benjohnde]

That is correct. You need to create a transaction, a scope or a session (whatever it is called) for each request.

An example middleware for using SQLAlchemy:

class DatabaseSession(object):
    """
    Initiates a new Session for incoming request and closes it in the end.
    """
    def __init__(self, Session):
        self.Session = Session

    def process_resource(self, req, resp, resource, params):
        resource.session = self.Session()

    def process_response(self, req, resp, resource):
        if hasattr(resource, "session"):
            resource.session.close()

Cheers!

[jmvrbanac]

Since SQLAlchemy provides support for connection pooling, I usually let it handle all of the connections for me and just use scoped sessions. When it comes to manually closing them, I usually only do that when I'm shutting down my API or handling errors. This page might help Using connections-pools with multiprocessing

As a side note:
While I know a bunch of people do it, I'm not a fan having db sessions managed by middleware. It results in hard coupling between a layer of middleware and various points across my API. I find the cleanest way to do it is to have a DatabaseManager class that is instantiated with the API. I then pass the manager down when I instantiate Resources and Middleware. It creates a clean independent module to interface with the connections and sessions in an ORM. It's easier to test things that way :-)

[foresmac]

@synic That's not really a problem because we run Falcon in gunicorn, so each worker has its own process and each instance of falcon API has a single connection and processes a single request at a time. All I can say is with a busy API we never once found any evidence of a race condition.

[benjohnde]

I am not deeply into falcon, when it comes to request handling. What I did read was the the following: http://docs.sqlalchemy.org/en/latest/orm/contextual.html#using-thread-local-scope-with-web-applications. If concurrency of requests can not be a problem, then it is unncessary to use a new fresh "Session" for each request (as my naive middleware does).

The approach to cleanly inject the dependencies when instantiating the resource objects is obviously the better one :) Nevertheless I had no problems with my middleware when it came to testing.

[foresmac]

Yeah, I mean, as far as I know a standard WSGI-based framework is not multithreaded. There are lots of different ways to run them, but our typical approach was to fire up num_procs * 2 workers behind gunicorn, and that was behind nginx. So nginx handled request queuing, and gunicorn basically ran a copy of the falcon API in each worker. It's pretty easy to understand and reason about.

You could use something like twisted or tornado, which let you balance multi-thread and multi-process, and then you will have to be more careful about connection pooling and the like. But those frameworks have very different design patterns to begin with.

I'd be very interested in seeing what an ASGI version of falcon might look like, though! ;)

[suhailars]

how can i handle request session(not db session ) in falcon ?
how can i use beaker with falcon?