Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

DZone's Guide to

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

When working with data in a web application, it's often nice to be able to call upon more tools than just your knowledge of JavaScript.

· Web Dev Zone
Free Resource

Tips, tricks and tools for creating your own data-driven app, brought to you in partnership with Qlik.

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:

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):

var express = require('express');
var database = require('./database.js'); //a lightweight wrapper on the oracledb driver

function getRouter() {
    var router = express.Router();

    router.route('/users')
        .post(postUser);

    return router;
}

module.exports.getRouter = getRouter;

function postUser(req, res, next) {
    database.getPool().getConnection(function(err, connection) {
        if (err) return next(err);

        connection.execute(
            'INSERT INTO users (' +
            '   first_name, ' +
            '   last_name, ' +
            '   email ' +
            ') VALUES ( ' +
            '   :FIRST_NAME, ' +
            '   :LAST_NAME, ' +
            '   :EMAIL ' +
            ')',
            {
                FIRST_NAME: {
                    val: req.body.FIRST_NAME
                },
                LAST_NAME: {
                    val: req.body.LAST_NAME
                },
                EMAIL: {
                    val: req.body.EMAIL
                }
            },
            {
                isAutoCommit: true
            },
            function(err, results) {
                if (err) {
                    return connection.release(function() {
                        next(err);
                    });
                }

                connection.release(function(err) {
                    if (err) return next(err);

                    res.send('some response here');
                });
            }
        );
    });
}

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:

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):

var express = require('express');
var database = require('./database.js'); //a lightweight wrapper on the oracledb driver

function getRouter() {
    var router = express.Router();

    router.route('/users')
        .post(postUser);

    return router;
}

module.exports.getRouter = getRouter;

function postUser(req, res, next) {
    database.getPool().getConnection(function(err, connection) {
        if (err) return next(err);

        insertUser(connection, req, res, next);
    });
}

function insertUser(connection, req, res, next) {
    connection.execute(
        'INSERT INTO users (' +
        '   first_name, ' +
        '   last_name, ' +
        '   email, ' +
        '   region_id ' +
        ') VALUES ( ' +
        '   :FIRST_NAME, ' +
        '   :LAST_NAME, ' +
        '   :EMAIL, ' +
        '   :REGION_ID ' +
        ') RETURNING id INTO :USER_ID',
        {
            FIRST_NAME: {
                val: req.body.FIRST_NAME
            },
            LAST_NAME: {
                val: req.body.LAST_NAME
            },
            EMAIL: {
                val: req.body.EMAIL
            },
            REGION_ID: {
                val: req.body.REGION_ID
            }
        },
        function(err, results) {
            if (err) {
                return connection.release(function() {
                    next(err);
                });
            }

            getUserRole(results.outParams.USER_ID, connection, req, res, next);
        }
    );
}

function getUserRole(userId, connection, req, res, next) {
    if (req.body.ROLE_ID) {
        insertUserRole(userId, req.body.ROLE_ID, connection, req, res, next);
    } else {    
        connection.execute(
            'SELECT default_role_id ' +
            'FROM regions ' +
            'WHERE id = :REGION_ID',
            {
                REGION_ID: req.body.REGION_ID
            },
            {
                outFormat: oracledb.OBJECT
            },
            function(err, results) {
                if (err) {
                    return connection.release(function() {
                        next(err);
                    });
                }

                insertUserRole(userId, results.rows[0].DEFAULT_ROLE_ID, connection, req, res, next);
            }
        );
    }
}

function insertUserRole(userId, roleId, connection, req, res, next) {
    connection.execute(
        'INSERT INTO user_roles (' +
        '   user_id, ' +
        '   role_id ' +
        ') VALUES ( ' +
        '   :USER_ID, ' +
        '   :ROLE_ID ' +
        ')',
        {
            USER_ID: {
                val: userId
            },
            ROLE_ID: {
                val: roleId
            }
        },
        {
            isAutoCommit: true
        },
        function(err, results) {
            if (err) {
                return connection.release(function() {
                    next(err);
                });
            }

            connection.release(function(err) {
                if (err) return next(err);

                res.send('some response here');
            });
        }
    );
}

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 the 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 a 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.

