SQLModel + ChatGPT = 😻

[umaxfun]

I want to share with the community my process of extending the database and an underlying SQLModel model with ChatGPT.

1. I add new schema elements to the database (tables etc.) using pretty much any tool, GUI or plain SQL
   2 If I wrote some SQL migration I use it as is. If I used any sort of GUI, I run Migra to get a migration out of the dev database
2. I ask ChatGPT a simple query asking to create an SQLModel code from the migration code, like this:

create an sqlmodel model for this sql:

create table "content"."summaries" (
    "id" bigint generated by default as identity not null,
    "created_at" timestamp with time zone not null default now(),
    "channel_id" bigint not null,
    "message_id_from" integer not null,
    "message_id_to" integer not null,
    "summary" text not null
);


CREATE UNIQUE INDEX summaries_pkey ON content.summaries USING btree (id);

alter table "content"."summaries" add constraint "summaries_pkey" PRIMARY KEY using index "summaries_pkey";

alter table "content"."summaries" add constraint "content_summaries_channel_id_fkey" FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE not valid;

alter table "content"."summaries" validate constraint "content_summaries_channel_id_fkey";

4. This query results in mostly correct code!

from sqlmodel import SQLModel, Field, Relationship
from typing import Optional
from datetime import datetime

class Summary(SQLModel, table=True):
    __tablename__ = "summaries"
    __table_args__ = {'schema': 'content'}

    id: Optional[int] = Field(default=None, primary_key=True, index=True)
    created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
    channel_id: int = Field(foreign_key="channels.id", nullable=False)
    message_id_from: int = Field(nullable=False)
    message_id_to: int = Field(nullable=False)
    summary: str = Field(nullable=False)

    channel: Optional["Channel"] = Relationship(back_populates="summaries")

class Channel(SQLModel, table=True):
    __tablename__ = "channels"

    id: Optional[int] = Field(default=None, primary_key=True)
    # Define other fields and relationships for the Channel model here
    summaries: list[Summary] = Relationship(back_populates="channel")

For sure it requires some tuning and rewrites, but for Postgres it works good enough on most tasks, including many-to-many relationships, saving lots of time and effort. Worths trying, especially if you write any sort of tests for the db-related code :)