Goodbye MongoDB, Hello PostgreSQL • Hacker News Board

Re: Goodbye MongoDB, Hello PostgreSQL

#211

Post by rdtsc » Tue, Mar 10, 2015, 9:22 PM UTC

hammerandtongs wrote: post #145

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…

You've formulated the composibility reasonably in that sentence but it's important to remember that is not the only problem the world faces in data manipulation. I think the irony is that much of the problems of sql are related to a lack of tooling. The lack of tooling is result of large numbers of devs being distracted by ORM which moves that particular problem into the language ide/editor. Essentially we lack great…

> Essentially we lack great *.sql editing, ide, macro, refactoring because of the lack of focus on sql itself.

Except that my other tools are just an editor like emacs and makefiles. So now to be productive I have to have and ide for sql, learn sql, learn macros.

Interesting that SQL came first. If it was so easy and obvious to use it we would not have seen any ORMs by now. But every other project that uses SQL databases ends up with some kind of ORM.

One of the reason is probably because there is an impedance mismatch. Code is already written dealing with objects, functions, pointers, data. But when it talks to the database it has to talk via a command line string interface so to speak.

There is another type of impedance mismatch and that is learning a new language. One can argue SQL is there to keep & manipulate the most precious things you have -- data, so one should spend time learning it well. But the other point is, SQL is there to keep & manipulate the most precious things you -- data, so don't mess with it if you don't know SQL very well. Let someone else learn it (the author of the ORM).

I am not arguing for one point or the other, just kind saying how it is interesting how we have been going in a circle the last 15 years or so.

Re: Goodbye MongoDB, Hello PostgreSQL

#212

Post by emodendroket » Tue, Mar 10, 2015, 9:23 PM UTC

hammerandtongs wrote: post #145

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…

You've formulated the composibility reasonably in that sentence but it's important to remember that is not the only problem the world faces in data manipulation. I think the irony is that much of the problems of sql are related to a lack of tooling. The lack of tooling is result of large numbers of devs being distracted by ORM which moves that particular problem into the language ide/editor. Essentially we lack great…

You mean something like this? https://www.jetbrains.com/dbe/

Re: Goodbye MongoDB, Hello PostgreSQL

#213

Post by nawitus » Tue, Mar 10, 2015, 9:24 PM UTC

SnacksOnAPlane wrote: post #88

warble wrote: post #58

Earlier quoted context omitted.

I agree, I've read a few 'screw Mongo, I'm going to SQL' type blogs, it really seems like they either didn't grasp how to architect Mongo correctly and tried to do things that don't work well with it, or they had a problem that was better solved by SQL in the first place. SQL does many things very well that noSQL stuff won't. Also vice-versa.

I honestly don't think I've ever seen a valid use case for Mongo. If you're going to query your data, you have to know what fields you're looking for, right? So why not create a schema that has those fields?

You can know a field or fields of the data while leaving the possibility of extra fields that you don't need to know. In practice if you're making a web application and dealing with JSON, it's typically very easy to work with JSON-based databases instead of using serialization functions. You can enforce the schema with various tools at the application level when needed.

MongoDB also has some performance benefits over many traditional databases, and has "native" support for binary data.

I personally prefer CouchDB over MongoDB for most application that are well suited to use a "NoSQL" database, but MongoDB is sometimes decent too.

Re: Goodbye MongoDB, Hello PostgreSQL

#214

Post by rdtsc » Tue, Mar 10, 2015, 9:26 PM UTC

mbreese wrote: post #162

ad_hominem wrote: post #155

Earlier quoted context omitted.

This is a query builder. If you're using your programming language to dynamically assemble the SQL statement fragments at runtime, then you're using a query builder regardless of if it is a library with a fancy DSL that assembles an in-memory SQL AST or some kind of ad-hoc string concatenation you rolled yourself like this. The question I'm asking is pointed towards the people who are implying that you can "just use…

Yes, logically, it's a query builder. But it's one that doesn't force any extra dependencies in my project and is very easy to troubleshoot. Seriously, how is this different from "just use SQL". SQL was never really intended to be used by itself (except for manually typing queries into a console). Yes, what I wrote is code that writes code, but it's far different from a larger ORM or querying framework.

> SQL was never really intended to be used by itself.

Hmm I thought that is how it was intended to be used? If it didn't, it would look like datalog, lisp or some binary protocol with prefixed lengths and whatnot.

It was intended to be typed in by analysts at a console. Who would then print the report on the dot matrix printer and mail to the headquarters or something of that sort.

Re: Goodbye MongoDB, Hello PostgreSQL

#215

