Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Literate SQL

modern-sql.com

Post reply on HN
Print view View original

31–40 of 100 posts

Re: Literate SQL

#31

Post by walshemj »

enjoiful wrote: post #19
caseymarquis wrote: post #8

It's critical to have a good understanding of SQL, but once you do, ORMs with a functional syntax solve this problem for many of us. I know it's not possible for some people to use ORMs as they can't risk the ORM making a performance mistake, but I think the solution is to improve ORMs to the point that writing raw SQL is akin to writing assembly instead of using a higher level language. That said, we're not there ye…

I love Django -- but what it does is mostly magic to me. That being said... I'm fairly competent in being effective in Django -- although not for advanced and efficient querying. I want to get strong in SQL -- where/how do I start?

If you can get an employer to pay for it the Into course that Oracle does is fairly good it certainly got me up to speed to be able to work on an oracle project.

Re: Literate SQL

#32

Post by tfigment »

richardlblair wrote: post #27
paulddraper wrote: post #21

Earlier quoted context omitted.

I see this opinion a lot. I don't quite understand it. Your DBMS isn't some ambivalent data store with simple universal semantics. I'm not just talking about SQL features like complex subselects or CTEs, but index hints, lock order, transactional visibility, non-trivial column constraints, index locks... These matter in appreciably sized public-facing (e.g. web) systems. Granted, with enough transactions and roundtri…

I think your post makes a lot of assumptions. I do not believe scale to be a reason not to use an ORM. If you pay attention to what you're doing you can scale a system that uses an ORM just fine. The orm is usually not the problem, it's usually that people don't know what they are doing and introduce serious performance issues.

My db issues for last ten years have frequently been a mix of bad query plans and terrible orm layers. Doctrine in Symfony rarely yield good queries in mysql for us. We've been moving away from orm where possible because we don't need to support multiple databases. Further if I switch my db to postgres I'm changing out everything else anyway to remove php from the codebase. I didn't write the original code but my team has to maintain it. The orm makes that hard actually compared to staight sql in the end.

Re: Literate SQL

#33

Post by [deleted] »

mistermann wrote: post #28
NumberCruncher wrote: post #22

Earlier quoted context omitted.

Even if you work with an ORM instead of writing raw SQL you should know when single row processing is OK and when bulk load / data set processing is a must. My last employer lost 5M+ euros (not reveue but profit) every month because of excessive use of PL/SQL single row processing.

How could they lose that much $ due to single row processing? Having to buy that much more processing power?

[deleted]

Re: Literate SQL

#34

Post by AlisdairO »

enjoiful wrote: post #19
caseymarquis wrote: post #8

It's critical to have a good understanding of SQL, but once you do, ORMs with a functional syntax solve this problem for many of us. I know it's not possible for some people to use ORMs as they can't risk the ORM making a performance mistake, but I think the solution is to improve ORMs to the point that writing raw SQL is akin to writing assembly instead of using a higher level language. That said, we're not there ye…

I love Django -- but what it does is mostly magic to me. That being said... I'm fairly competent in being effective in Django -- although not for advanced and efficient querying. I want to get strong in SQL -- where/how do I start?

[self plug] Take a look at https://pgexercises.com/ . It's a learn-by-doing set of SQL exercises. Focused on Postgres, but the large majority is standard SQL.

Re: Literate SQL

#35

Post by Asooka »

paulddraper wrote: post #21
caseymarquis wrote: post #8

It's critical to have a good understanding of SQL, but once you do, ORMs with a functional syntax solve this problem for many of us. I know it's not possible for some people to use ORMs as they can't risk the ORM making a performance mistake, but I think the solution is to improve ORMs to the point that writing raw SQL is akin to writing assembly instead of using a higher level language. That said, we're not there ye…

I see this opinion a lot. I don't quite understand it. Your DBMS isn't some ambivalent data store with simple universal semantics. I'm not just talking about SQL features like complex subselects or CTEs, but index hints, lock order, transactional visibility, non-trivial column constraints, index locks... These matter in appreciably sized public-facing (e.g. web) systems. Granted, with enough transactions and roundtri…

The reason for me to use ORM rather than direct SQL queries is so I can keep everything written in a single language, so I can develop with one IDE. Having everything under one roof is a huge boon to productivity, rather than having to edit the logic in the IDE, then edit the database queries in a different editor, and the two knowing nothing about each other. It lets me directly map my source language's type system to the database's schema, avoiding discrepancies.

Re: Literate SQL

#36

Post by Dangeranger »

mshenfield wrote: post #20
zzzeek wrote: post #13

Earlier quoted context omitted.

You can have functional SQL query languages that map directly to SQL without object oriented row mapping. Everyone can use these tools without performance issues. Disclaimer: i wrote SQLAlchemy.

> You can have functional SQL query languages that map directly to SQL without object oriented row mapping. Well said. I always think of this as the canonical example: http://sqlkorma.com/ I don't know what rock I've been under, but didn't realize SQLAlchemy had the same idea w/ SQLAlchemy Core.

Another excellent example of this is Elixir's Ecto library for composing SQL from modular fragments https://github.com/elixir-ecto/ecto/blob/master/README.md

Re: Literate SQL

#37

Post by gregmac »

caseymarquis wrote: post #8

It's critical to have a good understanding of SQL, but once you do, ORMs with a functional syntax solve this problem for many of us. I know it's not possible for some people to use ORMs as they can't risk the ORM making a performance mistake, but I think the solution is to improve ORMs to the point that writing raw SQL is akin to writing assembly instead of using a higher level language. That said, we're not there ye…

I disagree about SQL being like assembly. It's a fairly high level language, because underneath the engine is figuring out how to use indexes and actually run your query. If you want to draw parallels, ORMs are more like writing code in a template language that's written in the language you're actually working in.

I've spent far too much time fighting with ORMs trying to get the SQL I want generated. Additionally for complex queries, I often develop in a SQL manager interface, because it's so much more direct. When I'm done, I have a working SQL query - I can paste it in to my code and parameterize it, why should I spend even more time fiddling with an orm?

I like "micro ORMs" that just map rows to objects. Generating INSERT and UPDATE statements is ok, and single table selects with a single where clause are sometimes ok. Beyond that, I'd rather just skip the middleman.

That said, for small databases (a GB or two) that are reasonably well designed (e.g. 3NF), the speed of current I/O and optimization engines in databases is such that even the worst SQL will generally run fine - so if you find it faster to use ORM and understand the future pitfalls, go for it.

Re: Literate SQL

#38

Post by 3131s »

enjoiful wrote: post #19
caseymarquis wrote: post #8

It's critical to have a good understanding of SQL, but once you do, ORMs with a functional syntax solve this problem for many of us. I know it's not possible for some people to use ORMs as they can't risk the ORM making a performance mistake, but I think the solution is to improve ORMs to the point that writing raw SQL is akin to writing assembly instead of using a higher level language. That said, we're not there ye…

I love Django -- but what it does is mostly magic to me. That being said... I'm fairly competent in being effective in Django -- although not for advanced and efficient querying. I want to get strong in SQL -- where/how do I start?

Write raw SQL queries. My feeling on the Django ORM is that it's useful for defining models and managing migrations, but I prefer to write raw SQL for all queries.

The PostgreSQL documentation is really good too.

Re: Literate SQL

#39

Post by AndrewOMartin »

I see that this is about a way of writing SQL, but as Literate Programming is cited in the title, I wanted to point out that you can already use the true Literate Programming tool noweb to write SQL with or without "with" statements.

The advantage is that you'll be able to write full documentation amongst the SQL, present it in any order, and reuse chunks.

The disadvantage is that it outputs to stdout, so if that's no good for your task then it's no help.

Re: Literate SQL

#40

Post by paulddraper »

Asooka wrote: post #35
paulddraper wrote: post #21

Earlier quoted context omitted.

I see this opinion a lot. I don't quite understand it. Your DBMS isn't some ambivalent data store with simple universal semantics. I'm not just talking about SQL features like complex subselects or CTEs, but index hints, lock order, transactional visibility, non-trivial column constraints, index locks... These matter in appreciably sized public-facing (e.g. web) systems. Granted, with enough transactions and roundtri…

The reason for me to use ORM rather than direct SQL queries is so I can keep everything written in a single language, so I can develop with one IDE. Having everything under one roof is a huge boon to productivity, rather than having to edit the logic in the IDE, then edit the database queries in a different editor, and the two knowing nothing about each other. It lets me directly map my source language's type system…

I can sympathize.

For me, that's Scala JVM on the server, ScalaTags/ScalaCSS/ScalaJS in the web browser, Slick for the ORM, and SBT for the build system. The bliss of learn once, program anywhere.

When this hits reality though, I use Relate https://github.com/lucidsoftware/relate (disclaimer: I'm a contributor)

It's SQL, but with minimal syntactic overhead.

    sql"SELECT * FROM users WHERE email IN ($emails)".asList[User]()
Post reply on HN