SQLAlchemy VS sqlmodel

SQLAlchemy

For pipeline implementation, Python with SQLAlchemy is the standard stack for custom ETL with relational databases. PostgreSQL handles both pipeline operational state (dead-letter tables, run logs) and destination storage. For orchestration-level error handling and retry policies, Apache Airflow provides per-task retry configuration and failure branching.

For monitoring infrastructure, Prometheus and Grafana are widely used for pipeline metrics collection and alerting. For orchestration that includes built-in run observability, Apache Airflow tracks run history, task durations, and failure states in a web UI. Python with SQLAlchemy is the standard stack for custom pipeline implementation with relational state management.

at the very least, if you are really writing lots of INSERTs by hand I bet you are either not quoting properly or you are writing queries with 15 placeholders and someday you'll put one in the wrong place.

ORMs and related toolkits have come a long way since they were called the "Vietnam of Computer Science". I am a big fan of JooQ in Java

https://www.jooq.org/

and SQLAlchemy in Python

https://www.sqlalchemy.org/

Note both of these support both an object <-> SQL mapper (usually with generated objects) that covers the case of my code sample above, and a DSL for SQL inside the host language which is delightful if you want to do code generation to make query builders and stuff like that. I work on a very complex search interface which builds out joins, subqueries, recursive CTEs, you name it, and the code is pretty easy to maintain.

Updating a database schema as part of your development process often feels more complicated than it should be. If you’ve ever worked with tools like SQLAlchemy, Alembic, or EF Core, you probably know the drill: you first update your model classes in code, then generate a migration file, and finally apply those changes to your database. It's not a terrible process—but it's slow, easy to mess up with the correct migration order, and repetitive. You constantly have to switch contexts: from writing model code, to terminal commands, to reviewing raw SQL.

Two good examples of "builders to SQL" are

https://www.jooq.org/

and

https://www.sqlalchemy.org/

JooQ isn't everybody's taste but I use it for my job and I think it's great particularly in that you can reuse expressions and write generators for complex queries. We have a powerful search interface that combines full-text with other kinds of queries ("Is about topic T", "Data was collected between S and E") that is beautiful. I think it's funny how JooQ has that lispy f(a,b) style (no accident it is like ferocity) and how Sqlalchemy is really fluent and takes advantage of operator overloading.

6. SQLAlchemy - Database Interaction using ORM

Object-Relational Mapping frameworks like Hibernate (Java), SQLAlchemy (Python), and Sequelize (Node.js) typically use parameterized queries by default and abstract direct SQL interaction. These frameworks help eliminate common developer errors that might otherwise introduce vulnerabilities.

This sounds great and there's a real gap in the ecosystem for a tool like this. https://sqlmodel.tiangolo.com/ looked promising but it's actually worse than useless because if you add it to your Pydantic models, it disables all validation: https://github.com/fastapi/sqlmodel/issues/52

an SQLModel entity backed by a database table doesn't validate its fields on creation, which is the point of Pydantic.

https://github.com/fastapi/sqlmodel/issues/52#issuecomment-1...

Source: https://github.com/fastapi/sqlmodel

This is exactly the problem the creator of FastAPI tried to solve with the library SQLModel.

Seems like this is a known bug in SQLModel: https://github.com/tiangolo/sqlmodel/issues/10

I have not used it myself but the creator of fastapi has made https://github.com/tiangolo/sqlmodel

I have already posted this question in Stack Overflow and GitHub and have been ignored in both 😢. You guys are my last hope.

I had a requirement to process and validate large payloads of JSON concurrently for a web service, initially I implemented it using jsonschema and fastjsonschema but I found the whole JSON Schema Specification to be confusing at times and on top of that wanted better performance. Albeit there are ways to compile/cache the schema, I wanted to move away from the schema specification so I wrote a validation library inspired by the design of tiangolo/sqlmodel (type hints) to solve this problem easier.

Github Repository