Hacker News Reader

Comment by marcus_holmes | Hacker News Reader

Unfortunately I think Rails has poisoned the well here. Too many web devs seem to expect that any environment they code in should have all the bells and whistles that Rails has.

My particular bugbear is working with people who refuse to learn SQL as if it's some arcane bit of weirdness on a par with machine code. They've only ever used ORMs and have no idea how to actually talk to their data. /rant

I recently looked at Phoenix, and ran into a few dependency issues straight out of the box. Thankfully, because it reminded my why I stopped using frameworks ;) I started getting to grips with Plug instead... much better :)

View on HN

I'm pretty glad ORMs caught on; I remember the bad old days when developers did write all their own SQL queries: we ended up with piles of SQL injection everywhere.

ORMs are a massive benefit to productivity and security for about 80% of your database access. The problem is that people expect to use them for 100% of their database access. For that last 20%, they just get in the way and cause more problems than they solve.

> For that last 20%, they just get in the way and cause more problems than they solve.

If I had a nickel for every project I worked on that started using an ORM but eventually was forced to write actual SQL to cover those use cases the ORM just couldn't I'd be rich. In fact it's the number 1 reason I don't even bother with ORMs anymore.

If I abstract my data access to a set of APIs then all I have to do is reimplement the SQL or whatever behind each API when I need to move to a new storage system which usually ends up not being very difficult compared to the headaches of trying to get an ORM to cover that last 20%.

While I do write SQL during exploration, for the purposes of production systems I've never had a query that I haven't been able to represent using SQLAlchemy. The reason for this is that in addition to being an ORM, it exposes an object-oriented general SQL abstraction layer.

> For that last 20%, they just get in the way and cause more problems than they solve.

This is why my preferred measure of an ORM is how effectively one can write your own SQL with it, and what you get out from your database call when you do so (do you still get "real" business objects or just key-value pairs?)

That said, I've found you can trick even a rather recalcitrant framework into making sophisticated queries by setting up a database view and tell your ORM that the view is just another table...

On which world?

We still do them by hand, as ORMs don't match the performance of the data that we work with.

Plus it is dumb to have data cross the wire for DB operations that can happily stay on the server.

I always enjoy showing some devs how well written SQL outperforms their beloved ORM.

> WELL WRITTEN SQL outperforms their beloved ORM

"Well, that's the real trick, isn't it?" </Han Solo>

The only time I've had a problem with SQL injection is with a PHP dev who just didn't get the whole issue. Mind you, he didn't get a lot of things.

It's one of those things you generally learn once and never have a problem with again.

Though I'm glad ORMs caught on too, preventing injections is covered in almost newbie tutorial when dealing with SQL.

Yes, ORMs let newbies code queries safely.

However you can deal with SQL safely bypassing the ORM if you compose the queries correctly. An example with Ruby and the pg driver for PostgreSQL:

    db.prepare("select_title", "select title from posts where author = $1::text")
    ...
    db.exec_prepared("select_title", [ author ]) do |result|
      ...
    end

I concede that it's more prone to unsafe coding than Post.where(author: author).pluck(:title) Everybody in the team must resist the temptation to ever write a db.exec("select title from posts where author = #{author}")

