Connecting Go application to Oracle Database–On Prem and Autonomous , with and without Oracle Client libraries

I have been struggling a bit with this one: how to connect from a Go application to an Oracle Database – both a locally running database as well as an Autonomous Database Instance running on Oracle Cloud. Ideally, my application does not require Oracle Client Libraries to be installed in order to contact a database as that rather complicates the installation process and general portability of the Go application. On the one hand this smooth single binary that can run without additional requirement on a runtime configuration and JRE like installation and then one the other this non-trivial set up of Oracle Instant Client libraries.

I can use the pure (client library free) go_ora library to connect to a local database as well as a to an Autonomous Database on OCI (using an Oracle Wallet). Using the GoDrOr package – which does require installation of the Oracle Instant Client libraries – I was also able to successfully connect to and interact with both a local Oracle Database as well as an Autonomous Database instance in Oracle Cloud. This is the result one would except or at least hope for. However, to get this far I was struggling for some considerable time – to get the syntax of the connection string exactly right. To save others (you, myself in the future) some time, I have described my experiences in this article. The corresponding code is available in this GitHub Repo: https://github.com/lucasjellema/go-oracle-database.

This figure visualizes the topics in this article: Go applications connecting to local Oracle Database and to Autonomous Database on OCI. One application uses only package go_ora and does not require the Oracle Instant Client library. The other uses package godror (pka goracle) and requires the Oracle Instant Client. For connecting to the local database both applications only use simple connection details – user, password, host, port, service name – but no wallet). For the connection to the Autonomous Database both applications use Oracle Wallet (to at least provide the certificate used for securing the transport to the database).

Note that anything described here about the local database equally applies to any other Oracle Database that is accessible in the tradtional way (without the use of an Oracle Wallet, just relying on user, password, host, port, service name ). And what is described regarding the Autonomous Database applies to any database that is accessed using Oracle Wallet for securing the network interactions.

Preparing Databases

I have used two Oracle Databases for the experiments described in this article. One a local Oracle Database 21c XE running in a Docker container on my laptop, the other an Oracle Autonomous Database (ATP) 19c running on Oracle Cloud Infrastructure (in region Ashburn, US East).

To run the local database, I used a very simple and straightforward statement:

docker run -d -p 1521:1521 -e ORACLE_PASSWORD=TheSuperSecret1509! gvenzl/oracle-xe

Gerald Venzl of Oracle maintains a series of (Docker) Container Images that run a slim  version of Oracle Database, the XE edition, that is free to use (up to 20GB of data and using a maximum of 2 CPU threads & 2 GB RAM). He describes these images and how to use them in this blog article. The previous statement runs an XE 21 database (at least, that is what it does today when 21c is the latest available container image) and sets the passwords for SYS and SYSTEM to the indicated value.

Inspecting the logs for the container gives me further information:

To run an Autonomous Database Instance is almost simpler still. An ATP instance can be created in several ways (including through OCI CLI and Terraform), but most straightforward for a first time is probably through the OCI browser console. Tim Hall provides a good description – and there are many more to be found. Two things are important for me: the database is accessible from everywhere (in order to connect to it from my laptop) and I need the database wallet that contains the SSL certificates needed for the mTLS interaction. Download the wallet for the ATP instance (click the DB Connection button in the ATP page in the OCI Console).

After creating these two databases, I also installed the Oracle VS Code extension that allows me to connect to these databases, browse their contents and interact with them similar to the way I use SQL Developer. I used this extension to establish that the databases are running and can be accessed using the connection details I have gathered – including the wallet – and over the network locally and from laptop to cloud – that I am on.

Preparing local development environment

I am not going to describe in detail how to set up the local development environment. In my case, it runs on Windows 10, uses WSL 2 with Ubuntu 20.4 and has Go 1.17.5 running.

I belated educated myself a little on the Go database/sql package – Go’s equivalent to Java’s JDBC it seems – using this fine tutorial – a general introduction to working with the database/sql package – http://go-database-sql.org/overview.html .

Oracle Database Connection without Instant Client using pure Oracle Go Driver go_ora

The ideal case for me is connecting from Go to Oracle Database without using additional “stuff” to set up. Stuff such as Oracle instant client with OCI libraries and possible environment variables to be defined. Just run my Go application with the required database connection details and have it just work feels like the most desirable solution. And with go_ora I have that working perfectly against the locally running database.

