Simple CRUD Operations

[anshum4n-git]

The current SQL generation based implementation is excellent for fetching complex chains of objects in a simple query based on usecase. However, that is the rarer occurance. Most apps need lots of CRUD and simple list / search / paginate operations. It would be good to implement them programattically i.e. they don't have to be generated as they are pretty straightforward to construct the queries for.

On a project, imagine we have 20 objects. Just crud and list operations on those end up generating 100+ sql queries to maintain. Instead, if we had crud methods, there is only few low digit of queries that would need to be generated based on the real complex fetch patterns. The goal is to provide the following out of the box.

T = TypeVar('T')

class CrudWrapper(Generic[T])):

    def create(obj:T) -> T:
        """INSERT INTO {table_name}({field_names}) VALUES ({field_values})"""
        # set the generated id (if any as obj.id) 
        return obj

    def list(**kwargs) -> List[T]:
        """SELECT * FROM {table_name} WHERE {key=value}"""
        return objects

    def delete(obj:T): ...
    def update(obj:T, **kwargs) -> T: ...
    def get(T, obj_id) -> T: ...

Assuming T is a pydantic class, we would need to

1. Find field names on the type T
2. Table name for type T

For first implemenation, we can assume this is only for single tables at a time and avoid complexity of JOINS. There may be other cleaner mechanisms of achieving something like above to avoid too many SQL queries being generated and having to be maintained.