Our great sponsors
-
-
-
InfluxDB
Power Real-Time Data Analytics at Scale. Get real-time insights from all types of time series data with InfluxDB. Ingest, query, and analyze billions of data points in real-time with unbounded cardinality.
-
-
sql-template-tag
ES2015 tagged template string for preparing SQL statements, works with `pg`, `mysql`, and `sqlite`
-
WorkOS
The modern identity platform for B2B SaaS. The APIs are flexible and easy-to-use, supporting authentication, user identity, and complex enterprise features like SSO and SCIM provisioning.
-
-
-
-
dbt-core
dbt enables data analysts and engineers to transform their data using the same practices that software engineers use to build applications.
-
-
-
-
SaaSHub
SaaSHub - Software Alternatives and Reviews. SaaSHub helps you find the best software and product alternatives
First of all, thank you for SQLAlchemy! If I ever had to make a final choice in how I would interact with a database for a very large project that involves a considerable dev team, I would always bet on SQLAlchemy. Not that I would necessarily like all aspects of it, but when it comes to Python and SQL - “Nobody ever got fired for picking SQLAlchemy.”.
With that out of the way, despite ORMs doing much more than "just writing SQL", it is exactly on that point that I flinch: Most devs should be exposed to SQL. And if your project allows you to build around simple enough abstractions so that you aren't reinventing the wheel, you should definitely be writing SQL. Especially if you don't know SQL yet - which is the growing case of new devs coming into the job market.
You can achieve a lot with SQlAlchemy Core, a tool that I absolutely recommend, but my post is just a simple alternative to get developers to think about their approach. If that results in some devs reconsidering using "full fat" SQLAlchemy and to try SQLAlchemy Core, that's a win for me!
Your gist tries to highlight the difficulty of doing certain things without an ORM. Migrations (as just 1 example) doesn't need to be hard, simple tools like flyway, or migrate (https://github.com/golang-migrate/migrate) achieve a similar result (while also keeping you on the path of writing SQL!). Deep and complex relationships between objects also don't need to be hard - typically people approach this subject with a requirement to be very flexible in the way they want to build queries and objects, but that to me in a sign that maybe they should reconsider their business logic AND reconsider that, just maybe, their project doesn't require all that flexibility, it is fairly straightforward to extend objects and introduce some more complex representations as and when it is needed - will all of this make me write code faster? Absolutely not. That is why you have spent so much time perfecting SQLAlchemy, but then again, I am not advocating for devs to go and replace their usage of ORMs, just presenting an alternative that may or may not fit their needs for a new project + give devs the chance to learn something that the ORM might have taken away.
Seems like there's 3 groups of opinions on ORMs:
Firstly (1); "I want to use the ORM for everything (table definitions, indexes, and queries)"
Then second (2), on the other extreme: "I don't want an ORM, I want to do everything myself, all the SQL and reading the data into objects".
Then thirdly (3) the middle ground: "I want the ORM to do the boring reading/writing data between the database and the code's objects".
The problem with ORMs is that they are often designed to do number 1, and are used to do number 3. This means there's often 'magic' in the ORM, when really all someone wanted to do was generate the code to read/write data from the database. In my experience this pushes engineers to adopt number 2.
I'm a big fan of projects like sqlc[1] which will take SQL that you write, and generate the code for reading/writing that data/objects into and out of the database. It gives you number 3 without any of the magic from number 1.
[1] https://sqlc.dev/
I've had good experience using PugSQL to write SQL in Python [1]. You write the SQL inside SQL files so you can actually benefit from syntax highlighting, static analysis, etc. At the different of writing strings of SQL inside Python files.
[1] https://pugsql.org
For those looking for a rubyish approach to this see: https://github.com/discourse/mini_sql
Shameless plug, with channel support: https://github.com/semrekkers/sqlz
Thea answer to your prayers already exists: http://sequel.jeremyevans.net/.
By far the best database toolkit (ORM, query builder, migration engine) I have seen for any programming language.
You can avoid this entirely with JavaScript's tagged template literals. Here is an example library: https://github.com/blakeembrey/sql-template-tag
We've always used https://github.com/jmoiron/sqlx which is just the standard package + mapping to/from structs.
As a data engineer, the pattern the OP shares is very familiar. I find it much preferable to use of ORMs for wide variety of reasons. However, I view implementing with SQL as an antiquated problem rather than a pragmatic feature. The evolution of this pattern would be to integrate database querying into languages more directly and eliminate SQL entirely. While this could be achieved in Python, I find that a language like Clojure, via functional programming (FP) primitives and transducers, is a natural candidate, particularly for JVM implemented databases. Rather than encapsulating SQL via query building or ORM based APIs, an FP core could be integrated into database engines to allow, via transducers, complex native forms to be executed directly across database clusters. Apache Spark is an analog of this. In particular the Clojure project, powderkeg (https://github.com/HCADatalab/powderkeg) demonstrates the potential of utilizing transducers in a database cluster context.
It's fine advice - if you can type check your queries. My colleague wrote a mypy plugin for parsing SQL statements and doing type checking against a database schema file, which helps to identify typos and type errors early: https://github.com/antialize/py-mysql-type-plugin
One challenge working with SQL from statically typed languages (including Python + Mypy) is that you have to convert the query inputs/outputs to/from types and it's a lot of boilerplate. I started an experiment to generate this from annotated queries. [1] Python support is still incomplete, but I'm using it somewhat successfully for using SQLite from Rust so far.
[1]: https://github.com/ruuda/squiller
similar project that generates types for queries into an intermediate representation that can be consumed by, say TypeScript, to get static Types: https://github.com/vlcn-io/typed-sql
I'm in a 4th camp: we should be writing our applications against a relational data model and _not_ marshaling query results into and out of Objects at all.
Elaborations on this approach:
- https://github.com/papers-we-love/papers-we-love/blob/main/d...
- https://riffle.systems/essays/prelude/
I really dislike SQL, but recognize its importance for many organizations. I also understand that SQL is definitely testable, particularly if managed by environments such as DBT (https://github.com/dbt-labs/dbt-core). Those who arrived here with preference to python will note that dbt is largely implemented in python, adds Jinja macros and iterative forms to SQL, and adds code testing capabilities.
Can you explain a bit more about the Django ORM being very thin and easy to read? It does seem like the Django ORM is thin (from an architecture perspective), but it doesn't seem to be small, it seems to be pretty big. Maybe I'm not understanding it though, so here's what I see:
The "ORM" part of Django seems to be everything in `django.db.models.Model`, which seems to require you to declare your Models as subclasses of the aforementioned class. Looking into that code though, it seems like the implementation supporting all this is around ~20,000 lines of Python: https://github.com/django/django/tree/main/django/db/models
That doesn't strike me as a super lightweight. For comparison, all of Flask (a Python WSGI web app framework, but mostly a 10+ year old project to compare to, and excluding tests) is ~4,000 lines of Python.
Is there a small subsection of the code in `django/db/models/` that is all that's necessary to use the ORM part? Or maybe I'm missing something about the "core" of the ORM?
If you want to try out something cool, check out
https://github.com/sqlc-dev/sqlc
It's written in Go and used to convert sql migrations and queries into a typesafe code that you use access your database.
It currently has a plugin for Python that's in Beta, but what essentially does something similar to what this post is saying.
https://github.com/sqlc-dev/sqlc-gen-python
You write your migrations, and queries and a config file and it does the rest.
There's a whole family of libraries like that. Yesql is the first I became aware of. The repo has an (incomplete) list of ports to other languages: https://github.com/krisajenkins/yesql#other-languages
That above pattern is one I've seen people do even recently, using the "select().c" attribute which from very early versions of SQLAlchemy is defined as "the columns from a subquery of the SELECT" ; this usage began raising deprecation warnings in 1.4 and is fully removed in 2.0 as it was a remnant of a much earlier version of SQLAlchemy. it will do exactly as you say, "make a subquery for each filter condition".
the moment you see SQLAlchemy doing something you see that seems "asinine", send an example to https://github.com/sqlalchemy/sqlalchemy/discussions and I will clarify what's going on, correct the usage so that the query you have is what you expect, and quite often we will add new warnings or documentation when we see people doing things we didn't anticipate.