0.15.1

0.15.1 Summary

Added

• Support for fastapi >=0.100

What's Changed

• now supporting fastapi >= 0.100.0 by @igorbenav in #166

Full Changelog: v0.15.0...v0.15.1

0.15.0

0.15.0 Summary

Added

• Models and Schemas for Task Management (Batch 3) by @slaarti
• Models and Schemas for Articles, Authors, and Profiles (Batch 4) by @slaarti
• update_override Argument to upsert_multi Method by @feluelle
• Configurable is_deleted Field in Soft Delete Logic by @gal-dahan

Improved

• Fixed Complex Parameter Filter with between Operator by @wu-clan
• Fixed Cryptography Package Vulnerability by @igorbenav
• Resolved Update Column Name Collision in Update Method by @igorbenav

Fixed

• Vulnerability in cryptography Package updated to cryptography = "^43.0.1" by @igorbenav
• Update Column Name Collision in the update method by @igorbenav

Documentation Updates

• Added Documentation for New Models and Schemas by @slaarti
• Updated upsert_multi Method Documentation with update_override Usage by @feluelle
• Clarified Endpoint Simplification and Deprecation Notices by @igorbenav

Warnings

• Deprecation Notice: The _read_paginated endpoint has been removed. Please transition to using _read_items with pagination parameters. Docs here.
• Deprecation Notice: Handling of Depends is now only callable within _inject_depend. Update your code accordingly.
• Configuration Change Alert: Endpoints are simplified by default. Adjust your configurations to align with the new defaults. Docs here.

Details

---

Endpoint Simplification and Deprecation of _read_paginated

Description

To streamline API endpoint configurations, endpoints with empty strings as names are now the standard. Additionally, the _read_paginated endpoint has been removed, with its functionality merged into _read_items.

Changes

• Simplified Endpoint Configuration: Endpoints can now be defined with empty strings to create cleaner paths.
• Removed _read_paginated Endpoint: Pagination is now handled via optional parameters in _read_items.

Usage Examples

Paginated Read Example:

curl -X 'GET' \
  'http://localhost:8000/items?page=2&itemsPerPage=10' \
  -H 'accept: application/json'

Non-Paginated Read Example:

curl -X 'GET' \
  'http://localhost:8000/items?offset=0&limit=100' \
  -H 'accept: application/json'

Warnings

Warning

Deprecation Warning: The _read_paginated endpoint is deprecated. Use _read_items with pagination parameters instead.

Warning

Configuration Change: Default endpoint names are now empty strings. Adjust your configurations to match the new defaults.

---

update_override Argument in upsert_multi Method

Description

The upsert_multi method now includes an update_override argument, giving developers the ability to override the default update logic during upsert operations. This enhancement provides greater flexibility for custom update scenarios, such as utilizing SQL CASE statements or other complex expressions.

Changes

• update_override Argument: Allows custom update logic in upsert_multi.
• Dialect Support: Implemented for PostgreSQL, SQLite, and MySQL.
• Tests: Added comprehensive tests to ensure functionality across different SQL dialects.

Usage Example

from fastcrud import FastCRUD
from sqlalchemy import case
from .models.item import Item
from .database import session as db

crud_items = FastCRUD(Item)

await crud_items.upsert_multi(
    db=db,
    instances=[
        ItemCreateSchema(id=1, name="Item A", price=10),
        ItemCreateSchema(id=2, name="Item B", price=20),
    ],
    update_override={
        "price": case(
            (Item.price.is_(None), db.excluded.price),
            else_=Item.price,
        )
    }
)

---

Configurable is_deleted Field in Soft Delete Logic

Description

The is_deleted field in the soft delete logic is now optional and configurable. This change allows developers to customize the soft delete behavior per model, providing flexibility in how deletion states are handled.

---

What's Changed

• Models and Schemas, Batch 3: Task Management by @slaarti in #140
• fix complex parameter filter with between by @wu-clan in #150
• Add update override arg to upsert_multi by @feluelle in #153
• Make is_deleted Field Optional and Configurable in Soft Delete Logic by @gal-dahan in #152
• Models and Schemas, Batch 4: Articles, Authors, and Profiles by @slaarti in #151
• fix for cryptography issue by @igorbenav in #161
• Fix update column by @igorbenav in #162
• Preparations for 0.15.0 by @igorbenav in #163
• added changelog, changed pyproject version by @igorbenav in #164

New Contributors

