CODEWITHSUNDEEP
pythonpostgresqldockerfastapi
Sumit Dadwal
Sumit Dadwal

Reputation: 181

Docker: ERROR: relation "users" does not exist at character 13

I am trying to dockerize my app made with Python and FastAPI. I successfully created the images and container.

I tried to dockerize my postgres database it was done successfully until I try to create a new user. It throws the following error:

 ERROR:  relation "users" does not exist at character 13
10 02:24:37.586 UTC [71] STATEMENT:  INSERT INTO users (email, password) VALUES ('[email protected]', '$2b$12$VNya4IkKSGCuapswkJrh3u6POJVsdU2GSeIaV/ya4GprxNqEt5oim') RETURNING users.id

It shows the following error in my FastAPI app image:

 Traceback (most recent call last):

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context

    self.dialect.do_execute(

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute

    cursor.execute(statement, parameters)

psycopg2.errors.UndefinedTable: relation "users" does not exist

LINE 1: INSERT INTO users (email, password) VALUES ('sumitdadwal11@g...

                    ^

    The above exception was the direct cause of the following exception:


Traceback (most recent call last):

  File "/usr/local/lib/python3.9/site-packages/uvicorn/protocols/http/httptools_impl.py", line 376, in run_asgi

    result = await app(self.scope, self.receive, self.send)

  File "/usr/local/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py", line 75, in __call__

    return await self.app(scope, receive, send)

  File "/usr/local/lib/python3.9/site-packages/fastapi/applications.py", line 208, in __call__

    await super().__call__(scope, receive, send)

  File "/usr/local/lib/python3.9/site-packages/starlette/applications.py", line 112, in __call__

    await self.middleware_stack(scope, receive, send)

  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/errors.py", line 181, in __call__

    raise exc

  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/errors.py", line 159, in __call__

    await self.app(scope, receive, _send)

  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/cors.py", line 84, in __call__

    await self.app(scope, receive, send)

  File "/usr/local/lib/python3.9/site-packages/starlette/exceptions.py", line 82, in __call__

    raise exc

  File "/usr/local/lib/python3.9/site-packages/starlette/exceptions.py", line 71, in __call__

    await self.app(scope, receive, sender)

  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 656, in __call__

    await route.handle(scope, receive, send)

  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 259, in handle

    await self.app(scope, receive, send)

  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 61, in app

    response = await func(request)

  File "/usr/local/lib/python3.9/site-packages/fastapi/routing.py", line 226, in app

    raw_response = await run_endpoint_function(

  File "/usr/local/lib/python3.9/site-packages/fastapi/routing.py", line 161, in run_endpoint_function

    return await run_in_threadpool(dependant.call, **values)

  File "/usr/local/lib/python3.9/site-packages/starlette/concurrency.py", line 39, in run_in_threadpool

    return await anyio.to_thread.run_sync(func, *args)

  File "/usr/local/lib/python3.9/site-packages/anyio/to_thread.py", line 28, in run_sync

    return await get_asynclib().run_sync_in_worker_thread(func, *args, cancellable=cancellable,

  File "/usr/local/lib/python3.9/site-packages/anyio/_backends/_asyncio.py", line 818, in run_sync_in_worker_thread

    return await future

  File "/usr/local/lib/python3.9/site-packages/anyio/_backends/_asyncio.py", line 754, in run

    result = context.run(func, *args)

  File "/usr/src/app/./app/routers/user.py", line 18, in create_user

    db.commit()

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1428, in commit

    self._transaction.commit(_to_root=self.future)

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 829, in commit

    self._prepare_impl()

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl

    self.session.flush()

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3345, in flush

    self._flush(objects)

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3485, in _flush

    transaction.rollback(_capture_exception=True)

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__

    compat.raise_(

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_

    raise exception

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3445, in _flush

    flush_context.execute()

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute

    rec.execute(self)

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 630, in execute

    util.preloaded.orm_persistence.save_obj(

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 244, in save_obj

    _emit_insert_statements(

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 1221, in _emit_insert_statements

    result = connection._execute_20(

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1614, in _execute_20

    return meth(self, args_10style, kwargs_10style, execution_options)

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection

    return connection._execute_clauseelement(

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1481, in _execute_clauseelement

    ret = self._execute_context(

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context

    self._handle_dbapi_exception(

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception

    util.raise_(

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_

    raise exception

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context

    self.dialect.do_execute(

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute

    cursor.execute(statement, parameters)

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "users" does not exist

LINE 1: INSERT INTO users (email, password) VALUES ('sumitdadwal11@g...

                    ^


[SQL: INSERT INTO users (email, password) VALUES (%(email)s, %(password)s) RETURNING users.id]

[parameters: {'email': '[email protected]', 'password': '$2b$12$5tcxP4b0hwVJpmHfyF10wuosYsdIxBkm1nhk1b1BZlLFZyCymodhK'}]

(Background on this error at: https://sqlalche.me/e/14/f405)

This is my docker-compose.yml file:

   version: "3"
services: 
  api:
    build: .
    ports:
      - 8000:8000
    # env_file:
    #      ./.env
    environment:
      - DATABASE_HOSTNAME=postgres
      - DATABASE_PORT=5432
      - DATABASE_PASSWORD=password123
      - DATABASE_NAME=fastapi
      - DATABASE_USERNAME=postgres
      - SECRET_KEY=secretkeysecretkeysecretkeysecretkey
      - ALGORITHM=HS256
      - ACCESS_TOKEN_EXPIRE_MINUTES=30
  postgres:
    image: postgres
    environment:
      - POSTGRES_PASSWORD=password123
      - POSTGRES_DB=fastapi
    
    volumes:
      - postgres-db:/var/lib/postgresql/data

volumes:
  postgres-db:

This is my Dockerfile:

  FROM python:3.9.9

WORKDIR /usr/src/app

COPY requirements.txt ./

RUN pip install --no-cache-dir -r requirements.txt

COPY . .

CMD ["uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "8000"]

First I thought somethings wrong with my code but it is working fine when I am running it locally on my system.

Also, I am very new to docker.

I have tried different online solution but nothing worked out.

I think it getting connected to the wrong table named postgres but I cant find the reason behinf it.

UPDATE: What I have noticed is that my postgress server has three databases: -fastapi -fastapi-project

Please let me know if you need any additional information.

Thanks in advance!

Upvotes: 2

Views: 5946

Answers (1)

apgsn
apgsn

Reputation: 751

I ran into the same problem following Sanjeev's tutorial. Given your settings, you are connecting to the right db, the issue is that it has no tables. This can be solved via Alembic.
In your docker-compose.yml, under api: add the line:

command: bash -c "alembic upgrade head && uvicorn app.main:app --host 0.0.0.0 --port 8000 --reload"

That's it!

To check that the tables have been successfully created, open a terminal in your project folder and do the following:

  • Run docker compose

    docker-compose up -d

  • Find the pg container name, in your case it should be something like fastapi-postgres-1, then start a bash session into your pg container

    docker ps
docker exec -it fastapi-postgres-1 bash

  • From here, access psql and find your db name, in your case it should be fastapi

    su - postgres
psql
\l

  • Access your db and check that the tables have been created

    \c fastapi
\dt

If result is something like this, you're good to go.

              List of relations
 Schema |      Name       | Type  |  Owner   
--------+-----------------+-------+----------
 public | alembic_version | table | postgres
 public | posts           | table | postgres
 public | users           | table | postgres
 public | votes           | table | postgres
(4 rows)

Upvotes: 4

Related Questions