Creating a REST API from an existing Database using Graal Cloud Native

January 22, 2024 | 8 minute read
Kris Foster
Principal Product Manager
Text Size 100%:

Creating a REST API from an existing Database using Graal Cloud Native

TLDR;

With the latest release of our developer tooling for VS Code, we have added some great new features that enable you to easily create a REST-based Java application from an existing database schema. Find out how in this blog post.

Introduction

We have all been in the situation of needing to expose an existing database schema through an API. This can be a time-consuming process as you first need to understand the tables in the schema and then the relationships between the tables. Then you need to create a Java project, add support for your database, set connection details and the list goes on. The support for Oracle Autonomous Database (ATP) in the latest version of the Micronaut® Tools extension (available within the Graal Cloud Native Extension Pack) for VS Code streamlines this process radically. In this blog post, we step through the process of going from zero to a working application that exposes an existing database schema, contained within an ATP instance in the cloud, built on top of the Graal Cloud Native framework.

Setting Up

The only things you need to follow along with this post are:

  1. Install and configure the Oracle Cloud Infrastructure Command Line Interface (OCI CLI).
  2. Install the latest version of the Graal Cloud Native Extension Pack. This guide shows you how to get set up with no fuss: Setting up Your Deskttop.

With the above two dependencies installed and your existing database, we are good to go.

Creating a new Graal Cloud Native Project

We can use the "new project wizard" for Graal Cloud Native (GCN) to create a new Maven Java project for us. Open the VS Code command palette (on macOS: Cmd+Shift+P, on Windows: Ctrl+Shift+P) and search for "Graal Cloud Native: Create New Project".

Create a new Graal Cloud Native Project

The wizard walks you through the process of creating a new GCN project. GCN is a cloud-agnostic development framework that works with GraalVM Native Image and lets you easily connect your applications to different clouds. In this post, we are going to use Oracle Cloud Infrastructure. Select the following options when creating the project :

  1. Micronaut Version: Default
  2. Application Type: Application
  3. Select installed Java runtime to use for local builds: Pick one of your local JDKs
  4. Project Name: Demo
  5. Base Package: com.example
  6. Project Services: NONE
  7. Features: Oracle Cloud Autonomous Transaction Processing (ATP)
  8. Build Tool: Maven
  9. Test Framework: JUnit
  10. Cloud Provider to use: OCI
  11. Generate Sample Code: Yes 

Then save the newly created project. We need to edit the project configuration file to modify a few configuration properties. In a future release of the tooling, this won't be required. Open the file named oci/src/main/resources/application-oraclecloud.properties and comment out the following lines:

#flyway.datasources.default.enabled=...
#datasources.default.ocid=...
#datasources.default.walletPassword=...

We now have a GCN project that is ready to go, along with some helpful sample code. Let's look next at how to connect to an Oracle database running in the cloud, and to generate code from the existing tables.

Connect to an Oracle Autonomous DB Instance

Before we connect to the database we need an existing schema. Follow the steps in Database Sample Schemas to install the Oracle sample HR schema into your Oracle ATP database. So with a database containing some tables and data, and our newly generated GCN project, the next step is to connect them together. In the VS Code sidebar, you will see a DATABASES tab. Open this and click Add Oracle Autonomous DB. This launches a wizard to select an existing Oracle Autonomous DB.

Connect to an Oracle Autonomous Database

When you have selected your Oracle Autonomous DB it will show up in the DATABASES tab - see below:

View the database schema

Make sure you connect to the database after adding it. You can do this by right-clicking on the database in the DATABASES tab which brings up a context menu and then by clicking on Connect. The Micronaut Tools extension in VS Code will take care of providing the database connection details to the application when you run it, without requiring you to explicitly set any connection details in property files or as environment variables. More on this later.

Great, so now we have our application with some sample code and it is connected to an existing Oracle Autonomous Database. We now have enough set up that we can start generating our application's REST API directly from the database tables. Let's start by creating our Micronaut Data Entity classes. As a quick aside it is worth noting that GCN is build upon the Micronaut Framework. So when we build a GCN application we are building a Micronaut application and we can make use of all of the modules offered by the Micronaut Framework. In this particular case, we will be generating classes that use Micronaut Data to access the database. If you installed the sample database schema linked earlier in this article into your database you should now have the following tables available:

  • COUNTRIES
  • DEPARTMENTS
  • EMPLOYEES
  • JOBS
  • JOB_HISTORY
  • LOCATIONS
  • REGIONS

