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

151–160 of 388 posts

Re: Goodbye MongoDB, Hello PostgreSQL

#151

Post by jballanc »

YorickPeterse wrote: post #85
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…

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…

I'd argue it's less about composability, and more about laziness (in the computational sense). As pointed out elsewhere, if you have all of the information needed to generate a dynamic query, it's often not a huge leap from an ORM to composing strings (especially given how relatively flexible SQL syntax can be).

However, sometimes I want one part of my program to be responsible for one bit of a query, and a separate piece to be responsible for something else. To take a trivial example, say I want one object/function to be responsible for doing the right sorting, another to be responsible for any pagination, and yet another to be able to group results when needed. In that case, having a programmatic abstraction over a query (whether it be an object or a datatype, doesn't matter) can be very useful.

Re: Goodbye MongoDB, Hello PostgreSQL

#152

Post by falcolas »

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…

    query = sprintf("select count(*) from data where %s = ?", column)
    results = sql.execute(query, filter_value);
So long as you leave the "value" portion of the query as a '?' (or %s, or whatever your connector requires) and don't use direct user input for the column names, you're still safe from SQL injection.

Re: Goodbye MongoDB, Hello PostgreSQL

#153

Post by Jweb_Guru »

tannerj wrote: post #132
pikzen wrote: post #122

Earlier quoted context omitted.

> Is MongoDB useless as a database, or are people being bitten for thinking it's a silver bullet and throwing it at every problem? A bit of both really. NoSQL databases allow for rapid prototyping, as do weakly and dynamically typed languages. It's amazing if you want to just get a product out of the door. NoSQL is the short term answer. And MongoDB is the answer if writing your data to /dev/null feels like a good id…

Yea, I'm going to check out PostgreSQL's json storage. >But at the end of the day, what matters is that your product works. With my requirements, I could just write to a flat file and be fine... I seem to like complicating things just enough that I no longer understand how what I'm building works. LOL.

SQLite is great for "I really just want a nicer flat file" use cases.

Re: Goodbye MongoDB, Hello PostgreSQL

#154

Post by rikkus »

YorickPeterse wrote: post #85
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…

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…

Here's an example in C#. Imagine you're querying a database of products (here represented by integers). Users can enter filter parameters - you want to build your query dynamically based upon what they enter. With LINQ, you can do this kind of composing with no effort. You also get to run the same code on any kind of Queryable, so if you feel like doing some of the work in RAM and some using a DB, your query is usually going to be very similar, if not the same.

Yes, you still have to understand what you're querying against and how you should build your queries to make best use, but I'd much rather write this kind of code than try to concatenate SQL.

  IQueryable Source()
  {
    return Enumerable.Range(0, int.MaxValue).AsQueryable();
  }

  class UserFilter
  {
    public bool? EvensOnly { get; set; }
    public int? Minimum { get; set; }
    public int? Maximum { get; set; }
  }

  IEnumerable Search(IQueryable source, int currentPage, int pageSize, UserFilter filter)
  {
    var result = source;

    if (filter.EvensOnly.HasValue && filter.EvensOnly.Value)
    {
      result = result.Where(i => i % 2 == 0);
    }
		
    if (filter.Minimum.HasValue)
    {
      result = result.Where(i => i >= filter.Minimum.Value);
    }
		
    if (filter.Maximum.HasValue)
    {
      result = result.Where(i => i

Re: Goodbye MongoDB, Hello PostgreSQL

#155

Post by ad_hominem »

mbreese wrote: post #143
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 might be over thinking this... something like this might work out just fine. (I wouldn't necessarily do things this way, but rather keep a list of clauses and join them with " AND " to avoid keeping track of the "WHERE"s and "AND"s, but you get the point...) sql = 'SELECT * FROM products' args = [] if title: sql += ' WHERE title LIKE %?%' args.append(title) if upc: if args: sql += ' AND' else: sql += ' WHERE' sql…

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 SQL" as static statements that are not dynamically assembled. Like a static function or prepared statement that takes some parameters, and at runtime you only pass in those parameters - not rejigger the actual SQL statement fragments.

Re: Goodbye MongoDB, Hello PostgreSQL

#156

Post by falcolas »

mbreese wrote: post #143
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 might be over thinking this... something like this might work out just fine. (I wouldn't necessarily do things this way, but rather keep a list of clauses and join them with " AND " to avoid keeping track of the "WHERE"s and "AND"s, but you get the point...) sql = 'SELECT * FROM products' args = [] if title: sql += ' WHERE title LIKE %?%' args.append(title) if upc: if args: sql += ' AND' else: sql += ' WHERE' sql…

I've done this quite a bit as well: string building is pretty well understood, and works remarkably well.

Re: Goodbye MongoDB, Hello PostgreSQL

#157

Post by troels »

esilverberg2 wrote: post #78

The author's assertion that "Another problem with MySQL is that any table modification (e.g. adding a column) will result in the table being locked for both reading and writing. This means that any operation using such a table will have to wait until the modification has completed." is no longer correct as of Mysql 5.6: http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-o... If you specify ALGORITHM=INPLACE,L…

It's not exactly a common operation either, so basing the choice of rdbms on it seems a bit arbitrary.

Re: Goodbye MongoDB, Hello PostgreSQL

#158

Post by rpedela »

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…

That can be solved by writing a SQL function and the user input values are arguments to the function.

Re: Goodbye MongoDB, Hello PostgreSQL

#159

Post by stephenboyd »

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?

Mongo doesn't stop you from using a schema, but you'll have to enforce it in your application code rather than the database itself.

Re: Goodbye MongoDB, Hello PostgreSQL

#160

Post by jimbokun »

mbreese wrote: post #143
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 might be over thinking this... something like this might work out just fine. (I wouldn't necessarily do things this way, but rather keep a list of clauses and join them with " AND " to avoid keeping track of the "WHERE"s and "AND"s, but you get the point...) sql = 'SELECT * FROM products' args = [] if title: sql += ' WHERE title LIKE %?%' args.append(title) if upc: if args: sql += ' AND' else: sql += ' WHERE' sql…

Now how is this an improvement over the Sequel example?

I understand the impulse to "Just write SQL." But in practice, with all the string concatenation needed to generate actual queries, you can't really see what the SQL will be without running all the code in your head anyway.

Post reply on HN