How to drop and create a database in Docker — Part 1
I am a Django developer and from time to time we work on projects that interact with the database. This is where the Django ORM comes in, it helps the developer to create and update database tables.
In order to create an environment that closely mimics that of the production environment on my local machine, I use Docker to containerize my project.
However, there are times you can have some challenges when you make migrations and for some reason, they are all mixed up and you get errors that you feel the best thing to do is to drop the database and create it again. This is ideal if you are working on a new project or you have already backed up your data.
It is usually confusing how to go about dropping and creating a database in Docker. One way to go about this is to use the exec command. A typical docker-compose file will look like the following:
This is a simple docker-compose file with just two services — web and db.
To drop the database, I would need to first get the container ID which can be retrieved by the command docker ps . This will show all the running containers on your system. In our case, we would see the 2 containers that represent the web and the database from our docker-compose file. The one that is important to us is the one that belongs to the database — postgres:11 .
docker-compose exec <database service name> psql -U <db_user_name> -d postgres -c “DROP DATABASE <db_name>;” .
An example will be:
docker-compose exec db psql -U dbuser -d postgres -c “DROP DATABASE appdb;”
From the above example, the database service name is db because that is the name we use to reference the database in the docker-compose file. dbuser is the name used for the database user and appdb is the database name.
If you run this command with the two containers still running, you will get the error — ERROR: database “<db_name>” is being accessed by other users . This is because the other container — web is still accessing the db container. To resolve this issue, we shut down the container using docker-compose down .
Then we can comment out the web section in the docker-compose file, leaving only the db section. Then we run the container again using docker-compose up . This time, it's only the db container that is running.
We can now run the following:
docker-compose exec <database service name> psql -U <db_user_name> -d postgres -c “DROP DATABASE <db_name>;”
docker-compose exec <database service name> psql -U <db_user_name> -d postgres -c “CREATE DATABASE <db_name>;”
Now that the database has been recreated, stop the container, uncomment the web section and restart the container so we can have both the web and db running together again.
Now, you can run docker-compose exec web python manage.py migrate to run your migrations on your new database.
I am sure there are many other ways to do the same thing, please let me know in the comments.
Enjoy.
