MongoDB with mongoose or SQL

Question

I'm a bit new to mongoDB and I'm trying to figure out if I should use MongoDB or to use the familiar SQL.

At first I really wanted to use MongoDB but it's going really slow, and I'm having a hard time how to design my DB and queries.

For instance: SQL

Let's say I have these tables:

User Table (Id, Name)

Group (Id, Name)

GroupInvites (UserId, GroupId, Status[A - active, I - invited])

And if I wanted to know if a player has and group invitations, it would be pretty easy the SELECT and UPDATE from the GroupInvites table.

MongoDB

But in MongoDB the GroupInvites is redundant, because we can have the Status in the group scheme.

const BetGroupSchema = mongoose.Schema({
    name: {
        type: String,
        required: true
    },
    users: [{
        userId: {
            type: mongoose.SchemaTypes.ObjectId,
            ref: 'users',
            required: true
        },
        userStatus: {
            type: String,
            required: true
        }
     }]
})

But now, if I wanted to SELECT only the groups that a specific user has an invite for, it would be much harder -

Group.find({
    "users.userId": req.user._id,
    "users.userStatus": "I"
})

The result of this would give a all group that have the UserID and all the groups that have UserStatus = 'I'.

And also I can't find a quick way to update the DB directly, If I wanted to update a specific user status to 'A'.

Maybe I got this wrong and someone can show me a good way to implement it in MongoDB or you think it would be better to go with SQL.

Just because you can nest the users within group does not mean you have to. In your case, I would still have three distinct collections (i.e. tables). You can do $lookup queries (i.e. joins) in MongoDB, too. — str
– str, Commented Jun 30, 2018 at 9:49
@str If I keep 3 collections what is the advantage using mongoDB if I create a relational DB like SQL. — Ore T
– Ore T, Commented Jun 30, 2018 at 10:07
@str sure you can do but since mongodb allow you to embedded your document so that you get the data in one shot , lookup queries are costly compare to single collection query. at the end it depends how you want to design your sachemas. — Dinesh Nagar
– Dinesh Nagar, Commented Jun 30, 2018 at 10:20
@OreT Nesting is not the only difference to SQL. NoSQL database engines have advantages and disadvantages so I cannot tell you whether MongoDB is better in your case. Nesting might be great for some functionalities, but in your case it might also make things harder. See Data Model Design. — str
– str, Commented Jun 30, 2018 at 10:39

Dinesh Nagar · Accepted Answer · 2018-07-02 07:35:10Z

1

I can understand as a beginner it becomes hard to do even a simple query well, if I would be at your place, I will do the query like this in a following way

1. Find query for a document nested in an array:

 Group.find({users: {$elemMatch: {userId:req.user._id, userStatus:"I"}}})

2. Update query for a document nested in an array:

Group.update( { "name": "John","users.userId" : req.user._id }, 
              { "$set": { "users.$.userStatus": "A" } } )

edited Jul 2, 2018 at 7:35

answered Jun 30, 2018 at 9:45

Dinesh Nagar

7782 gold badges11 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ore T Over a year ago

Can you explain the $ between the dot notation?

Dinesh Nagar Over a year ago

@OreT sure, its a positional "$" operator , for example if one of the field in your schema is an array of objects , in your case it is "users" field, then in order to point out and update a specific one object , you will need to use "$" operator in your query.

Ore T Over a year ago

Isn't the update function suppose to be on the result of the of the find? Or am I missing something? Because in this way the update will occur on all Group's that have a user named John OR the UserId.

Dinesh Nagar Over a year ago

@OreT Did that solve your problem or doubt? , let me know if you are still facing issues. Thanks

Dinesh Nagar Over a year ago

since userId is unique, so update query will update a individual object field at a time. for example the query group.update( { "name": "John","users.userId" : req.user._id }, { "$set": { "users.$.userStatus": "A" } } ) means update the userstatus of a user for which userid is 'bla bla' and name is 'John'.

Collectives™ on Stack Overflow

MongoDB with mongoose or SQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related