Post by nawitus » Tue, Mar 10, 2015, 9:28 PM UTC

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…

Note that just because something is possible with PostgreSQL doesn't mean it's as easy to do with PostgreSQL than with an alternative database. The question could be rephrased as "why use PostgreSQL when you can do it in x?".

Re: Goodbye MongoDB, Hello PostgreSQL

#216

Post by bigpeopleareold » Tue, Mar 10, 2015, 9:29 PM UTC

ad_hominem wrote: post #135

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…

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…

I'll deal with the problem when I have to.

However, the point is that I'd rather use SQL, it's simple as that.

Re: Goodbye MongoDB, Hello PostgreSQL

#217

Post by bigpeopleareold » Tue, Mar 10, 2015, 9:31 PM UTC

moe wrote: post #171

ivanr wrote: post #168

Earlier quoted context omitted.

There is. MyBatis, which is a minimal ORM that aims to keep you as close to SQL as possible, has support for dynamic SQL: https://mybatis.github.io/mybatis-3/dynamic-sql.html Edit [responding to moe, below]: that's a matter of taste. I prefer to have my SQL _outside_ my code. If I have to write a little XML to make it happen, so be it. Additionally, unlike your example, a strict separation of SQL and data ensures SQL…

MyBatis Does MyBatis have an API similar to what I outlined above? Because the page that you link to shows a horror that I can't even begin to describe: SELECT * FROM BLOG WHERE state = ‘ACTIVE’ AND title like #{title} SQL mixed with proprietary XML soup? Talk about combining the worst from all worlds...

Get rid of the XML and you have a good start, I'd say.

Re: Goodbye MongoDB, Hello PostgreSQL

#218

Post by rmrfrmrf » Tue, Mar 10, 2015, 9:33 PM UTC

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…

I guarantee you're not designing your databases correctly if they only take you 10 minutes and only a CREATE TABLE statement every few months.

Re: Goodbye MongoDB, Hello PostgreSQL

#219

Post by bigpeopleareold » Tue, Mar 10, 2015, 9:35 PM UTC

hammerandtongs wrote: post #145

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…

You've formulated the composibility reasonably in that sentence but it's important to remember that is not the only problem the world faces in data manipulation. I think the irony is that much of the problems of sql are related to a lack of tooling. The lack of tooling is result of large numbers of devs being distracted by ORM which moves that particular problem into the language ide/editor. Essentially we lack great…

I am rather fond of Sqitch and pgTAP as tools for database development.

Re: Goodbye MongoDB, Hello PostgreSQL

#220

Post by aidos » Tue, Mar 10, 2015, 9:37 PM UTC

moe wrote: post #180

zo1 wrote: post #170

Earlier quoted context omitted.

Python SQLAlchemy works a bit like this, if you use it that way: query = session.query(Products) if limit: query = query.limit(limit_val) if offset: query = query.offset(offset_val) Sort is a bit tricky, but as I'm a noob at SQL Alchemy, I had some not-so-pretty boilerplate code implemented for decorating queries with custom sorts. But essentially it boiled down to: if sort_type == SORT_REVERSE_CONST: sort_obj = sort…

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 language, using strings. They let you post optimise your loading strategies.

You might want "exactly like that", but it's not going to be as flexible as a chainable wrapper system.

Hell, I could probably give you pretty close to that in python with a little work, but it's not something I'd want to use myself.

I get it. I like working in SQL too. I know it really really well, and I cringe when I see developers writing totally sub-optimal code because they don't understand the relational data model.

But there are other ways to do things, and what you're describing doesn't give you much more than raw SQL, so why not just use raw SQL? You've added some syntactic sugar that, in my language (Python), would be a bit of a horror show (strings access local variables implicitly, no thanks). What else do you gain?

This is more verbose, granted:

    q = session.query(Products)
    if title:
        q = q.filter(Products.title.like(title))
    if price_range:
        q = q.filter(Products.price.lte(price_range))
    if offset:
        q = q.offset(offset)
    if limit:
        q = q.limit(limit)
    products = q.all()

But then you get more stuff for free, like drilling down into the other tables:

    p = products[0]
    p.supplier.contracts[0]

But that's rubbish, because you'll be loading in a really inefficient way. That's ok though, tell the system how you're going to want to load the additional data.

    q = q.options(
        joinedload('supplier').
        subqueryload('contracts')
    )

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.

Use whatever makes sense for your use-case. Don't limit yourself because you'd have to learn something new. Honestly, before using SQLAlchemy I mostly felt the same as you do. Many ORMs get in the way, but that's not really a problem intrinsic to ORMs.