This is another approach to cursor pagination by moving the cursor logic from the crud endpoint to a separate file and created the CursorPagination class

This PR adds a few new options for range-headers. This allows the api to send data about current pages and total number of records back to the client along with the data, which can help avoid excessive calls. I was a little unsure how to deal with filtering parameters, but I think the correct for a content-range header is to always return the total number of records as the "total" regardless of any filtering applied to the current query.

adding this will make it easier to use piccolo-api as a backend for django-admin (https://marmelab.com/react-admin).

I haven't added documentation yet, waiting to get maintaners' feedback before I do so.

At version 0.29.0 there seems to be no support for ARRAY types in the CRUD generation. Code is generated, but an ARRAY of VARCHAR comes out as a single Varchar, and the field cannot be properly used.

In the Piccolo Admin the Arrays of Varchar are handled ok. Are there already plans to support Array types in CRUD?

@dantownsend I hope you meant something similar to this. Once you've checked and merged this, I'll make the changes in Piccolo Admin. Related to this issue.

It would be useful to show an app which has all of the session auth components working together (session_login, session_logout and SessionsAuthBackend).

Here's an example:

import datetime
from fastapi import FastAPI
from piccolo_api.csrf.middleware import CSRFMiddleware
from piccolo_api.openapi.endpoints import swagger_ui
from piccolo_api.session_auth.endpoints import session_login, session_logout
from piccolo_api.session_auth.middleware import SessionsAuthBackend
from starlette.middleware import Middleware
from starlette.middleware.authentication import AuthenticationMiddleware
from starlette.routing import Route

app = FastAPI()

app.mount(
    "/login/",
    session_login(),
)

private_app = FastAPI(
    routes=[
        Route("/logout/", session_logout()),
    ],
    middleware=[
        Middleware(
            AuthenticationMiddleware,
            backend=SessionsAuthBackend(
                increase_expiry=datetime.timedelta(minutes=30)
            ),
        ),
        Middleware(CSRFMiddleware, allow_form_param=True),
    ],
    docs_url=None,
    redoc_url=None,
)

# The Swagger docs which come with FastAPI don't support CSRF middleware, so we mount
# a custom one which Piccolo provides (accessible at /private/docs):
private_app.mount("/docs/", swagger_ui(schema_url="/private/openapi.json"))

@private_app.get('/my-secret-endpoint/')
def my_endpoint():
    # This is just a normal FastAPI endpoint, and is protected by Session Auth
    pass

app.mount("/private/", private_app)

if __name__ == "__main__":
    import uvicorn
    uvicorn.run(app)

As an alternative to adding hooks/signals to piccolo-orm itself, I wanted to explore if such functionality could be more easily added to piccolo-api instead. My goal is to have extensibility points in piccolo-api (and maybe thereby also in piccolo-admin) that expand on the existing crud operations.

My propsal is that PiccoloCRUD taks an optional hooks parameter, which is a list of coroutines and when to trigger them. So far I've only added pre_save as a proof of concept. This lets me create a test app looking like this (i've just snipped the relevant parts of the app:

class Customer(Table):
    email: str = Varchar(null=True, default=None)
    first_name: str = Varchar(null=True, default=None)
    last_name: str = Varchar(null=True, default=None)


class NiceCustomer(BaseModel):
    email: str = None


async def awesome_hook(row: Customer):
    dict_row = row.to_dict()
    model_thing = NiceCustomer(**dict_row)
    async with httpx.AsyncClient() as client:
        r = await client.post(
            "https://good-url.example.com",
            json=model_thing.dict()
        )


FastAPIWrapper(
    root_url="/api/v2/customer/",
    fastapi_app=app,
    piccolo_crud=PiccoloCRUD(
        page_size=50,
        table=Customer,
        read_only=False,
        hooks=[
            Hook(
                hook_type=HookType.pre_save,
                coro=awesome_hook
            )
        ]
    )
)

I'd be happy to continue building on this, but I want to get maintainer's initial feedback first.

Based on https://github.com/piccolo-orm/piccolo_api/pull/171

I made two modifications. The main thing is there's now a single argument called upload_metadata for passing in the various S3 options. S3 has so many options available:

https://boto3.amazonaws.com/v1/documentation/api/latest/reference/customizations/s3.html#boto3.s3.transfer.S3Transfer

If we just let the user pass in a dictionary instead, we cover all future use cases.

The other modification was adding a sign_urls argument. So if it's being used with a public bucket we're not wasting resources generating signed URLs when they're not required.

@dantownsend I've just upgraded my infrastructure to the latest version of FastAPI and I'm now getting a strange error when trying to view the Swagger UI. In the first instance FastAPI is giving me a "failed to load API definition" which is a problem with retrieving the OpenAPI JSON spec. Digging a bit more into the problem however reveals that something is going on when creating the Pydantic response models.

Fetch error Response status is 500 /api/openapi.json

Digging a bit more into the problem however reveals that something is going on when creating the Pydantic response models. I assume this is soemthing to do with create_pydantic_model. I'm getting an error like the one here, which is the closest I can find to any explanation of the issue:

https://github.com/tiangolo/fastapi/issues/1505

The relevant bit in my case is:

File "/usr/local/lib/python3.7/site-packages/fastapi/openapi/utils.py", line 364, in get_openapi flat_models=flat_models, model_name_map=model_name_map File "/usr/local/lib/python3.7/site-packages/fastapi/utils.py", line 28, in get_model_definitions model_name = model_name_map[model] KeyError: <class 'pydantic.main.Lemma'>

I'm stumped, as nothing has changed at all in my model / table definitions since the update. Is this likely something due to the interaction of Piccolo - Pydantic - FastAPI or out of Piccolo's scope?

I am struggling a bit with Piccolo's authentication systems. I have a FastAPI app and am wrapping it with Starlette's AuthenticationMiddleware, as hinted in the docs, with the joint SessionAuth and SecretTokenAuth providers. The secret token seems to be working alright; my API client won't get results without the correct header-cum-token. However whatever I do on the browser gives me "Auth failed for all backends". I can't get to the login endpoint, though this appears properly configured according to the docs. I tried 'allow unauthenticated' to see if this would loosen up the permissions, but even the FastAPI docs give me this error. Is there any robust example app with SessionAuth to see how everything should be organized?

Added the ability to move in different directions (forward and backward) from next_cursor.

Example:
http://localhost:8000/posts/?__page_size=3&__order=id&__cursor=NA== (ASC forward)
http://localhost:8000/posts/?__page_size=3&__order=id&__cursor=NA==&__previous=yes (ASC backward)
http://localhost:8000/posts/?__page_size=3&__order=-id&__cursor=OQ== (DESC forward)
http://localhost:8000/posts/?__page_size=3&__order=-id&__cursor=OQ==&__previous=yes (DESC backward)

This is quite tricky with a lot of edge cases. I don't know exactly how to use cursor pagination in piccolo_admin. LimitOffset pagination works good (up to 200,000 rows), and is quite convinient. We may use cursor pagination above that number (maybe millions of rows, but I’m have never encountered such large data sets), but then we lose the ability to sort by all columns except by id, because the cursor must be unique. You probably know much better than I how to implement that. Cheers.

Currently, the __page and __page_size query params aren't documented.

Also, PiccoloCRUD should have a max_page_size argument, to limit abuse of an endpoint.

If the max_page_size is exceeded, an error should be returned. A 403 feels most appropriate, with a body such as The page size limit has been exceeded.

See: https://www.cockroachlabs.com/docs/stable/timestamp.html

Cockroach always uses Timestamptz (set to +00:00) when Timestamp is specified. This is confusing to piccolo migrations.

gnat@gnat:~/Desktop/piccolo_examples-master/headless_blog_fastapi$ piccolo migrations forwards all

                              BLOG                              
----------------------------------------------------------------
👍 1 migration already complete
🏁 No migrations need to be run

                          SESSION_AUTH                          
----------------------------------------------------------------
👍 1 migration already complete
⏩ 1 migration not yet run
🚀 Running 1 migration:
  - 2019-11-12T20:47:17 [forwards]... The command failed.
expected DEFAULT expression to have type timestamp, but 'current_timestamp() + '01:00:00'' has type timestamptz
For a full stack trace, use --trace

Suggestions? Too new to Piccolo to know what course of action to take in order to make this work smoothly.

Otherwise Cockroach seems to work fairly flawlessly with Piccolo, because of asyncpg.

It would be good if we modified PiccoloCRUD, so it could accept a media_storage argument (like create_admin does in Piccolo Admin).

PiccoloCRUD(
    Movie,
    media_columns=[
        LocalMediaStorage(Movie.poster, media_path='/srv/media/movie_posters/')
    ]
)

We could then add a new GET parameter called something like __media_urls, which then auto adds the URL to the response for accessing the media.

GET /1/?__media_urls
{
    'id': 1,
    'name': 'Star Wars',
    'poster': 'some-file-abc-123.jpg',
    'poster_url': '/media/some-file-abc-123.jpg',
}

Once this is in place, we can refactor Piccolo Admin slightly, so it passes the media_storage argument to PiccoloCRUD, and lets PiccoloCRUD do all of the heavy lifting.

I want to create blockchain backend app, based on piccolo. So I need custom User model (generally, without password field). As suggested in docs, I must implement custom user app. But I also want to use session mechanism. How can I achieve that case?

BTW, Great project! I've struggled with fastapi-sqlalchemy stack and it's back and forth model transitions. I've looked other ORMs, compatible with pydantic and FastAPI (Tortoise, SQLModel, Databases, GINO), but those projects looks too young or unmaintained. And only piccolo had my heart from first look). Thank you and keep doing your great work!

PiccoloCRUD currently has hooks like pre_save, which is used to modify the data before insertion into the database. We should add a post_save too, which is run after insertion into the database. It can be used for logging, sending an email etc.

Just like we've done with other endpoints. There are many potential use cases:

• If someone keeps getting their password wrong, we may want to log it
• When someone's password is successfully changed, we might want to send an email to that user
• We might want to do some custom validation on the password