piccolo-orm / piccolo Goto Github PK

Hi,

I want to congratulate you on this successful project.

I came across Piccolo a few weeks ago. I use Django-Ninja to create APIs, moved from DRF for many reasons beyond this comment.

Django-Ninja benefits FastAPI with Django ORM and Admin; however, I believe Piccolo represents a huge potential to move back to FastAPI. I already tried it on one of my projects and absolutely loved it.

Despite the feature limitation, like an obvious and automatic way to implement M2M, it has all the features required by most of my projects.

My question is: is Piccolo stable enough for production on mid to large-scale projects? pet projects don't reveal the beast inside :) and I very much like to try the performance gains of FastAPI combined with Piccolo!

Best of luck

Tables containing indices trigger a DuplicateTableError even when passed the if_not_exists=True argument.

class Example(Table):
    id = Integer(primary_key=True)
    value = Varchar(length=255, index=True)
 
# Initial table creation
await Example.create_table(if_not_exists=True)
# Duplicate table creation should be idempotent
await Example.create_table(if_not_exists=True)

This causes the following error:
asyncpg.exceptions.DuplicateTableError: relation "example_value" already exists

In converting from SQLAlchemy, I was expecting that declaring a column as JSONB would make it auto-serialize to/from JSON for me (particularly with orjson since I included that optional dependency). It would be a good idea to indicate that this column expects and returns a str to prevent confusion, and potentially include an option to auto ser/de.

Right now piccolo admin returns a weird ID when uuid is used as a primary key

