Running PostgreSQL using Docker on your local machine is very straightforward, ..and very handy when you’re developing!

The following docker-compose file launches a PostgreSQL 13.1 Database instance that stores the data in your local folder /Users/localname/data. You can connect to the database with user userxyz and password password123.

file: docker-compose.dev.yml

version: "3"
services:
  postgres:
    image: postgres:13.1-alpine
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: userxyz
      POSTGRES_PASSWORD: password123
    volumes:
       - /Users/localname/data:/var/lib/postgresql/data

Launching a PostgreSQL instance:

> docker-compose -f docker-compose.dev.yml up

Finally, test if the PostgreSQL instance is running with Python. First install the PostgreSQL Python library when needed:

> pip install psycopg2-binary

Connection to the database:

import psycopg2

user = "thijs"
password = "aZABcsXxt1VqyVUjXYav"

url = f"postgresql://{user}:{passwords}@localhost:5432/{user}"
engine = create_engine(url)

Create a table, insert a record, and execute a query:

# create a table
engine.execute('CREATE TABLE "EX1" (id INTEGER NOT NULL, name VARCHAR, PRIMARY KEY (id));')

# insert a record
engine.execute('INSERT INTO "EX1" (id, name) VALUES (1, \'John\')')

# Query and print records
result = engine.execute('SELECT * FROM "EX1"')
for row in result:
    print(row)

When done, stop the PostgreSQL instance,

> docker-compose -f docker-compose.dev.yml down

One thing to note is that you should never store passwords in files. In one would use variables like DB_USER and DB_PASSWORD in the docker-compose.yml file:

version: "3"
services:
  postgres:
    image: postgres:13.1-alpine
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: ${DB_USER}
      POSTGRES_PASSWORD: ${DB_PASSWORD}
    volumes:
       - /Users/thijs/Projects/DeepFund-API/DeepFund-API-admin/data:/var/lib/postgresql/data

Docker-compose will automatically look for a .env is the current directory, and substitute variables found in that .env file

DB_USER=userxyz
DB_PASSWORD=password123

Alternatively, you can also specify a custom environment variables file. This is handy when you need to test various configurations / environments with different settings.

> docker-compose --env-file=dev_settings.txt -f docker-compose.dev.yml up