Hacker News Reader

Comment by agentgt | Hacker News Reader

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).

View on HN

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.

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.

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

I much prefer:

    var query = from c in db.MyTable
                where lessThan    == null ? true : c.MyColumn < lessThan    
                where greaterThan == null ? true : c.MyColumn > greaterThan 
                where name        == null ? true : c.Name == name        
                select c;

Although IQueryable supports your approach also:

    var query = db.MyTable;

    if (lessThan != null) 
        query = query.Where(c => c.MyColumn < lessThan);

    if (greaterThan != null) 
        query = query.Where(c => c.MyColumn > greaterThan);

    if (!String.IsNullOrEmpty(name)) 
        query = query.Where(c => c.Name == name);

    var results = query.ToList();

> other than sometimes poor documentation

Thanks for your feedback. We're currently collecting API that is not documented well enough yet. Your feedback would be very welcome: https://github.com/jOOQ/jOOQ/issues/5816

Lukas

there is at least one way to write plain sql and get compile time checking, with f#

> 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>.

"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.

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.

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.

But how do you draw the line on required knowledge? At some point you need to say "This should be DBA territory", and that's the soft wall where responsibility gets thrown over to the other side. Do I spend time poking around at a problem I've got, or do I hand it off?

The thing is that soft wall is wayyyy too vague for anyone to prepare themselves before actually joining a team. I may not want to join your team if you require everyone to be a database expert because I don't like databases. But because the communication around this is so damned vague, there's no way to know before joining.

If you somehow have a SQL-oblivious developer, he should be identified early on because it's not compatible with the level of competence you've established for your business.

Why is this guy suddenly writing performance-critical queries for your webapp and not working with his strengths?

Does he have any strengths? No? Why was he hired? Shouldn't someone be reviewing this guy's code?

The SQL-oblivious developer is a perfectly natural occurrence from a time past when front and back end job postings were not wrapped into a full stack developer position for less pay than those positions combined. It's just that it's so easy to pile on requirements that it just outpaces some people.

Can we identify those people reliably? Yes? Congrats, you've probably got a way to actually test developer competence without invoking another 300-post threadnought on HN and you will likely be very, very rich.

But see, you can't go off studying every single thing mentioned in the context of "Things important for developers to know" even if you wanted to try and keep up. There have been tons and tons of blog posts and articles and angry posts about terrible situations created due to a lack of knowledge and everyone just says "Hey, you should know this, get to it". Even if I wanted to filter through all of those posts, how do I know who to listen to?

If you want to specialize in javascript and html that's fine. The middle/backend/API then ought to be developed by someone knowledgeable in SQL - but it's still a developer, not a DBA. DBAs are sysadmins not programmers. If you want to specialize in middle/backend development and think you should be able to defer all your SQL problems to a DBA then no your dead wrong. You simply cannot be a good backend developer for web applications utilizing RDBMSs and not know this shit.

If you do sane things with your DB then you really don't need to mess with all the edge case crap that breaks ORM's.

Great DBA's are rare, but vastly more important than most people think.

I constantly pick up the metaphorical phone to call the DBA then remember that I am the DBA...

I've found that specialization is an emergent property of teams and organizations. Each developer naturally has propensity for and interest in some areas over others. Imagine a completely flat company with twenty developers, where everyone is supposed to be a full-stack, you-need-to-know-everything-about-everything developer. Well, in even a short amount of time, you're naturally going to end up in a situation where some developers specialize in certain sets of frameworks and tools, and become the go-to experts in that.

So something like a DBA, i.e. an expert in your software's database, will emerge naturally over time on a product. In my current project, there are definitely some areas that I am the "go to" guy on, and likewise there are other people on my team that are the "go to" people for other areas. And yes, we even have two people who are especially knowledgeable about our backing datastore, and the library we use to interact with it.

That a DBA can make almost every query faster and every db schema better is often an unknown unknown for developers. Combine that with the need of having the DBA at hand all the time, because developers create new queries and possibly new tables or suboptimal solutions all the time. Small companies might get a DBA only when it's too late and they would need a major rewrite of some code and of the db schema. I've even seen companies with plenty of DBAs in Operations and none in development and no chances for them to affect the development process, both because the unknown unknown and because rigid compartmentalization between company departments.

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")

