For just over two years now, I have had the pleasure of working with OrientDB -- a multi-model database supporting multi-master replication. That is a mouthful. In practice, it is a very capable database and not one that is free of worts. It is, for all intents and purposes, production ready. I'll briefly talk about what I like about this database and then I will talk about the age old question in the relational database community, "Should we use stored procedures?"

First, about OrientDB, it makes working with your data fun. It supports schemaless design so you can store data in your fields without first creating them. It also supports a structured approach where you can create your schema first and then fill in your data later. I prefer and always recommend using the schema first approach. There is no substitute for a good database design. One of the beautiful aspects of OrientDB is that it can be used as a key-value store, a document database, or a graph database. Modeling for this database is a learning exercise and the documentation misses opportunities from time-to-time to make features of the database easily accessible. Even with its shortcomings, it manages to cover the basics.

Developers working with NoSQL databases will express excitement when they hear they don't have to create stored procedures before they can write application code. This excitement stems from working with relational databases with a DBA acting as gatekeeper. In the relational database world, having stored procedures has benefits. And, as I will explain shortly, it has many benefits in the OrientDB world as well. Among its benefits, it reduces the number of network calls, it allows the DBA to restrict access to the applications that need it, and it helps the DBA optimize the queries so the data is retrieved as quickly as possible. All those are just a few benefits and there are DBAs who can rattle off about it for a whole week if you let them.

Now, OrientDB falls in the NoSQL camp. The first question any developer will ask when debating about stored procedures is, "Why bother?" To that, I say, consider the following. When we look back at our experience with Stored Procedures, the languages built-in to the databases were not the easiest to rationalize at times. Depending on the database, it felt like it lacked the ability to describe our effort elegantly. This was enough to get the novice engineer to fear the database monster. With time and experience, we learn to wrangle with the SQL language and the stored procedure language. In the case of OrientDB, the stored procedure language is JavaScript. To be exact, OrientDB does not call these routines Stored Procedures, it calls them Stored Functions.

I propose that you should write application logic inside of Stored Functions when using OrientDB. The practical reason for doing so is that it helps isolate your application logic into discrete functions. It also becomes a natural implementation of a repository pattern for your data. Your application code, presuming you are building modern applications with lots of APIs, becomes a thin controller around these stored functions. A clean separation of application logic. Now, I am not advocating that you must put 100% of your application logic inside stored functions. However, I will say that most of your application functions can benefit from having a stored function. After all, you are the best judge of your application design.

I can hear the nay sayers. How do you then go about internationalizing error messages? How do you go about taking care of conditional logic? How do you save yourself from the trouble of applying schema migrations? To all the nay sayers I say, all those can be handled elegantly. This is the first article in a series of articles I will write to cover all those issues. I will show how we can handle things like schema migrations where you can have stored functions and schema changes. I will show some samples of work you can do inside a stored function as well. Being that the language is JavaScript, it opens up an entire world of code snippets you can insert into your database.

All this power and flexibility is thanks to the Nashorn engine built into the OrientDB server. I hope you will return as I continue this series. I look forward to hearing your thoughts about Stored Functions inside of OrientDB if you are a seasoned expert. Until next time.