Goodbye MongoDB, Hello PostgreSQL

I'm not a fan of MongoDB, but you don't know what you're talking about.

MongoDB is durable. While it doesn't quite support SQL transactions, it is durable. The data is journaled before being confirmed, and once confirmed will be written to disk. It can be consistent, but this sort of breaks the whole idea of scaling and distributing the load in Mongo, or causes massive performance problems, so that is something to consider when using it. Understanding and adopting eventual consistency is tough, but it's an issue with every distributed database, not just MongoDB.

And you don't "keep your entire data set in memory". You should make sure your indexes fit in memory. Your data can be as large as you like. Most people with terabytes of data in MongoDB don't keep terabytes of RAM in their servers.

Foursquare did NOT keep every single check-in in RAM. They kept an index of them in RAM, sure. But the problem was they had a sharded MongoDB deployment and one of the shards became unbalanced and exceeded the available RAM. If that happens (harder to do these days, but not impossible), it can be very difficult to recover.

MongoDB tries to bridge that gap between NoSQL and SQL. I think the MongoDB folks originally ignored decades of database research when developing MongoDB, but they've been forced to adopt it as the years have passed. Is it an Oracle killer? No. But it can be a useful and productive tool if you understand and apply it appropriately.

You easily could have this in SQL if you designed a poor schema or failed to migrate data from the previous field name.

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 write SQL" one wouldn't need to refer to documentation to become productive, "not once". I don't personally think it's practical to use any database library without reading the documentation, but certainly the moment you get away from "the SQL I type in goes straight to the database" you're going to need documentation to tell you what's going to change.

What's being described here sounds like a library that parses SQL fragments, combines them into whole queries, and sends them off to the database. To implement this this without resorting to simple string concatenation needs an intermediate representation of SQL language components. That intermediate representation is going to look a lot like SQLAlchemy, or Sequel, or Arel. That means that instead of "just writing SQL" you're actually adding an extra layer of abstraction and another chunk of documentation to read.

All that said, the "just use SQL" approach is already fairly well served. Most languages have mature bindings to specific databases as well as at least one ODBC-style API to simplify access to those bindings. Nearly all of these have support for client-prepared statements, which handles the interpolation problem. If you want to build more complex queries on the fly, you can use string concatenation or CTEs or decide to use a SQLAlchemy/Sequel/Arel-type library for just that one case.

And, honestly, you don't need to give up SQL to make use of Sequel. I personally really dislike the query composition API demonstrated at the top of this thread, but you can do useful things with Sequel without it:

    db = Sequel.sqlite
    db.execute "create table foos (id int not null, name text)"
    db.execute "insert into foos (id, name) values (1, 'bar')"
    # or
    db[:foos].insert(id: 2, name: "baz")
    db["select * from foos where id = ?", 1].first # ==> {:id=>1, :name=>"bar"}
    # or
    db[:foos]["id = ?", 2] # ==> {:id=>2, :name=>"baz"}

No need to go any further than raw SQL, but increasing levels of abstraction available if you feel like it.

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 security (any secure DB interface needs to somehow be notified that strings are safe to put into a query without escaping). If I'm writing code that leans heavily on Sequel to build queries, I'll make a private method #sql that is an alias for Sequel.lit.

It's entirely feasible to use Sequel this way. I've used it like that in production. I loathe the query building DSL, myself, but it's strictly optional.

Agreed. Also it should be generating queries based on foreign keys/indexes (including joins).

Others should not be allowed in an interactive app anyway: developer should be warned/prevented using a "batch" query by accident.