What does HackerNews think of asyncpg?
A fast PostgreSQL Database Client Library for Python/asyncio.
I believe it because that team have done lots of great stuff but I haven't used it, I just remembered thinking it was interesting the performance was so good. Not sure how related it is to running on the asyncio loop (or which loop they used for benchmarks).
* aiosql[0] to write raw SQL queries and having them available as python functions (discussed in [1])
* asyncpg[2] if you are using Postgres
* Map asyncpg/aiosql results to Pydantic[3] models
* FastAPI[4]
Pydantic models become the "source of truth" inside the app, they are designed as a copy of the DB schema, then functions receive and return Pydantic models in most cases.
This stack also makes me think better about my queries and the DB design. I try to make sure each endpoint makes only a couple of queries. Each query may have multiple CTEs, but it's still only a single round-trip. That also makes you think about what to prefetch or not, maybe I want to also get the data to return if the request is OK and avoid another query.
[0] https://github.com/nackjicholson/aiosql [1] https://news.ycombinator.com/item?id=24130712 [2] https://github.com/MagicStack/asyncpg [3] https://pydantic-docs.helpmanual.io/ [4] https://fastapi.tiangolo.com/
Since switching to asyncpg [0] these problems have vanished. It commands a deeper knowledge of actual SQL, but I would argue this knowledge is absolutely necessary and one of the disadvantages of an ORM is that it makes the SQL that is eventually run opaque.
Not sure if there are equivalents to asyncpg for other RDBMS's.
I am using asyncpg[1] (much more performant and provides close mapping to PostgreSQL, making it much easier to use its advanced features) through raw SQL statements without problems.
https://www.postgresql.org/docs/12/libpq-async.html
Sometimes with the right approach (mapping PostgreSQL calls 1:1) and right tooling (uvloop, Cython) you can write code in Python and still squeeze better performance than in Go[1], but you need to know what you're doing.
If you want to explore types, these resources[2][3] were useful for me.
[1] https://github.com/MagicStack/asyncpg
Regarding database access, my recommendation might not be popular, but I really like asyncpg[1]. They basically dropped DBAPI and instead created their own API that functionally matches PostgreSQL, giving you full control and increasing performance.
As a result of that you probably won't be able to use ORMs (there's SQLAlchemy driver, but as I understand it is hacky and you are losing the performance benefits).
Personally I compared my code with and without ORM and there's no change in number of lines. If you use PyCharm and configure it to connect to your database (I think that might be a pro feature) it will then automatically detect SQL in your strings provide autocomplete (including table and column names) and provide highlighting, removing all the advantages that ORM provided.
The asyncpg library[1][2] could be used for direct, asynchronous database access, from within a Django 3.x app.
The asyncpg library is a low-level async Postgres adaptor, similar to psycopg2, except that it provides much better abstractions and automatic encoding / decoding (vs just piping bytes back and forth like psycopg2), not to mention it is much faster than anything else out there. IOW, asyncpg would serve as a much better foundation for a future ORM. My best guess is that, in the future, Django's ORM will be refactored to optionally support async and will supply an adaptor for asyncpg.
That's how asyncpg can outperform[1] some libraries that you normally would think was not possible.
https://github.com/MagicStack/asyncpg https://gitlab.com/pgjones/quart
https://github.com/MagicStack/asyncpg
They're both incredibly experienced developers, and I don't have any good steer on the discrepancy.
Would love to see some independent benchmarking on a typical use case to get a clearer picture on how valuable (or not) asyncio is for high-throughput database operations.