Async CRUD Operations with SQLAlchemy & SQLModel

Project Overview

The goal of this project is to follow the DRY (Don't Repeat Yourself) principle by creating reusable classes that handle CRUD operations on any database table, eliminating the need to repeatedly write session functions for each table. This solution is designed to simplify and streamline the process of performing database operations across various models.

Features

• Asynchronous Operations: Using SQLAlchemy’s asyncio extension for non-blocking database queries and updates.
• Reusable Classes: Create CRUD classes that work with any SQLModel table without having to reimplement basic session operations every time.
• Error Handling: Graceful handling of errors, including rolling back transactions in case of failures.
• Extensibility: Easily extend the base CRUD functionality to handle more complex operations.

Getting Started

Prerequisites

• Python 3.13.1
• SQLAlchemy (sqlalchemy and sqlmodel)
• SQLite with aiosqlite for asynchronous database operations

Installation

1. Clone the repository:

   git clone https://github.com/emekadefirst/OrmI.git
   cd async-crud-sqlalchemy

2. Set up a virtual environment and install dependencies:

   python -m venv venv
   source venv/bin/activate  # On Windows, use `venv\Scripts\activate`
   pip install -r requirements.txt

Database Configuration

The project uses SQLite for simplicity, but can easily be adapted for other databases by changing the DATABASE_URL.

Modify the DATABASE_URL in the code:

DATABASE_URL = "sqlite+aiosqlite:///database.db"  # Change to the appropriate DB URL

Usage

1. Define Your Models: Define your models using SQLModel. For example:

   from sqlmodel import SQLModel, Field
   import uuid
   
   class Club(SQLModel, table=True):
       id: Optional[uuid.UUID] = Field(default_factory=uuid.uuid4, primary_key=True)
       name: str
       short_name: str

2. Use the CRUD Classes:

   • The CreateListSession class provides methods to create and list records.
   • The RetrieveUpdateDelete class provides methods for retrieving, updating, and deleting records.

   Example:

   import asyncio
   
   # Create CRUD session for the Club model
   crud = CreateListSession(Club)
   
   # Create a new club record
   new_club = asyncio.run(crud.create(name="Manchester United", short_name="MAN-U"))
   
   # List all club records
   clubs = asyncio.run(crud.list())
   print(clubs)

Classes Overview

CreateListSession

This class contains methods for creating and listing records in a database.

• create: Adds a new record to the table.
• list: Retrieves records from the table with optional filtering, pagination, and ordering.

RetrieveUpdateDelete

This class contains methods for retrieving, updating, and deleting records.

• retrieve: Fetches a record by its ID.
• update: Updates a record with the specified ID.
• delete: Deletes a record by its ID.

Example Code

Here's how the CreateListSession class can be used to create and list records for the Club model:

import asyncio

crud = CreateListSession(Club)

# Create a new club
asyncio.run(crud.create(name="Manchester United", short_name="MAN-U"))

# List all clubs
clubs = asyncio.run(crud.list())
for club in clubs:
    print(club.name)

Error Handling

The CRUD classes handle errors gracefully. If a database operation fails (e.g., integrity error), it will be caught and a meaningful error message will be raised. For example, if you try to create a club with a name that already exists, an IntegrityError will be raised.

Contributing

Contributions are welcome! Feel free to fork the repository and submit pull requests.

1. Fork the repository
2. Create a new branch (git checkout -b feature-branch)
3. Commit your changes (git commit -am 'Add new feature')
4. Push to the branch (git push origin feature-branch)
5. Open a pull request

License

This project is licensed under the MIT License - see the LICENSE file for details.

This updated `README.md` introduces the main goal of the project upfront, and then provides a clear guide to using the CRUD operations with reusable classes. Let me know if you'd like to make any further adjustments!