Dockerizing Sybase and connecting to it from Elixir

By: on May 29, 2019

On a recent project, we were required to connect our software to a Sybase DB on Linux, which we managed through FreeTDS and ODBC. I will summarize the relevant details in this post. Please be aware that a number of alternative solutions are conceivable.

I built Docker images for our software, so that I would have to work out the setup of various dependencies just once. From then on, everybody on the team could run our software on their local machines (without having to know any details), and deployment to production was straightforward, too.

It is tremendously useful to be able to run one’s entire software stack in local on-demand environments. This makes it possible to run end-to-end tests before deploying to production. Even though Sybase was part of the client’s environment, a local environment would not be functional without it. Building a Docker image for Sybase turned out to be an interesting challenge.

The remainder of this post comprises two sections. The first describes how Sybase can be set up to run inside a Docker container and the second describes how to set up FreeTDS and ODBC such that Elixir can communicate with Sybase.

Dockerizing Sybase

A Linux installation archive for Sybase is available from SAP; please ensure that you have an appropriate license for your use case.

On my journey, I found a number of resources helpful:

My solution is sufficiently different from the above insofar as it uses a different Linux distribution, stores all actual data on the host filesystem, and has a simple import mechanism. You can find a skeleton project on GitHub, which you can adapt to your own needs.

Given that you have obtained the Sybase installation archive as per the project’s README.md, you can build a docker image by invoking the build_images.sh script.

This script builds a number of intermediate images until the final image, sybase:fresh, can be assembled. One reason for using multiple steps is that one cannot bind-mount a host directory during docker build. The first image (sybase:base) merely carries out the basic installation without reference to bind-mounted directories. The resulting image is then run as a container with a bind-mount for the data directory. The container id is recorded, and a script for creating the actual databases is executed inside the container, resulting in the bind-mounted directory becoming populated. Once terminated, the container is converted into an image (sybase:empty) via docker commit container_id. The script then archives the bind-mounted directory so that Sybase can be reset to this pristine state of emptiness in the future. The next step adds an entry point for starting up Sybase, producing the final image, sybase:fresh. Finally, a script for table creation is executed in an instance of sybase:fresh, resulting in changes on the bind-mounted directory, which are then archived (into sybase_data_archives/sybase_empty_tables.tgz) for future re-use.

The overall process takes a rather long time. Not everybody on our team had the means to build the final image from scratch, and given that we did not have a private registry set up, I resorted to exporting the image via:

docker save sybase:fresh -o sybase_fresh.tgz

Once in possession of the sybase_fresh.tgz, team members could get bootstrapped by invoking:

docker load -i sybase_fresh.tgz

In order to run an instance, you first need to extract/copy the Sybase data files to a convenient location for bind-mounting:

cd /tmp
tar xvfz sybase_empty_tables.tgz

You can then start a container as follows:

docker run -d --name sybase --network endtoend -p 5000:5000 -v /tmp/sybase_data:/var/sybase sybase:fresh

If you made it this far, you can use a DB explorer such as DBeaver to connect to your Sybase instance.

host: localhost
port: 5000
user: user1
pass: pass123

Elixir

Erlang has an ODBC interface. With the help of three fantastic libre libraries FreeTDS, unixODBC and tdsodbc, a connection to Sybase can be established.

First, a basic Dockerfile:

FROM elixir:1.8.1

RUN apt-get update && apt-get install -y --allow-unauthenticated freetds-bin unixodbc tdsodbc
COPY docker /setup
RUN mv /setup/odbc.ini /etc && \
mv /setup/odbcinst.ini /etc && \
cat /setup/freetds.conf.sybase >> /etc/freetds/freetds.conf && \
odbcinst -i -s -f /etc/odbc.ini && \
rm -rf /setup

The above Dockerfile assumes that there is a docker subdirectory with the following contents:

docker/freetds.conf.sybase:

[Test]
  database = mydb
  host = sybase
  port = 5000
  tds version = 5.0
  client charset = ISO 8859-1
  tds version = 5

docker/odbc.ini:

[TESTdsn]
  Driver = FreeTDS
  Trace = No
  ServerName = Test
  Database = mydb

docker/odbcinst.ini:

[FreeTDS]
  Description = tdsodbc
  Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
  Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
  FileUsage = 1
  CPTimeout = 5

Building an image is as simple as:

docker build . -t elsy:1

Run an iex session:

docker run -it -network endtoend elsy:1

Connect to Sybase (assuming a container from the previous section is running):

:odbc.start()

{:ok, conn} = "DSN=TESTdsn;UID=user1;PWD=pass123" |> String.to_charlist() |> :odbc.connect([])

:odbc.sql_query(conn, 'select @@version')

If you get greeted with the following, then everything is connected up just fine:

{:selected, [[]],
[
{'Adaptive Server Enterprise/16.0 SP03 PL02/EBF 27415 SMP/P/x86_64/SLES 11.1/ase160sp03pl02x/3096/64-bit/FBO/Fri Oct 6 04:51:57 2017'}
]}

Conclusion

It is astonishing what can be done these days and that so many helpful resources exist. At the same time, I feel an inexplicable urge to search the internet for videos of Rube Goldberg machines.

Share

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*