In this tutorial, I'll show how you can run a Postgres (also known as PostgreSQL) database locally in docker and connect to it using psql.

Run the Docker container

First we need to run the postgres container:

docker run --name postgres -d -p 5432:5432 -e POSTGRES_PASSWORD=postgres -e POSTGRES_USER=postgres \
-e POSTGRES_DB=employees -v ${PWD}/postgres-docker:/var/lib/postgresql/data postgres

Let's deconstruct this docker command..

Run a container called postgres using the postgres image:

docker run --name postgres [OPTIONS] postgres

Run as a detached container, so it runs in the background of your terminal

-d

Map port 5432 on the localhost to 5432 in the container

-p 5432:5432

Next we pass some Postgres specific environment variables to the Postgres container. You will need to use the User and Password to connect.

-e POSTGRES_PASSWORD=postgres
-e POSTGRES_USER=postgres
-e POSTGRES_DB=employees

Finally map the container volumne to a local volume:

-v ${PWD}/postgres-docker:/var/lib/postgresql/data postgres

Connect to the Postgres database

Once the container is up-and-running, we can connect to the Postgres database using the sqlalchemy Python package. First, you create an engine object using the Postgres login credentials specified above:

from sqlalchemy import create_engine 

db=create_engine("postgresql://postgres:postgres@localhost:5432/employees")

Write an SQL string to:

Create a new table called employee_details
Populate this table with some data

bootstrap_sql = """

CREATE TABLE EMPLOYEE_DETAILS(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   JOIN_DATE      DATE
);

INSERT INTO EMPLOYEE_DETAILS (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) 
VALUES (1, 'John', 32, 'London', 20000.00,'2001-07-13');

INSERT INTO EMPLOYEE_DETAILS (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE)
VALUES (2, 'David', 25, 'Dublin', 30000.00, '2007-12-13');

INSERT INTO EMPLOYEE_DETAILS (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE)
VALUES (3, 'Sarah', 25, 'Edinburgh', 40000.00, '2007-12-13');
"""

Pass that SQL string to the database engine object:

with db.connect() as con:
    
    try:

        rs = con.execute(bootstrap_sql)
    
    ## TODO: Add proper error handling
    except:
        pass

View results using psql

First we'll want to exec into the postgres container:

docker exec -it postgres bash

Once you're in the container you can run psql commands to query data:

root@12345abcde1:/#
root@12345abcde1:/#  psql -U postgres -d employees -c "select * from employee_details"

 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
  1 | John  |  32 | London                                             |  20000 | 2001-07-13
  2 | David |  25 | Dublin                                             |  30000 | 2007-12-13
  3 | Sarah |  25 | Edinburgh                                          |  40000 | 2007-12-13

root@12345abcde1:/#