4 ms·

I used to love ORM, I used it everywhere. Writing another language in the language I am coding is wrong. ORM simplifies my programs. No, ORM does not simplif

I used to love ORM, I used it everywhere. Writing another language in the language I am coding is wrong. ORM simplifies my programs. No, ORM does not simplify coding! It's a big complex adapter which does not fit many cases. RDBM itself is complex enough, let's put another complex abstraction above it so we can forget about the tables and columns and joins and foreign keys. Complexity added upon another complexity does not make a polished interface. If you want to use ORM, you have to learn both SQL and ORM with some depth at least. When problems occur, you have to debug both. 20 years in, I am still learning about RDBM. I still hate to compose SQL in code, but there is no better way. Databases are used in every situation. Want to store data? Database. Want to communicate between processes? Database. Want to store logs? Database. There are different solutions for different scenarios, but as long as the DB chugs along, why? Sometimes it's sickening. So 20 years in, the ROI of learning SQL is great. Please don't avoid learning about RDBM by using an ORM. Just learn it. Actually, I'm not without ORM now a days. Light-weight ORM like Dapper serves well, it does not abstract out the concepts of RDBM, but make things easier.

BulgarianIdiot 5y ago
> Writing another language in the language I am coding is wrong That's not what's wrong. But rather writing in some language within a string literal is what feels wrong to you. With better integration it'd be natural. The problem is, of course, there's no single SQL dialect.
takeda 5y ago
If you use IntelliJ IDE in their commercial version (at least of PyCharm) they integrated DataGrip. Basically if you connect to your database and give an option to fetch your schema the IDE starts scanning for strings and if it detects SQL it provides IDE features to it as well (like auto completion, and some refactoring etc). I think ORM and query builders were trying to hack around to make IDEs understand SQL, when in reality this approach is what actually was needed.
- BiteCode_dev 5y ago
  No, ORM provides an inspectable central entry point for your models with a standardized API. That's the feature. That's why you get a great django ecosystem: the ORM abstractions allow all libs to rely on the fact the rest of the code access the model the same way.
  - takeda 5y ago
    The issue is that if you try to squeeze a relational model into object oriented model you won't get an efficient solution. Solutions like django might be good when you're starting the project or it is something very simple, otherwise you'll have to fight with it to get something done more efficiently. My point is that using plain SQL (I personally prefer asyncpg as it provides interface matching postgresql) is actually also easy. Especially if you have IDE that supports it. I also realized that with this approach I rarely need to even transform the data in any way. Usually whatever I want to do I can get in a single SQL statement (even for things that have some hierarchy, thanks to aggregation functionality). So in the end the function just gets data and displays it.
    - BiteCode_dev 5y ago
      Sure if you don't need to build an ecosystem, don't need introspection and can forgo integration, sql is indeed easy. Also, SQLA proves that an ORM doesn't have to prevent you from getting an efficient solution, you just have to offer several layers of granularity. Eventually, it's not an or proposition. In django you do use raw sql when you need so. but your auth system doesn't, and a plugin will solve it for you.
