postgreSQL: SQLModel.metadata.create_all(engine) doesn't create the database file

[Matthieu-Tinycoaching]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from datetime import datetime
from typing import Optional, Dict
from sqlmodel import Field, SQLModel, create_engine

class SemanticSearch(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    id_user: int
    date_time: datetime
    query: str
    clean_query: str
    
engine = create_engine('postgresql://postgres:postgres@localhost:5432/embeddings_sts_tf', echo=True)

SQLModel.metadata.create_all(engine)

Description

Following the tutorial user guide based on sqlite I tried to do the same with postgresql database, but contrary to sqlite the SQLModel.metadata.create_all(engine) command doesn't seem to create my embeddings_sts_tf postgresql database

Operating System

Linux

Operating System Details

Ubuntu 18.04 LTS

SQLModel Version

0.0.4

Python Version

3.8.8

Additional Context

No response

[rodg]

From this stack-overflow post you can't create a postgresql database via create_engine or create_all. Following the steps in that SO answer works, with the caveat of the SQLAlchemy 2.0 engine not allowing you to just execute on text (and create_engine from SQLModel has future=True by default). For 2.0 you need to use the text() construct to execute text like that. YOLO importing text from SQLModel seems to import the right thing, so you don't even need to import it from SQLAlchemy apparently but I've not seen this import documented anywhere.

from datetime import datetime
from typing import Optional, Dict
from sqlmodel import Field, SQLModel, create_engine, text

class SemanticSearch(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    id_user: int
    date_time: datetime
    query: str
    clean_query: str
    
engine = create_engine('postgresql://postgres:postgres@localhost:5432/postgres', echo=True)

conn = engine.connect()
conn.execute(text("commit"))
conn.execute(text("create database your_db_name"))
conn.close()
conn.dispose()

engine = create_engine('postgresql://postgres:postgres@localhost:5432/your_db_name', echo=True)

SQLModel.metadata.create_all(engine)

[YuriiMotov]

Code examples in docs use SQLite for simplicity. SQLIte creates DB file automatically if not exist.

PostgreSQL is more complex thing and it works differently - you need to start the database server (the easiest way is to start the docker container) and create the database manually before running the code.

See:

• https://www.docker.com/blog/how-to-use-the-postgres-docker-official-image/
• https://squaredup.com/blog/running-postgres-in-docker/