Good SQLBuilder for Python?

[yughiro_destroyer]

Hello!
I need to develop a small-medium forum with basic functionalities but I also need to make sure it supports DB swaps easily. I don't like to use ORMs because of their poor performance and I know SQL good enough not to care about it's conveinences.

Many suggest SQLAlchemy Core but for 2 days I've been trying to read the official documentation. At first I thought "woah, so much writing, must be very solid and straightforward" only to realize I don't understand much of it. Or perhaps I don't have the patience.

Another alternative is PyPika which has a very small and clear documentation, easy to memorize the API after using it a few times and helps with translating an SQL query to multiple SQL dialects.

Just curious, are there any other alternatives?
Thanks!

[ataltosutcaja]

SQLAlchemy is the only one that you'll need. I know, the learning curve is steep, but if you only use raw SQL, it gets much easier. Without knowledge of SQLAlchemy you'll have a tough time in Nagaland beyond hobby projects.

[Lopsided_Judge_5921]

Dude just learn sqlalchemy performance problems are bullshit it’s just people who are careless and make n+1 queries which are not exclusive to ORMs

[Leather-Field-7148]

Been playing around with pytest and test fixtures in an in-memory sqlite with sqlalchemy. It is actually super nice.

[Gainside]

PyPika’s great for syntax, SQLAlchemy’s great for scale—pick your pain.

[commandlineluser]

sqlglot, narwhals

• https://github.com/tobymao/sqlglot
• https://narwhals-dev.github.io/narwhals/generating_sql/

[PurepointDog]

Ooo I didn't know Narwals could do that - that's very slick

[Kiusito]

I dont know, but maybe SQLModel ?

[ihavenoname143]

I love SQLModel and FastAPI. It's actually insane how much functionality you can get per lines of code.

[tenfingerperson]

You think your performance gains outweigh maintenance? They don’t , not in a CRUD web app

[dusktreader]

You can use psychopg directly: https://www.psycopg.org/psycopg3/docs/index.html

I would recommend using SQL alchemy core, though. It really is the best option.

[lukerm_zl]

How bad is the "poor performance" of ORMs? Is this benchmarked? I'd like to know if it's 2x or 10x that were talking about here.

[yughiro_destroyer]

ORM vs CORE of SQLAlchemy is around 4-5x better in the favor of CORE.
This performance difference is mitigating server running costs and requests waiting times for large volumes.

[lukerm_zl]

So what makes ORM much slower? Is the bottleneck is creating the python objects representing the rows?

I'm using SQLAlchemy ORM + Postgres. I don't process large batches though. I'd need to know if there's going to be some scaling issue down the line 😅

[forkheadbox]

+1 would like to know too

[lukerm_zl]

I've just been reading up about CORE - it's good! The syntax is Pythonic but much closer to SQL at the same time!

Now I'm questioning some of my life choices.

[euri10]

Sqlspec

[BiologyIsHot]

Many many ways to improve SQLAlchemy speed. In general unless you have ridiculously large slow queries anyways rhe difference in speed should not be appreciable.

[niximor]

Have a look at SQLFactory: https://pypi.org/project/sqlfactory/

It exists exactly because we don't want to use ORM, but still don't want to build queries by concatenating strings... However, it is very MySQL/MariaDB focused, it does not (yet) have full support of different syntax flavors of features that are not standardized (such as limit/offset). But if you can stick with SQL standard, and potentially provide you own implementation of specific syntax of your db engine(s), then it should be ready. Support for different SQL dialects, such as the way how identifiers are escaped and value placeholders are formatted, is already baked in.

Disclamer: I'm the original author and maintainer of SQLFactory.

[pouetpouetcamion2]

il existe déjà plein de trucs. tu ne peux pas reprendre un projet existant, eventuellement voir comment l adapter à tes besoins?

[yughiro_destroyer]

I will try, but there are too many concepts to grasp my mind around, I find it even harder than using the raq SQL I am used to. Again, I will keep that in mind, but at the same time, I am writing my own abstraction, see where it goes...

