7 ms·

I know many are going to disagree but I have come to despise almost all SQL generators unless they are as close to 1-1 as possible (aka Lukas Eder's jOOQ is acc

by agentgt 10y ago

I know many are going to disagree but I have come to despise almost all SQL generators unless they are as close to 1-1 as possible (aka Lukas Eder's jOOQ is acceptable in my book). Even more controversial... I don't want to make it any easier for developers to interact with the database unless it is absolutely correct and transparent. I know.. I know... how could I say such I thing! But you see I want every interaction with the our data repository... our precious data repository... that is usually the bottleneck... I want you to know WTF you are doing when you touch it. We (my company and probably others) are past the days of monolithic rapid prototype apps with ORMs. Now you pick a database and you learn the hell out of it because you can stick to it. Back in the day one of the reasons ORMs were so popular because companies would have to ship on premise ware that would have to work with multiple databases. With the cloud that is no longer the case. However I still think a lot of .NET shops come from the on premise mentality. Thus performance is less of concern (speed of release is more desired).

NicoJuicy 10y ago
Here's a linq to Lukas Eder's JOOQ https://www.jooq.org/ https://www.jooq.org/ I do like the syntax also though.
alkonaut 10y ago
How many apps even deploy to the larger web and not just some intranet? It would surprise me if 99% of database backed applications weren't tiny-to-medium intranet and LOB type apps with very modest performance requirements. Hell, a pretty large number of Db apps are single user!
brightball 10y ago
I half agree with you and half totally disagree with you. The happy medium is a flexible ORM that automates the repetitive stuff but let's you call out to the higher level stuff...because if you don't have that your developers are going to end up building their own awful ORM-ish layer to automate the repetitive stuff anyway.
- agentgt 10y ago
  And I sort of half agree with you as well :) except I think the repetitive part is the mapping of the objects of which I think the automation should be of generating objects that you are going to map into. In Java there are host of libraries that do this. I have even used Hibernate to do the mapping at times as I do think it does a pretty good job of that. What I mean by mapping is minimal code generation (either host language or SQL) of Class <-> Entity and Row result <-> Object. I think jOOQ approaches that medium you talk about fairly well btw. JDBI and MyBatis are also fairly good as well (both mapping technologies).
umanwizard 10y ago
SQL is itself an abstraction over the "language" of query execution plans. Why do you regard SQL as particularly more fundamental than any other abstraction? I don't think it's conceptually right to regard LINQ as a "SQL generator". I think it's better to think of both LINQ and SQL as just different database control languages. (It might be the case that behind the scenes LINQ is implemented by generating SQL, but that's an implementation detail. Just like Haskell is implemented by generating C behind the scenes but it's more reasonable to think of Haskell as its own separate programming language than as a "C generator")
- 6nf 10y ago
  > SQL is itself an abstraction over the "language" of query execution plans. I don't agree with this at all. SQL does not abstract a specific execution plan. The execution plan is calculated by the RDBMS to efficiently compile the results required by a given SQL statement. The plan depends on available indexes, table statistics, input parameters and so on. The execution plan might change from time to time as new indexes are built or as table statistics change. When you write an SQL statement, you are defining what you want and how the tables are related in your context. You are NOT telling the RDBMS what the actual execution plan should look like.
  - umanwizard 10y ago
    How does this contradict my point? C is an abstraction over machine language, in the same way; but so is Prolog.
    - 6nf 10y ago
      C statements map to very specific ASM, you can look at a C statement and be pretty sure what the ASM will end up looking like. SQL does not map any specific execution plan. The execution plan will be very different depending on external factors such as the contents of the tables and so on. Have you looked at some actual execution plans? Related rhetorical question for you: What is the difference between a 3rd generation and a 4th generation programming language? (SQL is a 4th GL and C is a 3rd GL)
openasocket 10y ago
For me there are two primary reasons for having an ORM or ORM-like framework 1. Compile time checking. I hate when I'm writing a straight SQL query in a string literal and misspell one of the column names, because everything will compile and unit tests will pass, and the mistake isn't caught until I run integration tests. I want a short edit-compile-debug cycle. jOOQ is a good example of something that helps here. 2. Arbitrary filters. Often I have to expose a REST API where the user can add an arbitrary number of filters to their query. You can't write SQL literals for these because there's a combinatorial explosion of possibilities. So you wind up dynamically generating SQL yourself by concatenating strings in the WHERE clause, hoping and praying you don't accidentally allow SQL injection. I want an ORM to deal with that shit for me.
- siddboots 10y ago
  3. Modularity and abstraction. Views only get you so much, because they don't let abstract away the table or columns that you are targeting. Also, I typically don't want my views to clutter the main namespace if I'm just trying to refactor some queries. I can use the "WITH" syntax in some cases, but bizarrely it comes with an unavoidable optimiser fence on most RDBMSs (i.e. your subquery will always be materialised), which severely limits its utility.
- ReidZB 10y ago
  jOOQ helps with both 1 and 2. With jOOQ you can write something like (totally off-the-cuff code incoming, written outside of an IDE with only basic syntax highlighting, be warned, etc) List<Condition> conditions = new ArrayList<>(); if (notNull(lessThan)) { conditions.add(MY_TABLE.MY_COLUMN.lt(lessThan)); } if (notNull(greaterThan)) { conditions.add(MY_TABLE.MY_COLUMN.gt(greaterThan)); } if (notEmpty(name)) { conditions.add(MY_TABLE.NAME.eq(name)); } dslContext.selectFrom(MY_TABLE).where(conditions).fetch(); (This is obviously really contrived but I hope it illustrates the ideas) We use constructions like this with jOOQ relatively frequently, especially for cases where we offer all sorts of arbitrary filter knobs (useful for internal tools especially). In general, you can do some pretty fancy manipulation with `Condition` in jOOQ, like you can build a list of conditions and then call `DSL.or(conditionList)` to OR them all together. Stuff like that. Source: use jOOQ in production code -- been very pleased so far, other than sometimes poor documentation
stupidcar 10y ago
I think the problem is that too many developers think of ORMs as a black box that lets them avoid SQL and the DB, rather than a powerful tool for working with them. Vlad Mihalcea, a Hibernate developer/evangelist, has been doing amazing work improving the project's documentation and providing educational material about how to use it properly without sacrificing performance[1]. I can't recommend his book High-Performance Java Persistence enough: https://leanpub.com/high-performance-java-persistence https://leanpub.com/high-performance-java-persistence For me, his key lesson is that ORMs can be incredibly useful if, and only if, your understanding of SQL and DB performance is good enough that you can use them in a DB-friendly way. It's really simple things like expressing relationships between your domain entities such that the resulting tables can be easily joined. You still get the productivity that comes from skipping loads of JDBC boilerplate, but you don't feel like there's a load of magic between you and the DB. He also pushes the idea that you should use Hibernate in combination with other DB tools: Liquibase/Flyway for migrations, and JOOQ for optimised queries with lots of joins,or DB-specific work. By having a decent ORM, SQL builder, and migration tool, you can use be flexible depending on the situation. Complex, performance-insensitive business logic can use the domain model from the ORM, whereas API-backing queries can be expressed a single, tightly constrained JOOQ call, and the actual creation and maintenance of the schema can be done via pure SQL scripts that are managed by your migration tool. [1] https://vladmihalcea.com/ https://vladmihalcea.com/
BinaryIdiot 10y ago
I agree with you. I haven't found an ORM that doesn't end up making a mess of my data structure eventually. Data is becoming one of the biggest, most important currencies of our times. People wanting to automate what their data looks like baffles me. Personally I like creating an API of stored procedures. Make users that can _only_ execute those sprocs with zero dynamic SQL in them. Users can only do what you tell them they can do via your sproc API.
- ako 10y ago
  20 year old oracle dba best practices...
FLUX-YOU 10y ago
> that is usually the bottleneck... I want you to know WTF you are doing when you touch it. Why don't you hire a DBA and have all major queries go through them if it's that important instead of asking developers to master yet another thing because you got burned in the past? I'm all for learning database stuff, but you and everyone else are pulling developers in all different directions by simultaneously claiming "YOU NEED TO KNOW THIS SHIT" about <THING THAT HURT YOU PREVIOUSLY>.
- gfody 10y ago
  "you need to know this shit" is something I say often to developers displaying complete ignorance of SQL. I think if you're an embedded systems programmer or an games developer focusing on the rendering pipeline or something then sure be blissfully ignorant of SQL. If you are a web application developer, where 99.999% of web applications use a backing RDBMS and a huge part of the heavy lifting and most likely bottlenecks and nearly all performance tuning is in the RDBMS - then you need to know this shit.
  - FLUX-YOU 10y ago
    Performance doesn't necessarily kill a product or service. And since they were too busy figuring out all of the odds and end of X database, the UX went to shit because they didn't learn about some quirk of Chrome, Firefox, Safari or something on mobile and suddenly a deployment breaks stuff for one of the browsers/platforms. If you can't afford a DBA when DB performance is critical to your application, you probably don't have good QA for all of those platforms either. But suddenly your developer is supposed to stretch over all three areas? When people talk about hilariously long laundry lists of technical requirements, it's an aggregation of demands like these over time that has inflated those requirements.
    - gfody 10y ago
      in my experience 99/100 major debilitating webapp performance issues are from SQL-oblivious developers making basic variations on the "n+1" problem, executing a thousand queries instead of one, failing to think in sets, treating the database like a dumb key-value store and paying extreme taxes on roundtrips and connection overheard. Bring a DBA into this situation, he might look at your queries, look at your indexes, and say "everything is fine". It's the developers who need to know how to use a database.
lowken10 10y ago
I love that mindset. By your logic we should all be coding in assembly.
lowken10 10y ago
I love that mindset. By your logic we should all be coding in assembly.
lowken10 10y ago
I love that mindset. By your logic we should all be coding in assembly.
lukaseder 10y ago
Lukas Eder here. Absolutely in favour of 1-1 SQL. In some cases jOOQ is great help, writing SQL queries with it. In other cases jOOQ is also great help accessing your stored procedures where the SQL queries are.

by agentgt 10y ago

NicoJuicy 10y ago

Here's a linq to Lukas Eder's JOOQ https://www.jooq.org/ https://www.jooq.org/ I do like the syntax also though.

alkonaut 10y ago

How many apps even deploy to the larger web and not just some intranet? It would surprise me if 99% of database backed applications weren't tiny-to-medium intranet and LOB type apps with very modest performance requirements. Hell, a pretty large number of Db apps are single user!

brightball 10y ago

I half agree with you and half totally disagree with you. The happy medium is a flexible ORM that automates the repetitive stuff but let's you call out to the higher level stuff...because if you don't have that your developers are going to end up building their own awful ORM-ish layer to automate the repetitive stuff anyway.

agentgt 10y ago
And I sort of half agree with you as well :) except I think the repetitive part is the mapping of the objects of which I think the automation should be of generating objects that you are going to map into. In Java there are host of libraries that do this. I have even used Hibernate to do the mapping at times as I do think it does a pretty good job of that. What I mean by mapping is minimal code generation (either host language or SQL) of Class <-> Entity and Row result <-> Object. I think jOOQ approaches that medium you talk about fairly well btw. JDBI and MyBatis are also fairly good as well (both mapping technologies).

