How to use postgres configuration from .env file in piccolo_conf.py?

[Tar8117]

Hello!
Is there a way to make piccolo look to .env file to configure Postgres? I'm trying to run Postgres from Docker.
When doing docker-compose up I'm getting Warning: Unable to connect to database - [Errno 111] Connect call failed ('127.0.0.1', 5432).
I tried different ways to configure .env file and piccolo_conf, but always failed.

Here is my picolo_conf.py:

from piccolo.engine.postgres import PostgresEngine

from piccolo.conf.apps import AppRegistry


DB = PostgresEngine(config={ i guess i should write here smth correct to make piccolo look to env})

APP_REGISTRY = AppRegistry(
    apps=["home.piccolo_app", "piccolo_admin.piccolo_app"]
)

My env file is standard:

DB_NAME=postgres
POSTGRES_USER=postgres
POSTGRES_PASSWORD=postgres
DB_HOST=db
DB_PORT=5432

I need help with correct syntax and methods to solve this error.

[dantownsend]

In my apps I do something like this (reading the environment variables defined by Docker):

import os

from piccolo.conf.apps import AppRegistry
from piccolo.engine.postgres import PostgresEngine


DB = PostgresEngine(
    config={
        "port": int(os.environ.get("DB_PORT", "5432")),
        "user": os.environ.get("POSTGRES_USER", "postgres"),
        "password": os.environ.get("POSTGRES_PASSWORD", ""),
        "database": os.environ.get("DB_NAME", "my_app"),
        "host": os.environ.get("DB_HOST", "localhost"),
    }
)

If using a .env file, I do the same, but I run load_dotenv() (from python-dotenv) near the top of the file:

import os

from dotenv import load_dotenv
from piccolo.conf.apps import AppRegistry
from piccolo.engine.postgres import PostgresEngine

load_dotenv()


DB = PostgresEngine(
    config={
        "port": int(os.environ.get("DB_PORT", "5432")),
        "user": os.environ.get("POSTGRES_USER", "postgres"),
        "password": os.environ.get("POSTGRES_PASSWORD", ""),
        "database": os.environ.get("DB_NAME", "my_app"),
        "host": os.environ.get("DB_HOST", "localhost"),
    }
)

If you're still getting that error, then PostgresEngine must be getting called somehow with the default connection parameters. I'll see if I can replicate this with my own apps.

[Tar8117]

Still nothing.
here is my docker-compose.yml:

version: '2'
services:

  db:
    image: postgres:13.0-alpine
    volumes:
      - /var/lib/postgresql/data/
    env_file:
      - .env
    restart: always

  backend:
    build: .
    restart: always
    volumes:
      - .:/backend
    ports:
      - "8000:8000"
    depends_on:
      - db
    env_file:
    - .env

.env:

DB_NAME=postgres
POSTGRES_USER=oliver
POSTGRES_PASSWORD=oliver
DB_HOST=db
DB_PORT=5432

piccolo_conf.py:

import os

# from dotenv import load_dotenv
from piccolo.engine.postgres import PostgresEngine
from piccolo.conf.apps import AppRegistry


DB = PostgresEngine(config={
        "port": int(os.environ.get("DB_PORT", "5432")),
        "user": os.environ.get("POSTGRES_USER", "postgres"),
        "password": os.environ.get("POSTGRES_PASSWORD", "postgres"),
        "database": os.environ.get("DB_NAME", "postgres"),
        "host": os.environ.get("DB_HOST", "localhost"),
})

APP_REGISTRY = AppRegistry(
    apps=["home.piccolo_app", "piccolo_admin.piccolo_app"]
)

Here is my files. They are on the same level in a structure. Maybe I can't see some stupid mistake, but I suppose everything is correct here.

BTW, when I changed piccolo_conf.py as in your example, I started getting only this error: backend_1 | Unable to connect to the database . Without [Errno 111] Connect call failed ('127.0.0.1', 5432).

[dantownsend]

One issue might be that in your .env file you have this:

DB_NAME=postgres

Which is telling Piccolo to connect to a database called postgres.