What I do is using the ORM all the times, except for complex queries that would be a nightmare to code with ActiveRecord/Arel and to understand when written in Ruby. In those case I use ActiveRecord's find_by_sql. It can be protected by SQL injections easily. I quote the documentation:

    Post.find_by_sql ["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date]

Again, it has the same problem of having to enforce the discipline of passing the arguments in the right way but there are countless ways of making a project unsafe and the team must be trained to prevent unsafe coding practices. SQL is only one of them.

Intro tutorials will cover lots of ways to prevent common security issues.

Hard experience has taught that programmers still won't adopt those approaches in significant numbers unless their tools are forcing it on them by default.

Understanding the SQL an ORM generates is definitely an underrated skill. I've probably fixed hundreds of inefficient/N+1 queries and injection vulnerabilities created by developers who either didn't know what SQL they were generating or didn't think about it.

Not nearly as underrated as an understanding of normalization.

SQL can just be picked up if you don't know it. It's a known unknown.

A lack of understanding of normalization is something that leads people to create epic, incredibly hard to escape from architectural fuckups without even realizing the hole they've dug for themselves.

I have been thinking of writing an SQL tutorial specifically for folks who have only worked in ORMs. Do folks here have a favorite SQL tutorial that I should look at for stylistic inspiration?

The things that no-one seems to know about from ORMs are:

1. Stored procedures. the idea that you treat your SQL like other code, and put it in sprocs, parameterise it, version it, etc.

2. Optimising tables and playing around with different normal forms and indexes to get the best results. Database design, basically. The 1:1 Object:Table mapping that ORMs use is not always optimal.

3. Indexing multiple fields that get queried together. If you commonly query on user name and state, then create an index for that. Maybe. If that table is read from more than it's written to. If read time optimisation is more important than write time optimisation.

I haven't looked at a SQL tutorial recently, so I can't help with that, sorry

I'm spoiled by ORM's, but I have no problem using SQL to query the database. What I have trouble with though is creating tables using raw SQL and managing schema migrations. Even when using an ORM it seems kind of messy. I'd love a tutorial on how to properly maintain migrations without an ORM.

http://www.liquibase.org/

Stanford has an online databases course that is a bit more thorough than you'd probably intend, but it is very excellent.

In Rails for complex applications I always start with the SQL in my mind, and I bend the ORM to produce my SQL (if I can, otherwise I use plain SQL). For example to generate a NOT IN : ModelA.where.not(id: ModelA.joins(:model_b).where(...).select(:id))

Can't help you with the refusal, but the easiest path from Rails ORM to SQL is the ".explain" method - or just paying attention to the console output when running an ORM query, and seeing how your ORM code gets translated to SQL.

What kind of dependency issues?

to be honest I can't remember, as I flipped the table after my third time trying to get it all to work, and deleted the whole thing.

I was using this tutorial: http://phoenix.thefirehoseproject.com/0.html which was featured recently in HN and uses a pretty old version of Phoenix that no longer works out of the box (something about an Access error?). Trying to work out which version of Phoenix since 0.8 does actually work out of the box was fun.

There was the problem with babel needing the ES2015 in a .babelrc in the application folder. Took me a while to find that one. It's been an outstanding issue on the Phoenix repo for a while now.

Then my version of Chrome wasn't playing nicely with Elixir - I would get a 400 response no matter what. This isn't a Phoenix problem, I get the same thing with Plug. Bit of a head-scratcher, works fine in Incognito mode, doesn't work in vanilla even with all the extensions disabled. Took me a while to work out where that problem was.

then I got to building a controller, and it just wouldn't compile for some reason. I got a bit fuzzy around here due to the rising rage at the bloody thing and why won't it just bloody work I'm sure I haven't fat-fingered that it should just bloody work why oh for god's sake stuff this.

I've been getting a bit farther along with Phoenix - just building a toy Instagram-lite site for learning purposes.

I like the Elixir language, but I'm a little concerned that in a real-world production app you'd really need to learn the fundamentals of Erlang and OTP as well (especially for things like error handling). So while Elixir is fairly easy (if you've done a bit of Ruby and functional programming) it's really the tip of the iceberg.

Phoenix itself is OK, but the lack of documentation and examples is brutal. It's very easy to go off into the weeds trying to do very basic stuff. I'm sure I'm doing a lot of things wrong, but there's a lack of clear examples to show the right way, even for simple things like "how to get the first row of a complex query with Ecto". I've stayed clear of doing an SPA so I don't get bogged down in Javascript tooling and pipelining issues, but a lot of things are missing from the basic setup - for example, Phoenix uses Bootstrap by default, but for some bizarre reason they don't include the glyphicon fonts out of the box, so you actually have to download/install bootstrap somewhere and copy fonts over. Maybe not a big deal in itself but enough of these annoying issues can sap momentum and enthusiasm.

I get that Phoenix is alpha, non-production ready software unless you happen to be a core dev, but the deeper feeling I keep getting though is that I don't really like big-box frameworks anymore. Sure, Django is still my go-to for a straightforward, lots-of-CRUD-screens type of project and it does that job very well. But I don't want to learn another Django. A lot of side projects I just want to start small, build out a nice UI in React or Vue (or play around in the mobile space) and add an API when I need a backend store or SMTP or whatever. For that Flask is fine, or maybe Go (or in your case, Plug). Use whatever libraries you need and follow sensible design patterns that suit the case at hand.

Phoenix is definitely production ready, as I am using it in many production systems now. 1) You have to eventually get into OTP. Elixir language is ok but all the stuff which makes it attractive is OTP and it's concurrency. If you are building a very small API you don't have to learn OTP ans you can get very far with that. 2) I find Phoenix documentation very good and the number of examples are increasing a lot day by day. 3) The default included bootstrap is just to show the landing page. You have to add your own css which you have to do if you use any other framework or library. 4) Phoenix is nothing like Django or Rails. Phoenix is very modular. See http://theerlangelist.com/article/phoenix_is_modular

