Setting Up & Connecting to PostgreSQL (from Host) via Docker
Published:
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.