Setting Up & Connecting to PostgreSQL (from Host) via Docker

A brief note on how to set up PostgreSQL via Docker and create tables in a database.

Step A. Create a docker compose file (docker-compose.yml) with the following content.

version: "3"
services:
  db:
    image: "postgres:latest"
    container_name: "my_postgres"
    ports:
      - "54320:5432"

In the above configuration, we simply create a service called db with several information, such as:

• Created from an image postgres using the latest version
• A container created from the image called my_postgres
• Mapping host port at 54320 to container port at 5432. Doing so enables us to connect to the database via the server / host port. Every request connected to port 54320 will be redirected to port 5432

Step B. Create environment variables for PostgreSQL.

Let’s presume that the environment variables are created temporarily. Open up your Terminal and type the following commands:

export POSTGRES_USER=postgres
export POSTGRES_HOST=localhost
export POSTGRES_DATABASE=my_test_db
export POSTGRES_PORT=54320

The above commands tell us several things, such as:

• We will log in to the database server with username postgres
• Since the container is located on our own machine (local), we set the database host to localhost. Please adjust with your needs accordingly
• The database name is defined as my_test_db
• All request to the database is passed to the local machine’s port

Step C. Create tables in the database

To do this, we need to write several scripts, such as the followings:

File: my_test_table_orm.py

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Table

Base = declarative_base()


class MyTestTableORM(Base):
    __tablename__ = “my_test_table”
    __table__ = Table(__tablename__, Base.metadata, 
                      Column(‘field_pk’, String, primary_key=True), 
                      Column(‘field_a’, Integer), 
                      Column(‘field_b’, String)
                )

File: my_test_table_util.py

import os

from sqlalchemy import create_engine

class PostgresUtil(object):

	def get_sql_alchemy_engine(self):
            SQLALCHEMY_CONNECTION_FORMAT = 'postgres://{user}:{password}@{host}:{port}/{database}'
		
            db_connection = SQLALCHEMY_CONNECTION_FORMAT.format(
                                          user=os.getenv(POSTGRES_USER), 
                                          password=os.getenv(POSTGRES_PASS), 
                                          host=os.getenv(POSTGRES_HOST), 
                                          port=os.getenv(POSTGRES_PORT), 
                                          database=os.getenv(POSTGRES_DATABASE)
                            )
            db = create_engine(db_connection)

            return db
	
	def create(self):
	    engine = self.get_sql_alchemy_engine()
	    MyTestTableORM.__table__.create(engine, checkfirst=True)

Step D. Create a main function

File: main.py

from my_test_table_orm import MyTestTableORM
From my_test_table_util import PostgresUtil


if __name__ == '__main__':
	postgresutil = PostgresUtil()
	test_table = postgresutil.create()

	print('ORM Columns’)
	print(MyTestTableORM.__table__.columns)

Step E. Run the PostgreSQL container

Use the following command.

docker-compose up -d

Step F. Run the main function

Use the following command.

python path_to_the_main_file

Step G. Check the results.

We can do this by getting into the container and execute the relevant commands.

docker exec -it my_postgres psql -p 5432 -U postgres

In other words, we ask the container to execute psql -p 5432 -U postgres. We use port 5432 as we execute the command inside the container. Otherwise, please use the mapped port (in this case, use port 54320).

After we’ve entered the PostgreSQL shell, we can use the below commands to get a general overview of the server.

• Use \l to list all the existing databases
• Use \c name_of_the_database to select a database
• Use \d to list all tables in the selected database

VOILA!

This dockerized approach is the best if you don’t want to mess up with any kind of installation.

Thank you for reading.