• @wu-clan made their first contribution in #150
• @gal-dahan made their first contribution in #152

Full Changelog: v0.14.0...v0.15.0

You may also see this in the docs.

0.14.0

0.14.0 Summary

Added

• Type-checking support for SQLModel types by @kdcokenny
• Returning clause to update operations by @feluelle
• Upsert_multi functionality by @feluelle
• Simplified endpoint configurations by @JakNowy, streamlining path generation and merging pagination functionalities into a unified _read_items endpoint, promoting more efficient API structure and usage. Details in #105

Improved

• Comprehensive tests for paginated retrieval of items, maintaining 100% coverage
• Docker client check before running tests that require Docker by @feluelle

Fixed

• Vulnerability associated with an outdated cryptography package
• Return type inconsistency in async session fixtures by @slaarti

Documentation Updates

• Cleanup of documentation formatting by @slaarti
• Replacement of the Contributing section in docs with an include to file in repo root by @slaarti
• Correction of links to advanced filters in docstrings by @slaarti
• Backfill of docstring fixes across various modules by @slaarti
• Enhanced filter documentation with new AND and OR clause examples, making complex queries more accessible and understandable.

Models and Schemas Enhancements

• Introduction of simple and one-off models (Batch 1) by @slaarti
• Expansion to include models and schemas for Customers, Products, and Orders (Batch 2) by @slaarti

Code Refinements

• Resolution of missing type specifications in kwargs by @slaarti
• Collapsed space adjustments for models/schemas in fast_crud.py by @slaarti

Warnings

• Deprecation Notice: _read_paginated endpoint is set to be deprecated and merged into _read_items. Users are encouraged to transition to the latter, utilizing optional pagination parameters. Full details and usage instructions provided to ensure a smooth transition.
• Future Changes Alert: Default endpoint names in EndpointCreator are anticipated to be set to empty strings in a forthcoming major release, aligning with simplification efforts. Refer to #67 for more information.

Detailed

---

Simplified Endpoint Configurations

In an effort to streamline FastCRUD’s API, we have reconfigured endpoint paths to avoid redundancy (great work by @JakNowy). This change allows developers to specify empty strings for endpoint names in the crud_router setup, which prevents the generation of unnecessary // in the paths. The following configurations illustrate how endpoints can now be defined more succinctly:

endpoint_names = {
    "create": "",
    "read": "",
    "update": "",
    "delete": "",
    "db_delete": "",
    "read_multi": "",
    "read_paginated": "get_paginated",
}

Moreover, the _read_paginated logic has been integrated into the _read_items endpoint. This integration means that pagination can now be controlled via page and items_per_page query parameters, offering a unified method for both paginated and non-paginated reads:

• Paginated read example:

curl -X 'GET' \
  'http://localhost:8000/users/get_multi?page=2&itemsPerPage=10' \
  -H 'accept: application/json'

• Non-paginated read example:

curl -X 'GET' \
  'http://localhost:8000/users/get_multi?offset=0&limit=100' \
  -H 'accept: application/json'

Warnings

• Deprecation Warning: The _read_paginated endpoint is slated for deprecation. Developers should transition to using _read_items with the relevant pagination parameters.
• Configuration Change Alert: In a future major release, default endpoint names in EndpointCreator will be empty strings by default, as discussed in Issue #67.

Advanced Filters Documentation Update

Documentation for advanced filters has been expanded to include comprehensive examples of AND and OR clauses, enhancing the utility and accessibility of complex query constructions.

• OR clause example:

# Fetch items priced under $5 or above $20
items = await item_crud.get_multi(
    db=db,
    price__or={'lt': 5, 'gt': 20},
)

• AND clause example:

# Fetch items priced under $20 and over 2 years of warranty
items = await item_crud.get_multi(
    db=db,
    price__lt=20,
    warranty_years__gt=2,
)

---

Returning Clauses in Update Operations

Description

Users can now retrieve updated records immediately following an update operation. This feature streamlines the process, reducing the need for subsequent retrieval calls and increasing efficiency.

Changes

• Return Columns: Specify the columns to be returned after the update via the return_columns argument.
• Schema Selection: Optionally select a Pydantic schema to format the returned data using the schema_to_select argument.
• Return as Model: Decide if the returned data should be converted into a model using the return_as_model argument.
• Single or None: Utilize the one_or_none argument to ensure that either a single record is returned or none, in case the conditions do not match any records.

