Cannot auto change database like update/delete except create new tables.

[Justinianus2001]

Hi,

Thank you for providing this boilerplate code.

I'm running the repository within a Docker container. However, I've noticed that edits or removals of model files don't seem to be reflected in the database. The tables remain unchanged unless I create a new model.

I've tried searching the codebase for relevant functions and found only create_tables in src/app/core/setup.py.

Could you please advise on how to ensure model changes are reflected within the Docker container's database?

[igorbenav]

I believe if you just migrate with alembic it will work.

[igorbenav]

Could you please let me know if it works when you try it?

[Justinianus2001]

I have a Docker container running and I initiated a migration using the command poetry run alembic revision --autogenerate in the /src directory, but I encountered an error:

asyncpg.exceptions.InvalidPasswordError: password authentication failed for user "postgres"

I can connect successfully with pgAdmin using this username and password. Please help me resolve this issue.

Here is the database configuration in my .env file:

# ------------- database -------------
POSTGRES_USER="postgres"
POSTGRES_PASSWORD="password"
# default "localhost", if using docker compose you should use "db"
POSTGRES_SERVER="db"
# default "5432", if using docker compose you should use "5432"
POSTGRES_PORT=5432
POSTGRES_DB="hello_fastapi"

docker-compose.yml

db:
    image: postgres:13
    env_file:
      - ./src/.env
    volumes:
      - postgres-data:/var/lib/postgresql/data
    # -------- replace with comment to run migrations with docker --------
    # expose:
    #   - "5432"
    ports:
      - 5432:5432

[igorbenav]

Try changing this part in your src/migrations/env.py:

config.set_main_option(
    "sqlalchemy.url",
    f"{settings.POSTGRES_ASYNC_PREFIX}{settings.POSTGRES_USER}:{settings.POSTGRES_PASSWORD}@localhost/{settings.POSTGRES_DB}",
)

To

config.set_main_option(
    "sqlalchemy.url",
    f"{settings.POSTGRES_ASYNC_PREFIX}{settings.POSTGRES_USER}:{settings.POSTGRES_PASSWORD}@{settings.POSTGRES_SERVER}:{settings.POSTGRES_PORT}/{settings.POSTGRES_DB}",
)

(Note that you should have a settings.POSTGRES_SERVER in your .env)

[Justinianus2001]

Apologies, but the error persists.

socket.gaierror: [Errno 11001] getaddrinfo failed

There is a problem with the DNS configuration for the Docker run. It appears that the address resolution is not correctly configured.

[igorbenav]

Can you please send me your full docker-compose? Easier if you can create a minimal repository that I can just clone to reproduce

[Justinianus2001]

You can examine my cloned repository here. I have named my .env file as .env.local for easier verification.

[Justinianus2001]

Hello, I intend to utilize your template for my company project, but the migrations are not yet satisfactory. Could you assist me in finding a solution? Thank you!

[igorbenav]

Hey, @Justinianus2001, I'm taking a look at it soon. I just need to fix some fastcrud stuff before

[igorbenav]

The correct way is indeed

config.set_main_option(
    "sqlalchemy.url",
    f"{settings.POSTGRES_ASYNC_PREFIX}{settings.POSTGRES_USER}:{settings.POSTGRES_PASSWORD}@localhost/{settings.POSTGRES_DB}",
)

I cloned your project and can't reproduce the asyncpg.exceptions.InvalidPasswordError: password authentication failed for user "postgres" error, it's working for me.

The table isn't being created if I don't define endpoints though, I'll upload a fix for it, but I can't help with the asyncpg.exceptions.InvalidPasswordError: password authentication failed for user "postgres" since I just cloned (and changed back to localhost) and it worked.

[igorbenav]

To update to the new version, add in src/app/models/__init__.py:

from .post import Post
from .rate_limit import RateLimit
from .tier import Tier
from .user import User

And in src/app/core/setup.py add this import after all of the others:

from ..models import *

Plus, don't forget to change back in src/migrations/env.py:

config.set_main_option(
    "sqlalchemy.url",
    f"{settings.POSTGRES_ASYNC_PREFIX}{settings.POSTGRES_USER}:{settings.POSTGRES_PASSWORD}@localhost/{settings.POSTGRES_DB}",
)

[Justinianus2001]

Understood. The error asyncpg.exceptions.InvalidPasswordError: password authentication failed for user "postgres" occurred in my case because I had another local instance of Postgres running, which blocked the connection from this project. I have stopped it, and now I can establish a connection and successfully run migrations following your advice.

I modified the setup for my project (Postgres username, password, db, ...), but Docker displayed an error. I couldn't find any issues with your boilerplate. Therefore, I think I need to learn more about Pydantic & SQLAlchemy.

On another note, thank you very much for your support ❤️.

[igorbenav]

Glad you found the issue, @Justinianus2001! Feel free to create another issue or a discussion if you need.