Creating a REST API: Handling POST, PUT and DELETE Requests

By April 11, 2018 Uncategorized

In the last post, you added logic to the API for GET requests which retrieved data from the database. In this post, you will finish building out the basic CRUD functionality of the API by adding logic to handle POST, PUT, and DELETE requests on the employees endpoint.

Adding the routing logic

To keep the routing logic simple, you will route all HTTP methods through the existing route path (with the optional id parameter). Open the services/router.js file and replace the current routing logic (lines 5-6) with the following code:

The updated routing logic maps the four most common HTTP methods used for basic CRUD operations to the correct controller logic. That controller logic, as well as the related database logic, will be built out over the next 3 sections of this post.

Handling POST requests

HTTP POST requests are used to create new resources (employee records in this case). The basic idea is to pull data out of the HTTP request body and use it to create a new row in the database. To add the controller logic for POST requests, open the controllers/employees.js file and append the code that follows.

The getEmployeeFromRec function accepts a request object and returns an object with the properties needed to create an employee record. The function was declared outside of the post function so that it can be used later for PUT requests as well.

The post function uses getEmployeeFromRec to initialize a variable that is then passed to the create method of the employees database API. After the create operation, a “201 Created” status code, along with the employee JSON (including the new employee id value), is then sent to the client.

Now you can turn your attention to the create logic in the database API. Open the db_apis/employee.js file and append the following code to the bottom.

The logic above starts by declaring a constant named createSql to hold an insert statement. Note that it uses bind variables, not string concatenation, to reference the values to be inserted. It’s worth repeating how important bind variables are for security and performance reasons. Try to avoid string concatenation whenever possible.

Within the create function, an employee constant is defined and initialized to a copy of the emp parameter using Object.assign. This prevents direct modification of the object passed in from the controller. It’s a shallow copy, but that’s sufficient for this use case.

Next, an employee_id property is added to the employee object (configured as an “out bind”) so that it contains all of the bind variables required to execute the SQL statement. The simpleExecute function is then used to execute the insert statement and the outBinds property of the result is used to overwrite the employee.employee_id property before the object is returned.

Because the oracledb module is referenced, you’ll need to require that in. Add the following line to the top of the file.

Handling PUT requests

PUT requests will be used to make updates to existing resources. It’s important to note that PUT is used to replace the entire resource – it doesn’t do partial updates (I will show you how to do this with PATCH in the future). Return to the controllers/employees.js file and add the following code to the bottom.

The put function uses getEmployeeFromRec to initialize an object named employee and then adds an employee_id property from the id parameter in the URL. The employee object is then passed to the database API’s update function and appropriate response is sent to the client based on the result.

To add the update logic to the database API, append the following code to the db_apis/employees.js file.

The update logic is very similar to the create logic. A variable to hold a SQL statement is declared and then simpleExecute is used to execute the statement with the dynamic values passed in (after copying them to another object). The result.rowsAffected is used to determine if the update was successful and return the correct value.

Handling DELETE requests

The last method you need to implement is DELETE which, unsurprisingly, will delete resources from the database. Add the following code to the end of the controllers/employees.js file.

The JavaScript engine will throw an exception if you try to declare a function named “delete” using a function statement. To work around this, a function named “del” is declared and then exported as “delete”.

At this point, you should be able to read and understand the logic. Unlike the previous examples, this HTTP request doesn’t have a body, only the id parameter in the route path is used. The “204 No Content” status code is often used with DELETE requests when no response body is sent.

To complete the database logic, return to the db_apis/employees.js file and add the following code to the end.

Because the JOB_HISTORY table has a foreign key constraint that references the EMPLOYEES table, a simple PL/SQL block is used to delete the necessary rows from both tables in a single round trip.

Parsing JSON request bodies

If you look back at the getEmployeeFromRec function in the controller, you’ll notice that the request’s body property is a JavaScript object. This provides an easy way to get values from the body of the request, but it’s not something that happens automatically.

The API you are building, expects clients to send JSON formatted data in the body of POST and PUT requests. In addition, clients should set the Content-Type header of the request to application/json to let the web server know what type of request body is being sent. You can use the built-in express.json middleware to have Express to parse such requests.

Open the services/web-server.js file and add the following lines just below the app.use call that adds morgan to the request pipeline.

When JSON data is parsed into native JavaScript objects, only the data types supported in JSON will be correctly mapped to JavaScript types. Dates are not supported in JSON and are typically represented as ISO 8601 strings. Using a reviver function, passed to the express.json middleware, you can do the conversions manually. Append the following code to the bottom of the services/web-server.js file.

Testing the API

It’s time to test the new CRUD functionality! Up until now, you’ve been using Firefox to test the API, but this will not work for POST, PUT, and DELETE requests. I’ll show you how to how to test the API using curl commands because that’s readily available in the VM. However, you might want to look into using a graphical tool, such as Postman (free) or Paw (Mac only, not free).

Start the application and then open another terminal window and execute the following command to create a new employee.

If the request was successful, the response should contain an employee object with an employee_id attribute. Here’s an example:

In my case, the employee_id value was 227 – you will need to modify the following commands based on the employee_id value you get. For example, to update the new record, issue a PUT against the URL with that id value.

According to my PUT request, it looks like I’m getting a new job!

The UPDATE_JOB_HISTORY trigger in the HR schema will detect the job change and add a row to the JOB_HISTORY table. If you look in that table, you should see a record for the new employee. Execute the following command to delete the job history and employee records.