These are the tables that get created and populated when you install the sample HR schema. To create data entity classes from these tables we will use the "Micronaut Templates" functionality shipped within the GCN Extension Pack.

  1. Select the package where you want to create the classes and right-click to bring up the context menu. Select New from template. I have created new packages for the various classes we are going to create and you can see them in the screenshot below.
  2. Next, select the Micronaut templates.
  3. Then, select Micronaut Data Entity Classes from Database.
  4. Select the tables from which you want to create Micronaut Data entity classes:

Create the Micronaut Data Entity Classes

Add Data Repository Classes

We now need to wrap each Micronaut Data entity class in a repository, which provides a convenient way to query, update and delete the entities. Our support for Micronaut Data enables you to create both a CRUDRepository or a PageableRepository to wrap around your data entity. We will be creating a PageableRepository from our COUNTRIES entity. The process is much the same as before: select the package in which to create our repository classes, then use the right-click context menu to select New from template, and then select Micronaut Data Repository Interfaces from Entities, taking care to select the entity class for the COUNTRIES table. Create the Micronaut Data Respoitory Interfaces

Now we have a data repository class to wrap around our data entity class. Easy. As this is a Micronaut Data repository it offers all of the great functionality you get with Micronaut Data, such as out-of-the-box CRUD operations and support for Micronaut Data’s find by methods.

Expose Your Tables Through a Controller

The final step, before running our application, is to add a controller class. This is the HTTP-based front end that will wrap around our data repositories and provide endpoints that we can call to read and update the underlying database tables.

This again is easily done through the predefined Micronaut templates that are provided. Select the package in which to create our controller class, then use the right-click context menu to select New from template, and then select Micronaut Controllers (from repositories), taking care to select each of the repositories you want to expose.

CReate the Micronaut Controllers from the Micronaut Data Repository Interfaces

We also support creating DELETE and GET methods for the controller that call through to the repository and will be supporting the POST method in the future.

Add Delete and Get Methods to the Micronaut Controller

Run the REST-Based API wrapped in an Application

With the above steps complete we now have a working REST API that wraps around our existing database tables. Next, let’s run the application. If we navigate to the main application class for our application, oci/src/main/java/com/example/Application.java, we can run the application by clicking the Run code lens that appears above the main() method in the class. VS Code will provide the database connection details to the running application. No need to put these in a configuration file or to set any environment variables!

Run the Application

And to test out the functionality we can take a sneak peek at the Micronaut Tools REST Endpoint Explorer tool. More on this in a future post. The REST Endpoint Explorer can be found in the sidebar of VS Code. Click on the Micronaut logo to open it and it can be seen in the bottom half of the sidebar window. We will use it to query the /country/ endpoint.

You may get prompted to install the REST Client extension. Install this as this works with the REST Endpoint Explorer.

Testing the REST Endpoints

Summary

We hope you have had a taste of how the Graal Cloud Native Tooling for VS Code can accelerate working with existing databases and schemas. The tools available within the Graal Cloud Native Extension Pack for VS Code (add link) enable working with Oracle Autonomous Databases in the cloud; generating the boilerplate data model classes from the database; generating a REST-based API from your data model; All without leaving your IDE. We hope this post will inspire you to try this out!

Micronaut® is a registered trademark of Object Computing, Inc. Use is for referential purposes and does not imply any endorsement or affiliation with any third-party product.

Kris Foster

Principal Product Manager

Kris Foster is a Product Manager for GraalVM and Graal Cloud Native. He has been working in software development for over 20 years, a large part of that work building Java applications as an independent consultant. 


Previous Post

JDK 21.0.2, 17.0.10, 11.0.22, and 8u401 Have Been Released!

Clifford Wayne | 1 min read

Next Post


Graal Cloud Native Tools Release - 4.2.1

Kris Foster | 4 min read