umanwizard 10y ago

SQL is itself an abstraction over the "language" of query execution plans. Why do you regard SQL as particularly more fundamental than any other abstraction? I don't think it's conceptually right to regard LINQ as a "SQL generator". I think it's better to think of both LINQ and SQL as just different database control languages. (It might be the case that behind the scenes LINQ is implemented by generating SQL, but that's an implementation detail. Just like Haskell is implemented by generating C behind the scenes but it's more reasonable to think of Haskell as its own separate programming language than as a "C generator")

6nf 10y ago
> SQL is itself an abstraction over the "language" of query execution plans. I don't agree with this at all. SQL does not abstract a specific execution plan. The execution plan is calculated by the RDBMS to efficiently compile the results required by a given SQL statement. The plan depends on available indexes, table statistics, input parameters and so on. The execution plan might change from time to time as new indexes are built or as table statistics change. When you write an SQL statement, you are defining what you want and how the tables are related in your context. You are NOT telling the RDBMS what the actual execution plan should look like.
- umanwizard 10y ago
  How does this contradict my point? C is an abstraction over machine language, in the same way; but so is Prolog.
  - 6nf 10y ago
    C statements map to very specific ASM, you can look at a C statement and be pretty sure what the ASM will end up looking like. SQL does not map any specific execution plan. The execution plan will be very different depending on external factors such as the contents of the tables and so on. Have you looked at some actual execution plans? Related rhetorical question for you: What is the difference between a 3rd generation and a 4th generation programming language? (SQL is a 4th GL and C is a 3rd GL)

