Goodbye MongoDB, Hello PostgreSQL • Hacker News Board

Re: Goodbye MongoDB, Hello PostgreSQL

#371

Post by collyw » Wed, Mar 11, 2015, 4:52 PM UTC

dustingetz wrote: post #229

bigpeopleareold wrote: post #124

Earlier quoted context omitted.

I once agreed with this, but now I don't. I just want to write SQL (dammit!). I can never, ever remember the intricacies of the Sequel API or any one of these query builder APIs. I am always looking up something that is rather trivial because I am thinking in SQL, the language, and always have to convert back to Ruby or whatever language I am working in. CTEs and SQL functions in PostgreSQL strike a good balance in t…

Orms are not about syntax, they're about things you can't natively "think about" in SQL, like inheritance, composition, references and graph navigation

I prefer them for taking away the grunt work of needing to code updates and inserts for everything.

Re: Goodbye MongoDB, Hello PostgreSQL

#372

Post by collyw » Wed, Mar 11, 2015, 5:08 PM UTC

mbreese wrote: post #137

YorickPeterse wrote: post #85

Earlier quoted context omitted.

It's not so much about not wanting to write/understand SQL (both are still very much required), but about composability. If you want to re-use bits of a SQL query written as a string literal your only option is string concatention or using some kind of string builder/template system. In both cases there's little validation of the query's correctness (syntax wise) until you actually run it. While I agree that many ORM…

How often do you really reuse bits of a SQL query? SQL is the language that an ORM will generate. The question is, how much work is done to avoid using SQL and is it worth it? The only time that I've found ORMs useful for composing queries when I have to dynamically create a query at runtime based upon user input. And even in that case, today, I'd probably still just concatenate strings for a proper SQL statement.

I think there is a problem of tooling in SQL, which means it is difficult to reuse it.

I spent the other day writing a big query, joining about 10 tables. So usually I start by joining the two main ones. Check that gives me the results I want. Add in another table or conditions, check again. Repeat until all tables are joined into the query and conditions are added.

Then I noticed the results of my GROUP_CONCAT were not as I expected. I had a couple of suspect joins that I tried removing. Same problem. In these situations, it is often easier to go through the same process from scratch of adding in one table at a time and ensure that it is working.

We need some kind of unit test equivalent for SQL.

Re: Goodbye MongoDB, Hello PostgreSQL

#373

Post by xgil » Wed, Mar 11, 2015, 5:48 PM UTC

I also keep reading about how bad is MongoDB for lots of projects. I don't think MongoDB is worthless, but from my experience I also have concerns about it.

If so many people migrate to other technologies, why is MongoDB still so popular? Have a look at http://db-engines.com/en/ranking_trend, MongoDB has just passed PostgreSQL

Re: Goodbye MongoDB, Hello PostgreSQL

#374

Post by pkolaczk » Wed, Mar 11, 2015, 6:19 PM UTC

Cakez0r wrote: post #273

michaelochurch wrote: post #167

My aversion to NoSQLs is derived from the readiness with which uninformed people dive into them, integrate them with their products, and create a web of complexity around something that should ideally be boring and reliable: the database. There are so many things that I've heard you "can't do in SQL" that are false, at least pertaining to Postgres. Semi-structured data, full-text search, "web scale" programming, geog…

Agree with all of your points. The thing that perhaps annoys me the most is that people seem to assume that nosql is magically fast and that sql is slow... Sure, a key-value pair lookup runs like shit off a shovel, but as soon as you want to run anything more complex than that, it's likely faster with a relational database and a few indexes... Usually the same people that think running an application on a cloud platf…

