Use Emacs sql-mode to connect to database on a Docker image

While working on a project I had to load and process some resources from a MySQL database. I had a database dump and all I needed was to sudo apt-get install mysql but I decided against it because that would just bloat my OS with software used only once and drain my laptop battery with the service running in the background.
Instead, I decided to restore the database on a Docker image for MySQL and query the data using mysql-client.

Install mysql-client locally

This one is simple; just run:

sudo apt-get install mysql-client  

Install Docker on Ubuntu

The first thing to do is to head to Docker documentation for instalation instructions which I've copied here:

  • Install packages to allow apt to use a repository over HTTPS:
sudo apt-get install \  
    apt-transport-https \
    ca-certificates \
    curl \
    software-properties-common
  • Add the official GPG key for Docker
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -  
  • Setup the stable repository Makee sure to select the proper architecture; in my case it's amd64.
sudo add-apt-repository \  
    "deb [arch=amd64] https://download.docker.com/linux/ubuntu \
    $(lsb_release -cs) \
    stable"
  • Update the package index
sudo apt-get update  
  • Install Docker
sudo apt-get install docker-ce  

Restore MySQL database to a Docker container

  • Download the Docker image for MySQL
sudo docker pull mysql  
  • Create an empty database
sudo docker run --name <container-name> -e MYSQL_ROOT_PASSWORD=<password> -e MYSQL_DATABASE=<db-name> -d mysql  

This will create a new container with an empty database and login root.

  • Restore database from dump
sudo docker exec -i <container-name> mysql -u<user-name> -p<password> --database=<db-name> < <path-to-sql-file>  

Notes:

  1. Make sure that there is no space between -u and <user-name>, e.g. for user root the option should be -uroot
  2. The same goes for password - e.g. if password is my-secret then the option should be -pmy-secret
  3. path-to-sql-file should point to a file on host OS

Connect to MySQL database running on Docker container from Emacs

  • First, start the container
sudo docker start <container-name>  
  • Get the IP Address of the container
    • Get the container configuration using sudo docker inspect <container-name>
    • Copy the IP Address from the output under NetworkSettings/IPAddress
  • In Emacs execute M-x sql-mysql
    • for User: enter the value for <user-name> (root is the default)
    • for Password: enter the value for <password>
    • for Database: enter the value for <db-name>
    • for Server: enter the IP Address from 2.

That's it. Happy querying!