These additions are aligned with existing CRUD API functions, enhancing consistency across the library and making the new features intuitive for users.

Usage Example

Returning Updated Fields

from fastcrud import FastCRUD
from .models.item import Item
from .database import session as db

crud_items = FastCRUD(Item)
updated_item = await crud_items.update(
    db=db,
    object={"price": 9.99},
    price__lt=10,
    return_columns=["price"]
)
# This returns the updated price of the item directly.

Returning Data as a Model

from fastcrud import FastCRUD
from .models.item import Item
from .schemas.item import ItemSchema
from .database import session as db

crud_items = FastCRUD(Item)
updated_item_schema = await crud_items.update(
    db=db,
    object={"price": 9.99},
    price__lt=10,
    schema_to_select=ItemSchema,
    return_as_model=True
)
# This returns the updated item data formatted as an ItemSchema model.

---

Bulk Upsert Operations with upsert_multi

The upsert_multi method provides the ability to perform bulk upsert operations, which are optimized for different SQL dialects.

Changes

• Dialect-Optimized SQL: Uses the most efficient SQL commands based on the database's SQL dialect.
• Support for Multiple Dialects: Includes custom implementations for PostgreSQL, SQLite, and MySQL, with appropriate handling for each's capabilities and limitations.

Usage Example

Upserting Multiple Records

from fastcrud import FastCRUD
from .models.item import Item
from .schemas.item import ItemCreateSchema, ItemSchema
from .database import session as db

crud_items = FastCRUD(Item)
items = await crud_items.upsert_multi(
    db=db,
    instances=[
        ItemCreateSchema(price=9.99),
    ],
    schema_to_select=ItemSchema,
    return_as_model=True,
)
# This will return the upserted data in the form of ItemSchema.

Implementation Details

upsert_multi handles different database dialects:

• PostgreSQL: Uses ON CONFLICT DO UPDATE.
• SQLite: Utilizes ON CONFLICT DO UPDATE.
• MySQL: Implements ON DUPLICATE KEY UPDATE.

Notes

• MySQL and MariaDB do not support certain advanced features used in other dialects, such as returning values directly after an insert or update operation. This limitation is clearly documented to prevent misuse.

What's Changed

• Simplified endpoints by @JakNowy in #105
• new tests for get_items paginated, coverage at 100% by @igorbenav in #114
• Add type-checking support for SQLModel types. by @kdcokenny in #112
• Add returning clause to update by @feluelle in #111
• Doc formatting cleanup by @slaarti in #124
• Fix return type for async session fixture in tests. by @slaarti in #125
• Add upsert_multi by @feluelle in #119
• vulnerability fixed with outdated cryptography package by @igorbenav in #129
• Models and Schemas, Batch 1: Simple/One-Off Models by @slaarti in #127
• Replace Contributing in docs w/include to file in repo root. by @slaarti in #134
• Correct links to advanced filters in docstrings? by @slaarti in #128
• Models and Schemas, Batch 2: Customers, Products, and Orders by @slaarti in #130
• kwargs missing type by @slaarti in #131
• Backfill docstring fixes. by @slaarti in #133
• Add collapsed space for models/schemas in fast_crud.py by @slaarti in #132
• Add docker client check before running tests that require docker by @feluelle in #138
• Preparations for 0.14.0 by @igorbenav in #139

New Contributors

• @kdcokenny made their first contribution in #112
• @feluelle made their first contribution in #111

Full Changelog: v0.13.1...v0.14.0

You may also see this in the docs.

0.13.1

0.13.1 Summary

Added

• More Advanced Filters by @JakNowy 🎉 (docs)

Fixed

• Bug where object with null primary key are returned with all fields set to None in nested joins #102

Detailed

---

Advanced Filters

FastCRUD supports advanced filtering options, allowing you to query records using operators such as greater than (__gt), less than (__lt), and their inclusive counterparts (__gte, __lte). These filters can be used in any method that retrieves or operates on records, including get, get_multi, exists, count, update, and delete.

Single parameter filters

Most filter operators require a single string or integer value.

# Fetch items priced between above $5
items = await item_crud.get_multi(
    db=db,
    price__gte=5,
)

Currently supported single parameter filters are:

