Reach Oracle DB in serverless

Published in

Google Cloud - Community

8 min readNov 7, 2019

In my previous story, I detailed how to reach on-premise resources with serverless product by performing a simple ping. Now, I want to use these resources with real workload. One of them is an Oracle database and I want to be connected and to query it.

But, which serverless product to use to reach an Oracle DB? Cloud Functions? Cloud Run? App Engine?

For performing tests in the cloud, you can set up an Oracle XE database on a n1-standard-1 VM with a public IP. Don’t forget to open the firewall rules!

Cloud Functions

Cloud Functions is a popular serverless product. You bring your code, a function, and you run a gcloud command for deploying it. Compilation, deployment, scaling up and down (up to 0) are managed by the platform.

However, the main trade-off is the limited number of language supported: Python, Go and NodeJS. Anyway, no problem, languages don’t matter, let’s try to use Oracle DB with Cloud Functions.

The Oracle constraints

Oracle products, tools and drivers aren’t open sourced and you can’t download them without being connected to the Oracle portal, being authenticated and having accepted the licence.

By the way, you can’t easily automate the build and there is no native library in Python, NodeJS or Go to connect directly your code to the Oracle database. You have to install an intermediary Oracle software, named “Instant Client”, on your environment to communicate with the database. Then, you can use open source libraries which create a link between your code and “Instant Client” software. And, thereby, to reach the database

In summary, you have to deploy your code AND an Oracle binaries on Cloud Function. It’s not possible. With Cloud Function you only bring your code, not 3rd party binaries. Cloud Functions with Python, NodeJS or Go aren’t the good product for using Oracle DB in serverless.

You have code examples on GitHub if you want to try and deploy by yourself

Cloud Run alternative

Announced early in 2019, Cloud Run is the new serverless product of Google Cloud. Cloud Run brings serverless capability to container. Developers have to respect 2 contracts: HTTP container (host a webserver) and stateless (no volume/disk mount). And that’s all!

Containers have the capability to break the existing limitation of Cloud Function: you can use any language and you can add any library, dependency and binary (here an example).

Cloud Run and Cloud Function are very close in their serverless approach; Cloud Run is better for portability, for the tests and, often, for reducing the costs.

Remove Cloud Functions limitations

Thanks to the containers’ capabilities, the objective is simple: Create a container with Instant Client and run the code in it.

Start by writing Go code for testing the connection. (Python version, NodeJS version are also available)

The webserver code is provided in GitHub but not presented here, no value for this topic.

package functionimport (
	"database/sql"
	"fmt"
	_ "gopkg.in/goracle.v2"
	"net/http"
)func OracleConnection(w http.ResponseWriter, r *http.Request) {	dbIp := os.Getenv("ORACLE_IP")
	dbSchema := os.Getenv("ORACLE_SCHEMA")
	dbUser := os.Getenv("ORACLE_USER")
	dbPassword := os.Getenv("ORACLE_PASSWORD")	db, err := sql.Open("goracle", dbUser + "/" + dbPassword + "@" + dbIp + ":1521/" + dbSchema)
	
        if err != nil {
		fmt.Println(err)
		w.WriteHeader(http.StatusInternalServerError)
		fmt.Fprintln(w, err)
		return
	}
	defer db.Close()	rows, err := db.Query("select 'Great!' from dual")
	if err != nil {
		fmt.Println("Error running query")
		fmt.Println(err)
		w.WriteHeader(http.StatusInternalServerError)
		fmt.Fprintln(w, err)
		return
	}
	defer rows.Close()	var value string
	for rows.Next() {
		rows.Scan(&value)
	}
	w.WriteHeader(http.StatusOK)
	fmt.Fprintln(w, value)
}

Add the dependency to go.mod (or Python requirements.txt, NodeJS package.json)

module servergo 1.12require (
	github.com/gorilla/mux v1.7.1
	gopkg.in/goracle.v2 v2.20.1
)

Now, build the container with the Dockerfile (Python version, NodeJS version)

