4 ms·

Honestly, SQLAlchemy was the biggest design mistake we made in sourcehut, and we're working to remove it now. SQL tends to be the bottleneck, and ultimately we

Honestly, SQLAlchemy was the biggest design mistake we made in sourcehut, and we're working to remove it now. SQL tends to be the bottleneck, and ultimately we just want to write SQL queries which do the right thing, so that we can control for their performance better. Knowing the SQL you want and convincing SQLAlchemy to produce it is an annoying process which makes it hard to make performant queries. I would recommend that anyone starting a new project would avoid using an ORM and just write SQL queries instead. One thing which is occasionally useful is a query building tool. For Go we use squirrel for this purpose. If you need unrelated parts of the code to work together to produce a single SQL query, it can help to have such a tool. This is a lot less than a full blown ORM, though, it's more like passing around a SQL AST in memory.

linux2647 6y ago
That’s what the SQLAlchemy Core is for. You pass around a SQL AST and modify it as you go before executing it. It’s incredibly flexible and always produces what I expect in the resulting SQL. I’ve even written reports with it.
nicwolff 6y ago
SQLAlchemy ORM is built on SQLAlchemy Core, a very complete and flexible SQL query generation abstraction with extensions covering just about every non-standard feature of PostgreSQL and MySQL. Our services (backend content APIs for one of the Web's biggest and busiest content providers) use Core and Alembic (and Flask or FastAPI) and never instantiate the ORM's Models or Sessions.
collyw 6y ago
I get where you are coming from, but ORMs are very useful for inserting and updating data, a lot more efficient than writing SQL. Sure for complex queries it's a different story.
brandmeyer 6y ago
+1 to the siblings advocating for dropping down to Core. Additionally, in the rare cases that you need access to something that isn't available via Core's query builder, you can drop down to bare nekkid text* queries and still cooperate with the rest of the framework. * while still relying on safe query parameterization, of course.
gmac 6y ago
In general, this is also my feeling about ORMs. I'm not sure of the best options in Python, but for TypeScript this article has a very helpful section on query builders: https://phiresky.github.io/blog/2020/sql-libs-for-typescript/ https://phiresky.github.io/blog/2020/sql-libs-for-typescript... (disclaimer: one of them is mine).
tankerdude 6y ago
Others already pointed out that you can use a core method. What I see is that I actually rewrote a query that had 7 joins (yes, it was not great), and rewrote it in SQLAlchemy that made for a the query to have a lot fewer total lines of code to write. I validated it by checking out what it would generate via its internal SQL compiler. So for me, it was more like, am I competent enough to write less code and fewer mistakes, due to functions, autocomplete, etc. versus writing the full SQL. It's a time saving for me, and the queries are expected. I also echo the SQL query to make sure that that's the exact query I want. Lastly, I then groused at the engineer who decided that the query pulled in so much crap from so many tables in 1 query.

linux2647 6y ago

That’s what the SQLAlchemy Core is for. You pass around a SQL AST and modify it as you go before executing it. It’s incredibly flexible and always produces what I expect in the resulting SQL. I’ve even written reports with it.

nicwolff 6y ago

SQLAlchemy ORM is built on SQLAlchemy Core, a very complete and flexible SQL query generation abstraction with extensions covering just about every non-standard feature of PostgreSQL and MySQL. Our services (backend content APIs for one of the Web's biggest and busiest content providers) use Core and Alembic (and Flask or FastAPI) and never instantiate the ORM's Models or Sessions.

collyw 6y ago

I get where you are coming from, but ORMs are very useful for inserting and updating data, a lot more efficient than writing SQL. Sure for complex queries it's a different story.

brandmeyer 6y ago

+1 to the siblings advocating for dropping down to Core. Additionally, in the rare cases that you need access to something that isn't available via Core's query builder, you can drop down to bare nekkid text* queries and still cooperate with the rest of the framework. * while still relying on safe query parameterization, of course.

gmac 6y ago

In general, this is also my feeling about ORMs. I'm not sure of the best options in Python, but for TypeScript this article has a very helpful section on query builders: https://phiresky.github.io/blog/2020/sql-libs-for-typescript/ https://phiresky.github.io/blog/2020/sql-libs-for-typescript... (disclaimer: one of them is mine).

tankerdude 6y ago

Others already pointed out that you can use a core method. What I see is that I actually rewrote a query that had 7 joins (yes, it was not great), and rewrote it in SQLAlchemy that made for a the query to have a lot fewer total lines of code to write. I validated it by checking out what it would generate via its internal SQL compiler. So for me, it was more like, am I competent enough to write less code and fewer mistakes, due to functions, autocomplete, etc. versus writing the full SQL. It's a time saving for me, and the queries are expected. I also echo the SQL query to make sure that that's the exact query I want. Lastly, I then groused at the engineer who decided that the query pulled in so much crap from so many tables in 1 query.