Reach Oracle DB in serverless

guillaume blaquiere
Nov 7 · 8 min read

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

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

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

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

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 download
COPY . .
# 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-slim
ARG release=19
ARG update=3
RUN 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/yum
COPY --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

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.

App Engine solution

Standard environment

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

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-flex
env_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!

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

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-oracle
env_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 ?

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

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.

Google Cloud Platform - Community

A collection of technical articles published or curated by Google Cloud Platform Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

guillaume blaquiere

Written by

Team leader, scrum master, speaker, writer and polyglot developer, Google Cloud platform certified, serverless addict and Go fan.

Google Cloud Platform - Community

A collection of technical articles published or curated by Google Cloud Platform Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade