Piccolo API

Piccolo API provides utilities to expose Piccolo ORM tables as REST endpoints within ASGI applications like Starlette and FastAPI.

Key features include:

• REST Endpoints: Easily map Piccolo tables to API routes.
• ASGI Middleware: Built-in support for Session Auth, Token Auth, Rate Limiting, CSRF, and Content Security Policy (CSP).
• Pydantic Integration: Tools to automatically generate Pydantic models from your Piccolo table definitions, facilitating easy data validation and serialization.

Piccolo API provides protection against Cross-Site Request Forgery (CSRF). CSRF is a vulnerability where an attacker makes malicious HTTP requests to a domain on behalf of an authenticated user by leveraging the browser's behavior of automatically sending cookies.

To ensure your application is secure, Piccolo API applies CSRF protection specifically to unsafe HTTP methods. You must ensure that your implementation of safe methods (like GET) does not cause any side effects, such as modifying database state or performing other state-changing operations.

Since foreign keys are stored as integers, you can request their human-readable versions (defined in the Piccolo Table subclass) by appending __readable=true to your request.

This will add a field named {foreign_key_field}_readable to the response object.

Example: GET /movie/?__readable=true

Rate limiting in Piccolo API is decoupled through the use of Providers. A Provider is an abstraction responsible for two tasks:

1. Storing traffic data (tracking how many requests a client has made).
2. Signaling when a rate limit has been exceeded.

While InMemoryLimitProvider is the default, you can implement your own logic by following the RateLimitProvider interface.

PiccoloCRUD supports advanced filtering via HTTP query parameters using the field__operator=value or field__match=type syntax.

Numeric and Date/Time Operators

Use field__operator with the following values:

• lt: Less Than
• lte: Less Than or Equal
• gt: Greater Than
• gte: Greater Than or Equal
• e: Equal (default)
• ne: Not Equal

Example: GET /movie/?duration=200&duration__operator=gte (Movies lasting $\ge$ 200 mins)

Nullability

All field types support:

• is_null: Matches null values.
• not_null: Matches non-null values.

Example: GET /movie/?duration__operator=is_null

Text Match Types

For Varchar and Text fields, use field__match with:

• contains
• exact
• starts
• ends

Example: GET /movie/?name=star%20wars&name__match=starts

To get the total number of records without making a separate call to the /count endpoint, use the __range_header=true parameter. Piccolo will include a Content-Range header in the response.

You can customize the plural name used in the header via the __range_header_name parameter.

Example: GET /movie/?__page=2&__page_size=10&__range_header=true Header Example: Content-Range: movie 0-9/100

Piccolo API implements a defense-in-depth strategy to prevent Cross-Site Request Forgery (CSRF) attacks. It combines several security measures to protect users across different browser capabilities:

1. Double Submit Cookie: A random token is stored in a cookie. For unsafe requests (e.g., POST, DELETE, PUT), the client must include this same token in the X-CSRFToken header. The backend verifies that the header value matches the cookie value.
2. Referer Checking: When running over HTTPS, the middleware uses origin and/or referer headers to detect if requests are originating from a different domain.
3. Same Origin Cookies: Piccolo API provides session cookies that are marked as SameOrigin to leverage modern browser protections against cross-site requests.

In Piccolo API, Providers implement the logic specific to a particular authentication mechanism. While the core MFA (Multi-Factor Authentication) code is generic, the provider handles the implementation details of the specific method being used.

For example, an AuthenticatorProvider handles the logic for authenticating tokens from an authenticator app (like Google Authenticator) and knows how to generate the initial secrets required for a user to enable MFA.

Ordering

Use the __order query parameter to sort results.

• Ascending: ?__order=field
• Descending: Prepend a minus sign: ?__order=-field
• Multiple columns: Separate with commas: ?__order=-duration,name

Pagination

Use __page and __page_size to control result windows.

Example: GET /movie/?__page=2&__page_size=10 (Returns results 11 to 20)