• __gt - greater than
• __lt - less than
• __gte - greater than or equal to
• __lte - less than or equal to
• __ne - not equal
• __is - used to test True, False and None identity
• __is_not - negation of "is"
• __like - SQL "like" search for specific text pattern
• __notlike - negation of "like"
• __ilike - case insensitive "like"
• __notilike - case insensitive "notlike"
• __startswith - text starts with given string
• __endswith - text ends with given string
• __contains - text contains given string
• __match - database-specific match expression

Complex parameter filters

Some operators require multiple values. They must be passed as a python tuple, list or set.

# Fetch items priced between $5 and $20
items = await item_crud.get_multi(
    db=db,
    price__between=(5, 20),
)

• __between - between 2 numeric values
• __in - included in
• __not_in - not included in

OR parameter filters

More complex OR filters are supported. They must be passed as dictionary, where each key is a library-supported operator to be used in OR expression and values is what get's passed as the parameter.

# Fetch items priced under $5 or above $20
items = await item_crud.get_multi(
    db=db,
    price__or={'lt': 5, 'gt': 20},
)

What's Changed

• Missing sqlalchemy operators by @JakNowy in #85
• null primary key bug fixed by @igorbenav in #107
• changelog and pyproject by @igorbenav in #108

Full Changelog: v0.13.0...v0.13.1

You may also see this in the docs.

0.13.0

0.13.0 Summary

Added

• Filters in Automatic Endpoints 🎉
• One-to-many support in joins
• upsert method in FastCRUD class by @dubusster

Detailed

---

Using Filters in FastCRUD

FastCRUD provides filtering capabilities, allowing you to filter query results based on various conditions. Filters can be applied to read_multi and read_paginated endpoints. This section explains how to configure and use filters in FastCRUD.

Defining Filters

Filters are either defined using the FilterConfig class or just passed as a dictionary. This class allows you to specify default filter values and validate filter types. Here's an example of how to define filters for a model:

from fastcrud import FilterConfig

# Define filter configuration for a model
filter_config = FilterConfig(
    tier_id=None,  # Default filter value for tier_id
    name=None  # Default filter value for name
)

And the same thing using a dict:

filter_config = {
    "tier_id": None,  # Default filter value for tier_id
    "name": None,  # Default filter value for name
}

By using FilterConfig you get better error messages.

Applying Filters to Endpoints

You can apply filters to your endpoints by passing the filter_config to the crud_router or EndpointCreator. Here's an example:

from fastcrud import crud_router
from yourapp.models import YourModel
from yourapp.schemas import CreateYourModelSchema, UpdateYourModelSchema
from yourapp.database import async_session

# Apply filters using crud_router
app.include_router(
    crud_router(
        session=async_session,
        model=YourModel,
        create_schema=CreateYourModelSchema,
        update_schema=UpdateYourModelSchema,
        filter_config=filter_config,  # Apply the filter configuration
        path="/yourmodel",
        tags=["YourModel"]
    )
)

Using Filters in Requests

Once filters are configured, you can use them in your API requests. Filters are passed as query parameters. Here's an example of how to use filters in a request to a paginated endpoint:

GET /yourmodel/get_paginated?page=1&itemsPerPage=3&tier_id=1&name=Alice

Custom Filter Validation

The FilterConfig class includes a validator to check filter types. If an invalid filter type is provided, a ValueError is raised. You can customize the validation logic by extending the FilterConfig class:

from fastcrud import FilterConfig
from pydantic import ValidationError

class CustomFilterConfig(FilterConfig):
    @field_validator("filters")
    def check_filter_types(cls, filters: dict[str, Any]) -> dict[str, Any]:
        for key, value in filters.items():
            if not isinstance(value, (type(None), str, int, float, bool)):
                raise ValueError(f"Invalid default value for '{key}': {value}")
        return filters

try:
    # Example of invalid filter configuration
    invalid_filter_config = CustomFilterConfig(invalid_field=[])
except ValidationError as e:
    print(e)

Handling Invalid Filter Columns

FastCRUD ensures that filters are applied only to valid columns in your model. If an invalid filter column is specified, a ValueError is raised:

try:
    # Example of invalid filter column
    invalid_filter_config = FilterConfig(non_existent_column=None)
except ValueError as e:
    print(e)  # Output: Invalid filter column 'non_existent_column': not found in model

---

Handling One-to-One and One-to-Many Joins in FastCRUD

FastCRUD provides flexibility in handling one-to-one and one-to-many relationships through get_joined and get_multi_joined methods, along with the ability to specify how joined data should be structured using both the relationship_type (default one-to-one) and the nest_joins (default False) parameters.

