1

As I heard, mongoDB can store internal procedures.

How can I use it?

Official Help is very short.

Can I use stored proc. to implement small logic on this layer?

Same as Postgres pl/pgSQL.

Dmitry
  • 877
  • 1
  • 16
  • 30

1 Answers1

5

The duplicate question ( MongoDB Stored Procedure Equivalent ) does explain that you can store a procedure within MongoDB that can be called via the eval() command, however, it doesn't really explain why this is a bad thing.

Eval is a direct access to an, almost, unrestricted JS environment called from MongoDB's C++ code. Good to also mention that injection through unescaped parameters is very easy.

They are not stored procedures that work within MongoDBs own runtime (unlike the stored procedures you are thinking of) the JS engine is run from MongoDB, MongoDB is not programmed in JS; it is programmed in C++.

They are only available from a JS context, not from MongoDB's C++ context.

By default they can take global lock even with the nolock option set, it all depends upon the operations you call and the JS in itself is extremely slow in comparison to native MongoDB runtime.

As such:

Can I use stored proc. to implement small logic on this layer?

No. It is actually implemented on a third layer, separate from MongoDB.

MongoDB is designed to run this stuff from client side, there is a 90% chance you will get no real benefits by using "stored procedures". In fact in many ACID databases they are heavily abused and used in such a way that actually slows down applications and makes them more prone to failure. So you need to think very carefully about whether you really "need" them or not.

Community
  • 1
  • 1
Sammaye
  • 43,242
  • 7
  • 104
  • 146
  • Thank U! This exhaustive answer – Dmitry Dec 11 '14 at 14:08
  • to Sammaya. Can you help me better understanging with mongoDB and MVC pattern? – Dmitry Dec 11 '14 at 14:12
  • @Dmitry Make a new question with your problems – Sammaye Dec 11 '14 at 14:21
  • http://stackoverflow.com/questions/27426137/migration-from-postgres-to-mongodb – Dmitry Dec 11 '14 at 15:17
  • Can you please explain what do you mean when saying that stored procedures "... are heavily abused and used in such a way that actually slows down applications" ? – Dmitry O May 10 '17 at 00:57
  • Many use stored procedures for everything – Sammaye May 10 '17 at 06:13
  • @Sammaye - Do you have any verifiable examples or citations of stored procedures slowing down applications that use ACID databases? Stored procedures, at least in the relational DB world, let you write the data logic as close to the data as possible, therefore giving you great performance benefits and DRY code. True, if the procedures are written poorly, they are likely to perform poorly. You can refer to this thread for a good discussion: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:12083187196917 – dcp Dec 03 '18 at 16:46
  • @dcp I have seen it reduce speed in sites I have been asked to speed up, here is a simple bit of logic: if you just query an ACID DB, let's say MySQL, normally you will open a thread for the connection and then use RAM and CPU to process the result set and send it back, with MySQL processing the query in-memory. Stored procedures not only have to open a transactional lock on the DB but then also have to fetch the procedure from the DB store (normally HDD/SSD), this adds about double the work if your query is to return a single row. Stored procedures can be good but many will abuse them – Sammaye Dec 03 '18 at 17:30
  • @dcp if your logic is to move the weight of app logic to the DB I would strongly prefer transactions with the code in the app, since this makes it easier to maintain as well – Sammaye Dec 03 '18 at 17:30
  • @dcp another consideration: you do not normally want to give devs access to make stored procedures since that requires some special privileges in the DB normally, I mean if your dev is your sysadmin then fair, but you can see how in bigger teams this might pose security issues – Sammaye Dec 03 '18 at 17:50
  • @Sammaye - Agree with you that transaction control belongs in the app. But the code that manipulates the data, well, I believe that belongs in the database. The question is, what's going last longer, your app or the data (hint, it's not your app). If we put the data logic close to the data, then as the apps change over time due to latest whiz-bang tech, they can continue to use that data logic which is in one place (e.g.stored procs). Refer to that thread I linked, it's really good :). – dcp Dec 03 '18 at 18:14
  • @dcp the thing is, if the app changes then so too does the way it uses data, you might reuse old queries but quite commonly you will be altering the data processing to add features etc etc – Sammaye Dec 03 '18 at 18:16
  • @Sammaye - "you do not normally want to give devs access to make stored procedures since that requires some special privileges in the DB" Nah, you have a development environment where they can do all their development. For prod deployment, you have a DBA do it with a script you've written. At least, that's how it works in the shops I've worked in. – dcp Dec 03 '18 at 18:17
  • @Sammaye - "if the app changes then so too does the way it uses data, you might reuse old queries but quite commonly you will be altering the data processing to add features etc etc" In my experience, you might enhance the application, but a lot of the existing logic isn't going to change. We have a 20+ year old GL system we ported from VB6 to WPF/C# a couple years ago and I was so glad the data logic was in the DB and not the VB6 code. It probably saved us 6 months or more of work. – dcp Dec 03 '18 at 18:19
  • @dcp I normally find that the DBA is the Sysadmin combined, normally due to DevOps and DBAs needing to know the internals of how servers work to understand how to properly optimise DBs (such as how the working set and LRU affects workloads etc), and I normally find that even if there was a separate DB, you then have to work with that DBA to ensure that your application meets the needs of their query which just ends with a constant bottleneck in the process of development, the DBA, you would need an equal measure of Devs and DBAs to keep productivity up – Sammaye Dec 03 '18 at 18:20
  • @dcp hmm, I am more of a web dev, maybe this is where the difference is coming from? In web applications my method would win over yours, I know since it is the method that pretty much everyone uses, from Google to Facebook, proven to work the best – Sammaye Dec 03 '18 at 18:21
  • @Sammaye - I do web development as well. Google/Facebook aren't generating paychecks, writing GL systems, and doing stuff like that. If Facebook loses your post because Mongodb didn't sync things up in time, it's not a big deal. If your paycheck doesn't get generated, that's kind of a big deal. So that's why I lean more towards the DB being the be-all, end-all of things. I put the data logic as close to the data as I can. – dcp Dec 03 '18 at 18:24
  • @dcp but a stored procedure would not be key to ensuring data has written/processed, that can just as easily be done calling the query from client side. – Sammaye Dec 03 '18 at 18:27
  • @Sammaye - Ok, we probably need to stop here since this is getting a bit long :). I appreciate your viewpoint and comments. – dcp Dec 03 '18 at 18:28
  • @dcp agreed, the mods are probably mad already, but yeah this was going closer onto durability assets and going quite off topic really, good chat, gave me stuff to think about – Sammaye Dec 03 '18 at 18:29