> 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.

Thats the thing. There is nothing theoretical about this. It is about what is actually closer to the real thing which in this case is the data.

I agree LINQ is far easier to understand and is more productive but those benefit just don't outweigh my performance, safety, and concurrency concerns. Understanding the database along with its native SQL gets you that.

For others the productivity is worth it.

> (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")

Doesn't GHC backend compile directly to assembly?

> It is about what is actually closer to the real thing which in this case is the data.

Why is SQL closer to the data?

> Understanding the database along with its native SQL gets you that.

My whole point is that SQL is not any more "native" than LINQ. The database takes SQL and computes an execution plan in its own internal representation. This can look totally different from the SQL as the other commentator pointed out. SQL does not in any way correspond to the "native" way the data is stored or how query results are computed.

> Doesn't GHC backend compile directly to assembly?

Maybe now but at some point it generated C.

> My whole point is that SQL is not any more "native" than LINQ. The database takes SQL and computes an execution plan in its own internal representation. This can look totally different from the SQL as the other commentator pointed out. SQL does not in any way correspond to the "native" way the data is stored or how query results are computed.

Yes please point a reference to me to a database that speaks LINQ or how LINQ directly generates to an execution plan (I admit I haven't touched MS SQL Server in some time).

In theory you are right but not in reality. To give another analog you still have to know Javascript to know TypeScript or Coffeescript (or whatever else in vogue) because WASM isn't a reality yet.

SQL is an abstraction over relational algebra. Relational algebra is, for the most part, easy to grasp. You can use it to define a query tree trivially.

Any tool that can map to that query tree has the same level of abstraction as SQL.

Repeat after me, SQL is nothing special. Its semantics are extremely limited. Nothing it does can't really be done by LINQ or a similar DSL that maps to relational algebra operators.

> 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.

> 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.

Which is the exact same thing you do when you write a LINQ query against a database context...

Both are just languages that describe what you want to achieve, not how to achieve it.

How does this contradict my point?

C is an abstraction over machine language, in the same way; but so is Prolog.

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)

> 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.

This is totally not true unless you have optimizations turned off. Have you ever looked at what clang or gcc generates with -O2 or -O3 ?

Anyway, regardless of whether you agree with me on this point, I don't see how it relates to the general point I'm making. This seems like it's just disagreement over the definition of the word "abstraction". It doesn't change (and in fact, actually supports!) my main point, which was that SQL is not properly regarded as "native" to database engines in some special way that can't be true of LINQ or any other language.

You still seem to think that LINQ to SQL is an 'implementation detail' and it would be possible to do LINQ to DB Execution Plan directly.

This is just plain wrong and you are missing important details of how LINQ and SQL and RDBMSes work in the real world.

I can't see a reason why LINQ to DB Execution plan couldn't exist. If it's wrong, please tell us why.

Today's db optimizer builds a set of execution plans and chooses the best one based on "cost". Number of rows, memory access vs. disk access, and other things factor into this cost. Also, the higher the number of joins, the greater the number of plans. 7 joins would be 7! possible plans +. This has to happen fast, so db optimizer cannot generate every possible plan and pick the optimal one. There's a body of research to delve more into how it does this. The point is that the optimizer does real work, as fast as possible to generate the execution plan (they called this dynamic programming in one paper I read).

The LINQ interpreter on the client would have to retrieve the statistics from the server's tables and indexes of interest and perform the same operation as the optimizer (costing more network and some db cpu). Next it would have to send back the plan to the db executor in some way. This plan "marshalling" would cause more network traffic, requiring the executor to "unmarshall" the plan costing more CPU. This is less efficient than the current scheme.

Alternatively the LINQ language could be implemented on the database but with its own inherent difficulties, but in reverse.

Once a plan is cached and is reused, this inefficiency goes away to some degree. So there could be a possible mechanism for the server to send back to LINQ client a hash identifying the plan if it is cached and then have LINQ only send the hash with parameters to the DB on next execution. (You would have to see if this isn't covered by some patent of course!)

The constraint is the network connection. If the network connection between client and server were faster and bigger than the data bus on the computers, then it would change the equation significantly. But longer distances means slower communication all things being equal (lightspeed and all). So a network connection being better than the data bus would be inefficient and quickly remedied in a competitive marketplace.

How is implementing LINQ on the database any different implementing SQL on the database?

Linq is compiled once. The database will continually monitor performance of the query and recompile the execution plan as needed. Linq runs on the client machine. The query planner runs on the database machine. Many clients will connect to the same database server, and they may not even know beforehand what exactly the RDBMS is capable of, what indexes are available, what hardware the database server is running on, etc. Therefore LINQ can't compile an optimal execution plan before sending the request off to the database server.

In short - LINQ runs on the client machine, the execution plan happens on the database server.

Think of it this way: Lets say you have a database of clients and contacts. Lots of systems in your company connects to this database to access this data. Each of those systems will submit SQL in the form of 'select clientname from clients where id = 123' or whatever. Now lets say the client list grows and the old execution plan is not optimal any more. Our smart RDBMS can just dynamically fix the execution plan and performance goes up for every system accessing the database. If the RDBMS instead received a rigid execution plan, EVERY client system will need to recalculate the execution plan.

Also: lets say your client app connects to several different database servers. What's a good exection plan on one server it not going to be a good execution plan on another server, so LINQ would need to keep a list of database servers with table statistics, indexes, etc etc and continually monitor all of those for changes. It's massive duplication of work. It's much more efficient to have each database server look after its own execution plans.

Sorry,I should have been clearer. The GP post mentioned 2 ways to implement a LINQ to execution plan transform. The first way would be to do it on the client. This is problematic because the client doesn't have enough information to generate an efficient execution plan. That makes sense to me. The second way would be to do it on the server. This is the part I don't understand. Why can't we do it that way? This is how it is done for SQL.

Interesting discussion as it considers how SQL is implemented, what is an abstraction, etc.

On the topic of concept-rightness you quickly veer into a language game. gcc transforms c code into machine code (and may also output error messages, warnings and such). For some, this is not important, and may not be conceptually right. But if you are interested in say the ABI for instance, you do care.

On the point of "fundamentalness of abstraction", I interpret it as a measure of how many layers of abstraction (Ab) can be decomposed into the concrete thing of interest. LINQ generates another layer, that of SQL. If it generated an execution plan and sent that to the database optimizer, then I would say it does not generate another layer and would be equal in level of Ab to SQL.

But if LINQ can generate SQL that runs on the database, then it is also possible for the database to support LINQ natively. And maybe that was the original intent.

However, as it stands today LINQ to SQL costs CPU resources. So in adopting LINQ one of the tradeoffs you are adopting is CPU resources for developer use/benefit of LINQ.

>>you can look at a C statement and be pretty sure what the ASM will end up looking like.

Perhaps 20 years ago, perhaps more. Optimizing compilers have gone long way.

Compiling an execution plan is a significantly easier task than actually guessing how exactly an optimizing compiler would schedule the code, down to assembly. (and cheating w/ hints in SQL- [looking at Oracle]just reinforces that.)

Have you ever written or edited an execution plan directly?

They are all abstractions, but not at the same level of abstraction.

"SQL is itself an abstraction over the "language" of query execution plans."

This is completely wrong. SQL is a declarative language, it tells database what to do, not how to do it (aka query plan).

I'm not sure what you guys think an abstraction is, but a declarative language on top is just another kind of abstraction.

Yes, hence it's an abstraction.

I didn't say SQL is a way of writing query execution plans but it seems some people are interpreting my post that way.

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

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/

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!

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.

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).

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.

20 year old oracle dba best practices...

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.

Here's a linq to Lukas Eder's JOOQ https://www.jooq.org/

I do like the syntax also though.

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

Reply on news.ycombinator.com