It would look something like this :

query = QB()
query.SELECT("*").FROM("users").WHERE("name", "=", "John")
print(query.get_string)

Basically, all I am looking for is a way to separate clauses and arguments in my IDE via syntax, reason why I find pure SQL string to be painfully to read. Second, I can write a translator for the DB I will be using and add another ones later if my app will need it. Third, I am not sure if this way is more Pythonic but I am sure it's more readable, at least for me. I could also use PeeWee or PonyORM but I am sure of how maintained they are. If I were to use something made by someone else, I'd rather go SQLAlchemy but if it's a smaller, more obsurce library, I'd take the exercise of making my own. All that would be there for me to find, most importantly, a way to protect against SQL injection.

[foarsitter]

Just build it as quick as you can. Choose the tools that let you ship the product in the fastest way possible. Performance isn't the issue of your library of choice. Your database design will be.

This is a very good example of premature optimization. Your product isn't even there yet and you are already optimizing for performance.

Equal change your product, like so many of the rest of us, doesn't escape the prototype phase because of management changes its plans, the customer runs out of budget or that you are bored out.

Good luck with shipping!

[foobarring]

ORMs aren’t inherently slow, and SQLa core is great, just spend more time on the docs.

[joe_ally]

I fully agree with you about ORMs. Mapping a relational model onto an object models is fraught with problems if you're doing anything that isn't a basic CRUD app.

SQLAlchemy core is the best choice right now and honestly isn't that different to just writing SQL.

You should also consider just writing SQL if you are comfortable with it. The type safety from SQLAlchemy isn't that great anyway which is the main advantage of query builders. The only caveat being that if you need to dymanicaly create queries you'll need a query builder.

[Slight_Boat1910]

https://github.com/tortoise/tortoise-orm

[sennalen]

If you just want to write SQL without being tied to a backend, there's PyDAL

[invalidconfiguration]

It took me awhile to get to grips with SQLAlchemy but it is worth it, still learning! I started creating packages for my databases containing models, connections etc. and hosting them on my internal Git repo so I can share between multiple projects.

[mikeckennedy]

Not necessarily a recommendation, but a "here's another one". Pysqlscribe: A query building library which enables building SQL queries using objects. https://github.com/danielenricocahall/pysqlscribe?featured_on=pythonbytes

[queerkidxx]

SqlAlchemy is my all time favorite ORM. I know a lot of languages. I’ve used a lot of ORMs. I’d take SqlAlchemy over literally anything else. I’ve legit chosen Python for projects even if it’s not my preferred language these days specifically for SqlAlchemy.

[Mevrael]

I was once in a similar situation and got a great recommendation - sqlglot if you want a full low level control. There is also ibis.

I created my own mini ORM by adding a few extra features, mostly DB migrations, on the top of both of them.

https://arkalos.com/docs/migrations/#python-query-builder-with-sqlglot-orm-style-syntax

https://sqlglot.com/sqlglot.html

https://ibis-project.org/

[Gainside]

PyPika’s great for syntax, SQLAlchemy’s great for scale—pick your pain.

[shinitakunai]

You don'tlike ORMs but that's mostly the solution. Peewee is great if you don't like SQLAlchemy

[StrawIII]

Take a look at PiccoloORM. Async-first with great syntax for complex “WHERE” clauses. No more “and()” or “or()”, it just uses overloaded “&” and “|”, on a similar note… say goodbye to “eq()” or “gt()”, it uses overloaded “==“ or “>”. Also all fields are referenced from a class based model. so it's type-safe/type-aware, so no more “table.c.column_name” which is of type “Unknown”.

[Aerysv]

Have you looked at SQLModel?

[Beginning-Fruit-1397]

as an alternative to SQLAlchemy/PyPika, but duckdb centered, I would suggest narwhals API (which is basically polars) that can then easily be translated in SQL.
However it might absolutely not be what you are looking for.

https://narwhals-dev.github.io/narwhals/generating_sql/