One-to-One Relationships

• get_joined: Fetch a single record and its directly associated record (e.g., a user and their profile).
• get_multi_joined (with nest_joins=False): Retrieve multiple records, each linked to a single related record from another table (e.g., users and their profiles).

Example

Let's define two tables:

class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    tier_id = Column(Integer, ForeignKey("tier.id"))

class Tier(Base):
    __tablename__ = "tier"
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)

Fetch a user and their tier:

user_tier = await user_crud.get_joined(
    db=db,
    join_model=Tier,
    join_on=User.tier_id == Tier.id,
    join_type="left",
    join_prefix="tier_",
    id=1
)

The result will be:

{
    "id": 1,
    "name": "Example",
    "tier_id": 1,
    "tier_name": "Free"
}

One-to-One Relationship with Nested Joins

To get the joined data in a nested dictionary:

user_tier = await user_crud.get_joined(
    db=db,
    join_model=Tier,
    join_on=User.tier_id == Tier.id,
    join_type="left",
    join_prefix="tier_",
    nest_joins=True,
    id=1
)

The result will be:

{
    "id": 1,
    "name": "Example",
    "tier": {
        "id": 1,
        "name": "Free"
    }
}

One-to-Many Relationships

• get_joined (with nest_joins=True): Retrieve a single record with all its related records nested within it (e.g., a user and all their blog posts).
• get_multi_joined (with nest_joins=True): Fetch multiple primary records, each with their related records nested (e.g., multiple users and all their blog posts).

Warning

When using nest_joins=True, the performance will always be a bit worse than when using nest_joins=False. For cases where more performance is necessary, consider using nest_joins=False and remodeling your database.

Example

To demonstrate a one-to-many relationship, let's assume User and Post tables:

class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary key=True)
    name = Column(String)

class Post(Base):
    __tablename__ = "post"
    id = Column(Integer, primary key=True)
    user_id = Column(Integer, ForeignKey("user.id"))
    content = Column(String)

Fetch a user and all their posts:

user_posts = await user_crud.get_joined(
    db=db,
    join_model=Post,
    join_on=User.id == Post.user_id,
    join_type="left",
    join_prefix="post_",
    nest_joins=True,
    id=1
)

The result will be:

{
    "id": 1,
    "name": "Example User",
    "posts": [
        {
            "id": 101,
            "user_id": 1,
            "content": "First post content"
        },
        {
            "id": 102,
            "user_id": 1,
            "content": "Second post content"
        }
    ]
}

What's Changed

• feat: ✨ add upsert method in FastCRUD class by @dubusster in #49
• [WIP] Filters in Automatic Endpoints by @igorbenav in #87
• One-to-many support in joins by @igorbenav in #93
• tests fixed by @igorbenav in #95
• Using the same session for all tests by @igorbenav in #96
• warning added to docs by @igorbenav in #97
• preparations for 0.13.0 by @igorbenav in #98

Full Changelog: v0.12.1...v0.13.0

0.12.1

0.12.1 Summary

Added

• Deprecation Warning for dependency handling.

Detailed

---

If you pass a sequence of params.Depends type variables to any *_deps parameter in EndpointCreator and crud_router, you will get a warning. Support will be completely removed in 0.15.0.

What's Changed

• deprecation warning added to depends by @igorbenav in #82

Full Changelog: v0.12.0...v0.12.1

0.12.0

0.12.0 Summary

Added

• Unpaginated versions of multi-row get methods by @slaarti in #62 🎉
• Nested Join bug fixes
• Dependency handling now working as docs say
• Option to Skip commit in some fastcrud methods
• Docstring example fixes
• __in and __not_in filters by @JakNowy 🎉
• Fastapi 0.111.0 support

Detailed

---

Unpaginated versions of multi-row get methods

Now, if you pass None to limit in get_multi and get_multi_joined, you get the whole unpaginated set of data that matches the filters. Use this with caution.

from fastcrud import FastCRUD

from .models.item import Item
from .database import session as db

crud_items = FastCRUD(Item)
items = await crud_items.get_multi(db=db, limit=None)
# this will return all items in the db

Dependency handling now working as docs say

Now, you may pass dependencies to crud_router or EndpointCreator as simple functions instead of needing to wrap them in fastapi.Depends.

from .dependencies import get_superuser

