Comment by polyrand | Hacker News Reader

Comment by polyrand | Hacker News Reader

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 [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/

The 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.

This 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?

I 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.

Is there any automatic glue between SQL and Pydantic, or is each mapping hand-written?

It 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

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.

I 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.

Absolutely! 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.

It 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.

Do 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.

I 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.

Thank 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.

We do something like this, also we took some inspiration from hashura. Asyncpg it's so fast an ergonomic.

That sounds great! I’ve always kept an eye on how Hasura does things. Do you have any open projects using this stack?

Reply on news.ycombinator.com