FROM golang:1.12 as builder# Copy local code to the container image.
WORKDIR /go/src/function/
COPY go.mod .
ENV GO111MODULE=on
RUN CGO_ENABLED=1 GOOS=linux go mod downloadCOPY . .
# Perform test for building a clean package
#RUN go test -v ./...
RUN CGO_ENABLED=1 GOOS=linux go build -v -o server# Now copy it into Oracle base image.
FROM oraclelinux:7-slimARG release=19
ARG update=3RUN  yum -y install oracle-release-el7 && \
  yum-config-manager --enable ol7_oracle_instantclient && \
  yum -y install oracle-instantclient${release}.${update}-basic && \
  rm -rf /var/cache/yumCOPY --from=builder /go/src/function/server /server
CMD ["/server"]

Time to test!

Build the container (or use Cloud Build, see README.md)

gcloud builds submit --tag gcr.io/<your project>/oracle-connection

Deploy on Cloud Run (Update your environment variables with your values)

gcloud beta run deploy oracle-connection --platform managed\
    --image gcr.io/<your project>/oracle-connection
    --region us-central1 --allow-unauthenticated
    --set-env-vars ORACLE_IP=<YOUR IP>,ORACLE_SCHEMA=<YOUR SCHEMA>,\
ORACLE_USER=<YOUR USER>,ORACLE_PASSWORD=<YOUR PASSWORD>

Test your query

curl $(gcloud beta run services describe oracle-connection \
    --region us-central1 --format "value(status.address.hostname)" \
    --platform managed)> Great!

Boom, Connected!

Cloud Run trade off

Great, that works!! Hmmm, Yes and No. Yes, I can reach an Oracle database deployed on Compute Engine, but what about my on-premise Oracle DB?

Today (October 2019), it’s not yet possible to connect Cloud Run service to VPC with a serverless VPC connector. Only Cloud Function and App Engine are compliant with it. Cloud Functions can’t reach Oracle DB so, let’s try with App Engine.

Edit (May 2020)
Cloud Run is now compliant with serverless VPC connector in Beta. You no longer have network limitation

App Engine solution

App Engine is the oldest service on Google Cloud Platform. It exists in 2 versions: Standard and Flex

Standard environment

App Engine standard allow to bring you code, a microservice, and you run a gcloud command for deploying it. Compilation, deployment, scaling up and down (to 0) are managed by the platform.

Very similar the my Cloud Function definition and, thereby, the main issue is also similar: Impossible to install Oracle software on the App Engine standard environment. Still not the right solution.

Code is present in the Go, Python and NodeJS directory if you want to test the deployment. See README.md

Flexible environment

App Engine Flexible remove many trade off of standard version, like the limited number of language or the impossibility of installing software. How? Like Cloud Run, App Engine flexible is based on containers.

Great, let’s try to deploy our working container on App Engine Flex. For this, I will use the custom runtime.

Start with a app-flexible.yaml description file for Go container

runtime: custom
env: flex
service: go-serverless-oracle-flexenv_variables:
    ORACLE_IP: "<YOUR IP>"
    ORACLE_SCHEMA: "<YOUR SCHEMA>"
    ORACLE_USER: "<YOUR USER>"
    ORACLE_PASSWORD: "<YOUR PASSWORD>"

Deploy on App Engine Flexible

gcloud app deploy app-flexible.yaml

Test your query

curl $(gcloud app browse --no-launch-browser \
     -s go-serverless-oracle-flex)> Great!

Awesome! That works! And App Engine is compliant with serverless VPC Connector! Problem solved!!!

It’s not because I’m never satisfied, but in reality, this solution is not “perfect”. Indeed, for me, App Engine Flexible has 1 main default: it doesn’t scale to 0. Sure, you don’t have to care about server configuration and scale up. But you only have 1 running instance and pay for it, event if there is no traffic.

App Engine Flex isn’t my “strict” definition of serverless. I want to pay as I use the service. Can we do better?

My Java, My Hero!

I limited my tests to Python, NodeJS and Go because of function limitation. But, on App Engine, Java is also supported. Currently, Java is not a trendy language in serverless and microservice domain.

However, the Java community is very active with several language flavors (Kotlin, Groovy, Scala), several new microservice-oriented frameworks (Quarkus, Vertx, Micronaut) and new JIT/AOT (just in time/ahead of time) compilation with GraalVM. By the way, the language is still up-to-date and very interesting to use and to test.

Oracle and Java are close for years now, and, because of this or not, and surprisingly, the Oracle Java library (Jar) doesn’t required any Oracle binary to work to be connected to the database

Of course, you still have to download the Jar driver from the Oracle portal and save it in your project (see resource directory) because it’s not directly downloadable by anonymous build systems (Maven in my case)

