Building a simple Node.js application with Oracle

Anyone who knows me will tell you, my expertise is the Oracle Database and not web applications or JSON. But my new role is all about pushing myself out of my comfort zone, so I wanted to get some hands on experience with both application development and JSON in the Oracle Database.

My colleague, Mark Drake, the PM for JSON in the Oracle Database, suggested I get my feet wet by trying the new Movie Ticketing Application tutorial available on the JSON page on Oracle.com.

The Movie Ticketing Application is written in Node.js and connects to the Oracle Database via a REST Data Service. Since I recently signed up for the Oracle Database Exadata Express cloud service, I had the perfect Database setup for a web application.

So, armed with the application code and a detailed set of instructions, I began my journey.

The first thing I had to do was install Node.js. Since my main environment is an Apple Mac, installing Node.js was very simple: download the installer followed by a couple of clicks. To check I had done it correctly my husband taught me the equivalent of “Hello World” in JavaScript.

MCOLGAN-mac:~ mcolgan-mac$ node

> a='Hello World'

'Hello World'

> console.log(a)

Hello World

Mark supplies all of the application code; you just need to install it.

The installation was a little more complicated for me as I had to navigate around the Oracle firewall but I followed Mark’s instructions carefully and Voila, the application is now installed.

The application creates and retrieves JSON documents, so the next step is to create a document store, which is a schema-less environment where JSON documents can be stored and accessed.

Starting in 12c (12.1.0.2), JSON documents can be stored and accessed directly in the Oracle Database. Unlike XML, there is no new JSON data type in 12c. Instead JSON is stored as text, in any table column, using a VARCHAR2, CLOB or BLOB data type. Using existing data types ensures that JSON data is automatically supported with all of the existing database functionality, including Oracle Text and Database In-Memory.

That’s great but how do we get the JSON documents in and out of the database with a Node.js application?

12c also offers new APIs known as Simple Oracle Document Access (SODA) that provides a documents and collections interface to the Oracle Database to support schema-less applications. SODA comes in two flavors, Java and REST. Mark’s application uses the REST API to store and access JSON documents in the Oracle Database via HTTP calls.

So, what do I have to do to set up SODA?

Since I’m using the Oracle Database Exadata Express cloud service as my backend, I get to cheat on this step. Right on the front page of the Exadata Express console is a button that will automatically set up a document store.

Clicking the button opens a dialog window that enables you to create a new database schema and enable SODA for REST for that schema.

Don’t panic if you don’t have access to a cloud account. You can setup SODA very easily on any Oracle 12c database. Beda Hammerschmidt (the developer responsible for JSON support in the Oracle Database) does a great job of explain how to manually setup SODA in an on-premises database on his blog. Note SODA for REST requires Oracle Database patch 20885778 to be installed.

But I digress. Let’s get back to the tutorial. If I look in the MOVIES schema that I just setup on my Exadata Express account, I see that its currently empty.

SELECT TABLE_NAME FROM user_tables;
 
no ROWS selected

Since this is a schema-less application, there are no tables or indexes required upfront. Once the application starts loading data, SODA will automatically create tables based on the collections or documents inserted. More on this in a moment, as we haven’t quite finished the setup.

The final part of the setup is the editing of the Node.js application configuration files. These files tell the application which database to connect to and what schema to use etc. Editing these files gave me my first taste of a real-world JSON document, which wasn’t as scary as I had originally thought. After all it’s a text file that contains name / value pairs. All I had to do was edit the values to match my environment.

Once configured, start the Node.js application server by executing the command:

node INDEX.js

Now the application server is running, we can launch the application in a web browser.

Before we can use the application to find what movies are currently playing in our local theaters, we need to load the theater, movie and showtime information into the database. The information comes from theMovieDB.org and Mark explains how to get your own account with them in the tutorial. To begin the population of theater and movie information, clicking on the load button.

After the theater information is populated, we can now see objects in the MOVIES schema, including a table called THEATER that contains the JSON documents with all of the information we need about each movie theater.

SELECT TABLE_NAME FROM user_tables;
 
TABLE_NAME
------------------------
DR$THEATER_SEARCH$I
THEATER
DR$THEATER_SEARCH$K
DR$THEATER_SEARCH$R
DR$THEATER_SEARCH$N
DR$THEATER_SEARCH$U
 
6 ROWS selected.
 
DESC THEATER 
 Name					   NULL?    TYPE
 ----------------------------------------- -------- ----------------
 ID					   NOT NULL VARCHAR2(255)
 CREATED_ON				   NOT NULL TIMESTAMP(6)
 LAST_MODIFIED				   NOT NULL TIMESTAMP(6)
 VERSION				   NOT NULL VARCHAR2(255)
 JSON_DOCUMENT					    BLOB

