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

Our journey in dropping the ORM in Go

alanilling.medium.com

Post reply on HN
Print view View original

101–110 of 157 posts

Re: Our journey in dropping the ORM in Go

#101

Post by hparadiz »

toast0 wrote: post #60
aidos wrote: post #56

Earlier quoted context omitted.

Can you provide some examples? I use sqlalchemy, and the amount of work that it does for me when it comes to handling mutating data is pretty remarkable.

No real specifics, but I usually run into ORM in the context of I'm the person on the team with experience running MySQL and someone has a slow query. When they're hand built queries, I can usually provide a query or sequence of queries that provides the same data without knocking the server over and it gets put into production within the same day. When they're ORM, it takes days to find the query, and then more days…

You should be using an APM like NewRelic to find those slow queries. If that's not an option you should have a profiler that tracks how long each query took and have it print out the query to a log. Turn it on for a couple hours during peak time and analyze it for slow queries.

Also use static table design when applicable and avoid running any query that locks the table up. If the query takes any longer than 500 ms you should be running it as a background job instead.

Re: Our journey in dropping the ORM in Go

#102

Post by EdwardDiego »

aitchnyu wrote: post #63

I use this pattern in Django and I feel without ORM or query builder this will need 4 raw queries. ``` results = Foo.objects()... if a: results = results.filter(a=a) if b: results = results.filter(a=b) ```

    select * from foo 
    where case 
           when :filter_on_a and not :filter_on_b then a=:a 
           when :filter_on_b and not :filter_on_a then b=:b
           when :filter_on_a and :filter_on_b then a=:a and b=:b
           else true
          end
Should preserve all your semantics. I can't remember if Python DBAPI handles named parameters, so let's pretend it does and the colon-prefixed parameters are in the right syntax.

cur.execute("above query", filter_on_a=bool(a), filter_on_b=bool(b), a=a, b=b)

Whether the more complex SQL is worth it really depends on your use case.

Re: Our journey in dropping the ORM in Go

#103

Post by rmbyrro »

exdsq wrote: post #85
marcus_holmes wrote: post #68

Earlier quoted context omitted.

this. Don't write your SQL into the database. Write them as scripts (starting with "drop XYX", then "create XYZ", check them into git, and treat them as code. Migrations are for schema changes. Views and functions are not schema.

Wait. People don't do this?

No, most don't

Re: Our journey in dropping the ORM in Go

#104

Post by makeitdouble »

eric4smith wrote: post #87

Heh. Recently I had to stand up a quick elixir project and decided to write all queries by hand and not use Ecto. It was extremely enjoyable writing every query from the start. Just thinking carefully about what columns I needed, and crafting the best joins and where clauses made efficiency baked in from the beginning. If one is not careful and just be lazy with an ORM you get back all columns all the time - and this…

If you’re not careful and just be lazy with your hand crafted queries you’ll miss updating old queries when you alter your schema. You won’t convert your types the same way in each places you query the data, or you’ll forget checking your data’s consistency where it’s falling in the cracks between your SQL rules and the application’s requirements.

The “not careful and just be lazy” way won’t get you in a good place whatever approach you take.

Re: Our journey in dropping the ORM in Go

#105

Post by imposterr »

marcus_holmes wrote: post #3

So frustrating. You got like 80% of the way there, and then went "nope, too much work" and diverted to add more complexity. The answer is to write the SQL yourself, and the scan methods yourself. Code generation is better than ORM, but still a wrapper, still adds complexity, and still brings problems. Yes it's a pain in the arse to write all that boilerplate in one go (pun intended). But if you'd started without an O…

> I write a view for each access method (so I can change the schema without worrying about changing every access method), and a function for each update/insert/delete (for the same reasons)

I’d appreciate it if you could expand on what this means exactly.

Re: Our journey in dropping the ORM in Go

#106

Post by exdsq »

rmbyrro wrote: post #103
exdsq wrote: post #85

Earlier quoted context omitted.

Wait. People don't do this?

No, most don't

I understand why people might hate the idea of using raw SQL when I suggest it then! I always assumed it’d be version controlled and 100% replicable (minus data) from sql scripts

Re: Our journey in dropping the ORM in Go

#107

Post by mmcdermott »

nauticacom wrote: post #65
karmasimida wrote: post #40

Earlier quoted context omitted.

Is writing SQL query templates too much? The only valid advantage of ORM is to prevent SQL injection, which can be solved with prepared statement.

Okay so I write a prepared statement, send it to my database with some parameters, and get back some rows. Now what? How do I turn those rows into structs or objects or whatever that I can pass around for business logic? Maybe I write a function that takes a row and maps it to an object, I call that for every row I get back, whatever. Oh but now I'm joining a belongs-to relationship and I want that joined row represe…

There are two things that get bundled together as ORMs: mapping utilities and query generation. You can have one without the other - Dapper (.NET) and JDBI (Java) are examples of mapping code without generation.

The fact that you see mapping libraries without the query generation shows what's fundamentally broken about query generation. Namely, that mapping is a function of a result set. Going from a result set to an object is generally fairly straightforward. Going from an object structure to a result set (with the query merely being the declarative representation of that result set) is problematic.

The hatred comes from the query generation and is, I think, sensible. The time lost attempting to trick an ORM into generate performant SQL (on top of the time taken to set it up and learn its query language on top of SQL) is a poor trade. Frankly, if it came down to it, I think I could write the mapping code manually and come out ahead over dealing with the headaches of query generation.

Re: Our journey in dropping the ORM in Go

#108

Post by dnautics »

eric4smith wrote: post #87

Heh. Recently I had to stand up a quick elixir project and decided to write all queries by hand and not use Ecto. It was extremely enjoyable writing every query from the start. Just thinking carefully about what columns I needed, and crafting the best joins and where clauses made efficiency baked in from the beginning. If one is not careful and just be lazy with an ORM you get back all columns all the time - and this…

Ecto is pretty damn nice (also technically it's not an ORM - and that's not a trivial distinction). Yeah, it adds a little bit of latency, but that will usually be overshadowed by the DB transactions... Am I mistaken about this?

Are you not afraid that your queries will have SQL injections? Or handle unicode poorly, binary blobs, etc?

> you get back all columns all the time

I think this is why you can map multiple schemas to the same table in Ecto

Re: Our journey in dropping the ORM in Go

#109

Post by sa46 »

Having written a non-ORM Go-Postgres tool [1] similar to sqlc, I'm a big fan of this article, especially their acknowledgment that using SQL moves the eng culture towards data-centric engineering. Some thoughts:

- Application code should not rely on database table structure (like the ActiveRecord pattern). Modeling the database as a bunch of queries is a better bet since you can change the underlying table structure but keep the same query semantics to allow for database refactoring.

- Database code and queries should be defined in SQL. I'm not a fan of defining the database in another programming language that generates DDL for you since it's another layer of abstraction that usually leaks heavily.

- One of the main drawbacks of writing queries in plain SQL is that dynamic queries are more difficult to write. I haven't found that to be too much of a problem since you can use multiple queries or push some of the dynamic parts into a SQL predicate. Some things are easier with an ORM, like dynamic ordering or dynamic group-by clauses.

[1]: https://github.com/jschaf/pggen

Similar comment from a few months ago comparing Go approaches to SQL: https://news.ycombinator.com/item?id=28463938

Post reply on HN