Ironically, one of the universal things I’ve noticed in programmers (myself included) is that newbie coders always go through a phase of thinking “why am I writing SQL? I’ll write a set of classes to write the SQL for me!” resulting in a massively overcomplicated mess that is a hundred times harder to use (and maintain) than a simple SQL statement would be. The most hilarious example of this I ever saw was when I took over a young colleague’s code base and found two classes named “OR.cs” and “AND.cs”. All they did was take a String as a parameter, append " OR " or " AND " to it, and return it as the output. Very forward-thinking, in case the meanings of “OR” and “AND” were ever to change in future versions of SQL.
I did that myself back in the day. Not overly complicated, but a SQL builder.
I think it’s because SQL is sort-of awkward. For basic uses you can take a SQL query string and substitute some parameters in that string. But, that one query isn’t going to cover all your use cases. So, then you have at least 2 queries which are fairly similar but not similar enough that it makes sense just to do string substitutions. Two strings that are fairly similar but distinct suggests that you should refactor it. But, maybe you only make a very simple query builder. Then you have 5 queries and your query builder doesn’t quite cover the latest version, so you refactor it again.
But, instead of creating a whole query builder, it’s often better to have a lot of SQL repetition in the codebase. It looks ugly, but it’s probably much more maintainable.
Object Relational Mapping can be helpful when dealing with larger codebases/complex databases for simply creating a more programmatic way of interacting with your data.
I can’t say it is always worth it, nor does it always make things simpler, but it can help.
My standard for an orm is that if it’s doing something wrong or I need to do something special that it’s trivial to move it aside and either use plain SQL or it’s SQL generator myself.
In production code, plain SQL strings are a concern for me since they’re subject to the whole array of human errors and vulnerabilities.
Something like stmt = select(users).where(users.c.name == 'somename') is basically as flexible as the string, but it’s not going to forget a quote or neglect to use SQL escaping or parametrize the query.
And sometimes you just need it to get out of the way because your query is reaaaaaal weird, although at that point a view you wrap with the orm might be better.
If you’ve done things right though, most of the time you’ll be doing simple primary key lookups and joins with a few filters at most.
I used to use ORMs because they made switching between local dev DBs ( like SQLLite, or Postgres) and production DBs usually painless. Especially for Ruby/Sinatra/Rails since we were writing the model queries in another abstraction. It meant we didn’t have to think as much about joins and all that stuff. Until the performance went to shit and you had to work out why.
I don’t have a lot of experience with projects that use ORMs, but from what I’ve seen it’s usually not worth it. They tend to make developers lazy and create things where every query fetches half the database when they only need one or two columns from a single row.
Yeah. Unless your data model is dead simple, you will end up not only needing to know this additional framework, but also how databases and SQL work to unfuck the inevitable problems.
the problem with ORM is that some people go all in on it and ignore pure SQL completely.
In reality ORM only works well for somewhat simple queries and structures, but at some times you will have to write your own queries in SQL. But then you have some bonus complexity, that comes from 2 different things filling the same niche. It’s still worth it, but there is no free cake.
I’ve always seen as that as a scapehatch for one of the most typical issues with ORMs, like the the N+1 problem, but I never fully bought it as a real solution.
Mainly because in large projects this gets abused (turns out none or little of the SQL has a companion test) and one of the most oversold benefits of ORMs (the possibility of “easily” refactor the model) goes away.
Since SQL is code and should be tested like any other code, I rather ditch the whole ORM thing and go SQL from the beginning. It may be annoying for simple queries but induces better habits.
You can add SQL in the 70s. It was created to be human readable so business people could write sql queries themselves without programmers.
So is COBOL.
(Is there any sane alternative to SQL?)
AWK?
Well there is our lord and savior, or so I’m told by many MBA(s), No SQL
Oh wait you said Sane. Nevermind.
Yes, no SQL.
I lol’d
Ironically, one of the universal things I’ve noticed in programmers (myself included) is that newbie coders always go through a phase of thinking “why am I writing SQL? I’ll write a set of classes to write the SQL for me!” resulting in a massively overcomplicated mess that is a hundred times harder to use (and maintain) than a simple SQL statement would be. The most hilarious example of this I ever saw was when I took over a young colleague’s code base and found two classes named “OR.cs” and “AND.cs”. All they did was take a String as a parameter, append " OR " or " AND " to it, and return it as the output. Very forward-thinking, in case the meanings of “OR” and “AND” were ever to change in future versions of SQL.
I did that myself back in the day. Not overly complicated, but a SQL builder.
I think it’s because SQL is sort-of awkward. For basic uses you can take a SQL query string and substitute some parameters in that string. But, that one query isn’t going to cover all your use cases. So, then you have at least 2 queries which are fairly similar but not similar enough that it makes sense just to do string substitutions. Two strings that are fairly similar but distinct suggests that you should refactor it. But, maybe you only make a very simple query builder. Then you have 5 queries and your query builder doesn’t quite cover the latest version, so you refactor it again.
But, instead of creating a whole query builder, it’s often better to have a lot of SQL repetition in the codebase. It looks ugly, but it’s probably much more maintainable.
Object Relational Mapping can be helpful when dealing with larger codebases/complex databases for simply creating a more programmatic way of interacting with your data.
I can’t say it is always worth it, nor does it always make things simpler, but it can help.
My standard for an orm is that if it’s doing something wrong or I need to do something special that it’s trivial to move it aside and either use plain SQL or it’s SQL generator myself.
In production code, plain SQL strings are a concern for me since they’re subject to the whole array of human errors and vulnerabilities.
Something like
stmt = select(users).where(users.c.name == 'somename')is basically as flexible as the string, but it’s not going to forget a quote or neglect to use SQL escaping or parametrize the query.And sometimes you just need it to get out of the way because your query is reaaaaaal weird, although at that point a view you wrap with the orm might be better.
If you’ve done things right though, most of the time you’ll be doing simple primary key lookups and joins with a few filters at most.
I used to use ORMs because they made switching between local dev DBs ( like SQLLite, or Postgres) and production DBs usually painless. Especially for Ruby/Sinatra/Rails since we were writing the model queries in another abstraction. It meant we didn’t have to think as much about joins and all that stuff. Until the performance went to shit and you had to work out why.
I don’t have a lot of experience with projects that use ORMs, but from what I’ve seen it’s usually not worth it. They tend to make developers lazy and create things where every query fetches half the database when they only need one or two columns from a single row.
Yeah. Unless your data model is dead simple, you will end up not only needing to know this additional framework, but also how databases and SQL work to unfuck the inevitable problems.
deleted by creator
the problem with ORM is that some people go all in on it and ignore pure SQL completely.
In reality ORM only works well for somewhat simple queries and structures, but at some times you will have to write your own queries in SQL. But then you have some bonus complexity, that comes from 2 different things filling the same niche. It’s still worth it, but there is no free cake.
I’ve always seen as that as a scapehatch for one of the most typical issues with ORMs, like the the N+1 problem, but I never fully bought it as a real solution.
Mainly because in large projects this gets abused (turns out none or little of the SQL has a companion test) and one of the most oversold benefits of ORMs (the possibility of “easily” refactor the model) goes away.
Since SQL is code and should be tested like any other code, I rather ditch the whole ORM thing and go SQL from the beginning. It may be annoying for simple queries but induces better habits.
What part of this is irony?