Since the JSON_DOCUMENT is stored as a BLOB in the database, we can query it just as we would any BLOB.

SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(JSON_DOCUMENT)) FROM theater WHERE rownum < 2;
 
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(JSON_DOCUMENT))
------------------------------------------------------------------------------------------------------
{"id":1,"name":"Regal Jack London Stadium 9","location":{"street":"100 Washington St","city":"OAKLAND"
,"zipCode":"94607","state":"CA","phoneNumber":NULL,"geoCoding":{}},"screens":[{"id":1,"capacity":68,"f
eatures":{"threeD":FALSE,"reserveSeats":FALSE},"ticketPricing":{"adultPrice":14.95,"childPrice":9.95,"
seniorPrice":9.95}},{"id":2,"capacity":77,"features":{"threeD":FALSE,"reserveSeats":FALSE},"ticketPric
ing":{"adultPrice":14.95,"childPrice":9.95,"seniorPrice":9.95}},{"id":3,"capacity":104,"features":{"th
reeD":FALSE,"reserveSeats":FALSE},"ticketPricing":{"adultPrice":14.95,"childPrice":9.95,"seniorPrice":
9.95}},{"id":4,"capacity":103,"features":{"threeD":FALSE,"reserveSeats":FALSE},"ticketPricing":{"adult
Price":14.95,"childPrice":9.95,"seniorPrice":9.95}},{"id":5,"capacity":77,"features":{"threeD":FALSE,"
reserveSeats":FALSE},"ticketPricing":{"adultPrice":14.95,"childPrice":9.95,"seniorPrice":9.95}},{"id":
6,"capacity":69,"features":{"threeD":FALSE,"reserveSeats":FALSE},"ticketPricing":{"adultPrice":14.95,"
childPrice":9.95,"seniorPrice":9.95}},{"id":7,"capacity":126,"features":{"threeD":FALSE,"reserveSeats"
:FALSE},"ticketPricing":{"adultPrice":14.95,"childPrice":9.95,"seniorPrice":9.95}},{"id":8,"capacity":
95,"features":{"threeD":FALSE,"reserveSeats":FALSE},"ticketPricing":{"adultPrice":14.95,"childPrice":9
.95,"seniorPrice":9.95}},{"id":9,"capacity":110,"features":{"threeD":FALSE,"reserveSeats":FALSE},"tick
etPricing":{"adultPrice":14.95,"childPrice":9.95,"seniorPrice":9.95}}]}

A far more elegant way to query JSON data is to take advantage of our simplified syntax, which uses the same dot notation we use in SQL.

TABLE_NAME.COLUMN_NAME.JSON_STRING

So in our case we could see the first 10 movie theaters loaded into the database using the following query.

SELECT t.json_document.name FROM theater t WHERE rownum < 11;
 
NAME
----------------------------------------------
Regal Jack London Stadium 9
AMC Bay Street 16
UA Berkeley 7
AMC Metreon 16
Century San Francisco Centre 9 AND XD
CineArts @ Empire
UA Stonestown Twin
Century 20 Daly City AND XD
CineLux Chabot Cinema
Tiburon Playhouse 3 Theatre
 
10 ROWS selected.

The other tables visible in the MOVIES schema are for the Oracle Text index that is automatically built on top of the JSON documents by the application. The indexing strategy is defined in the collections.json file that Mark supplies with the application. I’ll blog in more detail about using Oracle Text to search JSON document in the future.

After the theaters are load, you need to load the movies, posters and screens. After which the following tables are in the MOVIES schema.

SELECT TABLE_NAME FROM user_tables;
 
TABLE_NAME
----------------------------------------------------------------------------------------------------
THEATER
DR$THEATER_SEARCH$I
DR$THEATER_SEARCH$K
DR$THEATER_SEARCH$R
DR$THEATER_SEARCH$N
DR$THEATER_SEARCH$U
MovieTicketLog
TicketSale
Movie
DR$MOVIE_SEARCH$I
DR$MOVIE_SEARCH$K
DR$MOVIE_SEARCH$R
DR$MOVIE_SEARCH$N
DR$MOVIE_SEARCH$U
Poster
Screening
 
16 ROWS selected.

So we have 6 tables automatically created, one for each collect and two Oracle Text indexes, one on the JSON_DOCUMENT column in THEATERS and the other on the
The application is now ready to use and the screen should automatically switch to list all of the theaters. If you click on a MOVIES link next to one of the theaters, it will show you what movies are playing in that theater and their showtimes.

If you like me, want to  get your feet wet with JSON, I recommend trying the new Movie Ticketing Application tutorial available on the JSON page!