All I need: database host and port (localhost and 1521 in this case), database service name (XE in this case) and a username and password. And a simple Go program that imports go_ora. The bare minimum in terms of code:

Here I have left out all error handling. Not a good idea obviously for serious code. But in terms what is the least you have to do to query the current timestamp of a local Oracle Database – this is probably it. No additional libraries are needed to be installed. This code will run as is – you need to provide your own database specifics of course.

In this example, go_ora/v2 provides the implementation of the database/sql driver that registers itself using “oracle” (and can therefore be referred to as such in the sql.Open() call. Note the import entry: _ “github.com/sijms/go-ora/v2” – this takes care of making the go-ora (v2) package available in the Go application. The call to sql.Open(“oracle”,…) is routed to the implementation in go_ora.

When the Oracle Database that I want to interact with needs to connected to using an Oracle Wallet – such as in the case of an Oracle Autonomous Database (ATP, ADW, AJD, …) – the I need to pass the file system location of the Oracle Wallet. More precisely: I need to specify the absolute directory path to the directory that contains the file cwallet.sso that is part of the wallet. The wallet typically is provided initially in a zip-archive. This archive should be extracted (or at least this file should) and the path to the directory that contains the file is what will be called the walletLocation.

The code for creating the connection string in case of a wallet to be used is shown below (in this case dbParams is a map[string]string that contains the values to be used.

    connectionString := “oracle://” + dbParams[“username”] + “:” + dbParams[“password”] + “@” + dbParams[“server”] + “:” + dbParams[“port”] + “/” + dbParams[“service”]

if val, ok := dbParams[“walletLocation”]; ok && val != “” {

        connectionString += “?TRACE FILE=trace.log&SSL=enable&SSL Verify=false&WALLET=” + url.QueryEscape(dbParams[“walletLocation”])

    }

   db, err := sql.Open(“oracle”, connectionString)

I have been struggling a while before I understood the right syntax for the connection string (WALLET=) and the need for escaping the value of the Wallet Location (to convert at least the slashes in the directory path to characters allowed in a URL)- hence the use of url.QueryEscape (in package net/url).

The code for accessing both local and remote Autonomous Oracle Database using go_ora is in this GitHub repo and more specifically in this file. (also check this common.go that provides the connection details and the SQL to be executed).

Here is the code that connects with or without Oracle Wallet

And the code that invokes it and provides the database connection details:

And the output from running the code:

Notice the huge difference in elapsed time for exactly the same statements running locally or intercontinentally against the database in the cloud – running 5000 KM from where my laptop is sitting).

Oracle Database Connection using the Oracle Instant Client and Oracle Go Driver godror

A quite prominent alternative to go_ora is the Go package godror (formerly called goracle, but renamed because of trademark issues – Oracle does not want anyone to use oracle their names). This package also provides an Oracle driver that database/sql can use when a sql.Open is performed for either “oracle” or “godror”. This package – unlike go_ora – requires an Oracle Instant Client library to be installed on the system running the Go application.

I will not discuss the installation of the Oracle Instance Client – see the resources for this article below.

The actual code for connecting with the database using godror is available in this file. It is very similar to the code shown previous for the go_ora package. The only differences are the ones highlighted here, and even these are not so different:

When I run the Go application to connect to the two databases using the godror package, the results are the same yet somewhat different:

The time taken for the interaction with the remote Autonomous Database is considerably longer, more than twice as long, compared to the go_ora package. This difference is a topic for another experiment – this article was all about connecting to the database, local and remote, from a Go application, using two of the most prominent Oracle database drivers for Go.

Resources

My Gitub Repo with the code described in this article: https://github.com/lucasjellema/go-oracle-database 

Tutorial on Go database/sql – general introduction to working with the database/sql package – http://go-database-sql.org/overview.html 

Gerald Venzl’s article on Oracle Database XE container images – https://geraldonit.com/2021/08/15/oracle-xe-docker-images/

Conversation with sijms () – creator of the go_ora library that does not require installation and set up of Oracle Instant Client

GitHub Repo for go_ora package,  Documentation for Go Ora package, Go Package Dev on go_ora.

Article  by @PallabRath: How to Connect a Go program to Oracle Autonomous Database (July 2021) – link

Installation instructions for GoDrOr package and some instructions on installing Oracle Instant Client.  Download page for Oracle Instant Client.

Introduction and overview the Oracle VS Code extension. Also this Quickstart for Oracle VS Code extension.