To JavaScript, or Not to JavaScript, That is the Question

By April 6, 2015 Uncategorized
Postman test

I should probably start this post off by stating that I LOVE JAVASCRIPT! But I will also confess that I am NOT a monoglot! I’ve studied various programming languages over the years to varying degrees. Most languages (as well as tiers, libraries, frameworks, and platforms) have sweet spots where they excel. Often times it can be tempting to use one language over another because that’s the one you know best. However, this type of thinking can eventually lead to performance, scalability, maintenance, and a number of other issues. In this post we’ll explore an example of how a little knowledge of PL/SQL can help keep your JavaScript/Node.js code neat and clean while providing some nice performance benefits!

It’s ALWAYS simple in the beginning

When creating a new application things always start off nice and easy. Perhaps you start by creating a table to hold users:
simple users table

An API that handles the creation of users for such a table may start off looking like this (assuming you’ve configured express w/body-parser middleware elsewhere):

Then the REAL business logic arrives

After a little while you get some new business requirements… Regions are introduced and users must be assigned to a region. Additionally, roles are introduced and users must be assigned roles which will be used for authorization purposes. If a role is not specified when creating a user, then the default role for the user’s region should be used. The data model now looks like this:

more complex erd

To accommodate the additional business logic you update the API to the following (note that RETURNING INTO isn’t currently supported by the driver but the code illustrates how one should be able to solve the business requirements in the near future):

As you can see we now need to execute up to 3 queries (it’s possible we can skip the lookup of the default role). By properly modularizing the code with named functions we can avoid callback hell, but other problems remain. For example, each query execution is a round trip to the database which could lead to performance and scalability issues as network has the highest IO cost. Additionally, the SQL statements mixed in the JavaScript are just strings so we don’t get any syntax highlighting, code completion, or any of the other goodies that IDEs like SQL Developer have to offer. And let’s face it, the queries I’ve shown are quite simple compared to what you might need in real application.

Enter PL/SQL

PL/SQL stands for the Procedural Language extensions to SQL. When it comes to optimizing access to data, hiding complexity, and maximizing reuse of business logic, PL/SQL can be a very powerful tool to add to your toolset. Best of all, it’s an amazingly simple language to learn, especially if you already know some SQL! Checkout this new page if you’d like to learn more about PL/SQL.

Lets see how we could update the API using some PL/SQL…

Okay, the SQL statements are nearly the same as they were, but now we’re back down to a single execute! Of course we had to create (and we’ll have to maintain) that GIANT string in JavaScript which is no fun. 🙁 ECMAScript 6 Template Strings may help somewhat, but not as much as a proper IDE for SQL and PL/SQL.

Moving PL/SQL to the database

PL/SQL can be passed to the database for execution, say from JavaScript as previously shown, or it can be compiled in the database and called by name. One of the advantages of working with code in the database is that we can use an IDE designed to help with exactly that. Here’s a screenshot of what it would look like if we were to use SQL Developer (which is free, by the way) to move the code into the database as a stored procedure:

create users procedure in database

The bind variables in the code have been moved to the top of the procedure as parameters. This allows us to greatly reduce the size of the string in the API:

Now we just have a tiny string to maintain which is much better! 🙂

Final Thoughts

PL/SQL is just one of many features of Oracle Database that you can leverage to help build awesome applications. It’s easy to learn and has many benefits related to performance, scalability, and maintenance.

Some people might argue that they’d rather have all of their business logic in one place so they don’t have to use two tools to maintain it. Others may go so far as to say that they only want to work in one language, such as JavaScript, to avoid the mental context switching that comes with working in multiple languages. My take is that developers should learn about the strengths and weaknesses of the tools they are using and play to the strengths where appropriate – even if it leads them out of their comfort zone.

One Comment

Leave a Reply