openasocket 10y ago

For me there are two primary reasons for having an ORM or ORM-like framework 1. Compile time checking. I hate when I'm writing a straight SQL query in a string literal and misspell one of the column names, because everything will compile and unit tests will pass, and the mistake isn't caught until I run integration tests. I want a short edit-compile-debug cycle. jOOQ is a good example of something that helps here. 2. Arbitrary filters. Often I have to expose a REST API where the user can add an arbitrary number of filters to their query. You can't write SQL literals for these because there's a combinatorial explosion of possibilities. So you wind up dynamically generating SQL yourself by concatenating strings in the WHERE clause, hoping and praying you don't accidentally allow SQL injection. I want an ORM to deal with that shit for me.

siddboots 10y ago
3. Modularity and abstraction. Views only get you so much, because they don't let abstract away the table or columns that you are targeting. Also, I typically don't want my views to clutter the main namespace if I'm just trying to refactor some queries. I can use the "WITH" syntax in some cases, but bizarrely it comes with an unavoidable optimiser fence on most RDBMSs (i.e. your subquery will always be materialised), which severely limits its utility.
ReidZB 10y ago
jOOQ helps with both 1 and 2. With jOOQ you can write something like (totally off-the-cuff code incoming, written outside of an IDE with only basic syntax highlighting, be warned, etc) List<Condition> conditions = new ArrayList<>(); if (notNull(lessThan)) { conditions.add(MY_TABLE.MY_COLUMN.lt(lessThan)); } if (notNull(greaterThan)) { conditions.add(MY_TABLE.MY_COLUMN.gt(greaterThan)); } if (notEmpty(name)) { conditions.add(MY_TABLE.NAME.eq(name)); } dslContext.selectFrom(MY_TABLE).where(conditions).fetch(); (This is obviously really contrived but I hope it illustrates the ideas) We use constructions like this with jOOQ relatively frequently, especially for cases where we offer all sorts of arbitrary filter knobs (useful for internal tools especially). In general, you can do some pretty fancy manipulation with `Condition` in jOOQ, like you can build a list of conditions and then call `DSL.or(conditionList)` to OR them all together. Stuff like that. Source: use jOOQ in production code -- been very pleased so far, other than sometimes poor documentation

