Multi-Use Databases in Docker

Containers Aug 26, 2021

As you delve further into containers and different types of services, you'll most likely start to come across docker-compose stacks which include their own databases. These tend to be photo servers like PhotoPrism, file-hosting or -sharing apps like Nextcloud/Owncloud, or wiki/blog containers like Wiki.js or Ghost, but there are a lot of other uses for databases.

Here I'll explain a bit more about them, but if you want to dive straight into getting your database set up, you can head over to the relevant MySQL or Postgres installation guides.


What is a database?

Well, it's a storage medium for the information you want to keep. It is structured in a way that makes the storage and queried retrieval of information quick and efficient.

The benefit of using a database rather than just a folder system is that your information is protected. Unless you specify otherwise, information can only be accessed by the user who created it. In this way if anyone managed to get into your system and succeeded in copying your database, unless they knew the user/password combination to access that database, the information would, in theory, remain unreadable.


Different types of Database

All the databases we will be looking at today are SQL (Structured Query Language) databases. In practice you are likely to come across 3 types of database for your containers:

PostgreSQL (also known as 'postgres')

First developed way back when in the late-1980s, this is the big brother of our trio. It is the heaviest and most resource-intensive, but is far better suited to larger databases with multiple analytics process requirements.

MySQL

Created in 1995 to be a more lightweight version of postgres, MySQL boasts users such as Facebook, Google and Github. It is faster and generally seen as the go-to for scalable web applications, though you wouldn't use it for the truly large-scale databases which postgres is still better suited to.

MariaDB

The baby of the family, MariaDB launched in 2009 and was designed to be a perfect replacement for MySQL - to the point that they are both fully compatible. It has the ability to use a multitude of different storage engines, counts users such as AWS and RedHat as its customers, and achieved one of its key goals early on: to be faster than MySQL.

notable mention for SQLite, a 'serverless' fork of MySQL. This means it doesn't use a traditional database structure, rather having the database integrated as part of an app. As it needs no management, we won't be including that in this article

Which database to use when

If you ever decided to get into a debate about this, it could well be endless. Apps are generally created in a way in which they're optimized for use with a particular database. Some are even optimized to a specific version number of that database.

Luckily for us, the developers of those containers will normally tell us which one we should use, and likely as not which version. In the cases where they leave it up to the end-user, choose the one you like the most.


Creating Our Databases

We'll now look at how to set up our database and management containers which will allow us to create multiple databases and users in the same container. As mariaDB is essentially MySQL for these purposes, I will provide walkthroughs for PostgreSQL and MySQL only.

In the interests of keeping these articles concise, I've created separate posts for the above:

MySQL

click here to visit the article

Postgres

click here to visit the article

note that while these walkthroughs are based on practices for creation on a Synology NAS, they are likely to work for docker instances on other machines too

A side-note on caching

In general, our databases have some caching capabilities, but nothing extensive. A way to improve this is to use another self-hosted service called Redis. This will work alongside other databases and can dramatically speed up recurring query returns. At some point I will put together an article on it, but for now you can read up more on the subject here.


Docker: Setting up MySQL and phpMyAdmin database containers
A step-by-step walkthrough to set up your MySQL or MariaDB container and their complementary management tool, phpMyAdmin, all in docker
PostgreSQL databases in docker and how to manage them
A blow-by-blow explanation to create a postgres and pgAdmin4 docker container stack, configuring them and creating multiple, accessible databases

PTS

PTS fell down the selfhosted rabbit hole after buying his first NAS in October 2020, only intending to use it as a Plex server. Find him on the Synology discord channel https://discord.gg/vgSq5pcT

Have some feedback or something to add? Comments are welcome!

Please note comments should be respectful, and may be moderated