Animats 5y ago
" I still hate to compose SQL in code, but there is no better way." The one big problem with SQL is intermixing query language and query parameters. This requires escaping and is the source of many PHP vulnerabilities. Instead of do_sql('SELECT * FROM tab WHERE name = "abc"') there should have been a standard where you wrote do_sql('SELECT * FROM tab where name = V1", "abc") or something like that. So you don't do string operations on the query parameters.
- setr 5y ago
  Aren’t you referring to parameterized/prepared queries? Any proper DB driver/library should have this feature. do_sql('SELECT * FROM tab where name = ?", "abc") https://www.php.net/manual/en/pdo.prepared-statements.php https://www.php.net/manual/en/pdo.prepared-statements.php I recall getting curious when I was younger where the deranged advice of “sanitize your queries” came from — realizing that you should be able to simply tell the database that this is a string, not part of the query itself. SQL injection should barely exist as a concept, let alone be the #1 web vulnerability. IIRC it turned out MySQL supported parameterized queries for ages, but the stdlib php MySQL library just didn’t add support for it. This discovery solidified my understanding that PHP has been giving developers brain damage for decades.
- throwaddzuzxd 5y ago
  I don't know if it's a standard but both Postgres and MySQL have PREPARE statements, and any sane language driver will implement it correctly
- loopz 5y ago
  Unless you're using hardcoded values, using bind variables is the only proper way. Otherwise, the DBMS has difficulty recognizing the same query pattern for performance, and you're open to injection vulnerabilities for security. https://www.databasestar.com/sql-bind-variables/ https://www.databasestar.com/sql-bind-variables/ The big problem is the shitty workplace environment of today that discourages sharing, learning and proper software development. A proper build-pipeline will include security scanners that detects these vulnerabilities automagically.
smitty1e 5y ago
One nifty use-case for ORMs is decoupling the logic from the data. I can prototype in Python/SQLAlchemy/SQLite and deploy to Python/SQLalchemy/PostGreSQL via a pipeline with confidence that I'm actually managing the complexity. Cracking open some legacy code with vast swaths of embedded SQL is a source of much weeping and gnashing of teeth when the time comes for maintenance.
- majewsky 5y ago
  I did this for the longest time, testing with SQLite and running with PostgreSQL, until I discovered that this was completely unnecessary extra complexity. It turns out that setting up and starting a fresh PostgreSQL database takes about one second, so it's just as easy to just spawn a Postgres for the unit tests. And now I get to enjoy some immensely useful Postgres-specific SQL syntax like "FOR UPDATE SKIP LOCKED".
reportgunner 5y ago
> Writing another language in the language I am coding is wrong. Yeah but you're writing your strings in english anyway.

BulgarianIdiot 5y ago

> Writing another language in the language I am coding is wrong That's not what's wrong. But rather writing in some language within a string literal is what feels wrong to you. With better integration it'd be natural. The problem is, of course, there's no single SQL dialect.

takeda 5y ago

If you use IntelliJ IDE in their commercial version (at least of PyCharm) they integrated DataGrip. Basically if you connect to your database and give an option to fetch your schema the IDE starts scanning for strings and if it detects SQL it provides IDE features to it as well (like auto completion, and some refactoring etc). I think ORM and query builders were trying to hack around to make IDEs understand SQL, when in reality this approach is what actually was needed.

BiteCode_dev 5y ago
No, ORM provides an inspectable central entry point for your models with a standardized API. That's the feature. That's why you get a great django ecosystem: the ORM abstractions allow all libs to rely on the fact the rest of the code access the model the same way.
- takeda 5y ago
  The issue is that if you try to squeeze a relational model into object oriented model you won't get an efficient solution. Solutions like django might be good when you're starting the project or it is something very simple, otherwise you'll have to fight with it to get something done more efficiently. My point is that using plain SQL (I personally prefer asyncpg as it provides interface matching postgresql) is actually also easy. Especially if you have IDE that supports it. I also realized that with this approach I rarely need to even transform the data in any way. Usually whatever I want to do I can get in a single SQL statement (even for things that have some hierarchy, thanks to aggregation functionality). So in the end the function just gets data and displays it.
  - BiteCode_dev 5y ago
    Sure if you don't need to build an ecosystem, don't need introspection and can forgo integration, sql is indeed easy. Also, SQLA proves that an ORM doesn't have to prevent you from getting an efficient solution, you just have to offer several layers of granularity. Eventually, it's not an or proposition. In django you do use raw sql when you need so. but your auth system doesn't, and a plugin will solve it for you.

Animats 5y ago

" I still hate to compose SQL in code, but there is no better way." The one big problem with SQL is intermixing query language and query parameters. This requires escaping and is the source of many PHP vulnerabilities. Instead of do_sql('SELECT * FROM tab WHERE name = "abc"') there should have been a standard where you wrote do_sql('SELECT * FROM tab where name = V1", "abc") or something like that. So you don't do string operations on the query parameters.

setr 5y ago
Aren’t you referring to parameterized/prepared queries? Any proper DB driver/library should have this feature. do_sql('SELECT * FROM tab where name = ?", "abc") https://www.php.net/manual/en/pdo.prepared-statements.php https://www.php.net/manual/en/pdo.prepared-statements.php I recall getting curious when I was younger where the deranged advice of “sanitize your queries” came from — realizing that you should be able to simply tell the database that this is a string, not part of the query itself. SQL injection should barely exist as a concept, let alone be the #1 web vulnerability. IIRC it turned out MySQL supported parameterized queries for ages, but the stdlib php MySQL library just didn’t add support for it. This discovery solidified my understanding that PHP has been giving developers brain damage for decades.
throwaddzuzxd 5y ago
I don't know if it's a standard but both Postgres and MySQL have PREPARE statements, and any sane language driver will implement it correctly
loopz 5y ago
Unless you're using hardcoded values, using bind variables is the only proper way. Otherwise, the DBMS has difficulty recognizing the same query pattern for performance, and you're open to injection vulnerabilities for security. https://www.databasestar.com/sql-bind-variables/ https://www.databasestar.com/sql-bind-variables/ The big problem is the shitty workplace environment of today that discourages sharing, learning and proper software development. A proper build-pipeline will include security scanners that detects these vulnerabilities automagically.

smitty1e 5y ago

One nifty use-case for ORMs is decoupling the logic from the data. I can prototype in Python/SQLAlchemy/SQLite and deploy to Python/SQLalchemy/PostGreSQL via a pipeline with confidence that I'm actually managing the complexity. Cracking open some legacy code with vast swaths of embedded SQL is a source of much weeping and gnashing of teeth when the time comes for maintenance.

majewsky 5y ago
I did this for the longest time, testing with SQLite and running with PostgreSQL, until I discovered that this was completely unnecessary extra complexity. It turns out that setting up and starting a fresh PostgreSQL database takes about one second, so it's just as easy to just spawn a Postgres for the unit tests. And now I get to enjoy some immensely useful Postgres-specific SQL syntax like "FOR UPDATE SKIP LOCKED".

reportgunner 5y ago

> Writing another language in the language I am coding is wrong. Yeah but you're writing your strings in english anyway.