app.include_router(
    crud_router(
        session=db,
        model=Item,
        create_schema=ItemCreate,
        update_schema=ItemUpdate,
        delete_schema=ItemDelete,
        create_deps=[get_superuser],
        update_deps=[get_superuser],
        delete_deps=[get_superuser],
        path="/item",
        tags=["item"],
    )
)

Option to Skip commit in some fastcrud methods

For create, update, db_delete and delete methods of FastCRUD, now you have the option of passing commit=False so you don't commit the operations immediately.

from fastcrud import FastCRUD

from .models.item import Item
from .database import session as db

crud_items = FastCRUD(Item)

await crud_items.delete(
    db=db, 
    commit=False, 
    id=1
)
# this will not actually delete until you run a db.commit()

__in and __not_in filters

You may now pass __in and __not_in to methods that accept advanced queries:

• __gt: greater than,
• __lt: less than,
• __gte: greater than or equal to,
• __lte: less than or equal to,
• __ne: not equal,
• __in: included in [tuple, list or set],
• __not_in: not included in [tuple, list or set].

What's Changed

• Add unpaginated versions of multi-row get methods (w/tests) by @slaarti in #62
• Join fixes by @igorbenav in #69
• Dependencies by @igorbenav in #70
• Skip commit by @igorbenav in #71
• Docstring fix by @igorbenav in #73
• feat: filter __in by @JakNowy in #57
• python support for 0.111.0 added by @igorbenav in #76
• version bump in pyproject.toml for 0.12.0 by @igorbenav in #77
• Updated docs by @igorbenav in #78

New Contributors

• @slaarti made their first contribution in #62

Full Changelog: v0.11.1...v0.12.0

You may also see this in the docs.

0.11.1

0.11.1 Summary

Added

• one_or_none parameter to FastCRUD get method (default False)
• nest_joins parameter to FastCRUD get_joined and get_multi_joined (default False)

Detailed

---

get

By default, the get method in FastCRUD returns the first object matching all the filters it finds.

If you want to ensure the one_or_none behavior, you may pass the parameter as True:

crud.get(
    async_session, 
    one_or_none=True, 
    category_id=1
)

get_joined and get_multi_joined

By default, FastCRUD joins all the data and returns it in a single dictionary.
Let's define two tables:

class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    tier_id = Column(Integer, ForeignKey("tier.id"))


class Tier(Base):
    __tablename__ = "tier"
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)

And join them with FastCRUD:

user_tier = await user_crud.get_joined(
    db=db,
    model=Tier,
    join_on=User.tier_id == Tier.id,
    join_type="left",
    join_prefix="tier_",,
    id=1
)

We'll get:

{
    "id": 1,
    "name": "Example",
    "tier_id": 1,
    "tier_name": "Free",
}

Now, if you want the joined data in a nested dictionary instead, you may just pass nest_joins=True:

user_tier = await user_crud.get_joined(
    db=db,
    model=Tier,
    join_on=User.tier_id == Tier.id,
    join_type="left",
    join_prefix="tier_",
    nest_joins=True,
    id=1,
)

And you will get:

{
    "id": 1,
    "name": "Example",
    "tier": {
        "id": 1,
        "name": "Free",
    },
}

This works for both get_joined and get_multi_joined.

Warning

Note that the final "_" in the passed "tier_" is stripped.

What's Changed

• Skip count call when possible by @JakNowy in #51
• refactor: ♻️ reuse of select method in FastCRUD by @dubusster in #55
• feat: ✨ add strict parameter to FastCRUD get method by @dubusster in #54
• Nested responses by @igorbenav in #56

New Contributors

• @JakNowy made their first contribution in #51 🎉

Full Changelog: v0.11.0...v0.11.1

0.11.0

0.11.0 Summary

Added

• multiple primary keys support by @dubusster in #31 🎉
• count made optional in get_multi and get_multi_joined methods
• validation bug when return_as_model is True fixed
• bug when passing db_row to methods fixed
• valid_methods bug fixed (raising wrong error type)
• FastAPI raised up to 0.111.0
• test coverage at 100%, workflow and badge added
• changelog in docs

Detailed

---

multiple primary keys support

Now, for a model such as :

class MultiPkModel(Base):
    __tablename__ = "multi_pk"
    id = Column(Integer, primary_key=True)
    uuid = Column(String(32), primary_key=True)
    name = Column(String, unique=True)

the endpoint creator should give a path like /multi_pk/get/{id}/{uuid} for the different endpoints (get, update and delete) that need primary keys to interact with the database. The order of path parameter is given by the column order.

