MongoDB Stored Procedure Equivalent

Question

I have a large CSV file containing a list of stores, in which one of the field is ZipCode. I have a separate MongoDB database called ZipCodes, which stores the latitude and longitude for any given zip code.

In SQL Server, I would execute a stored procedure called InsertStore which would do a look up on the ZipCodes table to get corresponding latitude and longitude and insert the data into the Stores table.

Is there something similar to the concept of stored procedures in MongoDB for this? Basically, for each insert I need to look up the latitude and longitude for that store and save that as well.

I am not too familiar with the concept of Map/Reduce, but would that be relevant here? Thank you!

Melon

RDBMS(say MySQL/MS-SQL/Oracle/...) is not just a data store, but can also be part of application design functionality(through triggers & stored procedures). NoSQL databases like MongoDB are just datastores. – Charolettecharon

Answer 1

The closest thing to an equivalent of a stored procedure in mongodb is stored javascript. A good introduction to stored javascript is available in this article on Mike Dirolf's blog.

Reception

In the current implementation of MongoDB, stored javascript is the closest thing to stored procedures, but I'm not sure I would go as far as to call it "equivalent." +1 for the helpful link though – Dorton

Ari, agreed that they are not equivalent. – Reception

Below is the answer – Charolettecharon

Answer 2

NOTE that according to the reference:

Do not store application logic in the database. There are performance limitations to running JavaScript inside of MongoDB. Application code also is typically most effective when it shares version control with the application itself.

So there is not any equivalent for stored procedure in mongodb.

Giacopo

What if you have large amounts of data to be processed. I currently have a big table in MSSQL. My stored procedures do the heavy lift, so I don't need to transfer all the data to the application. Would this be a good case to keep some logic in the DB? – Overgrow

15 years ago if I put application logic in an SQL database it would still work today, and my application could have gone from a vb6 app, to a .NET app, to a .NET Forms web app to a .NET MVC App, etc etc.. If I put that same application logic in the application it would have been re-written every time I upgraded the font end to the latest technology.. Front end technology keeps changing, database not so much, not sure I will ever agree with this "Don't store Application logic in the database" idea. – Letterhead

Very much agree, Shaun. MySQL stored procedures are extremely useful, and exactly mitigates against the case you identified, not to mention the advantage of by-passing the the compile and build process. – Ophir

15 years ago if you put application logic in your database and you worked at a real company, 10 other applications would learn to depend on it, it would have worked for the past 15 years so it would become a pattern everyone accepted, and code just like it would proliferate. Finally, when you had to change it you simply could not afford to – Bath

@Bath That's why the only logic you should put in the stored procedures is basic CRUD and single-entity filtering logic. Let your software put those pieces together and make the calls necessary so it remains flexible, but codify what an acceptable way to interact with your DB is in these CRUD procedures. It's literally good atomic design embodied. – Liquidity

@Liquidity I feel like there's a place for that, especially if you're interacting with a view - to me at that point though I would ask why am I doing this with a sproc vs application code. – Bath

@Bath if i have 5 places that can update a table and the logic is different in each, I have spaghetti on my hands. If disallow direct queries and only allow you to call my sprocs, you now essentially have an API into the database with expected responses and I can control what you can do. Excellent for code coherency and security. – Liquidity

@Liquidity the inverse of that is now you have five reasons to change a single thing and it can become its own ball of mud. It sounds like both are right depending on circumstances – Bath

@ShaunKeon Hence, we slap a service on top of the database and let our frontends interact with the service instead. All business logic goes into the service. It is also unit test-able; tests serve as documentation for the application logic. – Wagstaff

nah I dont agree with the Mongodb explanation.. I still believe storing the logic inside database are most efficient way retrieve records, they just include a more complex structure on the top, and explain its worthy to try even though they can even prove that it was fast – Marseilles

yes. procedure is good. but mongodb doesn't use stored procedure. – Hamal

Answer 3

You could use Triggers and Functions on the cloud with MongoDB Stitch.

Cutthroat

Can you include a basic example of a Trigger and Function? – Fail

Answer 4

MongoDB provides a powerful alternative to traditional SQL Server stored procedures through its Aggregation pipelines or MongoDB Atlas Function. Instead of stored procedures, MongoDB encourages the use of aggregation pipelines, which offer flexibility in data transformation and manipulation.

In your scenario of inserting latitude and longitude into the Stores collection based on ZipCodes, you can leverage aggregation pipelines to achieve this. Here's a simplified approach:

• Load your CSV data into MongoDB or retrieve it as needed.

• Create an aggregation pipeline that performs a lookup on the ZipCodes collection to fetch latitude and longitude data based on ZipCodes.

• Utilize the $merge stage within the aggregation pipeline to either merge the results with the Stores collection or insert them into a new collection.

This approach allows you to efficiently handle data transformation and insertion in MongoDB, offering similar capabilities to traditional stored procedures in SQL Server.

If you need more detailed guidance on migrating SQL-based stored procedures to aggregation pipelines in MongoDB, you can refer to the following technical guide: https://www.mongodb.com/collateral/technical-guide-migrating-stored-procedures-to-mongodb

Iraq