4 ms·

> IMO, the reason is that newer developers faced the choice of learning SQL or learning to use something with a Javascript API. The thing I dislike about this

> IMO, the reason is that newer developers faced the choice of learning SQL or learning to use something with a Javascript API. The thing I dislike about this type of comment – although I now notice yours doesn't explicitly say this – is the implication that devs don't like SQL because they're lazy or stupid. Well, sometimes that is probably true! But there are some tasks where you need to build the query dynamically at run time, and for those tasks MongoDB's usual query API, or especially its aggregation pipeline API, are genuinely better than stitching together fragments of SQL in the form of text strings. Injection attacks and inserting commas (but not trailing commas) come to mind as obvious difficulties. For anyone not familiar, just look at how close to being a native Python API pymongo is: pipeline = [ {"$unwind": "$tags"}, {"$group": {"_id": "$tags", "count": {"$sum": 1}}}, {"$sort": SON([("count", -1), ("_id", -1)])} ] result_cursor = db.things.aggregate(pipeline) Of course you could write an SQL query that does this particular job and is probably clearer. But if you need to compose a bunch of operations arbitrarily at runtime then using dicts and lists like this is clearly better. Of course pipelines like this will typically be slow as hell because arbitrary queries, by their nature, cannot take advantage of indices. But sometimes that's OK. We do this in one of our products and it works great. With JSONB and replication enhancements, Postgres is close to wiping out all of MongoDB's advantages. I would love to see a more native-like API like Mongo's aggregation pipeline, even if it's just a wrapper for composing SQL strings. I think that would finish off the job.

mattbreeden 8y ago
> genuinely better than stitching together fragments of SQL in the form of text strings. Injection attacks and inserting commas (but not trailing commas) come to mind as obvious difficulties. You're using the Pymongo library as an example. Someone can just as easily use SQLAlchemy and not have to worry about those things.
funkaster 8y ago
Well... you can also use a modern ORM. I think "stitching ... text strings" is definitively not the way to go when interfacing a SQL database. My go-to ORM is Sequel[1]. I think their API is one of the best I've seen: you can choose to use models, but you can also work directly with "datasets" (tables or views, or queries) and compose them as you like. It's really powerful and simple. [1]: http://sequel.jeremyevans.net/ http://sequel.jeremyevans.net/
aidos 8y ago
I’ll try to avoid a flame war, but since you’re using python, SQLAlchemy allows for composing sql strings.
- raverbashing 8y ago
  Yes, an ORM, not SQL itself.
  - yawaramin 8y ago
    Yes? What's the qualitative difference between using a best-of-breed SQL ORM and the Mongo API?
  - zrail 8y ago
    SQLAlchemy is not an ORM. There’s a companion ORM project if you want it, but it’s not necessary.
  - walrus1066 8y ago
    You can compose queries, using the queryAPI in SQLAlchemy, without touching the ORM.
- quietbritishjim 8y ago
  For it to replace MongoDB's aggregation pipeline, it would need to play nicely with JSONB. Does it do that? This is the thing I'm really missing. For example, if documents in the JSONB column all look roughly like this: { "someArrayField": [ { "key": "steve", "value": 7 }, { "key": "bob", "value": 15 }, ], "someOtherField": [ "whatever" ] } * Can I count the number of entries in someArrayField, summed across all records? * Can I get the per-record mean of the "value" sub-field, summed across all records? * Can I filter by records that have a "someArrayField" entry where "key" is "steve" and "value" is at least 10 (the above record should NOT match)?
  - luhn 8y ago
    Yes, you can! The jsonb_array_elements function is roughly similar to Mongo’s $unwind pipeline op. It explodes a JSON array into a set of rows. From there it’s pretty simple aggregates to achieve what you’re looking for. I was evaluating Mongo a couple months back to solve roughly the same problems. Eventually discovered Postgres already had what I was looking for.