But looking at the docs for the Postgres Docker image:

If you don't specify POSTGRES_DB, then the Postgres container will create a database with the same name as POSTGRES_USER which you have set to oliver.

So try changing your .env file to this, and trying it again:

DB_NAME=postgres
POSTGRES_USER=oliver
POSTGRES_PASSWORD=oliver
POSTGRES_DB=postgres
DB_HOST=db
DB_PORT=5432

[Tar8117]

Hello! Finally, I did it.

I created a new file, docker-entrypoint.sh . At the same level as my Dockerfile and docker-compose.yml.

#!/bin/bash

# Apply database migrations
# echo "Apply database migrations"
# piccolo migrations new home --auto

# Run database migrations
echo "Running database migrations"
piccolo migrations forwards session_auth
piccolo migrations forwards user
piccolo migrations forwards home

# Start server
echo "Starting server"
uvicorn app:app --host 0.0.0.0 --port 8000 --reload

Changed my Dockerfile.

FROM python:3.8-slim
WORKDIR /code
COPY requirements.txt /code
RUN pip install -r /code/requirements.txt --no-cache-dir
COPY . /code
RUN ["chmod", "+x", "./docker-entrypoint.sh"]
CMD ["./docker-entrypoint.sh"]

# CMD python main.py --host 0.0.0.0 --port 8000 --reload
# CMD uvicorn app:app --reload

.env.

DB_NAME=postgres
POSTGRES_USER=postgres
POSTGRES_PASSWORD=postgres
DB_HOST=db
DB_PORT=5432

piccolo_conf.py

import os

from dotenv import load_dotenv
from piccolo.engine.postgres import PostgresEngine
from piccolo.conf.apps import AppRegistry

load_dotenv()

DB = PostgresEngine(config={
        'database': os.getenv('DB_NAME'),
        'user': os.getenv('POSTGRES_USER'),
        'password': os.getenv('POSTGRES_PASSWORD'),
        'host': os.getenv('DB_HOST'),
        'port': os.getenv('DB_PORT')
})

APP_REGISTRY = AppRegistry(
    apps=["home.piccolo_app", "piccolo_admin.piccolo_app"]
)

docker-compose.yml

version: '2'
services:

  db:
    image: postgres:13.0-alpine
    volumes:
      - /var/lib/postgresql/data/
    env_file:
      - .env
    restart: always

  backend:
    build: .
    restart: always
    volumes:
      - .:/backend
    ports:
      - "8000:8000"
    depends_on:
      - db
    env_file:
    - .env

Deleted all old containers and images.

Then make docker-compose up --build . ( I made without -d key to see is there any errors). There were no errors, so I did
docker-compose up -d --build.
Then sudo docker-compose exec backend piccolo user create to create a new superuser for docker container and that's all. I think the problem was that I use "async" things. This method helps me. So I wrote here about solution, maybe it will help somebody else. It was not my idea, but however it helps me.

[dantownsend]

Nice! Using an entrypoint file is a good approach.

[Tar8117]

I found a new solution. This time, no need in docker-entrypoint.sh.
Only few changes in main.py and Dockerfile.

main.py

import os  # This line is new. Found it in documantation
os.environ['PICCOLO_CONF'] = 'app.piccolo_conf'  # This line is new. Found it in documantation


if __name__ == "__main__":

    import uvicorn

    uvicorn.run("app:app", reload=True)

Dockerfile

FROM python:3.8-slim
WORKDIR /code
COPY requirements.txt /code
RUN pip install -r /code/requirements.txt --no-cache-dir
COPY . /code
CMD uvicorn app:app --host 0.0.0.0 --port 8000 --reload   # This line is new.

And docker-compose up -d --build works here too.
But now it is necessary to make migrations of our app and for users and auth.

sudo docker-compose exec backend piccolo migrations forwards session_auth
sudo docker-compose exec backend piccolo migrations forwards user
sudo docker-compose exec backend piccolo migrations forwards home
sudo docker-compose exec backend piccolo user create

[dantownsend]

Cool - thanks for sharing, I'm sure it'll be useful for others too.