Let's see how we could update the API using some PL/SQL...

var express = require('express');
var database = require('./database.js'); //a lightweight wrapper on the oracledb driver

function getRouter() {
    var router = express.Router();

    router.route('/users')
        .post(postUser);

    return router;
}

module.exports.getRouter = getRouter;

function postUser(req, res, next) {
    database.getPool().getConnection(function(err, connection) {
        if (err) return next(err);

        connection.execute(
            'DECLARE ' +

            '   l_user_id USERS.ID%TYPE; ' +
            '   l_role_id ROLES.ID%TYPE; ' +

            'BEGIN ' +

            '   INSERT INTO users ( ' +
            '      first_name, ' +
            '      last_name, ' +
            '      email, ' +
            '      region_id ' +
            '   ) VALUES ( ' +
            '      :FIRST_NAME, ' +
            '      :LAST_NAME, ' +
            '      :EMAIL, ' +
            '      :REGION_ID ' +
            '   ) RETURNING id INTO l_user_id; ' +

            '   IF :ROLE_ID IS NOT NULL ' +
            '   THEN ' +
            '      l_role_id := :ROLE_ID; ' +
            '   ELSE ' +
            '      SELECT default_role_id ' +
            '      INTO l_role_id ' +
            '      FROM regions ' +
            '      WHERE id = :REGION_ID; ' +
            '   END IF; ' +

            '   INSERT INTO user_roles ( ' +
            '      user_id, ' +
            '      role_id ' +
            '   ) VALUES ( ' +
            '      l_user_id, ' +
            '      l_role_id ' +
            '   ); ' +

            'END;',
            {
                FIRST_NAME: {
                    val: req.body.FIRST_NAME
                },
                LAST_NAME: {
                    val: req.body.LAST_NAME
                },
                EMAIL: {
                    val: req.body.EMAIL
                },
                REGION_ID: {
                    val: req.body.REGION_ID
                },
                ROLE_ID: {
                    val: req.body.ROLE_ID
                }
            },
            {
                isAutoCommit: true
            },
            function(err, results) {
                if (err) {
                    return connection.release(function() {
                        next(err);
                    });
                }

                connection.release(function(err) {
                    if (err) return next(err);

                    res.send('some response here');
                });
            }
        );
    });
}

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:

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:

var express = require('express');
var database = require('./database.js'); //a lightweight wrapper on the oracledb driver

function getRouter() {
    var router = express.Router();

    router.route('/users')
        .post(postUser);

    return router;
}

module.exports.getRouter = getRouter;

function postUser(req, res, next) {
    database.getPool().getConnection(function(err, connection) {
        if (err) return next(err);

        connection.execute(
            'BEGIN ' +
            '   create_user( ' +
            '      :FIRST_NAME, ' +
            '      :LAST_NAME, ' +
            '      :EMAIL, ' +
            '      :REGION_ID, ' +
            '      :ROLE_ID ' +    
            '   ); ' +
            'END;',
            {
                FIRST_NAME: {
                    val: req.body.FIRST_NAME
                },
                LAST_NAME: {
                    val: req.body.LAST_NAME
                },
                EMAIL: {
                    val: req.body.EMAIL
                },
                REGION_ID: {
                    val: req.body.REGION_ID
                },
                ROLE_ID: {
                    val: req.body.ROLE_ID
                }
            },
            {
                isAutoCommit: true
            },
            function(err, results) {
                if (err) {
                    return connection.release(function() {
                        next(err);
                    });
                }

                connection.release(function(err) {
                    if (err) return next(err);

                    res.send('some response here');
                });
            }
        );
    });
}

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.

Explore data-driven apps with less coding and query writing, brought to you in partnership with Qlik.

Topics:
web dev ,javascript ,web application development ,web data

Published at DZone with permission of Dan McGhan, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}