NoSQL is not a well-defined term - there are huge differences between various solutions all commonly called "NoSQL", so the comparison to NoSQL is bogus. There are different NoSQL things designed with different use-cases in mind. On one side you'll have things like MongoDB, which is easy to set up, but doesn't really scale once your dataset grows out of memory, on the other side things like Cassandra which are unmatched by any RDBMS in terms of performance, scalability and availability on real-time, transactional workloads. Of course, your RDBMS of choice might be better at complex JOINs than Cassandra (which doesn't have JOINS at all, btw), but will it stand the chance at performace competition with Apache Spark or Hadoop? I don't think so.

Re: Goodbye MongoDB, Hello PostgreSQL

#375

Post by djur » Wed, Mar 11, 2015, 7:51 PM UTC

nknighthb wrote: post #328

djur wrote: post #325

Earlier quoted context omitted.

The psuedocode in question happens to be syntactically valid Ruby. If the goal was to demonstrate how simple query generation could be without abstraction layers like Sequel, it is a valid criticism to note that the example given is eliding a necessary feature. Especially since helping avoid injections is one of the primary advantages of such an abstraction layer. The original argument was that if one could "just wri…

> The psuedocode in question happens to be syntactically valid Ruby. So is everything else anyone types, code-like or not. He even said "Note how I deliberately shuffled the order and didn't bother with escaping.". The response was flippant, intelligence-insulting, and obviously the result of failing to read thoroughly. And speaking of intelligence-insulting, we all know you can run raw SQL through Sequel. You're not…

I said the code snippet looked like raw interpolation to me, and I asked how it could be made not vulnerable to injection. It was an honest observation and a genuine question. No flippancy was involved. You're free to think I'm an idiot, but you are the one being insulting and combative here.

Re: Goodbye MongoDB, Hello PostgreSQL

#376

Post by clebio » Wed, Mar 11, 2015, 8:04 PM UTC

meistro wrote: post #75

jgrahamc wrote: post #13

As a greying developer I am most amused by people discovering that 'old' technologies like SQL databases work really well. The only useful piece of advice I can give a younger developer is... be careful when drinking the newtech koolaid. And one more thing: star = Sequel.lit('*') User.select(:locale) .select_append { count(star).as(:amount) } .select_append { ((count(star) / sum(count(star)).over) * 100.0).as(:percen…

You should learn SQL and understand relational databases. But using an abstraction (ORM) to cover 80% of the use cases is much better than writing tons of boilerplate SQL. http://java.dzone.com/articles/martin-fowler-orm-hate

Thank you. I searched to see if his article had been mentioned, and glad to find you did. The link he gives to Ted Neward's post is gone, but I found it in the Wayback Machine: https://web.archive.org/web/20141205230114/http://blogs.tedn...

Seems like this thread has gone down a very old, well worn rabbit hole.

Re: Goodbye MongoDB, Hello PostgreSQL

#377

Post by djur » Wed, Mar 11, 2015, 8:07 PM UTC

moe wrote: post #345

djur wrote: post #327

Earlier quoted context omitted.

You don't have to pass a block to select_append. With Sequel, you can do this: # db is a Sequel::Database s = db[:foobars] s = s.select Sequel.lit("max(id) as best_id") s = s.select_append Sequel.lit("count(*) / sum(count(*)) * 100 as percentage") s.sql # ==> "SELECT max(id) as best_id, count(*) / sum(count(*)) * 100 as percentage FROM `foobars`" The only real difference here is Sequel.lit, which is needed for securi…

The only real difference here is Sequel.lit Well, no. I repeat: The real difference is that most people can't write even this utterly trivial snippet without studying the Sequel documentation first. Now what if I want a WHERE-clause? Do I have to use s.where? Or s.select_append("where ...")? What if I need to combine them with AND/OR? It's not ok that we have to think about all this boilerplate that has nothing to do…

I'm skeptical that it's possible to assemble a working SQL query of much complexity unambiguously from an arbitrarily-ordered set of clauses. CTEs, subselects, and boolean conditions would require a nesting construct. Boolean conditions, order clauses, and some joins need to be assembled in a meaningful order. Eventually you're going to find a level of complexity in the query where it's necessary for a developer to read the documentation, either to figure out the API or to understand the method the library uses to stitch queries together.

There might be a sweet spot yet unreached in terms of allowing developers to work with SQL on top of as minimal a native binding as possible. I'm certainly not claiming that Sequel/SQLAlchemy/etc. is at or near that sweet spot. It might be necessary to use a language with language-level support (or macros) to really reach it.

Re: Goodbye MongoDB, Hello PostgreSQL

#378

Post by rewqfdsa » Wed, Mar 11, 2015, 10:39 PM UTC

Silhouette wrote: post #287

threeseed wrote: post #251

Earlier quoted context omitted.

You sound like someone who thinks we thinks we should still be using Cobol and IBM Series mainframes. Technology changes. It improves. It gets faster, easier and more responsive to business requirements. If you don't embrace change in the IT industry then get out. Because you simply won't survive.

Technology changes. Yes. It improves. Sometimes. It gets faster, easier and more responsive to business requirements. Occasionally. There are a lot of ideas in technology that are widely accepted, particularly by younger, less experienced generations, as being The Best Way to do things. However, if challenged, most of those people don't really know why they believe that. They have never personally seen any hard evide…

I've noticed this phenomenon as well, and it's very frustrating. Let's add async-and-future-everything to the list as well. I honestly don't understand how to convince people to make an honest assessment of the alternatives, especially when they're unable to articulate what exactly they're purchasing with the complexity they're adding.

Will our profession ever stop being fad-driven?

Re: Goodbye MongoDB, Hello PostgreSQL

#379

Post by encoderer » Thu, Mar 12, 2015, 12:53 AM UTC

moe wrote: post #259

encoderer wrote: post #255

Earlier quoted context omitted.

> Why is there no ORM that works like this? Because you're only showing a query builder, the "relational" not the "object mapper". From an OOD point of view, if the end result of that query will be Product instances, why am I using a Select object to create them and why is it having to do some sort of string parsing to determine the objects I'm loading?

if the end result of that query will be Product instances, why am I using a Select object to create them Because we can just infer the type to be returned via the FROM-clause of the query. and why is it having to do some sort of string parsing to determine the objects I'm loading? Because, to cite the immortal Larry Wall: The computer should be doing the hard work. That's what it's paid to do, after all. -- Larry Wal…

How often do you have a need for non-engineers to be able to alter your database queries? Why is this a requirement or even desirable? There seem to be few cases where a query change needs to be made in isolation, without having to touch any of the downstream consumers of that data.

So if we remove this non-requirement of "anybody can write it", we're back to my original question: Ok, we the coders know we want a Product instance, but to build it we create a Select instance and then ask a library do so some sort of parsing?

Possibly no ORM exists that meet your criteria because there are issues with the design goals you're trying to achieve?

Re: Goodbye MongoDB, Hello PostgreSQL

#380

Post by johan_larson » Thu, Mar 12, 2015, 1:00 AM UTC

collyw wrote: post #372

mbreese wrote: post #137

Earlier quoted context omitted.

How often do you really reuse bits of a SQL query? SQL is the language that an ORM will generate. The question is, how much work is done to avoid using SQL and is it worth it? The only time that I've found ORMs useful for composing queries when I have to dynamically create a query at runtime based upon user input. And even in that case, today, I'd probably still just concatenate strings for a proper SQL statement.

I think there is a problem of tooling in SQL, which means it is difficult to reuse it. I spent the other day writing a big query, joining about 10 tables. So usually I start by joining the two main ones. Check that gives me the results I want. Add in another table or conditions, check again. Repeat until all tables are joined into the query and conditions are added. Then I noticed the results of my GROUP_CONCAT were…

Mature relational databases support views, pre-built queries that developers can define and other developers can then use as "tables" in higher-level queries, allowing some code reuse. This has been around a long time.