Primary keys named other than id are now supported as well.

---

Optional Count

Now, in get_multi and get_multi_joined you may pass return_total_count=False to get the data only, and not the count, like this:

crud.get_multi(
    db=db,
    return_total_count=False,
)

Returning

{"data": [...]}

Standard behavior is still return_total_count=True, with:

crud.get_multi(
    db=db
)

Returning

{"data": [...], "total_count": 999}

What's Changed

• changelog docs by @igorbenav in #40
• feat: ✨ add multiple primary keys support by @dubusster in #31
• Test Coverage at 100% by @igorbenav in #42
• coverage report workflow by @igorbenav in #43
• Update README.md code coverage badge by @igorbenav in #44
• Optional count by @igorbenav in #45
• Docs update for 0.11.0 by @igorbenav in #46
• 0.11.0 added to changelog by @igorbenav in #47
• coverage badge in docs by @igorbenav in #48

Full Changelog: v0.10.0...v0.11.0

0.10.0

0.10.0 Summary

Added

• select statement by @dubusster in #28 🚀
• Support for joined models in count method (passing joins_config)
• Filters added for joined models (as filters parameter in JoinConfig)
• type checking workflow (with mypy) plus fixes for typing
• linting workflow (with ruff)

Detailed

---

Select

 async def select(
     db: AsyncSession,
     schema_to_select: Optional[type[BaseModel]] = None,
     sort_columns: Optional[Union[str, list[str]]] = None,
     sort_orders: Optional[Union[str, list[str]]] = None,
     **kwargs: Any
 ) -> Selectable

This method constructs a SQL Alchemy Select statement, offering optional column selection, filtering, and sorting. It's designed for flexibility, allowing you to chain additional SQLAlchemy methods for even more complex queries.

Features:

• Column Selection: Specify columns with a Pydantic schema.
• Sorting: Define one or more columns for sorting, along with their sort order.
• Filtering: Apply filters directly through keyword arguments.
• Chaining: Chain with other SQLAlchemy methods for advanced query construction.

Usage Example:

stmt = await my_model_crud.select(schema_to_select=MySchema, sort_columns='name', name__like='%example%')
stmt = stmt.where(additional_conditions).limit(10)
results = await db.execute(stmt)

---

Improved Joins

JoinConfig is a detailed configuration mechanism for specifying joins between models in FastCRUD queries. It contains the following key attributes:

• model: The SQLAlchemy model to join.
• join_on: The condition defining how the join connects to other models.
• join_prefix: An optional prefix for the joined columns to avoid column name conflicts.
• schema_to_select: An optional Pydantic schema for selecting specific columns from the joined model.
• join_type: The type of join (e.g., "left", "inner").
• alias: An optional SQLAlchemy AliasedClass for complex scenarios like self-referential joins or multiple joins on the same model.
• filters: An optional dictionary to apply filters directly to the joined model.

Applying Joins in FastCRUD Methods

The count Method with Joins

The count method can be enhanced with join operations to perform complex aggregate queries. While count primarily returns the number of records matching a given condition, introducing joins allows for counting records across related models based on specific relationships and conditions.

Using JoinConfig

For join requirements, the count method can be invoked with join parameters passed as a list of JoinConfig to the joins_config parameter:

from fastcrud import JoinConfig
# Count the number of tasks assigned to users in a specific department
task_count = await task_crud.count(
    db=db,
    joins_config=[
        JoinConfig(
            model=User, 
            join_on=Task.assigned_user_id == User.id
        ),
        JoinConfig(
            model=Department, 
            join_on=User.department_id == Department.id, 
            filters={"name": "Engineering"}
        )
    ]
)

Fetching Data with get_joined and get_multi_joined

These methods are essential for retrieving records from a primary model while including related data from one or more joined models. They support both simple and complex joining scenarios, including self-referential joins and many-to-many relationships.

Simple Joins Using Base Parameters

For simpler join requirements, FastCRUD allows specifying join parameters directly:

• model: The target model to join.
• join_on: The join condition.
• join_type: Specifies the SQL join type.
• aliased: When True, uses an alias for the model in the join.
• join_prefix: Optional prefix for columns from the joined model.
• filters: Additional filters for the joined model.

Examples of Simple Joining

