4 ms·
Related to ORMs/queries/performance, I have found the following combination really good: * aiosql[0] to write raw SQL queries and having them available as pyth
by polyrand 6y ago
Related to ORMs/queries/performance, I have found the following combination really good:
* 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 https://github.com/nackjicholson/aiosql
[1] https://news.ycombinator.com/item?id=24130712 https://news.ycombinator.com/item?id=24130712
[2] https://github.com/MagicStack/asyncpg https://github.com/MagicStack/asyncpg
[3] https://pydantic-docs.helpmanual.io/ https://pydantic-docs.helpmanual.io/
[4] https://fastapi.tiangolo.com/ https://fastapi.tiangolo.com/
- BiteCode_dev 6y agoI always though it would be a nice alternative to an ORM: having a tool that take a marshmallow/pydantic/whatever model, optionally passing it additional db specific options, then it generates a bunch of sql files you can call with aiosql. The whole things would then let you optionally get the result wrapped in a model if you need to, with ORM like helpers for common CRUD things. That would have the benefit of the standardized api of an ORM and the flexibility of SQL, without the coupling.
- polyrand 6y agoAbsolutely! I think it should be doable, FastAPI and other libraries do heavy Pydantic model inspection, and some do automatic code generation. I should explore this a bit more.
- BiteCode_dev 6y agoIt also has some nice properties: - because you have all your queries written in the same place, when you create a db migration, you know where to look for code to update, and can even perform some automatically. - sql queries could be turned into a stored procedure with a simple marking, without changing any code. Of course the downside is that dynamically exploring data is not as easy as with an ORM.
- hangtwenty 6y agoThank you for sharing this stack! I'm a Pythonista at heart, recently was trying RxDB + TypeScript, and I was thinking hmm I'll bet I could do something with postgres and Pydantic.
- jordic 6y agoWe do something like this, also we took some inspiration from hashura. Asyncpg it's so fast an ergonomic.
- polyrand 6y agoThat sounds great! I’ve always kept an eye on how Hasura does things. Do you have any open projects using this stack?
- pantsforbirds 6y agoThe asyncpg library is honestly incredible. I wrote a backfill script that would: 1. dump the rows of a postgres table matching a query (usually a range on the index with a filter or two on other columns) 2. Do some very basic transformation on the rows (few replaces with small regex) 3. Take each transformed row and dump into a rabbitmq queue. I was using aio-pika for the rabbit queue and asyncpg and was getting a consistent 25k messages/sec for like 200 lines of code.
- fokinsean 6y agoThis sounds like a really cool stack. I'm testing the waters with FastAPI and SQLAlchemy right now, but SQLAlchmey feels like it just gets in the way. Do you have an example project which uses all of these I could look at?
- polyrand 6y agoI was writing a cookiecutter template for this kind of stack but never finished it. After using similar stacks for a couple of projects I am going to get back to it soon with many improvements and publish it.
- jononor 6y agoIs there any automatic glue between SQL and Pydantic, or is each mapping hand-written?
- mixmastamyk 6y agoDo you need to define your models more than once with these? I'm looking for a single source solution and haven't quite found it yet.
- polyrand 6y agoI only define them once, but I define all the database schema by hand (with an SQL script). I’d love to have something that translates Pydantic to an SQL schema definition. Each asyncpg result has a dictionary-like interface, so I can convert it to a Pydantic model easily.
- adsharma 6y agoIt would be cool to teach the python static type checkers such as pyright and mypy to do the same validation as pydantic, marshmallow. Then you could use dataclasses and map them to the database via sqlalchemy. https://github.com/adsharma/dataclasses-sql https://github.com/adsharma/dataclasses-sql Couple of other techniques to speedup python: * Transpile python to another language (py2many) * Compile a large graphql like query to a single query plan in python which can be accelerated. (Fquery) Both projects on my github.