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

Goodbye MongoDB, Hello PostgreSQL

developer.olery.com

Post reply on HN
Print view View original

341–350 of 388 posts

Re: Goodbye MongoDB, Hello PostgreSQL

#341

Post by cmdkeen »

moe wrote: post #149
ad_hominem wrote: post #135

Earlier quoted context omitted.

Let's say you have a product search screen in your application. There's a text field for filtering on product title (WHERE title LIKE), one for filtering on UPC (WHERE upc LIKE), a couple range filters for min/max prices (WHERE price =), and then on the results screen the user sort on a few different columns (ORDER BY) as well as paginate and set number of results per-page (LIMIT + OFFSET). How exactly are you going…

How exactly are you going to "just write SQL" if the actual query statement needs to change based on the user input? How about something like this: s = Select.new s.add "WHERE title LIKE #{title}" if title s.add "WHERE price Note how I deliberately shuffled the order and didn't bother with escaping. Also note how anyone who knows SQL could immediately work with this, learning curve: 5 seconds. Why is there no ORM tha…

One of the things I like about EntityFramework is that you write LINQ, which odds are these days every C# dev should be familiar with. So, standby a few gotchas (which SQL has as well), the code you write to query an in memory collection is the same code you write to query the database. This also makes it fairly easy to test because you can create in memory representations of your data and check you filters, projections, orders etc.

Re: Goodbye MongoDB, Hello PostgreSQL

#342

Post by hueving »

aidos wrote: post #220
moe wrote: post #180

Earlier quoted context omitted.

Python SQLAlchemy works a bit like this All ORMs work "a bit like this". I don't want "a bit like this". I want exactly like this. Because with my proposed interface I could be productive immediately and permanently. I would never have to refer to any documentation. Not once. With every other ORM this is a pipe dream. Their "fancy" chainable wrapper-APIs are nothing but a ball on a chain.

Well, no, they're more than a "ball on a chain" - they're a tradeoff - like everything in software. They give you the ability to manipulate the query in interesting ways at any point before you execute it. They let you join different queries together, built by different parts of the system, in a safe way. They let you work with the native language you're working in instead of having to construct clauses in a foreign…

>See what I got with my "ball and chain"? Turns out it was actually the anchor for the whole boat. Sure, you have to learn a new syntax, sure, it's not sql, but that doesn't make it bad or wrong.

If you are fine with the new debugging this entails. Take the joined load example you gave. Almost every time I encounter SQLAlchemy code, the author didn't understand the lazy joining semantics and every attribute access on each record is emitting a new query and you now have O(n) queries. It's not obvious that is happening until it bites you once you have thousands of records.

For another example, I can count on one hand the number of developers I have encountered that can understand the expiration of the objects in the session or what the engine builder stuff is doing in the boilerplate they paste in to get it to work. It always requires experimentation, a trip back to the SQLAlchemy docs, and then finally logging all SQL queries to see if it's finally emitting the optimal queries with the transaction guarantees you are looking for.

My gripe about SQLAlchemy is that it separates you too much from what is happening underneath.

Re: Goodbye MongoDB, Hello PostgreSQL

#343

Post by hueving »

xyzzy123 wrote: post #77
distantsounds wrote: post #60

Earlier quoted context omitted.

Really, you can't spend the 10 minutes designing a table structure in a SQL database? And now you have to spend months re-inventing the wheel because you wanted an easy out? This post reflects on developers being lazy, instead of doing it right the first time around. Oh no, you have to log in to the db and run a CREATE TABLE statement every few months when you need to scale. Cry some more. And even then, 'lazy' is su…

Actually, I think that's a great way to think about it: NoSQL is the "dynamic typing" of the database world. Put another way, it's like "what? you couldn't spend 10 minutes declaring types everywhere?" - yeah, it's less robust, yet dynamically typed languages remain popular. My excuse: When I'm just past the mock stage, and still playing with what UI functionality should be, sometimes I just want to get some JSON per…

The difference there is that you enter types for thousands of lines of code. OTOH a schema is changed relatively infrequently. Maybe a few times a day during really experimental stuff.

Re: Goodbye MongoDB, Hello PostgreSQL

#344

Post by cmdkeen »

Someone1234 wrote: post #118
mattmanser wrote: post #91

Earlier quoted context omitted.

