What ORMs have taught me: just learn SQL • Hacker News Board

Re: What ORMs have taught me: just learn SQL

#191

Post by adamlett » Tue, Aug 05, 2014, 11:32 AM UTC

lloeki wrote: post #180

makmanalp wrote: post #33

Over and over I keep finding that just an ORM is not enough, but raw SQL is hideous in a different way. ORMs map nicely when you are indeed modifying objects, but somethings don't map well that way. So don't map them that way! What we need is a low level abstraction layer alongside the ORM. The main problem with raw SQL is that what you really want is a genuine programming language. You almost want programmatic acces…

> The main problem with raw SQL is that what you really want is a genuine programming language. You almost want programmatic access to the SQL AST, so you can generate syntax as opposed to concatenate strings together. Kind of like a DOM API, but for SQL. Congratulations, you just described Arel. I liberally use rails/active_record where it shines (operating on a single record, or writing composable scopes) but very…

> Congratulations, you just described Arel.

Unfortunately SQL leaks through Arel's abstractions and make it behave in surprising ways. Arel falls short of achieving the goal of being able to modularise and compose queries. The same is true of nearly every SQL connectivity library (or so this article claims: http://www.try-alf.org/blog/2013-10-21-relations-as-first-cl...).

Re: What ORMs have taught me: just learn SQL

#192

Post by _ZeD_ » Tue, Aug 05, 2014, 11:59 AM UTC

NicoJuicy wrote: post #174

terhechte wrote: post #73

Earlier quoted context omitted.

Postgres indeed doesn't have Upsert yet, so I'm going the default way of locking the table, and implementing it via a slightly more complex query. I was just too lazy to explain that in my earlier comment. The problem is the same: The syntax below can't really be represented well in a ORM. BEGIN; LOCK TABLE search_tracking IN SHARE ROW EXCLUSIVE MODE; WITH upsert AS (UPDATE search_tracking SET count=count+1 WHERE key…

But this aint that hard, i suppose it could also be done in Postgres (query using MS SQL Server) Table1 SET (...) WHERE Column1='SomeValue' IF @@ROWCOUNT=0 INSERT INTO Table1 VALUES (...)

not atomic

Re: What ORMs have taught me: just learn SQL

#193

Post by CmonDev » Tue, Aug 05, 2014, 12:25 PM UTC

maaku wrote: post #35

euphemize wrote: post #15

I think ORMs like SQLAlchemy are really useful for many many use cases. I don't think most people who work with ORMs deal with the kind of complexity described by the author, let alone work on such a specific application for 30 months at a time. In that sense, ORMs are super powerful tools that cut down your work, shortens your code and do nifty optimizations once in a while With that being said, this article totally…

SQLAlchemy isn't like most ORMs. It's data model is actually closer to the relational model than OOP objects, and therefore lacks many of the ORM warts. I wish more ORMs were like SQLAlchemy.

Dynamic typing is quite good for this sort of integration scenarios.

Re: What ORMs have taught me: just learn SQL

#194

Post by ak39 » Tue, Aug 05, 2014, 12:25 PM UTC

callesgg wrote: post #151

The main problem with ORM's is that they keeps the database model in the code rather than in the database. I know that is what some people like, i myself USED to think that was nice. What i eventually learned was what Linus Torwalds said: http://programmers.stackexchange.com/questions/163185/torval... As long as your data structures are good and clear the code to handle them seams almost obvious.

Here's the quote: "Bad programmers worry about the code. Good programmers worry about data structures and their relationships."

Wow, thanks for posting the excellent link.

Re: What ORMs have taught me: just learn SQL

#195

Post by CmonDev » Tue, Aug 05, 2014, 12:32 PM UTC

dventimi wrote: post #61

Here's a question. WHY do we even bother with ORMs? Put another way, what problem are they trying to solve?

Make data layer more testable and refactorable by decoupling from a specific data storage. I would trade a horrendous large SP for a horrendous large C# codebase any day.

Re: What ORMs have taught me: just learn SQL

#196

Post by CmonDev » Tue, Aug 05, 2014, 12:36 PM UTC

sytelus wrote: post #170

After looking at kind of monstrosity like Entity Framework, I've given up on traditional ORMs. My current choice for db access is micro-ORMs like PetaPOCO.

Check the EF7 - they started from scratch:

https://github.com/aspnet/EntityFramework

Re: What ORMs have taught me: just learn SQL

