Sanic: Examples - SQLAlchemy

[channelcat]

Create a new example of how to use SQLalchemy with Sanic. It can be a simple python file or an organized project in its own folder, whichever is easiest to understand and gets someone started on the right track.

Example of sqlalchemy + aiopg:
https://aiopg.readthedocs.io/en/stable/#sqlalchemy-and-aiopg

More research needs to be done on integration with other popular DBs - I'm not sure if this will up the difficulty so I tagged it as both beginner and intermediate.

[jackfischer]

Thank you so much for all your work!! Any possibility of a demo for asyncpg integration?

[peterbe]

Might be worth considering https://github.com/MagicStack/asyncpg too. Supposedly a lot faster than aiopg.

[seanpar203]

I have a repo up that could be used for a simple SQLAlchemy, Alembic & Class based views setup.

It can be found here

[seemethere]

Awesome I'll make a PR to add a link to this from either the README or a separate documentation link.

[easydaniel]

I've tried sanic with sqlalchemy along with asyncpg. Both blueprint(master branch) and class based views are implemented. Hope someone can have me a better implementation
repo here

[seanpar203]

@easydaniel Looks good, have you done some tests on performance using SQLAlchemy async vs sync?

[easydaniel]

I made a test with using SQLAlchemy querying which is sync and asyncpg
It seems that sync db connector crashes when too many connection make at same time

```=sh
boom -c 100 -n 1000

SQLAlchemy sync querying

![2017-01-04 9 21 13](https://cloud.githubusercontent.com/assets/10112821/21643576/f722f8dc-d2c3-11e6-9a08-1c620f6027f0.png)

asyncpg querying

![2017-01-04 9 21 27](https://cloud.githubusercontent.com/assets/10112821/21643559/e98c6cda-d2c3-11e6-8848-da9f3ed10100.png)


```=sh
boom -c 1000 -n 1000 <url>

SQLAlchemy sync querying

asyncpg querying

[seanpar203]

@easydaniel Thanks for uploading that!

[r0fls]

Should this be closed since we have a sqlalchemy example now?
https://github.com/channelcat/sanic/blob/master/examples/sanic_aiopg_sqlalchemy_example.py

[gpip]

Should the example be updated instead so it doesn't pass an event loop to sanic?

[r0fls]

@gpip I've done so here: https://github.com/channelcat/sanic/pull/335

However there is some discussion going on about whether there is a better way, since that recreates the connection for each request.

[saggit]

@easydaniel I notice the asyncpg querying api is /api/auth which the response status code is 404?

that means this test does not hit the database?

[easydaniel]

@saggit I wonder if your database is reachable or not cuz as you can see my blueprint doesn't do any other thing except querying, I suppose the result is database issue.

[peterbe]

https://github.com/channelcat/sanic/blob/da924a359c849c2b2c90da31f9ce5abc9947d783/examples/sanic_aiopg_sqlalchemy_example.py is nice but I too wonder about the implications of having to create a connection on each request handler. Wouldn't be a problem if you use pgbouncer.
Would be awesome if you can have a global connection object with the ability to re-connect.

[danpozmanter]

Another way to do it is to use https://github.com/CanopyTax/asyncpgsa/wiki/Examples and connection pooling. You create the pool using a before_server_start Blueprint (and close it during after_server_stop). Then in a request you acquire a connection from the pool.

[sandys]

@danpozmanter we have a local pool (pgbouncer). how would the sanic example work in this case ? im fairly concerned about having two pools (having seen it blow up in my face in the past)

[danpozmanter]

@sandys That's a damn good question. I'm not sure? My hunch is that in that case you'd want to just grab a connection and let the pooling happen outside of the app?

[sandys]

@danpozmanter with SQLAlchemy, I use NullPool which is specifically designed for this. Not sure what it does. But this is the official way to use with external pooler. PGBouncer is a heavy duty production ready system, so it would not be nice to not use it.
I think you guys will need to build some abstraction around it - especially because of all these async semantics.
P.S. you guys are doing incredible work. Sanic is what we are looking to upgrade to from Flask in my startup versus golang.

[danpozmanter]

@sandys Oh I haven't contributed to Sanic!! Agreed the people behind Sanic are doing incredible work. I've just made a framework that rests on Sanic.

[sandys]

Asyncpgsa is an important part of what we are hopeful about ;)

[stopspazzing]

I believe this should be closed; have example currently. Will be included in wiki once its opened up.
https://github.com/stopspazzing/sanic/wiki/Examples#aiopg--sqlalchemy

[seemethere]

Should have been closed a long time ago...