No-one ever migrates their db unless they really are in some sort of serious shit. I'll also guess you've never done it because the EF + MySQL = a world of pain. MySQL does not like nested queries, the EF uses them like they're crack, one of the reasons the SQL it produces is so hard to read. I'd guess that the EF + [any db that's not MS SQL] probably suffers from similar "holy shit why did the DB just die, oh it's t…

> No-one ever migrates their db unless they really are in some sort of serious shit. I've been involved in several migrations. All of which were planned over years in some cases. There was no "serious shit" at any point in the process. One popular reason for migrations is licensing costs (e.g. escaping Oracle or IBM), or due to mergers where the other company had a different database system and they wanted to consoli…

Indeed for web development tools like MiniProfiler give you access to the queries in the page along with execution time and duplicate query detection. We use it on Oracle with the Devart provider and it's pretty snappy even with the somewhat bonkers looking queries it sometimes generates.

Because in my office the alternative is PL/SQL functions that dynamically build WHERE clauses, and I'll choose EF over that any day.

Re: Goodbye MongoDB, Hello PostgreSQL

#345

Post by moe »

djur wrote: post #327
moe wrote: post #259

Earlier quoted context omitted.

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…

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 with our actual query.

We shouldn't have to translate our perfectly unambiguous request from english (SQL) to klingon (Sequel API) in order to have it processed.

Re: Goodbye MongoDB, Hello PostgreSQL

#346

Post by babuskov »

ad_hominem wrote: post #226
joshuahutt wrote: post #179

Earlier quoted context omitted.

I would write a SQL function that takes all of those as optional parameters and includes a lot of these: WHERE (_title IS NULL OR title LIKE _title) AND (_minPrice IS NULL OR price > _minPrice) AND (_maxPrice IS NULL OR price

This actually answers the question, although I would imagine the ORDER BY handling will look pretty messy (CASE statement perhaps?). And I don't know what it would look like if the requirements changed to allow ordering by multiple columns with different possible sort directions... that might get back into dynamic SQL using a RETURN QUERY EXECUTE type of thing, which is basically using a query builder in your query l…

Most SQL engines support ordering by column number.

select c1, c2, c3, ... order by 2;

would order by "c2"

Re: Goodbye MongoDB, Hello PostgreSQL

#347

Post by rimantas »

distantsounds wrote: post #60
limelight wrote: post #44

This post reflects an interesting technical narrative of companies switching off MongoDB to more traditional relational databases as they grow. Importantly, I don't think that's an indictment of MongoDB. Instead, it highlights the key advantages of NoSQL: ease of use and rapid iteration. When you're first working on a project, MongoDB is very easy to slap in. You don't even have to create tables/collections. As you i…

Really, you can't spend the 10 minutes designing a table structure in a SQL database? And now you have to spend months re-inventing the wheel because you wanted an easy out? This post reflects on developers being lazy, instead of doing it right the first time around. Oh no, you have to log in to the db and run a CREATE TABLE statement every few months when you need to scale. Cry some more. And even then, 'lazy' is su…

In some cases you don't even have to log into anything, just type one line to create migration and another line to run it.

Re: Goodbye MongoDB, Hello PostgreSQL

#348

Post by grey-area »

moe wrote: post #265
djur wrote: post #250

Earlier quoted context omitted.

"WHERE price <= #{price_range}" looks like raw interpolation to me. How do you make that not vulnerable to injection unless you're escaping all variables that might be used in a query?

How do you make that not vulnerable to injection unless you're escaping all variables that might be used in a query? It was just a mockup. But you are right, in reality it would end up looking more like this (and use custom interpolation for escaping): s.add "WHERE foo > $(bar)" Likewise a smart syntax for clause combining (AND/OR) and some kind of nesting would probably be needed. I believe both of these problems sh…

You still need to supply the local variables to your ORM-like so that it can perform interpolation into the string (a feature which is actually best done by the database driver, NOT your ORM-like). Perhaps we can suggest this syntax:

s.add "WHERE foo > ?", bar

Or even this:

s.Where "foo > ?", bar

At that point, you've reinvented ActiveRecord or hundreds of other query builders, it also avoids you having to remember the ordering rules of sql as to which part of the query must be built first. There's a reason these query builders have converged on similar syntax.

I see where you're coming from - you don't want to learn two sets of syntax, but many query builders nowadays are very well thought out, and have a simple syntax which just echoes SQL while avoiding sqli and statement order issues, they also usually have an sql() option which lets you just send SQL if you wish for a complex query.

There are very good reasons people keep inventing layers over SQL:

They help avoid sqli

They centralise db access so you can optimise in one place by say adding caching layers to queries

They mean you don't have to deal with the vagaries of SQL for simple queries unless you want or need to

Re: Goodbye MongoDB, Hello PostgreSQL

#349

Post by aidos »

hueving wrote: post #342
aidos wrote: post #220

Earlier quoted context omitted.

Well, no, they're more than a "ball on a chain" - they're a tradeoff - like everything in software. They give you the ability to manipulate the query in interesting ways at any point before you execute it. They let you join different queries together, built by different parts of the system, in a safe way. They let you work with the native language you're working in instead of having to construct clauses in a foreign…

>See what I got with my "ball and chain"? Turns out it was actually the anchor for the whole boat. Sure, you have to learn a new syntax, sure, it's not sql, but that doesn't make it bad or wrong. If you are fine with the new debugging this entails. Take the joined load example you gave. Almost every time I encounter SQLAlchemy code, the author didn't understand the lazy joining semantics and every attribute access on…

Yup. I'll give you most of that - there's a pretty big learning curve with SQLAlchemy.

But, taking the lazy joining example. Someone didn't understand how it worked and now you have to fix it, which is probably as simple as changing that loading strategy to something more suitable for the way you're using the data.

Let's rewind to the late 90s. If I wanted two tables of data to write into the page in an interlaced fashion I would look at how wide they were. I'd make a decision about using two queries or using one larger join. The two query approach was sometimes required due to the size of the data, but it meant cryptic output logic to track positions of cursors relative to each other. The single joined query was simpler to deal with, but still required tracking a last_parent_id so you could swap during the interlacing.

Other developers (those same ones that didn't understand lazy joining) would loop over the first query, running extra queries in the loop (I saw this a lot). Same bad performance as the lazy join.

When you discovered this issue in the code it was a total pain to fix. You're talking about rewriting whole load of code to the point of being unrecognisable from the code you started with.

Contrast that with how easy it is to fix in the SQLAlchemy case. I mostly don't worry about loading strategies now until I'm deep in development. Something's running a bit slow, take a look at some logs, tweak a couple of things and you're golden again. That's such a powerful abstraction.

Regarding the config of the engines etc, again, it's something you need to learn. But really, someone's starting an application from scratch and they just want to dump some code in to handle all their db interactions, but they don't want to know how it works? That's on them, either learn it or use the sensible defaults (in, for example, flask-sqlalchemy).

SQLAlchemy ORM separates you from what's underneath, but it's an ORM, that's kind of the point. If you need to be closer to the metal, use SQLAlchemy Core.

Re: Goodbye MongoDB, Hello PostgreSQL

#350

Post by qooleot »

grey-area wrote: post #348
moe wrote: post #265

Earlier quoted context omitted.

How do you make that not vulnerable to injection unless you're escaping all variables that might be used in a query? It was just a mockup. But you are right, in reality it would end up looking more like this (and use custom interpolation for escaping): s.add "WHERE foo > $(bar)" Likewise a smart syntax for clause combining (AND/OR) and some kind of nesting would probably be needed. I believe both of these problems sh…

You still need to supply the local variables to your ORM-like so that it can perform interpolation into the string (a feature which is actually best done by the database driver, NOT your ORM-like). Perhaps we can suggest this syntax: s.add "WHERE foo > ?", bar Or even this: s.Where "foo > ?", bar At that point, you've reinvented ActiveRecord or hundreds of other query builders, it also avoids you having to remember t…

"You still need to supply the local variables to your ORM"

That can be done automatically in many languages. At least in the sense you can do:

"where value=$(bar)"

and not:

"where value = ?", bar

OpenACS was an old web framework for the Tcl language, but it even had that 20 years ago!. The company backing it failed in the .com era, and the lack of types was a turnoff for many - but it made SQL much much more readable. And of course the queries were turned into prepared statements. A query looked like this:

select foo from table where age>:min_age and name != :user_name

Things like this can also be done in node.js (disclaimer: I wrote the blog post below):

http://ivc.com/blog/better-sql-strings-in-node-js/

Post reply on HN