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

What ORMs have taught me: just learn SQL

wozniak.ca

Post reply on HN
Print view View original

211–220 of 245 posts

Re: What ORMs have taught me: just learn SQL

#211

Post by Xorlev »

beermann wrote: post #148
Xorlev wrote: post #91

For those of us on Java, I've grown to love http://jdbi.org/ . JDBI has a lot of features that are convenient (e.g. auto-mapping of columns to a POJO), but synthesizes DAOs for you from interfaces annotated with SQL queries, e.g. @RegisterMapperFactory(BeanMapperFactory.class) public interface TripDAO { @SqlQuery("SELECT trip_start AS start, MAX(timestamp) AS end FROM location_updates WHERE trip_start = :start GROUP…

I've been using JDBI for a new project for about a month or so and I like it a lot as well. I will say that the documentation is a bit sparse though, or at the least hard to find by digging through blog posts. When I figure out how to do what I'm trying to do however, I love it. For example, it took me a while to find out that @CreateSqlObject was a thing and that it solved any hackery I was trying to do to have my D…

I'll agree with that. I had to really dig through the user group to find answers to a lot of things. e.g. @SingleValueResult if you want to return an Optional from a query.

I didn't know about @CreateSqlObject, thanks!

Re: What ORMs have taught me: just learn SQL

#212

Post by JohnBooty »

plaguuuuuu wrote: post #122
JohnBooty wrote: post #103

I wrote "raw" SQL for many many years before using ORMs, so I feel a lot of this guy's pain and agree with most of his points. Especially the part about still having to know SQL even though you're using an ORM. Not sure I understand his solution to this one, though! Window functions are relatively advanced SQL that is painful to write with ORMs. Not writing them into the query likely means you will be transferring a…

Let's talk practicality rather than sitting in some ivory tower and muttering about best practices With AR, if this is the worst case where you write some raw SQL, what's the alternative? The alternative seems far more painful pragmatically speaking and this, while being a little ugly, seems to work just fine without impacting productivity or performance.

  > With AR, if this is the worst case where you write some
  > raw SQL, what's the alternative? The alternative seems
  > far more painful pragmatically speaking and this, while
  > being a little ugly, seems to work just fine without
  > impacting productivity or performance.
Do you know if other ORMs allow this kind of relatively painless use of "raw SQL?"

I've only used ActiveRecord and some of the .NET "micro ORMs" like Dapper and PetaPOCO.

Dapper and PetaPOCO definitely support raw SQL. That's kind of their main focus - they take a row of SQL results and map those database columns to your class's properties, and by design they don't do a whole lot else.

But I don't know about other big/popular ORMs like Hibernate...

Re: What ORMs have taught me: just learn SQL

#213

Post by JohnBooty »

blar wrote: post #134
rosser wrote: post #2

I've caught a lot of flak for saying this, but I'm convinced that all ORMs are ultimately tech debt. Sure, they get you up and running quickly, but once you're there, you'll invariably find yourself wanting to do things that require you to work against and around your ORM to accomplish. By pretty much any definition I've ever encountered, that's "tech debt"

For the last three years or so, I've been telling anyone who asked that ORM is an antipattern, to be avoided at all costs. I've settled into wrapping all queries in classes, with any parameters exposed as public properties. The SQL is written inside the class, essentially in a template. When necessary, the generated query can change based on the values assigned to the properties. All the mechanics of how the query is…

I've used this pattern and I like it a lot (for the reasons you say) but I find I still spent way too much time on really mundane stuff... particularly tableobject mapping if the database table has a lot of columns.

My current approach is:

1. I basically judge ORMs on how easily they allow me to use custom SQL. (ActiveRecord makes this pretty tolerable with find_by_sql) 2. Let the ORM handle as much CRUD and tableobject mapping as possible 3. If I have complex SQL, I try to wrap it in an appropriate database object (view, sproc, function).

Re: What ORMs have taught me: just learn SQL

#214

Post by marcosdumay »

hamburglar wrote: post #157
marcosdumay wrote: post #99

"I much prefer to keep the data definition in the database and read it into the application. It doesn't solve the problem, but it makes it more manageable. I've found that reflection techniques to get the data definition are not worth it and I succumb to managing the redundancy of data definitons in two places." My experiency is almost the oposite of that. I've found that automatic migrations are one of the best feat…

Do people actually use ORM-generated migration in large production environments? In my experience, schema mods to big production tables need to be planned out carefully and run by hand on the inactive master db (using mysql mmm replication terminology) in order to pull them off without downtime. You can't just have the next app push blindly start doing ALTER TABLE statements. Perhaps there are clever schemes modern O…

To be honest, I never tried migrating without downtime[1].

I don't see any reason why applying the changes in a inactive mirror would be dificult. They are fit for diverse environments, since you'd apply them first at the development machine, then testing[2], and only then at the live system. The main issue is what to do with the data generated while you migrate, but you solve that the same way you solve a normal hand-made migration, you ask the ORM to create the operations at the right order, and divide it in batches. It's just more work than a couple of minutes of downtime justified for me.

[1] Postgres has no problem doing some ALTER TABLE statements at the live mirror, what makes most migrations trivial, ORM or not. I mostly do those live, but monitor closely because it's dead-lock prone.

[2] I think data migration is the only reason I create separated testing environments nowadays. Otherwise, I try to keep development envs complete enough for tests, and just recreate them as needed. It makes everything much faster.

Re: What ORMs have taught me: just learn SQL

#215

Post by sk5t »

_ZeD_ wrote: post #192
NicoJuicy wrote: post #174

