Why is using SQL important to you?


#1

There has always been a lot of talk about how people want to use SQL with meteor. I’m wondering why this is. Is it because:

  • You’re more familiar with using SQL and don’t like having to learn Mongo
  • SQL is just generally a better DB than mongo
  • Your clients require/request you to use SQL
  • Or something else?

#2

2 of the reasons which I can see are -

  1. Where we need migrate the code from any other language to Meteor and we have to use an existing database as its being used on other apps as well.

  2. Now we can use multiple database and there are cases when SQL database is more efficient than MongoDB.


#3

I would say there are two primary reasons why I’m interested in SQL support. First, we have a large legacy project backed by a MySQL database that I would love to move over to Meteor, and SQL support would make it easier. Second, relational databases are better at modeling relational data, and SQL is better at querying relational data. Sometimes the relational model is what you want, and Mongo’s just not designed for it.

That being said, I’d love to see the Apollo project succeed at making Meteor apps data source agnostic. Looking forward to it!


#4

I dare say that relations are very often what you need.

As soon as you have a relationship between two models (e.g. a user and the user’s documents), you have issues with MongoDB.

And from my experiences, rare is the case where one model really stands on its own.

Yes, MongoDB now has $lookup. But a) it’s not available in Meteor / MiniMongo and b) it’s only a LEFT OUTER JOIN.


#5

My interest in using SQL database is similar to the others, be that i have sql data already being used in other legacy apps, and the most important factor for me its the lack of relational data, which is to expected in mongoDB, and its ok for it, but for large apps, or enterprise data, that’s almost impossible to work around. Which is why I’m open to contributing with the effort to build a similar to the mongoDB - Meteor approach with SQL databases.

Stil i think its worth having both databases available, because you could use the document DB to create data views and pre-processed reports, and the SQL to enforce data security. The would be really cool to work with.


#6

When you need to get data from multiple collections that are connected by particular keys e.g. (IDs), MongoDB is such a pain. I have been writing this kind of code:

const cursorCollection1 = Collection1.find({ userId: this.userId });
const collection1Ids = _.pluck(cursorCollection1.fetch(), "_id");

const cursorCollection2 = Collection2.find({ collection1Id: { $in: collection1Ids } }, { fields: { _id: 1 } });
const collection2Ids = _.pluck(cursorCollection2.fetch(), "_id");

const cursorCollection3 = Collection3.find({ collection2Id: { $in: collection2Ids }, collection3Id: { $exists: true } }, { fields: { collection3Id: 1 } });
const collection3Ids = _.pluck(cursorCollection3.fetch(), "collection3Id");

return Collection4.find({ _id: { $in: collection3Ids } })

With SQL the same thing can be done in one operation (one SQL statement containing some simple key joins would return what I want).

I studied relational database design as part of my university degree course a long time ago and the reasons for normalization all made logical sense for storing and working with data. It’s easier to keep the data cleanly organized (compartmentalized in separate tables) and maintain integrity (e.g. by never duplicating data across tables). So to me MongoDB and the like seems more like an anarchic anti-establishment way of going about managing data (e.g. an attitude like “I don’t need nor care about your so-called ‘best-practice’ database design principles, I’ll just do it the way that works for me”). I guess it works OK for simple data.

Large applications could have a variety of data across 20, 30, or more separate tables, and they may have been separated in that way because that’s how the data makes logical sense for the business (e.g. one table for employees, another for roles, another for departments, another for projects, another for clients etc.). I’d imagine using MongoDB with that many collections and needing to retrieve data from many of them at any one time for various purposes would be very messy.


#7

Answer to your question:

  • clients require/request to use SQL (and MongoDB seems to be controversial for enterprise clients)

@tab00 Meteor 1.4 with MongoDB 3.2 has joints https://www.mongodb.com/blog/post/joins-and-other-aggregation-enhancements-coming-in-mongodb-3-2-part-1-of-3-introduction


#8

Where is the official Meteor documentation that describes how to do joins in Meteor? I don’t think it is supported in Meteor, as rhywden stated before:

The Meteor Guide actually leans towards support for denormalization, which I think is bad practice and would discourage for reasons stated above. It can also make it more difficult to migrate to an SQL database in future.

Imagine trying to create an app with a MongoDB database with data organized like this:

or even the Northwind database, Microsoft’s sample database:


#9

It’s also interesting to see that it took them until version 3.2 to realize the error of their ways, yet it’s still early days of such support. It’s like it has been a young and rebellious teenager thinking that he knew better than his wise and experienced elders, but now is trying to appear mature.


#10

Yes, but the JOINs are not reactive in Meteor which makes them just a bit unattractive.