Learn Docker With My Newest Course

Dive into Docker takes you from "What is Docker?" to confidently applying Docker to your own projects. It's packed with best practices and examples. Start Learning Docker →

Docker Tip #49: Importing a Database SQL File with Docker Compose

blog/cards/docker-tips-and-tricks.jpg

When dealing with databases such as PostgreSQL, MariaDB or MySQL, you've likely wanted to import a raw SQL file. Here's how.

I’m going to write this in the context of using PostgreSQL and psql but these commands work just the same with MariaDB / MySQL and mysql. I’m also not setting any usernames or passwords in the commands to keep it readable.

Importing a SQL file without Docker (works):
psql exampledatabase < exampledata.sql

That’s easy enough. You just let psql know which database you want to import the data to and then redirect the file in through STDIN.

Importing a SQL file with Docker Compose (doesn’t work):

You would think it would be easy enough right? Just do:

docker-compose exec postgres psql exampledatabase < exampledata.sql

In theory that should work, but it won’t (at least not with Docker Compose 1.20). You’ll end up with an error of ValueError: file descriptor cannot be a negative integer (-1).

For some reason Docker Compose doesn’t like doing this. Now, you could try adding -T to exec to disable TTY, in which case it will import without errors but there won’t be any data. That doesn’t really help us.

Importing a SQL file with Docker (works):

Since we can’t do it with Compose until that bug is fixed, we need to do it with the raw docker command instead. Here’s how:

docker container exec -i $(docker-compose ps -q postgres) psql exampledatabase < exampledata.sql

You could simplify that by replacing $(docker-compose ps -q postgres) with the name of the container if you know it beforehand, otherwise it will dynamically grab it from whatever you have defined as the service name in your docker-compose.yml.

Well done. Now you know how to restore a database from a SQL file!

Free Intro to Docker Email Course

Over 5 days you'll get 1 email per day that includes video and text from the premium Dive Into Docker course. By the end of the 5 days you'll have hands on experience using Docker to serve a website.


Comments