QuinnWilton 8y ago
Elixir's primary database wrapper, Ecto [0], lets you dynamically build queries at runtime, and also isn't an ORM. Here's two examples directly from the docs: # Query all rows in the "users" table, filtering for users whose age is > 18, and selecting their name "users" |> where([u], u.age > 18) |> select([u], u.name) # Build a dynamic query fragment based on some parameters dynamic = false dynamic = if params["is_public"] do dynamic([p], p.is_public or ^dynamic) else dynamic end dynamic = if params["allow_reviewers"] do dynamic([p, a], a.reviewer == true or ^dynamic) else dynamic end from "posts", where: ^dynamic Across all the different means of interacting with a database I have experience with (from full-fledged ORMs like ActiveRecord, to sprocs in ASP.NET), I've found that it offers the best compromise between providing an ergonomic abstraction over the database, and not hiding all of the nitty-gritty details you need to worry about in order to write performant queries or use database-specific features like triggers or window functions. My main point, though, is that you don't need to reach for NoSQL if all you need is a way to compose queries without string interpolation. [0] https://github.com/elixir-ecto/ecto https://github.com/elixir-ecto/ecto
- inferiorhuman 8y ago
  > Across all the different means of interacting with a database I have experience with (from full-fledged ORMs like ActiveRecord, to sprocs in ASP.NET), I've found that it offers the best compromise between providing an ergonomic abstraction over the database, and not hiding all of the nitty-gritty details you need to worry about in order to write performant queries or use database-specific features like triggers or window functions. Ahh Elixir. My favorite language that really just tries so hard to shoot itself in the foot. I'm currently in the protracted process of trying to upgrade a Phoenix app to the current versions. Currently I'm at the rewrite it in Rust and try out Rocket + Diesel stage. Diesel is... interesting and makes me long for Ecto (which is often used as an ORM although the model bits got split off into a different project).
  - inferiorhuman 8y ago
    Love the downvotes instead of comments. I've walked away from Elixir as the best practice deployment methodology (Distillery) is non-op on FreeBSD[1] and has been for a few months while the Distillery author is mum. All of this despite the vast love that the Elixir community seems to heap on FreeBSD. Erlang and Elixir have plenty of promise but there simply is no good story for production deployments. Distillery and edeliver approximate capistrano, and that sounds great when it works (although I'd just as soon skip edeliver). But when it doesn't I'd much rather dig into the mess of ruby that is Capistrano than the mess of shell scripts, erlang, and god knows what else goes into a Distillery release. Elixir is a really interesting language, but Phoenix seems to still be pretty wet behind the ears and very much in flux. Ecto too to a much smaller extent. 1: Some of the distillery scripts can communicate with epmd, some just give up.