App Engine standard solution

By the way, App Engine standard which is compliant with Java 8 and Java 11, scales to 0 and is compliant with serverless VPC connector. Awesome! Lets deploy a Java version

Start by defining an endpoint for testing the connexion.

package dev.gblaquiere.serverlessoracle.java.endpoint;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class OracleConnection extends HttpServlet { 

    public void doGet(HttpServletRequest request,
                      HttpServletResponse response) 
                      throws IOException {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");

            String dbIp = System.getenv("ORACLE_IP");
            String dbSchema = System.getenv("ORACLE_SCHEMA");
            String dbUser = System.getenv("ORACLE_USER");
            String dbPassword = System.getenv("ORACLE_PASSWORD");

            Connection con = DriverManager.getConnection(
                    "jdbc:oracle:thin:@" + dbIp + ":1521:" + 
                     dbSchema, dbUser, dbPassword);


            Statement stmt = con.createStatement();

            ResultSet rs = stmt.executeQuery(
                               "select 'Great!' from dual");
            while (rs.next())
                response.getWriter().println(rs.getString(1));

            con.close();
            response.setStatus(HttpServletResponse.SC_OK);
        } catch (Exception e) {
            System.out.println(e);
            response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
            response.getWriter().println(e.getMessage());
        }
    }
}

Now, create the app.yaml file in the src/main/appengine directory (update the environment variables with your values)

runtime: java11
service: java11-serverless-oracleenv_variables:
    ORACLE_IP: "<YOUR IP>"
    ORACLE_SCHEMA: "<YOUR SCHEMA>"
    ORACLE_USER: "<YOUR USER>"
    ORACLE_PASSWORD: "<YOUR PASSWORD>"

runtime java11. Java8 is different and also provided in the source code

Install manually the Oracle dependencies

mvn install:install-file -Dfile=src/main/resources/ojdbc7.jar \
    -DgroupId=com.oracle -DartifactId=ojdbc7 -Dversion=1.0.0 \
    -Dpackaging=jar

Deploy with Maven (don’t forget to update the projectId maven properties in the pom.xml file)

mvn clean package appengine:deploy

Finally, test it

curl $(gcloud app browse -s java11-serverless-oracle \
    --no-launch-browser)> Great!

Boom! I got it! A serverless platform which scale to 0 and able to query my on-premise Oracle database!

What is different for container ?

Cloud Run and App Engine Flexible require a container. With Java, we don’t need to install Oracle Instant Client. Thereby, the container is simpler to build but it’s mandatory to install manually the Oracle Jar driver (with maven in my case)

FROM maven:3.5-jdk-8-alpine as builder

# Copy local code to the container image.
WORKDIR /app
COPY pom.xml .
COPY src ./src

# Install Oracle Jar to Maven
RUN mvn install:install-file \
    -Dfile=/app/src/main/resources/ojdbc7.jar \
    -DgroupId=com.oracle \
    -DartifactId=ojdbc7 -Dversion=1.0.0 -Dpackaging=jar

# Build a release artifact.
RUN mvn package -DskipTests

FROM adoptopenjdk/openjdk8:jdk8u202-b08-alpine-slim

# Copy the jar to the production image from the builder stage.
COPY --from=builder /app/target/java-*.jar /java.jar

# Run the web service on container startup.
CMD ["java","-Djava.security.egd=file:/dev/./urandom","-Dserver.port=${PORT}","-jar","/java.jar"]

Then the deployment and the tests are similar to other languages. See the source code for more details.

Summary

Finally, be connected to Oracle database implies more constraint than expected for building and deploying a solution on serverless platforms.

Hopefully, the container packaging solves a lot of problems but not all. Indeed, Cloud Run can’t reach on-premise resources (until the compatibility with the serverless VPC Connector) and App Engine Flex doesn’t scale to 0.

Unexpectedly, Java solves all the problems and can be deployed on App Engine standard which scale to 0 and can use serverless VPC Connector.

Now I’m looking forward to see the expected features officially announced and usable. 2020 will be an interesting year!

Additional remarks: Except for Java, the same code works on the the 4 serverless products (App Engine standard, App Engine flex, Cloud Run and Cloud Function) for Node, Python and Go. Only config files are to customize and it’s easy to change of platform to leverage of their specific features.