As someone who's thought about embracing over using the ORM, here's a couple issue that come up:
- Version control. The SQL functions end up just being data in your DB. This feels like an anti-feature in the world of continuous integration and code review.
- Lack of composability. An ORM gives you the ability to easily compose filters. This lets me write functions like "take this query, and make sure it's properly filtered down to one user's data". Because of how SQL works, each query needs to be hand-crafted, and it's hard to make parts of it generic.
- Rewriting business logic. I have business logic in Python already, but if I want to query off of that in the DB in SQL, now I need two implementations. You can sort of get around this in an ORM by annotations.
- I'm not sure what the developer tooling environment is like. PyCharm is pretttty nice.
To be honest, you citing that it's easy to get around the validation process when using Postgres is a major red flag. Why would I want to circumvent code review for things that are most undoubtably code?
The right way to have composability in a RDBMS is with things like parameterized views or table-valued functions. This would fall under embracing the RDBMS, leveraging its modern capabilities.
Version control. The SQL functions end up just being data in your DB. This feels like an anti-feature in the world of continuous integration and code review.
Functions can be treated the same way as migrations. With PostgreSQL you have
transactional DDL, so you can atomically deploy and rollback functions. There
plenty of different script packages to help manage this. At the end of the day
it’s deploy and rollback SQL scripts in text files, which are easily managed with
version control.
Lack of composability. An ORM gives you the ability to easily compose filters. This lets me write functions like "take this query, and make sure it's properly filtered down to one user's data". Because of how SQL works, each query needs to be hand-crafted, and it's hard to make parts of it generic.
The trade off here is that you’re trusting the ORM to produce an optimal query
(or queries) for whatever you’re trying to do compared with writing the SQL
yourself, with knowledge of your schema. Like any trade off, its one you need to
decide for yourself.
You may have filtering on the data that you’re returning from the database
that you chose not to do in the query itself. At this point, you should just
be dealing with data structures. I don’t think this would be different whether
you’re using an ORM or not.
Rewriting business logic. I have business logic in Python already, but if I want to query off of that in the DB in SQL, now I need two implementations. You can sort of get around this in an ORM by annotations.
I’m not sure what you’re getting at here, so I’m not sure if this addresses your
concerns: If you’ve got clean separation of your code, you should only need to
update a single location for any given piece of logic. Single source of truth, and all that.
I'm not sure what the developer tooling environment is like. PyCharm is pretttty nice.
SQL scripts are generally text files, so anything that supports some kind of SQL syntax
highlighting is likely all you need. I suspect PyCharm will work. The Jetbrains folks are
pretty sharp.
Why would I want to circumvent code review for things that are most undoubtably code?
Since you can have your scripts under version control, you can likey use the same code
review tools that you currently do. You can also use libraries such as pgtap[0] to test
your schema and functions indepedent of the rest of your application, if you so choose.
These are all issues I've dealt with in a large production PostgreSQL environment. They're good things to think about, and you're right to consider them. At the end, you need to make a decision you and your team is comfortable with.
- Version control. The SQL functions end up just being data in your DB. This feels like an anti-feature in the world of continuous integration and code review.
- Lack of composability. An ORM gives you the ability to easily compose filters. This lets me write functions like "take this query, and make sure it's properly filtered down to one user's data". Because of how SQL works, each query needs to be hand-crafted, and it's hard to make parts of it generic.
- Rewriting business logic. I have business logic in Python already, but if I want to query off of that in the DB in SQL, now I need two implementations. You can sort of get around this in an ORM by annotations.
- I'm not sure what the developer tooling environment is like. PyCharm is pretttty nice.
To be honest, you citing that it's easy to get around the validation process when using Postgres is a major red flag. Why would I want to circumvent code review for things that are most undoubtably code?