django orm
startdate = datetime.now() - timedelta(days=7)
enddate = datetime.now()
Model.objects.filter(created_at__range=[startdate, enddate]

is there already a possibility to run this with piccolo without a need for raw sql?

Right now foreign keys use the int type for values. It would be nice if another type could be specified like bigint or something else

It would be nice if piccolo supported choices like django does, preferably using the python stdlib enum to make a dropdown

As far as I know there's no way to set up any event loop other than Python's asyncio. And to my understanding if using uvloop performance will be improved.

I suggest we add uvloop as an extra dependency and maybe do one of the followings:

• Try using uvloop as default and if not available fallback to asyncio
• Pass the event loop to the engine confugarion
  Or any other ways you can suggest.

Piccolo is now used in production on several projects. The API is pretty stable. I'd appreciate any beta testers, who will install the library, provide feedback on the docs, and highlight any issues they have.

Hi,
Can the id field be allowed to be redefined in the child table/model with a different type.

• Need it to be UUID which is also supported in postgres? But piccolo doesn't allow it to be changed. So all I had to do was use my own field with a unique constraint.
• Moreover, can we not have composite primary keys? is there a way to define?

Issue

Currently when a schema is made, there is no way to run a function at time of creation. Although custom crud functions can be made, it would be better if some kind of functions ran at time of creation, deletion and update of the schemas through a pre_defined meta class.

Minimal Use Case

class User(Table, tablename='my_users'):
    username = Varchar(length=100, unique=True)
    password = Secret(length=255)
    first_name = Varchar(null=True)
    last_name = Varchar(null=True)
    email = Varchar(length=255, unique=True)
    active = Boolean(default=False)
    admin = Boolean(default=False)

class Profile(Table, tablename='foo'):
   uuid = UUID()
   user = ForeignKey(references=User)
   ......

Here, I would want that a function foo_func and foo_func2 be executed when it is deleted, updated or created.

Proposed solution

I can use a function like so:

def foo_func():
   do_something

def foo_func2():
   do_something_else

def foo_func3():
   do_something_interesting

class User(Table, tablename='my_users'):
    username = Varchar(length=100, unique=True)
    password = Secret(length=255)
    first_name = Varchar(null=True)
    last_name = Varchar(null=True)
    email = Varchar(length=255, unique=True)
    active = Boolean(default=False)
    admin = Boolean(default=False)
    
    class Config:
         pre_create = ['foo_func', 'foo_func2']
         post_create = []
         on_update = ['foo_func2']
         on_delete = ['foo_func3']

Now whenever I run INSERT, UPDATE or DELETE, these functions will run accordingly.

PS - I can start working on a PR if you think this should be done

Hi,
I couldn't find any links in the docs that support pagination in piccolo. Is there any support for it which I can integrate with my FastAPI server endpoint? (Mostly looking on the lines of Django (DRF) like pagination support!)
Even thought of writing my own paginator but before that wanted to check with you @dantownsend if there's any other suggestion on this.

I have the login endpoint mounted in my FastAPI like so:

from piccolo_api.session_auth.endpoints import session_login
from piccolo_api.session_auth.tables import SessionsBase
from piccolo.apps.user.tables import BaseUser

app = FastAPI()

app.mount(
    path="/login/",
    app=session_login(
        auth_table=BaseUser,
        session_table=SessionsBase,
        redirect_to=None,
        production=False,
        cookie_name="session"
    ),
)

Doing a GET request to /login/ throws a TemplateNotFound error

jinja2.exceptions.TemplateNotFound: login.html

Full trace here: https://sentry.io/share/issue/2149cf3dcfce4585846ad47d672495e8/

I'm guessing this endpoint was designed to be used together with the Admin app and create_admin(), where the template is available?

Might be good to add a check that returns a Method not allowed if a template doesn't exist.

Thoughts?

Because no versions of piccolo-admin match >0.10.3,<0.11.0
   and piccolo-admin (0.10.3) depends on uvicorn (>=0.11.0,<0.12.0), piccolo-admin (>=0.10.3,<0.11.0) requires uvicorn (>=0.11.0,<0.12.0).
  So, because pico depends on both uvicorn (^0.13.0) and piccolo-admin (^0.10.3), version solving failed.

Poetry is really annoying that way - but otherwise is a great tool. Would be great to get this fixed.

Is there any way of joining multiple tables today? I was trying to connect to a table at 3 relations ahead and I couldn't found a easy way to do that or any way, actually

It would be handy to only install the specific drivers for use and not install unused ones:

• pip install piccolo install core piccolo
• pip install piccolo[postgres] install piccolo + asyncpg
• pip install piccolo[sqlite] install piccolo + aiosqlite
• ...

Right now we make use of extra_requires in setup.py. I noticed celery has done the following project structure which is clean:

src/
tests/
requirements/
  extras/
    postgres.txt
    sqlite.txt
  dev-requirements.txt
  requirements.txt
  test-requirements.txt

This should be easy. But importing required engines at runtime might need some further investigation.

When I created the BlackSheep template, there wasn't support for mounting ASGI apps, such as the Piccolo admin. This is no longer the case, so requires a tweak to the template.

Hi @dantownsend
I have a Date column/field which showed postgres datatype as timestamp. After altering the field type in the model to use
Timestamptz, it still shows timestamp instead of timestamptz. Can you please check. It also breaks insert statements.

For now, auto migrations only support adding nullable columns to existing tables. This needs changing, so non-nullable columns also work.

Hello!

@dantownsend I'm afraid something in #122 made the migration manager sad at the sight of a JSONB field.

piccolo migrations new blog --auto

Running Postgres version 12.5
Creating new migration ...
The command failed...
Traceback (most recent call last):
...
    return json.dumps(data, default=str)
  File "/Users/nilskanevad/.asdf/installs/python/3.9.4/lib/python3.9/json/__init__.py", line 234, in dumps
    return cls(
  File "/Users/nilskanevad/.asdf/installs/python/3.9.4/lib/python3.9/json/encoder.py", line 199, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "/Users/nilskanevad/.asdf/installs/python/3.9.4/lib/python3.9/json/encoder.py", line 257, in iterencode
    return _iterencode(o, 0)
  File "/Users/nilskanevad/dev/kekou.eu/kekou-piccolo/.venv/lib/python3.9/site-packages/piccolo/columns/base.py", line 608, in __str__
    return self.querystring.__str__()
  File "/Users/nilskanevad/dev/kekou.eu/kekou-piccolo/.venv/lib/python3.9/site-packages/piccolo/querystring.py", line 82, in __str__
    return template.format(*converted_args)
IndexError: Replacement index 0 out of range for positional args tuple

And with orjson:

...
  File "/Users/nilskanevad/dev/kekou.eu/kekou-piccolo/.venv/lib/python3.9/site-packages/piccolo/utils/sql_values.py", line 25, in convert_to_sql_value
    return dump_json(value)
  File "/Users/nilskanevad/dev/kekou.eu/kekou-piccolo/.venv/lib/python3.9/site-packages/piccolo/utils/encoding.py", line 16, in dump_json
    return orjson.dumps(data, default=str).decode("utf8")
TypeError: Type is not JSON serializable: JSONB

It works fine with JSON. And both JSON and JSONB works in 0.23.0.

My knowledge of JSONB is very lacking, and I have no clue how to find the cause of this

How about form generator for piccolo models? I think it would be awesome if there was function something like greate_form(piccolo_table)!

Hi,
Currently piccolo migrations exits with code 1 even if there are no migrations left to run.

Output:

(my-service) ➜ pipenv run piccolo migrations forwards my_service all
Courtesy Notice: Pipenv found itself running within a virtual environment, so it will automatically use that environment, instead of creating its own for any project. You can set PIPENV_IGNORE_VIRTUALENVS=1 to force pipenv to ignore that environment and create its own instead. You can set PIPENV_VERBOSITY=-1 to suppress this warning.
Loading .env environment variables…
Running Postgres version 11.1
Running migrations ...
All migration ids = ['2020-12-15T18:55:12', '2020-12-16T19:54:32', '2020-12-17T10:58:36', '2020-12-17T16:01:04', '2020-12-17T16:15:15', '2020-12-17T16:19:27', '2020-12-17T20:23:06']
Haven't run = []
No migrations left to run!
(my-service) ➜  echo $?
1

Can this be made defensive so that the cli returns a code 0 instead of exiting with 1?? Exit 1 kind of indicates one or migrations have failed.

Hey there, seems like a nice library. It's just a small suggestion and opinion, but you can add __await__ for the queries, instead of kind of an annoying .run every time

class Q:
    async def _actual_call(self) -> T:
        # .... do_some_work .... '
        return 

    def __await__(self) -> Generator[None, None, T]:
        return self._actual_call().__await__()

q = Q()
await q

Suggestion related to picollo/query/base::Query

Problem

Write now, the code I have to write becomes lengthy and hard to read if there are multiple conditions.

Example:

rows = await MyModel.select().where(Mymodel.property1 == property1).where(Mymodel.property2 == property2).where(Mymodel.property3 == property3).where(Mymodel.property4 >= property4).run()

The only thing I can do to make it better is:

rows. = await MyModel.select().where(
    MyModel.property1 == p1 &  MyModel.property2 == p2 &  MyModel.property3 == p3 & Mymodel.property4 >= p4
).run()

This doesn't look quite as intuitive and is super difficult to read in case of larger queries where there are multiple OR and AND at different complexities.

Solution I would like

A simple nice proxy to where in the form of get. (like save is to insert/update)

rows = await Mymodel.get(property1=p1, property2=p2, property3=p3, property4=p4).run()

Do you think this should be worked upon. Willing to submit a PR

Is there a support for enum types in piccolo? or an approach to achieve it like DjangoMixin. This just an enquiry didn't have a forum to ask for help hence posting it here.

Thanks! Awesome work btw.

I'm working on a project where I want to maintain a directory structure like

api
├── db
│   ├── __init__.py
│   ├── piccolo_conf.py

Since my piccolo_conf.py file is no longer at the root of the project, I need to specify the PICCOLO_CONF environment variable. For the sake of clarity (and I'm happy to make a PR for this), is it valuable to add an example to the docs highlighting that the required format for the path looks like this:
api.db.piccolo_conf
rather than this?
api/db/piccolo_conf.py
I kept running into errors the when using the latter format, but the former is accepted.

Hello,

I read all the docs and could not find how to make subqueries. Do you have this functionality?

Thank you

Hello,

I'm trying to use Piccolo to one of my project, amazing work by the way, and I may have found a bug. I didn't figure this out yet, but I'll post here If I find something.
I trying to implement the mixin pattern that I saw in the docs, however, when I execute any query, the collumns shows the name of the last class I included it, something like:

class TimestampMixin:
    created_at = columns.Timestamp()
    updated_at = columns.Timestamp()

class Class1(TimestampMixin, Table, tablename='class_1'):
   # other columns

class Class2(TimestampMixin, Table, tablename='class_2'):
   # other columns

When I run a query for Class1, let's say a count query, this is what I get:

str(Class1.count())

# 'SELECT COUNT(*) AS "count" FROM (SELECT class_1.id, class_2.created_at, class_2.updated_at FROM class_1) AS "subquery"'

So as we can see the created_at and updated_at are using a reference from the last class. Do you know if I'm doing something wrong here? The workaround for this right now is manually copying the fields and ignoring the mixin

I'm usually not a fan of benchmarks but I think it'd be a good idea to have some benchmarks comparing piccolo with other sync/async ORMs.
It will also help with cases like #143 comparing piccolo with itself with different configurations.

ASGI middleware for creating / deleting connection pools.

• JSON fields
• More aggregation functions - e.g. SUM
• Aliases in SELECT queries (e.g. SELECT foo AS bar FROM my_table)
• Improved support for data migrations
• Fixtures
• Schema visualisation tool
• piccolo sql_shell new command - execute SQL directly in a shell, via the configured engine
• piccolo shell new command - like the playground, but for your own tables
• Shorter aliases for commands in CLI, or tab completion
• Improved documentation for how to test Piccolo apps
• Nested transactions
• Move Pydantic integration from Piccolo API into Piccolo
• Allow the ForeignKey references argument to accept a string such as 'my_app.SomeTable' when circular imports are an issue.
• Support Postgres Enum types.
• Row level constraints.
• Making foreign key constraints optional on ForeignKey columns.
• Allow UUIDs as primary keys?
• Subqueries

Django provides a capability to define DATABASE_ROUTERS in settings.

• It gives you a capability to define alias names in DATABASES for primary, replica*(1,2,3... so on).
• you can customize the connection to read from replicas whereas write only to primary.
• QuerySet object also provides assistance for this setting.
  https://docs.djangoproject.com/en/3.2/topics/db/multi-db/

Is there such a definition in piccolo to connect to multiple instances (or databases) and accordingly read and write queries can be split for efficiency? Did not find anything in the docs.

Explore using Github Actions for CI.

Postgres supports arrays like bigint array, integer array etc. It would be nice to have support for these perhaps similar to how Django does it with a base_field

Running Postgres version 13.3
Running migrations ...
All migration ids = ['2021-06-14T23:35:51', '2021-06-14T23:45:55', '2021-06-14T23:53:23', '2021-06-14T23:57:34']
Haven't run = ['2021-06-14T23:35:51', '2021-06-14T23:45:55', '2021-06-14T23:53:23', '2021-06-14T23:57:34']
Running MigrationManager ...
The command failed.
user is a protected name, please give your table a different name.

I don't have any table with this name.

Before that there was table naming error!

manager.add_table("Sessions", tablename="sessions")
manager.add_table("SessionsBase", tablename="sessions")  

Implementing abstract tables to avoid repeating columns for the tables. Obviously the abstract tables are ignored in automatic migrations. It would be something like this:

class Base(Table, abstract=True):
    created_at = Date()


class Band(Base):
    name = Varchar()

Hi,
Currently when I define a boolean field in a table/relation/model with a default value as True. Even while creating a record it still doesn't overwrite with a value other than the default value.
Example: passing False doesn't create a record with the boolean attribute value as False it still stays True if the default was provided as True.

• If possible, please do verify with other fields where one can provide a default value.(Haven't checked it)

Loading .env environment variables…
Running Postgres version 11.1
Creating new migration ...
Created tables                           0
Dropped tables                           0
Renamed tables                           0
Created table columns                    0
Dropped columns                          0
Columns added to existing tables         0
Renamed columns                          0
Altered columns                          0
No changes detected - exiting.
make: *** [Makefile:84: create_migrations] Error 1

As you can see when a new migration is attempted for creation it fails. Should be idempotent I believe. Instead of exiting with error code 1, can it be fixed with exit 0?

Hi,
When column type is Date(null=True, default=None). Migration fails!

Snippet:

Running migrations ...
All migration ids = ['2020-12-15T18:55:12', '2020-12-16T19:54:32', '2020-12-17T10:58:36', '2020-12-17T16:01:04', '2020-12-17T16:15:15', '2020-12-17T16:19:27', '2020-12-17T20:23:06', '2020-12-22T16:44:03', '2021-01-05T13:56:22', '2021-01-07T19:52:45', '2021-01-08T12:52:56', '2021-01-08T21:26:24', '2021-01-08T22:05:10']
Haven't run = ['2021-01-08T22:05:10']
Running MigrationManager ...
The command failed.
syntax error at or near "DEFAULT"

Content of migration file that failed:

manager.alter_column(
        table_class_name="Summary",
        tablename="summary",
        column_name="due_date",
        params={"default": None},
        old_params={"default": DateNow()},
    )

Is there an example of how to integrate piccolo into an existing fastapi application?

Something along the lines of the examples in the fastapi docs here and here.

I'm interested in trying the ORM out as it reminds me a lot of how EntityFramework operates in the .NET world, but right now it seems a lot of magic and black boxed and to require using one of the generators to put all the pieces into place. That's just my perception. Anyways, would love to see a clean and understandable example for how to put this into an existing fastapi app and use similarily to the ORMs already mentioned in the fastapi docs.

Thanks much - wg

Hello,

Is there a recommended way to run migrations programmatically, for example, when setting up a test suite?

It is possible to import fowards from https://github.com/piccolo-orm/piccolo/blob/master/piccolo/apps/migrations/commands/forwards.py#L83 but the code uses sys.exit(0) which kills the parent process.

I suppose one could shell out the command as part of setting up the test environment, but I don't think that is ideal.

It seems like even though primary is an available argument to a Column, using it messes with migrations, preventing auto from working properly. Through some trial and error, I seem to have found that an id column is automatically added as the primary key to models. This wasn't at all clear to me in reading through the documentation.

I would like the ability to declare my own primary key column called pk, as id is a builtin function in Python and shadowing it (e.g. in a param to a function) can cause accidental mistakes. At the very least, it would be good to make this default behavior more obvious in the documentation.

Is there a way to have piccolo work on legacy database schemas (like managed = False in django)

how can we filter not null values using piccolo?

example-:

Table Person :
Name | Country
Arvind | India
Alien | NULL

I want to filter null values from the country column

people = (
    Person.objects()
    .where(Person.country != None)
    .run_sync()
)

expected: Arvind | India
actual: empty list

Hello,

I just recently stumbled upon Piccolo and I'm very impressed so far with how much thought and effort has been put into it. Modern Python desperately needs an ORM that can keep up with the next generation of web development libraries. Piccolo looks to be well on it's way to fill that need.

I have a project in mind that I could use Piccolo with, but it does have a use case that requires the use of a PostGIS geography type column. I've been looking through https://github.com/piccolo-orm/piccolo/blob/master/piccolo/columns/base.py and it doesn't look too daunting to subclass Column and go from there. If I can get something useable, even with using raw for selects, it would be pretty great.

Since there doesn't seem to be any documentation on implementing custom columns, I'd figure I'd post an issue, even if there isn't really a problem yet just in case anyone had any advice or thoughts.

If I run mypy on a file like this, it complains that I can't use a Varchar like a str.

from piccolo.table import Table
from piccolo.columns import Varchar

class Something(Table):
    a_string = Varchar(100)

def get_the_thing():
    Something.objects().first().run_sync().a_string.split(",")

Similar issue if, say, trying to json.dumps a column of type JSONB. Leads to # type: ignore and cast all over the place.

I don't know how possible if at all it would be to properly annotate the columns in a way that type checkers will know what the proper type is for instances. But hopefully it is!

There isn't any documentation yet around testing with Piccolo. It's possible by using the PICCOLO_CONF environment variable, to specify a different configuration file containing settings for a test database.

One of the best sources of examples on Piccolo testing, are Piccolo's own tests.

Ideally:

• piccolo (all core ORM functionality)
• piccolo migrations
• piccolo auth (User model and auth middleware)
• piccolo CLI (other components can register commands with piccolo for use in a terminal)
• piccolo admin (UI on top of piccolo)
• piccolo scaffold (creates a basic app from these components)
• piccolo playground (sandbox for trying out queries)