Connecting to Oracle Databases using Godror and Sqlx

In recent weeks I've been re-learning Go, looking for similarities with existing Java libraries. Naturally, I went on a search for storing and retrieving data from an Oracle database. There are no shortages of options in the Java space for interacting with data stored in a database. Options range from plain JDBC, lightweight data mappers such as Sql2o and Jdbi, more elaborate mappers such as MyBatis, all the way to the full-fledged ORMs such as Hibernate, Eclipselink, and every other implementation of JSR 338 (JPA 2.2). However, on the Go side, things appear to be simpler. There's the well-known sql package which in a way is comparable to JDBC as it provides the building blocks for accessing data without data mappers. And then there's sqlx which provides lightweight data mappers similar to Sql2o and Jdbi. There are also a couple of ActiveRecord clones but I'm staying away from them as the concepts provided by it appear to map poorly outside of Ruby.

My colleague Sergio posted a while ago how to get started with Godror, the Go Driver for Oracle. Decided to take it for a spin. Note that Godror relies on a library called ODPI-C which supports connecting to Oracle databases using the C language. Other languages besides Go use this library to deliver support as well. Oh, and by the way, ODPI-C is provided and supported by Oracle as Open Source.

I usually run experiments on several platforms but in this case I wanted to give it a try on Apple hardware (i.e my laptop). There's no Oracle database available for OSX but that's not a problem these days, as you can run pretty much anything you want on Docker. Adrian Png wrote a deep dive on getting Oracle Express running on containers and though the post describes running Docker on Windows it pretty much works for OSX as well. Summarizing the setup steps, this is what I had to do:

• Latest version of Go (get it here).
• Docker Desktop for Mac (get it here).
• Latest Oracle Instant Client (get it here).

Then followed Adrian's instructions to create a Docker image for Oracle Express, have it running with port 1521 exposed to the host as 51521. You'll need to expose the dynamic libraries using any of the following methods:

• Copy all lib* files to the same directory where your Go program is located. This allows you to run quick checks and tests but will not let you run the binary anywhere else.
• Copy all lib* files to $HOME/lib.
• Copy all lib* files to /usr/local/lib.

The last two option do let you run the Go binary from any directory in the filesystem. When in doubt consult the ODPI-C Installation guide. If running OSX Catalina or later, you may need to grant access rights to each individual lib file when executing the program for the first time.

Alright, after verifying that the database is up and running and accepting connections (again Adrian's post is quite good on the steps to make this happen) it's time to turn to the code, which as it happens is quite straight forward. The following app connects to the database, creates a single table, inserts a couple of rows, then queries said rows with two different methods.

What's different from examples you may have seen of the "database/sql" package is that "sqlx" lets you load data directly into structs, that's the mapping capability I was looking for. There are other nice features as well, feel free to explore. I noticed on the first tries that the app would panic when a column could not be mapped to the target struct, mostly due to a mismatch in the case which is why you see name hints in the struct's members. Running this simple application results in the following output

And that's pretty much it. I found "sqlx" to be quite close in spirit to the lightweight JDBC mappers, i.e, you can craft SQL as needed and spend less time mapping types and values by hand.

Credits:
Photo by Martina Janochová