stupidcar 10y ago

I think the problem is that too many developers think of ORMs as a black box that lets them avoid SQL and the DB, rather than a powerful tool for working with them. Vlad Mihalcea, a Hibernate developer/evangelist, has been doing amazing work improving the project's documentation and providing educational material about how to use it properly without sacrificing performance[1]. I can't recommend his book High-Performance Java Persistence enough: https://leanpub.com/high-performance-java-persistence https://leanpub.com/high-performance-java-persistence For me, his key lesson is that ORMs can be incredibly useful if, and only if, your understanding of SQL and DB performance is good enough that you can use them in a DB-friendly way. It's really simple things like expressing relationships between your domain entities such that the resulting tables can be easily joined. You still get the productivity that comes from skipping loads of JDBC boilerplate, but you don't feel like there's a load of magic between you and the DB. He also pushes the idea that you should use Hibernate in combination with other DB tools: Liquibase/Flyway for migrations, and JOOQ for optimised queries with lots of joins,or DB-specific work. By having a decent ORM, SQL builder, and migration tool, you can use be flexible depending on the situation. Complex, performance-insensitive business logic can use the domain model from the ORM, whereas API-backing queries can be expressed a single, tightly constrained JOOQ call, and the actual creation and maintenance of the schema can be done via pure SQL scripts that are managed by your migration tool. [1] https://vladmihalcea.com/ https://vladmihalcea.com/

