— title: Python Data Management Workflow with PostgreSQL, SQLAlchemy (ORM Model), and Alembic created: Thursday, August 29th 2024, 3:45 pm updated: Friday, September 20th 2024, 5:56 pm — This guide assumes the PostgreSQL database, along with Alembic, are already configured. If not, ref. [[PostgreSQL and Alembic Setup]]. 1. **Define Domain Model:** Create a plain Python class representing the business logic of your application. This class is independent of the database. 2. **Define Database Model:** Create a SQLAlchemy model that maps to a table in your PostgreSQL database. This model should include the necessary fields, types, and relationships. 3. **Update `postgres_session.py` to Import Models and Create the Table**: By importing the models here, you ensure that they are recognized by SQLAlchemy during runtime. This is necessary for the application to be able to create tables, execute queries, and interact with the database using the defined models. 4. **Update `alembic/env.py` to Import Models and Support Automatic Migration Script Generation:** Modify the `env.py` file in your Alembic setup to include imports for your SQLAlchemy models. This step ensures that Alembic can automatically detect changes in the schema and generate appropriate migration scripts that reflect those changes. 5. **Update your PostgreSQL Session's `init_db()`.** 6. **Generate and Apply Alembic Migrations:** Use Alembic to generate and apply migrations to update the database schema based on your new or modified models. 7. **Enable Domain Model Creation with a Factory:** Create a factory class or function that instantiates your domain model, abstracting away any complex initialization logic. 8. **Enable DB to Domain Model Conversion and Vice Versa with a Mapper:** Implement a mapper to convert between the domain model and the database model, allowing for seamless interaction between your application logic and the database. 9. **Implement a Repository to Manage Data Access:** Implement a repository that abstracts the data access logic, including CRUD operations, utilizing the repository pattern, potentially combining multiple data sources (e.g., PostgreSQL for relational data and Pinecone for vector data). 10. **Create a Model Service to Manage Business Logic:** Implement a service class that manages business logic by interacting with other components like the factory, repository, external services, and utilities. [[Pycharm's DB Browser not Displaying Columns]]