# Fetch tasks with user details, specifying a left join
tasks_with_users = await task_crud.get_joined(
    db=db,
    model=User,
    join_on=Task.user_id == User.id,
    join_type="left"
)

Complex Joins Using JoinConfig

When dealing with more complex join conditions, such as multiple joins, self-referential joins, or needing to specify aliases and filters, JoinConfig instances become the norm. They offer granular control over each join's aspects, enabling precise and efficient data retrieval.

# Fetch users with details from related departments and roles, using aliases for self-referential joins
users = await user_crud.get_multi_joined(
    db=db,
    schema_to_select=UserSchema,
    joins_config=[
        JoinConfig(
            model=Department, 
            join_on=User.department_id == Department.id, 
            join_prefix="dept_"
        ),
        JoinConfig(
            model=Role, 
            join_on=User.role_id == Role.id, 
            join_prefix="role_"
        ),
        JoinConfig(
            model=User, 
            alias=manager_alias, 
            join_on=User.manager_id == manager_alias.id, 
            join_prefix="manager_"
        )
    ]
)

Many-to-Many Relationships with get_multi_joined

FastCRUD simplifies dealing with many-to-many relationships by allowing easy fetch operations with joined models. Here, we demonstrate using get_multi_joined to handle a many-to-many relationship between Project and Participant models, linked through an association table.

Note on Handling Many-to-Many Relationships:

When using get_multi_joined for many-to-many relationships, it's essential to maintain a specific order in your joins_config:

1. First, specify the main table you're querying from.
2. Next, include the association table that links your main table to the other table involved in the many-to-many relationship.
3. Finally, specify the other table that is connected via the association table.

This order ensures that the SQL joins are structured correctly to reflect the many-to-many relationship and retrieve the desired data accurately.

!!! TIP

Note that the first one can be the model defined in `FastCRUD(Model)`.

Scenario

Imagine a scenario where projects have multiple participants, and participants can be involved in multiple projects. This many-to-many relationship is facilitated through an association table.

Models

Our models include Project, Participant, and an association model ProjectsParticipantsAssociation:

from sqlalchemy import Column, Integer, String, ForeignKey, Table
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# Association table for the many-to-many relationship
projects_participants_association = Table('projects_participants_association', Base.metadata,
    Column('project_id', Integer, ForeignKey('projects.id'), primary_key=True),
    Column('participant_id', Integer, ForeignKey('participants.id'), primary_key=True)
)

class Project(Base):
    __tablename__ = 'projects'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    description = Column(String)
    # Relationship to Participant through the association table
    participants = relationship("Participant", secondary=projects_participants_association)

class Participant(Base):
    __tablename__ = 'participants'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    role = Column(String)
    # Relationship to Project through the association table
    projects = relationship("Project", secondary=projects_participants_association)

Fetching Data with get_multi_joined

To fetch projects along with their participants, we utilize get_multi_joined with appropriate JoinConfig settings:

from fastcrud import FastCRUD, JoinConfig

# Initialize FastCRUD for the Project model
crud_project = FastCRUD(Project)

# Define join conditions and configuration
joins_config = [
    JoinConfig(
        model=ProjectsParticipantsAssociation,
        join_on=Project.id == ProjectsParticipantsAssociation.project_id,
        join_type="inner",
        join_prefix="pp_"
    ),
    JoinConfig(
        model=Participant,
        join_on=ProjectsParticipantsAssociation.participant_id == Participant.id,
        join_type="inner",
        join_prefix="participant_"
    )
]

# Fetch projects with their participants
projects_with_participants = await crud_project.get_multi_joined(
    db_session, 
    joins_config=joins_config
)

# Now, `projects_with_participants['data']` will contain projects along with their participant information.

Practical Tips for Advanced Joins

• Prefixing: Always use the join_prefix attribute to avoid column name collisions, especially in complex joins involving multiple models or self-referential joins.
• Aliasing: Utilize the alias attribute for disambiguating joins on the same model or for self-referential joins.
• Filtering Joined Models: Apply filters directly to joined models using the filters attribute in JoinConfig to refine the data set returned by the query.
• Ordering Joins: In many-to-many relationships or complex join scenarios, carefully sequence your JoinConfig entries to ensure logical and efficient SQL join construction.

What's Changed

• feat: ✨ add select statement in FastCRUD by @dubusster in #28
• Select method changes and docs by @igorbenav in #32
• Improved Joins by @igorbenav in #35
• Type checking by @igorbenav in https://github.com/igo...