#197

Post by zak_mc_kracken » Tue, Aug 05, 2014, 12:43 PM UTC

dragonwriter wrote: post #145

zak_mc_kracken wrote: post #138

Earlier quoted context omitted.

> I NEVER want to go back to ORM Except that you just invented your own ORM. Think about it: you are encapsulating SQL data into classes, in other words, mapping relational data to objects. That's an ORM.

You've just broadened the definition of ORM so that any SQL abstraction layer in an OO language becomes an ORM. That's a rather nonstandard usage.

ORM is "Object Relational Mapper".

If you are taking data out of a relational database and mapping it into objects, you are implementing an ORM.

Seems like I'm sticking to the exact definition of an ORM, aren't I?

Re: What ORMs have taught me: just learn SQL

#198

Post by jimktrains2 » Tue, Aug 05, 2014, 12:53 PM UTC

makmanalp wrote: post #54

dharbin wrote: post #44

Earlier quoted context omitted.

Take a look at my library, swigql[1]. You can create a base query and then extend that query however you want via template inheritance. [1] https://github.com/civitaslearning/swigql

My personal opinion on such libraries is that they're cool, and I think they do make life easier but they're just a more advanced form of string concatenation. No notion of SQL types, syntax or structure. To follow the template analogy, a low level abstraction library allows me to not only change the variables but also change the template programmatically. And not by concatenating more stuff onto the template either…

> Prepared statements, by contrast, are also a form of string concatenation. And they have some understanding of type and syntax, but only because they learn them from the server:

Prepared statements are NOT string concatenation. The server parses the SQL, creates what internal representation it does, and then uses the parameters more as arguments to that executable statement than checks them and sticks them in. At no point does a SQL statement exist with the parameters and SQL.

Re: What ORMs have taught me: just learn SQL

#199

Post by julie1 » Tue, Aug 05, 2014, 1:07 PM UTC

Well I love SqlAlchemy, and DBIx::Class

I also like true RDBMS.

The most important feature of RdbMs is the Relationships for me and the «on delete/on update» combined with FKI and constraint. It makes possible to have complex in relationship that are consistent and that can be added/removed without destroying the consistency of the state in the DB. And most ORM only people I know overlook this part, which is a must for transactionality. Your data stay consistent other time.

Also, people tend to think querying with ORM are cool. They often query DB as random access record. However for me complex query are more like intersection, symmetric difference, union of records based on their joins. And SQL is so good at it.

ORM can have hook on transactions, making it possible to call a webservice on commit and rollback.

ORM can have mixins to help with having password handled carefully.

So, what is wrong at my opinion, is neither SQL nor ORM but the idea that only one is enough to learn.

Re: What ORMs have taught me: just learn SQL

#200

Post by drbawb » Tue, Aug 05, 2014, 1:25 PM UTC

terhechte wrote: post #27

I used to write raw SQL for many years, then, around 2005 switched over to ORMs in order to be able to target different databases, have a nice model, etc. Lets be honest here, the ease of justing doing: p.username = "Carl" p.age = 33 p.save instead of "update users set username=:username, age=:age where id=:id" has a ton of advantages. For one, some sort of syntax or type checker is actually trying to understand your…

>Strongly typed lanaguages are even cooler here ...

No expereince with Slick in particular; but I've been using jOOq[1] which I believe is similar.

To be honest I'm not entirely sold that these DSLs are what I'd consider "strongly typed." I can get jOOq to pretty easily yield queries that won't work if I switch out database dialects. (Ignoring, for a moment, that jOOq will let you embed SQL fragments as strings.)

As an example: jOOq will happily let you write an update query targeting a table bound to an alias. This type checks just fine. In fact this query will even work in Postgres; but it yields a syntax error from the database if you target SQL Server 2008 instead.

Then there's the issue of the dialects letting you use features the RDBMS doesn't support. For example you can build a merge statement regardless of which dialect is selected. If you were to select the Postgres dialect you'll get an "unsupported exception" at runtime.

If a method is RDBMS specific (in practice) then why is it part of the generic API?

---

My biggest beef with all these SQL abstraction layers is that many of them claim to be "write once, run anywhere." In practice I've just never seen that to be true.

I wish they would incorporate some sort of "capabilities" system that could run at compile time. Just imagine: when you swap in the Postgres driver your IDE throws a little red squiggle under the `.merge()` call, "method not found."

[1]: http://www.jooq.org/