Earlier quoted context omitted.

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

Thus transactions...

Re: What ORMs have taught me: just learn SQL

#216

Post by casperc »

thom wrote: post #84
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…

YeSQL seems a little bit like it's reinventing the Microsoft data access ecosystem of 10-15 years ago - stored procedures behind a code-generated API. Retro is cool. The Clojure SQL ecosystem is weird. clojureql seemed wonderful for a while, but has been left to rot and the fact that nobody's really picked it up implies people have just moved on with their lives. Korma and YeSQL seem to handle most of the Rails-like…

I think alot of people (including myself and my team) just tend to get on with their lives and use clojure/java.jdbc. Sure it's not that sexy, but it gets the job done.

And for the 80-90% queries you can create a small function or namespace with a nice API and converts to the types you like.

Re: What ORMs have taught me: just learn SQL

#217

Post by mamcx »

steverb wrote: post #90
mamcx wrote: post #56

For long time, I have no idea that SQL & OO are not friends. I work in a language where such problems don't exist. And it have nice ways to move data between tables and data structures and objects (For example: SELECT..INTO Array NAME). Is FoxPro. Even the stored procedures were foxpro, all along the stack, from UI to inner DB actions. Is a shame that this kind of programming is "lost" today. This is kinda like work…

Dear Lord in heaven, no. Yes, the data language and the application language were all the same language, which led to some awful spaghetti code where data access was strewn all over the place. Then you also get to deal with scaling issues because your db and application logic all run on the same box. But hey, you can still download and run Visual FoxPro from MSDN if want, Source: My team is in the process of slowly s…

That is the description of almost all apps out there. Only with the popularization of MVC is started to be cool to decouple things. I see the same behavior in several codebases and languages (I do mainly business apps and code cleanup).

But then, in the case of VFP, you still have a single kind of mess to clean, and a language/API small enough to fit in the head. Now, we are talking for several kind of languages, APIs and paradigms intermixed all around a project (like for example, mix a ORM, Sql, OO, not only python+js+html+css)

Re: What ORMs have taught me: just learn SQL

#218

Post by mamcx »

makmanalp wrote: post #79
mamcx wrote: post #56

For long time, I have no idea that SQL & OO are not friends. I work in a language where such problems don't exist. And it have nice ways to move data between tables and data structures and objects (For example: SELECT..INTO Array NAME). Is FoxPro. Even the stored procedures were foxpro, all along the stack, from UI to inner DB actions. Is a shame that this kind of programming is "lost" today. This is kinda like work…

You know, I remember visual foxpro coming with microsoft visual studio 6.0, and not really understanding much about it. Care to expand upon what made it awesome? I found some stuff here but it doesn't really explain it well: http://www.foxprohistory.org/articles_4.htm

It shine for business apps (aka: the most common of it).

Is more "batteries included" than python. You can do a full app with just Fox, because it include reporting, database, database designer, form builder, menu builder, OO, procedural, SQL, a really good grid. Seriously, I have only know of 2 great grid controls: The VFP one and the DevExpress for Delphi one.

Have a truly good grid was something damm useful. Manipulate data was easy, you could make a app only around the grid!

But all of this included.

And this could be made to work for the end user. So, your reports could be designed by a end-user. Or build forms. Or menus. Similar to lisp where everything are the "()" in fox everything was a table. A form? a table. A report? a table. A class? a table (if designed visually. You can make everything in text like a regular program too).

You can make dynamic the whole app if the developer learn to use the language fully.

Was also relatively easy to grasp. A power user could work on fox with a dozen of commands (and remember: The grid, like a excel spreadsheet, was enough for a lot of task), but in contrast with acces, you can "upgrade" the mess to be more professional.

Is simple for a programmer, too. My first programming class was with foxpro, and at the end of it we have made a FULL app with menus, forms, tables, reports and mini programs.

The closet thing today? Perhaps the django admin, but it is far less powerful and constrained.

The weak of fox was that MS prefer to push Acces (and both fight for the same kind of user), it neglect the DB engine pushing for sql server and, well, neglect the language.

Re: What ORMs have taught me: just learn SQL

#219

Post by lukaseder »

drbawb wrote: post #200
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…

> To be honest I'm not entirely sold that these DSLs are what I'd consider "strongly typed."

Absolutely! They're "quite" typesafe, much more than string-based SQL. Much less than actual compiled stored procedures.

> As an example: jOOq will happily let you write an update query targeting a table bound to an alias.

Yes, that currently cannot be detected.

> Then there's the issue of the dialects letting you use features the RDBMS doesn't support

That will be addressed in the near future when we implement an API "preprocessor" that will effectively remove all parts of the API that are not supported by your given dialect. We'll also distinguish between native support and emulated support, if this strictness matters in your application.

The relevant issue is here: https://github.com/jOOQ/jOOQ/issues/720

> In practice I've just never seen that to be true.

True, but if you need "write once, run anywhere", you're probably much better off with a headstart than if you started from scratch.

Re: What ORMs have taught me: just learn SQL

#220

Post by Zergy »

estebank wrote: post #172
Joeri wrote: post #154

Earlier quoted context omitted.

Writing sql by hand doesn't have to mean you abandon things like autocomplete and automatic highlighting of typo's. SQL can be inspected by a proper ide just like any other language.

To be fair, SQL has a syntax that is hard to provide (for example) autocompletion for, as the table comes after the fields, and the field names can be ambiguous.

It seems this would be bypassed by letting the IDE hit the DB to fetch table information.
Post reply on HN