Run postgres database in docker container

Share this video with your friends

Send Tweet
Published 2 years ago
Updated 2 years ago

In this tutorial we will spin the docker container with official postgres image and access the database with psql to set up an easy playground for PostgreSQL relational database.

Dimitri Ivashchuk: [0:00] We want to spin off the docker container with running postgres database instance inside of it. Let's write the docker-compose file. In docker-compose file we just need one service, which should be the postgres database using the official image. We already expose and map the ports to 5432, the default port for postgres.

[0:24] In our environment variables we specify that the POSTGRES_DB should be named prisma and we are trusting anyone who can connect to the server to access the database. We don't set up any authentication as a password for ease of this tutorial.

[0:42] The volumes help us persist the data between the restarts of the container, so when we restart the container the data will be kept in db/data and specified path.

[0:55] Let's start the docker-compose. You need, of course, to be running the Docker, Docker for Mac, or Docker for Windows, and have docker and docker-compose installed, and the CLI tools.

[1:08] Let's try to run docker-compose up to see if everything is working. We see that the postgres scripts are running and everything seems to be OK, which means that the database has been created, the server has been started.

[1:27] We can try to change the terminal window and do psql prisma with the default user postgres, and we are in database. We can try to do the list of databases and see that our prisma database has been successfully created.

[1:46] We're almost finished. Let's add the initial schema for the database that we want to have, so the creation of tables. We want to create just one table users with the structure of id, name, and id being the primary key.

[2:06] If we save this and run docker-compose up again, our database shall be initialized with the correct stuff already. Doing psql prisma postgres, get into database and then log in the \dt we see that we have the table users, which is already there.

[2:36] You might want to at some point play with your database initial structure, so there is a handy script that you can write that just does the removal of the data from volumes. As init script is only run when the container is first spinned off and the data is generated, you will need to remove the database from volumes to alter the table which is initially loaded.

[3:06] In our case, let's run the clean script. The data has been removed. Let's rename this to list and run docker-compose up. Let's do psql prisma postgres, and then list the database tables that we have. We see that the name now is list and not users as it was before.

Harry
Harry
~ 2 years ago

Thank you for sharing your knowledge. Can you share the code, probably on GitHub?