CRUD in JS
CRUD stands for Create, Read, Update, and Delete. It references the ways that we are going to communicate with our database. Because this is a recorder, we are only going to CUD, because we will be reading from our DynamoDB table. This is just a back-up.
By now you are probably wondering how to connect all of stuff that we have prepared to our API.
First, we are going to need the npm module “oracledb”. Run “npm install oracledb”.
Unlike other npm modules, you can’t just install it. You also need to make a library that it will expect.
Go to https://www.oracle.com/technetwork/topics/winx64soft-089540.html (note: if you are using something other than windows, you will need to find that operating system’s version). Accept the license agreement and download. Get the basic light package. It should be all you need.
Unzip the folder and put it in your Program Files folder
You are going to need to put it in your Environmental variables, just like you did with Python: AWS CLI
When you are done, it should look something like this:
In your project, make a folder called “recorder” with a .js file called index.js.
This is where we are going to implement our code that will connect us to our table.
Let’s start writing the index file. Let’s require the npm oracledb and format it a bit.
Then we are going to set up some params we can use. This is going to take some explanation, but let’s do that after it is written.
So, these are going to be the default parameters that our object is going to take. When something uses process.env, it is asking for an environmental variable so the first two(our username and password for oracle) are going to need to be in our environmental variables. We’ll put them there next. The second one checks for the HANDEL_ENVIRONMENT_NAME, which is only going to be found after something is uploaded to AWS using handel. This is there because there will be two different way to access the database depending on where we are. This code makes sure the right code is being used.
So let’s set up our environmental variables (if you are not using Webstorm, good luck).
If you are using Webstorm click RUN->Edit Configurations.
Click the “+” in the top left hand corner to make a new run configuration and make it a nodejs.
Name it “recorder” and set your environmental variables to your username and password for the database.
I didn’t circle it in the picture, but make sure that your JavaScript file is recorder\index.js.
Apply the changes and we are ready to go on. Let’s write a simple function that will test if you can connect to the database.
Line 13 lets you pass in different params if you want to.
Add a line to test the function:
Let’s run the program. Make sure your VPN is running and then run it in debugger.
Click the bug-> 
You should see something like this:
Let’s write a function that will add something to the table.
There are two options here to commit the changes you make to the database. The first is to explicitly make a commit to the database. You can do with the commit() function, like it is commented in my code, but there a better way. Just turn on auto-commit.
Let’s take a moment to talk about bind variables. They look like this:
Anything that is in the array after the SQL statement will be inserted in where the colon and follow by the bind variable is. The name of the bind variable isn’t really important, just the order they are in. This is really useful because it is readable and stops SQL injection. https://www.w3schools.com/sql/sql_injection.asp
Add a couple of lines to test your function and then your code should look a bit like this:
You should get something like this when you run it in debugger:
Just to be sure that it worked, let’s open up SQL Developer and look at our table.
Open up the data and it should look like this:
Nice!
Now we just need to update the table and delete from it.
These functions should be quite similar to addTable. If you get the SQL code, can you figure it out??
Give it a try!
Here is what it should look like:
Run some tests and once everything is working, pat yourself on the back and comment out all the tests.