BinaryIdiot 10y ago

I agree with you. I haven't found an ORM that doesn't end up making a mess of my data structure eventually. Data is becoming one of the biggest, most important currencies of our times. People wanting to automate what their data looks like baffles me. Personally I like creating an API of stored procedures. Make users that can _only_ execute those sprocs with zero dynamic SQL in them. Users can only do what you tell them they can do via your sproc API.

ako 10y ago
20 year old oracle dba best practices...

FLUX-YOU 10y ago

> that is usually the bottleneck... I want you to know WTF you are doing when you touch it. Why don't you hire a DBA and have all major queries go through them if it's that important instead of asking developers to master yet another thing because you got burned in the past? I'm all for learning database stuff, but you and everyone else are pulling developers in all different directions by simultaneously claiming "YOU NEED TO KNOW THIS SHIT" about <THING THAT HURT YOU PREVIOUSLY>.

gfody 10y ago
"you need to know this shit" is something I say often to developers displaying complete ignorance of SQL. I think if you're an embedded systems programmer or an games developer focusing on the rendering pipeline or something then sure be blissfully ignorant of SQL. If you are a web application developer, where 99.999% of web applications use a backing RDBMS and a huge part of the heavy lifting and most likely bottlenecks and nearly all performance tuning is in the RDBMS - then you need to know this shit.
- FLUX-YOU 10y ago
  Performance doesn't necessarily kill a product or service. And since they were too busy figuring out all of the odds and end of X database, the UX went to shit because they didn't learn about some quirk of Chrome, Firefox, Safari or something on mobile and suddenly a deployment breaks stuff for one of the browsers/platforms. If you can't afford a DBA when DB performance is critical to your application, you probably don't have good QA for all of those platforms either. But suddenly your developer is supposed to stretch over all three areas? When people talk about hilariously long laundry lists of technical requirements, it's an aggregation of demands like these over time that has inflated those requirements.
  - gfody 10y ago
    in my experience 99/100 major debilitating webapp performance issues are from SQL-oblivious developers making basic variations on the "n+1" problem, executing a thousand queries instead of one, failing to think in sets, treating the database like a dumb key-value store and paying extreme taxes on roundtrips and connection overheard. Bring a DBA into this situation, he might look at your queries, look at your indexes, and say "everything is fine". It's the developers who need to know how to use a database.

lowken10 10y ago

I love that mindset. By your logic we should all be coding in assembly.

lowken10 10y ago

I love that mindset. By your logic we should all be coding in assembly.

lowken10 10y ago

I love that mindset. By your logic we should all be coding in assembly.

lukaseder 10y ago

Lukas Eder here. Absolutely in favour of 1-1 SQL. In some cases jOOQ is great help, writing SQL queries with it. In other cases jOOQ is also great help accessing your stored procedures where the SQL queries are.