Setting up Database Servers for Development on Mac OS X Using Docker
If you are beginning your software development career and using Mac, and interested in using relational databases like MySQL/MariaDB, PostgreSQL, Microsoft SQL Server, Azure CosmosDB SQL or Oracle Database, then this article is for you!
If you like this article, please clap for it! Click on the little hands icon to the left or bottom of this page.
Now, in the past when I used to run Linux on my laptops, I'd just install each database directly into my environment. Sometimes I'd face problems like dependency-hell, conflicts, native libraries missing, and would eventually end up running the databases in isolated VirtualBox VMs. In today's containerized world, this is past.
If you really want to make your own developer life easier, get used to Docker and spin up databases in containers. All of them.
Running RDBMS in containers may not be suited for production, but for development/testing environments? It is the perfect fit.
If you haven't already done it, make sure you install Homebrew and Cask — package managers for Mac OS. Not only for this article but pretty much for everything you will eventually install in the future.
Pre-requisites
You will have to install Docker. And while Kitematic is optional, I actually recommend you do so. Easier to manage the containers in your system once you create them (start/stop/restart/delete).
$ brew cask install docker kitematicOnce you have Docker installed, make sure you have an account in the Docker Store — sign-up here. That will be needed for some of the databases. Once you have your account, log in to it either in the UI or with docker login.
CLIs for Database Clients
To connect to a database, you will need a client, and ideally one that you can quickly use through the command-line/terminal. For each database below you will find two instructions: one to install and start the database using Docker, and one to install and connect to that database using a CLI.
Most databases provide Client CLI within the Docker image, but I find it extremely useful to have these clients installed so you can easily connect to databases running elsewhere, such as in the Cloud, or remote in some server in your intranet. Plus, you can also automate stuff writing scripts.
Not that you can't do these things with CLI inside Docker… It's just my preference.
Getting Started
All four major databases provide Docker images these days. And I was closely involved in the build up of the Docker image for the Oracle Database, gracefully put together by my friend Gerald Venzl. But the other databases provide very useful and well-assembled Docker images too, and I am sure you will appreciate them all.
So let's begin.
MySQL
There are two MySQL images on Docker Hub you should be aware of:
- Official Oracle MySQL Server image
https://hub.docker.com/r/mysql/mysql-server/ - Community driven MySQL image
https://hub.docker.com/_/mysql/
I always prefer to use products through tools officially provided by the maker of that product. Feel free to try [2], but below are instructions for Oracle's MySQL Server.
Download and start MySQL container:
$ docker run --name=mysql1 -d -p 3306:3306 -e MYSQL_USER=dev -e MYSQL_PASSWORD=dev123 -e MYSQL_DATABASE=dev mysql/mysql-serverNote the use of special environment variables. Check the documentation of this image for more information and other options such as how to connect with root.
Install MySQL Shell and connect to the database:
$ brew cask install mysql-shell
$ mysqlsh dev@localhost:3306Microsoft SQL Server
Microsoft has been doing pretty cool stuff in recent years. Not only they have made this product available for Linux, they now also offer pretty good Docker images for it.
Download and start Microsoft SQL Server container:
$ docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=SqlServer2017' -p 1433:1433 -d microsoft/mssql-server-linux:2017-latestInstall and connect using sqlcmd — more details here.
$ brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
$ brew install --no-sandbox mssql-toolsConnect to SQL Server:
$ sqlcmd -S localhost,1433 -U SA -P SqlServer2017PostgreSQL
This image is provided and supported by the PostgreSQL Docker Community. Basically people at Docker and also folks who are involved in PostgreSQL development. I found this image to also be very straightforward and simple to use.
Download and run PostgreSQL:
$ docker run --name mypostgresdb -e POSTGRES_PASSWORD=psql123 -p 5432:5432 -d postgresInstall and connect using PSQL:
$ brew install psqlodbc
$ psql -h localhost -U postgresAzure CosmosDB
CosmosDB is an interesting Microsoft Azure’s service that allows developers to use different APIs to connect to the same datastore. For example, you can use CosmosDB as a drop-in replacement for MongoDB, or Cassandra. It also provides a SQL API. You just point an existing application previously developed for one of these databases on CosmosDB, and it will just work (well, give it or take). It also scales beautifully. Even Thomson Reuters is using it.
The easiest way to use CosmosDB is again, IMO, through the command line. But to play with it of course you will first need an Azure account. Besides giving some few hundreds of dollars for you to use in your first 30 days, Microsoft Azure also has many services that are always free (to certain limits/quotas), and some services that are free in your first 12 months, if you activate your subscription during or after the first 30 days.
So, go to azure.microsoft.com/free right now and create an account.
Install the Azure CLI. For Mac OS X you just use Homebrew:
$ brew install azure-cliFor Linux, check the documentation which contains instructions for different package managers and distributions.
And if you do decide to play with it, just follow this "Create an SQL API account using CLI" sample!
Oracle Database
During my time at Oracle, I led the company's presence on GitHub, helping teams structure their projects, and also launch newly open sourced libraries, tools, resources, and samples sets. There was, though, one particular project I was taking care of since the beginning: the docker-images repository. And I am extremely proud of the advancements and improvements of developer experience for some key Oracle products that we were able to deliver thanks to Docker and the amazing work from folks like Monica Riccelli, Avi Miller, Gerald Venzl and many other engineers and product managers.
In the past it used to be quite frustrating to install and run Oracle Database for development purposes, but thanks to Gerald and Docker, it is now as simple as it can get.
Now, while anyone can go to the docker-images repository, download Dockerfiles and build their own images for the commercial products, in fact there are some pre-built images available for common products on the Docker Store and also on the Oracle Container Registry server.
Below I outline the steps required fordownloading and running an Oracle Database image from the Docker Store.
At this point I will assume you already have a Docker ID (account). If you don't, create one.
— Go to the Oracle Database image page on Docker Store
— Click on Proceed to Checkout (blue button to the right)
— Log in with your Docker ID
— Fill the form, click on "I agree…", and then on Get Content
— You should now have access to the image documentation page, and instructions for the docker pull command
— Now you can go back to your terminal to continue this article.
In your terminal, log in and pull the Oracle DB image:
$ docker login
$ docker pull store/oracle/database-enterprise:12.2.0.1Sit tight. The image is almost 3GB because it is the (almost) fully featured database. Which is nice.
Run Oracle Database as a Docker container:
$ docker run -d -it — name oracledb -p 1521:1521 store/oracle/database-enterprise:12.2.0.1It may take a while, but it will work. Check the docker logs for progress.
In the old days you'd probably connect to Oracle Database using SQL*PLus. In the 21st century though, you may want to use SQLcl. This new tool is way more interactive in the terminal than SQL*Plus ever was. So I highly recommend you to try it. Besides, it is designed by friends of mine at Oracle, namely krisrice 🏒🍺🚴🏻 and Jeff Smith 🥃 ☜.
To install SQLcl and connect to the image created above, do the following:
— Download the ZIP file from Oracle
— Extract and go to the sqlcl/bin folder on yor terminal (feel free to drop a link on /usr/local/bin if you want)
Connect to the database:
$ ./sql sys/Oradoc_db1@//localhost/ORCLPDB1.localdomain as sysdbaSummary
I hope this article helps you get started with relational databases easily and painlessly. Because no matter what programming language you use, data must be stored :-)