mopierotti 8y ago
To add to the pile of responses: in Scala, Slick is great library that lets you compose sql queries and fragments of queries quite effectively. (http://slick.lightbend.com/ http://slick.lightbend.com/) At my company we built a UI on top of Slick that lets users of our web app define complex triggers based on dynamic fields and conditions which are translated to type-safe SQL queries.
- vips7L 8y ago
  QueryDSL and jOOq as well for java.
xp84 8y ago
I'm confused by the implication that someone doing things like the above would be writing in SQL. SQL is a little like assembly language in a game: You may need to drop down to it for some key highly-optimized areas, but you rarely need to directly use it for most tasks. While it's true that you should understand how it works so you don't generate queries that suck performance-wise, the same goes for Mongo's intricacies too. Every language I know of has great ORMs which do this for whatever SQL flavors people tend to use on that platform. I write things like this all the time, and it gets turned into SQL for Postgres: ```` Article.where(author_id: 37).order(:modified_date, :desc).where.not(published: false) ```` When using an ORM correctly (and indeed, the less I'm using any of my own bits of SQL the more this is true) I am also protected against injection attacks. I'm not saying NoSQL has no value, but I believe it to be the wrong tool for data that lends itself to an RDBMS. If you have a bunch of documents who have deeply nested or inconsistent structures and where it makes no sense that you'd want to query by something other than the primary key, sure, it's a no-brainer to use a NoSQL system. For a CMS, which has been implemented thousands of times in RDBMSs, it is madness though. I cringe at realizing that apaprently there are developers out there who have avoided learning SQL entirely in their career out of fear, and as a result have to use Mongo for every application because that's the only thing they know how to do. I'm sure they're out there, but I wouldn't hire one.

mattbreeden 8y ago

> genuinely better than stitching together fragments of SQL in the form of text strings. Injection attacks and inserting commas (but not trailing commas) come to mind as obvious difficulties. You're using the Pymongo library as an example. Someone can just as easily use SQLAlchemy and not have to worry about those things.

funkaster 8y ago

Well... you can also use a modern ORM. I think "stitching ... text strings" is definitively not the way to go when interfacing a SQL database. My go-to ORM is Sequel[1]. I think their API is one of the best I've seen: you can choose to use models, but you can also work directly with "datasets" (tables or views, or queries) and compose them as you like. It's really powerful and simple. [1]: http://sequel.jeremyevans.net/ http://sequel.jeremyevans.net/

aidos 8y ago

I’ll try to avoid a flame war, but since you’re using python, SQLAlchemy allows for composing sql strings.

raverbashing 8y ago
Yes, an ORM, not SQL itself.
- yawaramin 8y ago
  Yes? What's the qualitative difference between using a best-of-breed SQL ORM and the Mongo API?
- zrail 8y ago
  SQLAlchemy is not an ORM. There’s a companion ORM project if you want it, but it’s not necessary.
- walrus1066 8y ago
  You can compose queries, using the queryAPI in SQLAlchemy, without touching the ORM.
quietbritishjim 8y ago
For it to replace MongoDB's aggregation pipeline, it would need to play nicely with JSONB. Does it do that? This is the thing I'm really missing. For example, if documents in the JSONB column all look roughly like this: { "someArrayField": [ { "key": "steve", "value": 7 }, { "key": "bob", "value": 15 }, ], "someOtherField": [ "whatever" ] } * Can I count the number of entries in someArrayField, summed across all records? * Can I get the per-record mean of the "value" sub-field, summed across all records? * Can I filter by records that have a "someArrayField" entry where "key" is "steve" and "value" is at least 10 (the above record should NOT match)?
- luhn 8y ago
  Yes, you can! The jsonb_array_elements function is roughly similar to Mongo’s $unwind pipeline op. It explodes a JSON array into a set of rows. From there it’s pretty simple aggregates to achieve what you’re looking for. I was evaluating Mongo a couple months back to solve roughly the same problems. Eventually discovered Postgres already had what I was looking for.

QuinnWilton 8y ago

Elixir's primary database wrapper, Ecto [0], lets you dynamically build queries at runtime, and also isn't an ORM. Here's two examples directly from the docs: # Query all rows in the "users" table, filtering for users whose age is > 18, and selecting their name "users" |> where([u], u.age > 18) |> select([u], u.name) # Build a dynamic query fragment based on some parameters dynamic = false dynamic = if params["is_public"] do dynamic([p], p.is_public or ^dynamic) else dynamic end dynamic = if params["allow_reviewers"] do dynamic([p, a], a.reviewer == true or ^dynamic) else dynamic end from "posts", where: ^dynamic Across all the different means of interacting with a database I have experience with (from full-fledged ORMs like ActiveRecord, to sprocs in ASP.NET), I've found that it offers the best compromise between providing an ergonomic abstraction over the database, and not hiding all of the nitty-gritty details you need to worry about in order to write performant queries or use database-specific features like triggers or window functions. My main point, though, is that you don't need to reach for NoSQL if all you need is a way to compose queries without string interpolation. [0] https://github.com/elixir-ecto/ecto https://github.com/elixir-ecto/ecto

inferiorhuman 8y ago
> Across all the different means of interacting with a database I have experience with (from full-fledged ORMs like ActiveRecord, to sprocs in ASP.NET), I've found that it offers the best compromise between providing an ergonomic abstraction over the database, and not hiding all of the nitty-gritty details you need to worry about in order to write performant queries or use database-specific features like triggers or window functions. Ahh Elixir. My favorite language that really just tries so hard to shoot itself in the foot. I'm currently in the protracted process of trying to upgrade a Phoenix app to the current versions. Currently I'm at the rewrite it in Rust and try out Rocket + Diesel stage. Diesel is... interesting and makes me long for Ecto (which is often used as an ORM although the model bits got split off into a different project).
- inferiorhuman 8y ago
  Love the downvotes instead of comments. I've walked away from Elixir as the best practice deployment methodology (Distillery) is non-op on FreeBSD[1] and has been for a few months while the Distillery author is mum. All of this despite the vast love that the Elixir community seems to heap on FreeBSD. Erlang and Elixir have plenty of promise but there simply is no good story for production deployments. Distillery and edeliver approximate capistrano, and that sounds great when it works (although I'd just as soon skip edeliver). But when it doesn't I'd much rather dig into the mess of ruby that is Capistrano than the mess of shell scripts, erlang, and god knows what else goes into a Distillery release. Elixir is a really interesting language, but Phoenix seems to still be pretty wet behind the ears and very much in flux. Ecto too to a much smaller extent. 1: Some of the distillery scripts can communicate with epmd, some just give up.

mopierotti 8y ago

To add to the pile of responses: in Scala, Slick is great library that lets you compose sql queries and fragments of queries quite effectively. (http://slick.lightbend.com/ http://slick.lightbend.com/) At my company we built a UI on top of Slick that lets users of our web app define complex triggers based on dynamic fields and conditions which are translated to type-safe SQL queries.

vips7L 8y ago
QueryDSL and jOOq as well for java.

xp84 8y ago

I'm confused by the implication that someone doing things like the above would be writing in SQL. SQL is a little like assembly language in a game: You may need to drop down to it for some key highly-optimized areas, but you rarely need to directly use it for most tasks. While it's true that you should understand how it works so you don't generate queries that suck performance-wise, the same goes for Mongo's intricacies too. Every language I know of has great ORMs which do this for whatever SQL flavors people tend to use on that platform. I write things like this all the time, and it gets turned into SQL for Postgres: ```` Article.where(author_id: 37).order(:modified_date, :desc).where.not(published: false) ```` When using an ORM correctly (and indeed, the less I'm using any of my own bits of SQL the more this is true) I am also protected against injection attacks. I'm not saying NoSQL has no value, but I believe it to be the wrong tool for data that lends itself to an RDBMS. If you have a bunch of documents who have deeply nested or inconsistent structures and where it makes no sense that you'd want to query by something other than the primary key, sure, it's a no-brainer to use a NoSQL system. For a CMS, which has been implemented thousands of times in RDBMSs, it is madness though. I cringe at realizing that apaprently there are developers out there who have avoided learning SQL entirely in their career out of fear, and as a result have to use Mongo for every application because that's the only thing they know how to do. I'm sure they're out there, but I wouldn't hire one.