START WITH THE DEMO!!!
Why am I yelling? I’m a big fan of starting a presentation or conversation with the sexy bits up front, THE DEMO.
All of the data shown on screen in that animated GIF, was sorted from my Oracle Database, using REST APIs served by ORDS. I’ve demonstrated many times HOW to build REST APIs, and have given you tons of examples of how to put those together to accomplish specific tasks.
But to really make these APIs come ‘alive,’ you need an application!
Our database tools product manager Chris Hoina (blog, twitter, linkedIn) put together a nice Hands-On LiveLab demonstrating how to build an app that sources it’s data from your Oracle Database, using REST APIs.
- LiveLab tutorial – hosted, zero setup required
- Github project – grab the code, do what you want with it
I wanted to play with this myself, so I cloned his repo locally on my Mac, and had a go at it. What I’m showing you below is about 98.4% identical to what you’d see in the hosted LiveLab.
A look at our Flask App
Flask and Django are both super popular web frameworks. Since I’m not a web developer, working with Python is much less intimidating, for ME. And our goal here wasn’t really to teach you how to build a web app, but more to show how having REST APIs for your database makes it EASY to build a fast, secure web application.
Step 1: Configuring the local Python runtime environment
I already have python installed on my machine from playing with it and ORDS before, so if you haven’t done that, DO THAT. VS Code makes it easy, if you open a Python file, it volunteers to do this work for you.
Once I had a local environment (clone the repo or download the project local, unzip….), I fired up VS Code, and did the following:
- setup Flask
- installed the Requests library
That looked like this –
and then this –
Step Two: Get the database and REST APIs sorted
This is both complicated and simple. Complicated in the number of steps, but simple in the concepts that we’ve covered here before.
We essentially need the following for our local Python Flask app to ‘work’:
- an Oracle Database
- running instance of ORDS , configured for that database
- a table with our application data
- some REST APIs to serve up that data
If you take the LiveLab, there’s a hosted VM you can use that has everything setup, but if you want to supply your own DB/ORDS, that’ll work too.
I’m using an 19c database, with a MOVIE table, and the following definition:
And browsing the table, the data looks like this –
Breaking down the app and the REST APIs
To run the app, simply –
The main page of the app is pretty simple, by design.
The GENRE column is a JSON array, and a movie can have one or more GENREs. The list of Genres isn’t normalized, there isn’t a GENRE table that we could do a query against. So instead, we’re going to ask with SQL, for a list of unique strings found in that array across ALL of the rows in the table.
I wrote a blog post showing just how to do this.
If we look at the corresponding Flask app code –
The ‘Search’
In the web form, the user picks a genre via the Radio button, and then below that they move a slider to set the maximum runtime of the movies they’d like to browse. Those two values are appended to another REST API URI via query parameters.
If I’m running this app with Developer Tools open in my browser, I can see the network call to the 2nd REST API –
The backend of the search is powered by this REST API –
Here’s the code –
select movie_id, title, genres "{}genres", year, substr(summary, 0, 80) abbreviated_summary
from movie
where json_exists(genres, '$[*]?(@ == $v1)' -- $v1 is arbitrary
PASSING :genre as "v1") -- v1 is derived from '$v1'
and runtime <= :runtime
order by awards asc nulls lastSo unlike an AUTOREST API where you get all columns, my API here is opinionated. Here are the columns you’re gonna get…and i’m going to sort it by the movies that actually won some awards.
The SQL bits for finding the movies that have the ‘Horror’ tag is explained here, including why we need to use the PASSING clause to the JSON_EXISTS function when using BIND PARAMETERS in our REST APIs.
Navigating the Results
As we scroll down the page, we eventually get to the ‘end,’ but we have a few buttons, ‘Previous,’ ‘Next,’ and ‘Start Over.’
The app knows to activate the Previous and Next buttons IF the response payload includes these attributes (“next”, “prev”):
Getting the movie details
Our results page has a little (+) button on the end of each Summary. Clicking that fires off some JavaScript on the page to ‘fetch’ data from another REST API.
What that looks like in the JavaScript –
What that looks like on the web page with Dev Tools toggled on –
And the actual REST API that answers the Fetch call from our web page –
The movie_id value is appended to the URI, which tells ORDS what record in the table you want as that’s also the Primary Key value.
The AWARDS column in my 19c database is stored as a VARCHAR2, but with an IS_JSON CHECK constraint, so we need to tell ORDS that that data is coming as AS JSON. That’s why you see the awards “{}awards” notation in the SELECT field.
Note: In the LiveLab’s hosted environment this is running in a 23ai database, and the JSON data is being stored in JSON typed columns, so the SELECTs powering the REST APIs do not need any ‘tricks’ to get what you want.
And our page simply prints what we told it to –
Summary
Chris put a ton of work into not just building the app, but making it easy to learn these ORDS concepts by stepping through the Hands-On Lab. I asked him to make sure it was was commented, and he turned it into a ‘book.’
The concepts covered in the lab and put into use with the REST APIs include:
- SQL based endpoints
- Query Parameters
- Navigating by keys using :bind notation
- Querying JSON in your relational tables
- Paginating REST API responses
And if you want to build this all locally, you’re going to need the data. The table ddl and insert scripts can be found in the scripts folder of Chris’ project.
And if you don’t want to do any of this setup work at all, take the Lab!
