4 ms·

Here's the root of my problem: ORMs always force their world view onto the database, which is completely backwards from how things should work. The database is

Here's the root of my problem: ORMs always force their world view onto the database, which is completely backwards from how things should work. The database is the most important part of the application, it will probably outlive your current code base. Yet your data model ends up getting warped to support a single ORMs worldview. New table columns should never cause problems for existing code because you should never use `SELECT *` and you should never use positional semantics in your SQL, put a linter on it. As for typos, that's what SQL builder libraries are for, they allow you to work with your own language constructs instead of mashing together strings manually. ORMs are this thing that developers seem to think is a life preserver but as time goes on morphs into an anchor.

fiedzia 6y ago
> ORMs always force their world view onto the database, which is completely backwards from how things should work. I have different experience. There are situations where you work with database given of course, but in many projects ORM enforces good and unified practices (rows have unique id, constraints and indexes are properly named, relationships are done in always the same way), and generally database is just a servant to developer idea of data model, not a god to worship and bend to.
leetrout 6y ago
> you should never use positional semantics in your SQL If I'm understanding you, that along with your mention of a SQL builder means, for example, in Python, you'd expect CreateUser(name="blah") As opposed to something like db.Exec("INSERT INTO users (name) values (?)", "blah") Is that correct? I think we're agreeing on reducing the amount of raw SQL being tossed around within the code.
- MereInterest 6y ago
  SQL columns can be referred to either by name or by index. Avoiding positional semantics means using "ORDER BY transaction_date ASC" rather than "ORDER BY 3 ASC". On the calling code, your python library may return either a tuple or a dictionary. The dictionary is the right way to do it, because then you access parameters by name, and reordering or adding parameters to the query output doesn't break your script.
- blacktriangle 6y ago
  Less about the exact syntax and more about the tool, for example: https://github.com/sqlkata/querybuilder https://github.com/sqlkata/querybuilder. I just chose that since it was on top of a search but the idea is the same. Your code generates raw SQL, so it's 100% interchangeable with writing SQL yourself however the builder library deals with the syntax, proper ordering, quoting, full attribute names, etc. Some such libraries even let you define your schema in code to make your SQL generation type safe.

fiedzia 6y ago

> ORMs always force their world view onto the database, which is completely backwards from how things should work. I have different experience. There are situations where you work with database given of course, but in many projects ORM enforces good and unified practices (rows have unique id, constraints and indexes are properly named, relationships are done in always the same way), and generally database is just a servant to developer idea of data model, not a god to worship and bend to.

leetrout 6y ago

> you should never use positional semantics in your SQL If I'm understanding you, that along with your mention of a SQL builder means, for example, in Python, you'd expect CreateUser(name="blah") As opposed to something like db.Exec("INSERT INTO users (name) values (?)", "blah") Is that correct? I think we're agreeing on reducing the amount of raw SQL being tossed around within the code.

MereInterest 6y ago
SQL columns can be referred to either by name or by index. Avoiding positional semantics means using "ORDER BY transaction_date ASC" rather than "ORDER BY 3 ASC". On the calling code, your python library may return either a tuple or a dictionary. The dictionary is the right way to do it, because then you access parameters by name, and reordering or adding parameters to the query output doesn't break your script.
blacktriangle 6y ago
Less about the exact syntax and more about the tool, for example: https://github.com/sqlkata/querybuilder https://github.com/sqlkata/querybuilder. I just chose that since it was on top of a search but the idea is the same. Your code generates raw SQL, so it's 100% interchangeable with writing SQL yourself however the builder library deals with the syntax, proper ordering, quoting, full attribute names, etc. Some such libraries even let you define your schema in code to make your SQL generation type safe.