And there you have it, full CRUD functionality! The API is progressing nicely, but there’s still work to do. In the next post, I will show you how to add pagination, sorting, and filtering capabilities to GET requests.

One last thing, you may have noticed what could be a significant problem with the current PUT and DELETE logic – there’s no lost update detection. I’ll cover that in a future post as well.

22 Comments

  • Pedro Palmares says:

    First of all, congratulations on this tutorial!

    Where should employee_id be generated on the create function?
    I’m getting ORA-01400: cannot insert NULL into (“HR”.”EMPLOYEES”.”EMPLOYEE_ID”) when executing the post request.

    • danmcghan says:

      Thanks, Pedro!

      Are you using the VM described in this post? I am, and I see a trigger in the HR schema named EMPLOYEES_EMPLOYEE_ID_TRG that handles the primary key. Do you not have that?

      • Pedro Palmares says:

        Hi Dan!
        I’m using a XE instead of the VM you described and that’s why I was missing it. I already discovered the source code for it.
        Sorry for the confusion!
        Thanks,
        Pedro

  • Ken Chang says:

    Hi Dan,

    Thanks again for your timely blog posts. Just wanted to say that I’m using Oracle XE 11g and for the CREATE, I had to install a trigger to generate and employee_id on INSERT.

    CREATE SEQUENCE hr_employees_seq INCREMENT BY 1 START WITH 300;

    CREATE OR REPLACE TRIGGER hr_employees_trg
    BEFORE INSERT ON hr.employees
    FOR EACH ROW
    WHEN (new.employee_id IS NULL)
    BEGIN
    SELECT hr_employees_seq.nextval into :new.employee_id from dual;
    END;
    /
    Also, to do a test insert using CURL on Windows, I had to escape the double quotes
    curl -i -X POST -H “Content-Type:application/json” -d “{\”first_name\”: \”Dan\”, \”last_name\”: \”McGhan\”, \”email\”: \”dan@fakemail.com\”, \”job_id\”: \”IT_PROG\”, \”hire_date\”: \”2014-12-14T00:00:00.000Z\”, \”phone_number\”: \”123-321-1234\” }” http://localhost:3000/api/employees

    Best regards,
    Ken

  • Eduardo M Gomez says:

    Do you have all this code in a repository, or git hub so we can see the entire picture?

  • Danicobe says:

    ooh My lord, thank you very much, I was looking for this, man you are so insane, I’m from Bolivia, Takea Big Hug dude and once again thanks,

  • Sebastian says:

    Hi Dan, excellent article. you know that it is happening to me that when making a query to the database it brings more data than when making the query through postman. Why can this happen?

    • danmcghan says:

      Hi Sebastian, thanks!

      What version of the driver are you using? In version 1.x, the default value of maxRows was 100 but it was changed to 0 (meaning unlimited) in version 2. I recommend upgrading to the latest version if possible.

      In the future I will cover streaming large result sets.

  • Sebastian says:

    Dan, you have an idea why give me the following error:
    “Type error: Can not read property ‘rowsAffected’ of undefined”

    • danmcghan says:

      Hi Sebastian,

      You’ve found a bug. Modify this line in the “update” code from if (result.rowsAffected === 1) { to if (result.rowsAffected && result.rowsAffected === 1) {.

      I’ll update the blog and GitHub code soon. Thanks!

      Dan

  • sumit says:

    Hi Dan,
    Thanks for sharing accurate and easy to follow guide.
    A minor correction – Open the database/employee.js file — > it should be db_apis/employe.js

  • Rachid zoom says:

    Thanks for sharing Dan! greetings from morocco.

  • Tai Ho says:

    Hi Dan,
    You have an idea why give me the following error:
    “TypeError: Cannot read property ‘userid’ of undefined” in case post at function getEmployeeFromRec
    function getEmployeeFromRec(req) {
    const employee = {
    userid: req.body.userid,
    fullname: req.body.fullname
    };
    return employee;
    }

    curl -X “POST” “http://localhost:3000/api/employees” \-i \-H ‘Content-Type: application/json’ \-d $'{“userid”: “test”,”fullname”: “Test create by NodeJS”}’

    • danmcghan says:

      If you’re executing your curl command without newlines (as I have in the examples), then you’ll need to remove the backslashes in front of the -i, -H, and -d arguments.

  • James B says:

    Hi Dan,

    Great article. Really appreciate it!

    One question: how do we set the ID field to adhere to a particular format? For example, my DB stores IDs in the form of XXXX.XX (i.e. 2144.24) and then increments by 1 each time starting from the 4th integer and works its way backwards. (2144.24, 2145.24, 2146.24… …2151.24, 2152.24, etc.)

    I’m guessing that to complete this functionality we would have to change the code within this object (still trying to understand it at the moment!):
    entityCopy.PK = {
    dir: oracledb.BIND_OUT,
    type: oracledb.NUMBER
    }

    Regards,
    James.

    • danmcghan says:

      I’m guessing that to complete this functionality we would have to change the code within this object

      You may or may not need to change that object. What is the datatype of the ID column? If it’s a number, then perhaps you don’t need to change it. However, since it’s a decimal (which JavaScript doesn’t yet support), you might want to bind it out as a string. This will prevent any rounding errors that occur when going from Oracle’s Number to JavaScript’s Number.

      still trying to understand it at the moment!

      See this section of the doc and let me know if you have questions: https://oracle.github.io/node-oracledb/doc/api.html#bind

Leave a Reply