Good SQLBuilder for Python?
Posted by yughiro_destroyer@reddit | Python | View on Reddit | 38 comments
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!
SnackLoop@reddit
Based on your requirements (easy DB swaps, good SQL knowledge, clear API), I'd suggest taking another look at PyPika since you already mentioned it fits your criteria well. It's specifically designed for people who know SQL and just need dialect abstraction without the ORM overhead.
That said, I'd encourage you to give SQLAlchemy Core one more shot with a different approach to the docs. Instead of reading the whole thing front-to-back, try starting with the "SQL Expression Language Tutorial" and just build a few simple queries alongside it. The documentation is verbose because it covers every edge case, but the core concepts are actually pretty straightforward once you get past that initial wall of text. Many people in this thread are right that it's industry standard for a reason.
If you're still not feeling it after that, sqlglot (mentioned by several commenters) is another solid option that gives you low-level control with SQL dialect translation, though the API is quite different from PyPika. For your forum use case though, PyPika is probably sufficient and you'll be productive faster.
queerkidxx@reddit
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.
mikeckennedy@reddit
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
Kiusito@reddit
I dont know, but maybe SQLModel ?
ihavenoname143@reddit
I love sqlmodel and FastAPI. It's actually insane how much functionality you you get per lines of code.
euri10@reddit
Sqlspec
invalidconfiguration@reddit
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.
sennalen@reddit
If you just want to write SQL without being tied to a backend, there's PyDAL
Slight_Boat1910@reddit
https://github.com/tortoise/tortoise-orm
Throwaway__shmoe@reddit
Sqlalchemy is probably your best bet. It’s not just an ORM, it’s a wrapper around drivers that implement pep-249 (DB API spec) which allows it to swap databases that has ORM support.
Yes the documentation is not great. Factor that into your estimates. You’re not going to find a better tool.
If this will absolutely not work, then you are going to have to build your own abstraction layer on top of DB API drivers for your dialects of concern.
Source: ten years of enterprise Python experience. Sqlalchemy is a curse and a blessing, learn to power through the bs.
Decent-Mistake-3207@reddit
For your use case, pick SQLAlchemy Core and add a tiny adapter layer instead of switching to another SQL builder.
Practical setup that’s worked for me: define statements with Core (types matter), keep them in modules, and have one adapter that compiles a statement + params and runs it on the current driver. Use statement.compile(dialect=...) and literal_binds=True to debug exact SQL per backend. Stick to the common subset (ids, timestamps, booleans, limit/offset), and let Core handle types and placeholders across Postgres/MySQL/SQLite. Alembic keeps migrations sane; for async, pair Core with the databases library. If OP really hates Core’s API, PyPika is fine, but you’ll hit gaps around upserts, RETURNING, and vendor quirks, which Core already smooths out.
If you later need instant APIs, I’ve used Hasura and PostgREST for Postgres, and DreamFactory helped when I needed similar APIs across MySQL and SQL Server without hand-rolling endpoints.
Short version: Core + a thin adapter gives you portability without giving up SQL control.
yughiro_destroyer@reddit (OP)
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 :
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.
joe_ally@reddit
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.
foobarring@reddit
ORM’s aren’t inherently slow, and SQLa core is great, just spend more time on the docs.
Lopsided_Judge_5921@reddit
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@reddit
Been playing around with pytest and test fixtures in an in-memory sqlite with sqlalchemy. It is actually super nice.
lukerm_zl@reddit
How bad is the "poor performance" of ORMs? Is this benchmarked? I'd like to get know if it's 2x or 10x that were talking about here.
yughiro_destroyer@reddit (OP)
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@reddit
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@reddit
+1 would like to know too
lukerm_zl@reddit
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.
foarsitter@reddit
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!
pouetpouetcamion2@reddit
il existe déjà plein de trucs. tu ne peux pas reprendre un projet existant, eventuellement voir comment l adapter à tes besoins?
niximor@reddit
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.
dusktreader@reddit
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.
commandlineluser@reddit
sqlglot, narwahls
PurepointDog@reddit
Ooo I didn't know Narwals could do that - that's very slick
shinitakunai@reddit
You don'tlike ORMs but that's mostly the solution. Peewee is great if you don't like SQLAlchemy
non3type@reddit
I don’t think “knowing SQL” has much to do with the conveniences of using an ORM. Or I have concerns if they are lol.
BiologyIsHot@reddit
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.
marr75@reddit
The expression syntax in SQLAlchemy is quite good. I don't usually care much for ORMs but they can take care of your transactional CRUD (with multiple items in the object graph) pretty well.
I've seen some references to sqlglot and heavily endorse it as an interface to "transpile" SQL between target dialects and work with SQL ASTs. Ibis can be thought of as a higher level wrapper around sqlglot that allows a lazily evaluated, high performance interface to SQL as if it was a pandas or polars frame (it can use pandas and polars as backends, too). The default backend is duckdb (which immediately makes your operations faster than pandas and competitive with polars until working sets get large, at which point Duckdb leaves everything behind).
I highly recommend ibis for analytical queries.
Beginning-Fruit-1397@reddit
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/
Gainside@reddit
PyPika’s great for syntax, SQLAlchemy’s great for scale—pick your pain.
StrawIII@reddit
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”.
tenfingerperson@reddit
You think your performance gains outweigh maintenance? They don’t , not in a CRUD web app
Mevrael@reddit
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/
ataltosutcaja@reddit
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.
Aerysv@reddit
Have you looked at SQLModel?