How to Connect a Go program to Oracle Autonomous Database

In this blog post, we will see how to connect Oracle Autonomous Database in Go programming language. This can be divided into three parts.

Part 1) We need to download Autonomous Database client credentials.  More details

In the Autonomous database OCI console, open to the service console of the DB which we need to connect. There in the Administration tab, we will find the option to download the client credentials.

 Once It is downloaded, unzip the same to a directory of choice.

Part 2) To connect to the database we need the database user, password, and the connect string.

We can use the admin user, which was provided as part of the database provision, or any other database user. Here we need to form the connect string.

example: protocol://host:port/service_name?wallet_location=/my/dir&retry_count=3&retry_delay=20

We need to form our connect string for the autonomous database we want to connect. We will get details in the tnsnames.ora file which is part of the zip we had downloaded in above Part 1.

There we will find many connection services (more details about them), We can pick the one we want to use for our connection.

example: 

db202107181649_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-mumbai-1.oraclecloud.com))(connect_data=(service_name=iro8q5fzknp5ge4_db202107181649_medium.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-mumbai-1.oraclecloud.com, OU=Oracle ADB INDIA, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))

Now by referring to this we can create our connection string like below.

tcps://adb.ap-mumbai-1.oraclecloud.com:1522/iro8q5fzknp5ge4_db202107181649_medium.adb.oraclecloud.com?wallet_location=/Users/pallab/tool/wallet_DB/

The wallet_location is the path where we had unzipped our client credentials in above Part 1.

Part 3) Now let's write our go code.

We will use godror package to connect. 

Install godor # go get github.com/godror/godror

We need runtime dependency for Oracle Client libraries, We can download the free basic or free light version from https://www.oracle.com/database/technologies/instant-client/downloads.html.

package main

import (

"database/sql"

"fmt"

_ "github.com/godror/godror"

)

func main() {

connectToADb()

}

func connectToADb() {

fmt.Println("Connecting to Oracle Autonoumus database !!!")

db, err := sql.Open("godror", `user="admin" password="testPwd"

connectString="tcps://adb.ap-mumbai-1.oraclecloud.com:1522/iro8q5fzknp5ge4_db202107181649_medium.adb.oraclecloud.com?wallet_location=/Users/pallab/tool/wallet_DB/"

libDir="/Users/pallab/tool/instantclient_19_8/"`)

if err != nil {

fmt.Println(err)

return

}

defer db.Close()

rows, err := db.Query("select 'hello' from dual")

if err != nil {

fmt.Println("Error running query")

fmt.Println(err)

return

}

defer rows.Close()

var resData string

for rows.Next() {

rows.Scan(&resData)

}

fmt.Printf("The response is: %s\n", resData)

}

Note: In sql.open() we have passed our oracle client libraries which we downloaded and unzipped as libDir  parameter.

Useful Links

1. https://blogs.oracle.com/opal/how-connect-to-oracle-autonomous-cloud-databases

2. https://blogs.oracle.com/developers/how-to-connect-a-go-program-to-oracle-database-using-goracle

3. https://godror.github.io/godror/doc/installation.html

Hosting a static website on Oracle Object Storage

We can use Oracle cloud Object Storage to host a static website. Static web pages can contain client-side technologies such as HTML, CSS, and JavaScript. They cannot contain dynamic content such as server-side scripts like PHP, JSP or ASP.NET.

Step 1:

We need to create a public bucket and upload the static website contents. Once the bucket is created edit the visibility to give public access.  

Step 2:

Upload the files, here is the trick. Object storage doesn't support the hierarchy of folder structure. So we have to name each file based on its path.

For example, we have a index.html that has a link to the file page-1.html inside the pages folder.

We have to upload index.html as index.html and page-1.html as "pages/page-1.html". So that links will work.

 

I know this can be tedious since a website contains hundreds of html files, images, css and javascript. So I have automated this process. 

Upload Tool

The scripts are available at https://github.com/pallabrath/myexpjava/tree/master/oci-os-static-web-upload-util