> I find Phoenix documentation very good and the number of examples are increasing a lot day by day.

We can beg to differ. Yes, it's improving, but there's still a long way to go before it's at the standard of more mature frameworks.

> The default included bootstrap is just to show the landing page. You have to add your own css which you have to do if you use any other framework or library.

Sure, but if you're going to include bootstrap then you might as well do it properly rather than half-assing it. Better to not install bootstrap, I'd be fine with that, but to include it kinda-sorta then have bits missing is just going to confuse newbies.

> Phoenix is nothing like Django or Rails. Phoenix is very modular

Maybe it is, but you have to get far into the learning process to figure out where everything fits together, with pipelines, plugs, and so forth, before you can add and remove what you need. Out of the box Phoenix comes with a ton of dependencies and configuration you need to reason about first. In that respect it's very similar to Rails and Django.

Agree completely.

Starting small and adding functionality that I need as I go allows me to understand exactly what I'm doing and why. I get to make the choices about whether I need a dependency, and what the costs of that dependency are.

If all I need is 5% of the functionality, I can code up a small module to do just that without importing 150Kb of random unnecessary.

> My particular bugbear is working with people who refuse to learn SQL as if it's some arcane bit of weirdness on a par with machine code. They've only ever used ORMs and have no idea how to actually talk to their data. /rant

Lol this is me. People who write their SQL from scratch confuse me, I'm like, just use the ORM and then everything's consistent! :P

The real trick is to have a deep understanding of SQL before you use an ORM so that you know exactly what you are asking the ORM to ask the database to do.

Otherwise you will never be able to really understand what is going on and will not be able to optimize a complex app that needs to be performant at scale.

Learning both has its advantages and I think an ORM can indeed speed up development, most especially for the prototyping phase.

Learning about the relational model and its mapping in SQL is a really major level-up that you should really consider getting under your belt!

I used to do a lot of hiring of Rails developers, and always believed (though admittedly not scientifically enough) that the best differentiator between "sorta productive" and "capable of fully independent work" was an understanding of how AR maps to SQL and the ability to know when (and how best) to drop down into writing queries directly.

You might want to look at some of the SQL that your ORM is generating for your important queries... Because a lot of times, odds are it's much, much less efficient than it could be.

I've seen ActiveRecord create some head scratchers.

There is a big difference between ActiveRecord pre and post Arel. Give it another go if you haven't seen Arel in action.

A developer who doesn't understand SQL could be a little dangerous because they might not understand data structures and big-O concepts (sets, hash tables, etc..). I don't think someone needs to be a wizard with the syntax, but they need to understand what the database is doing. Otherwise they could write something that won't handle load under real world use.

One of the things I like about the .NET Linq-to-SQL and EF ORMs is that it's pretty easy to plug into them and get them to dump the actual SQL that they are executing.

Still, you really need to have a good handle on the difference between operations that build up the query, without executing it, and those that pull the data back from the database. Also understanding what code constructs that the ORM is capable of translating into SQL statements.

.NET is actually the framework that gave me such a bad taste for frameworks. It's important to note that my experience with it is from around 2010-2012 though so it could have gotten much better since.

The ORM in particular would sometimes cause me to write very inefficient queries. If I understood the ORM better, I probably could have avoided this, but I found it much easier to write a data layer that ran SQL queries directly and supplied the other layers with simple JSON-like objects.

me too. EF was what caused me to (finally) dump the entire .Net ecosystem and start learning again from scratch with Linux.

Yeah, most of the projects I've been working on lately wouldn't be possible with a licensed operating system. For me, working with linux is very difficult, but the capability far outweighs the difficulty. I don't catch on easily to concepts, commands, and multiple steps of such an advanced tool.

Don't trade consistency for power. If you know sql, you can answer questions about your data without writing a program, by just executing queries in a database UI. This ad-hoc querying is far more powerful than any other debugging technique for many classes of problems.

Reply on news.ycombinator.com