It can be cloned by git or download as a zip (https://github.com/pallabrath/myexpjava) and then unzip

1. Move to the oci-os-static-web-upload-util directory.

2. To run the script we need node and npm to be installed in our environment.

    Node - https://nodejs.org/en/
    Npm - https://docs.npmjs.com/downloading-and-installing-node-js-and-npm

3. Required inputs for this tool need to be configured in upload-config.json.

{
    "webdir" : "/Users/pallab/mylab/oci-os-static-web",   # This is the path of the static web need to be uploaded
    "index" : "index.html",                               # Its the index/home page of your website 
    "configurationFilePath" : "~/.oci/config",            # OCI credential configuration
    "configProfile" : "DEFAULT",                          # OCI credentail config profile 
    "comaprtmentOCId" : "ocid1.compartment.oc1......",    # OCI compartment OCID where we want to upload
    "bucketName" : "myexpdemo"                            # bucket name to be created
 } 

Details how to create OCI Credential configuration refer # https://docs.cloud.oracle.com/en-us/iaas/Content/API/SDKDocs/typescriptsdkgettingstarted.htm#Getting_Started

Example :

[DEFAULT]
user=ocid1.user.oc1..<your_unique_id>
fingerprint=<your_fingerprint>
key_file=~/.oci/oci_api_key.pem
tenancy=ocid1.tenancy.oc1..<your_unique_id>
customCompartmentId=ocid1.compartment.oc1..<your_unique_id>

How to create OCI API signing key

https://docs.cloud.oracle.com/en-us/iaas/Content/Functions/Tasks/functionssetupapikey.htm

4. Once the credentials are set and inputs are provided in upload-config.json. We are all set to run the util.

This will create a bucket with visibility public and publicAccessType = ObjectReadWithoutList.

It will upload all the files in the user-selected folder (mentioned as webdir in upload-config.json) to the bucket. 

The util will print the url of the index file. This will be the website homepage url.

Custom Domain

We can configure this as a http redirect or url forwarding to use the custom domain. 

Run a Spring Boot application on OCI Compute Instance

In this article, we will see how to deploy a spring boot application in Oracle cloud compute instance.

Creating a new Spring Boot app and running it locally

We will create a spring boot app and test locally. 

start.spring.io can be used to start. Or there is a sample Hello World project I have created can be referred https://github.com/pallabrath/myexpjava/tree/master/spring-boot/spring-web-rest-demo

package myexpjava.demo; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.ResponseBody; @Controllerpublic class DemoController { @GetMapping("/hello") @ResponseBody public String sayHello() { return "Hello World !!!"; } } ./gradlew bootRun This command will run the application and we can test the same in any browser in the url http://localhost:8080/hello/ It should print Hello World !!! in browser Creating an OCI compute instance and deploy the app. We will try to deploy this app directly to a single OCI compute instance and we will run it.  For this example, we have used the standard Oracle-Linux-7.8 image. To create a compute instance in OCI console, we need to navigate compute and create instance. To access the instance we need ssh keys, to genarte the same please refer https://docs.cloud.oracle.com/en-us/iaas/Content/Compute/Tasks/managingkeypairs.htm  Once the compute is ready. We need to install Java, as our Spring Boot App requires a java environment to run. 1. ssh to compute instance ssh -i ~/.ssh/oci_compute opc@<public ip> of the compute instance> 2. Install java in the compute instance, for this example jre is enough. sudo yum install jre-12.x86_64 3. Configure the firewall to open port 8080, as our app will run in 8080 sudo firewall-cmd --zone=public --permanent --add-port=8080/tcp sudo firewall-cmd --reload Make a directory where we will copy our spring app mkdir spring-app/ 4. Network configuration to allow Internet traffic to our compute instance.      a) We need an internet gateway configured in the VCN where the compute instance belongs      b) Route table entry to allow the traffic through the internet gateway     c) Security Ingress rule needs to be added to allow traffic.  5. Copy our Spring Boot app from laptop to compute instance and run. ./gradlew clean build This will build and create the spring-web-rest-demo-1.0.jar in ./build/libs To copy the same to our compute instance scp -i ~/.ssh/oci_compute build/libs/spring-web-rest-demo-1.0.jar opc@<public ip>:spring-app/ Now ssh to the compute and run the app ssh -i ~/.ssh/oci_compute opc@<public ip> of the compute instance> java -jar spring-app/spring-web-rest-demo-1.0.jar Our spring app should run in port 8080. If all the configs are correct  we should able to see Hello World !!! in the browser, by trying compute's  http://<public ip>:8080/hello If you don't see please revisit step 3 and 4