piccolo

Fixed a bug with auto migrations. If renaming multiple columns at once, it could get confused. Thanks to @theelderbeever for reporting this issue, and @sinisaos for helping to replicate it. See PR 457.

We ran a profiler on the Piccolo codebase and identified some optimisations. For example, we were calling self.querystring multiple times in a method, rather than assigning it to a local variable.

We also ran a linter which identified when list / set / dict comprehensions could be more efficient.

The performance is now slightly improved (especially when fetching large numbers of rows from the database).

Example query times on a MacBook, when fetching 1000 rows from a local Postgres database (using await SomeTable.select()):

• 8 ms without a connection pool
• 2 ms with a connection pool

As you can see, having a connection pool is the main thing you can do to improve performance.

Thanks to @AliSayyah for all his work on this.

Made improvements to piccolo schema generate, which automatically generates Piccolo Table classes from an existing database.

There were situations where it would fail ungracefully when it couldn't parse an index definition. It no longer crashes, and we print out the problematic index definitions. See PR 449. Thanks to @gmos for originally reporting this issue.

We also improved the error messages if schema generation fails for some reason by letting the user know which table caused the error. Courtesy @AliSayyah.

We used to raise a ValueError if a column was both null=False and default=None. This has now been removed, as there are situations where it's valid for columns to be configured that way. Thanks to @gmos for suggesting this change.

The where clause now raises a ValueError if a boolean value is passed in by accident. This was possible in the following situation:

await Band.select().where(Band.has_drummer is None)

Piccolo can't override the is operator because Python doesn't allow it, so Band.has_drummer is None will always equal False. Thanks to @trondhindenes for reporting this issue.

We've also put a lot of effort into improving documentation throughout the project.

• Lots of documentation improvements, including how to customise BaseUser (courtesy @sinisaos).
• Fixed a bug with creating indexes when the column name clashes with a SQL keyword (e.g. 'order'). See Pr 433. Thanks to @wmshort for reporting this issue.
• Fixed an issue where some slots were incorrectly configured (courtesy @ariebovenberg). See PR 426.

Fixed a bug with auto migrations which rename columns - see PR 423. Thanks to @theelderbeever for reporting this, and @sinisaos for help investigating.

Added Xpresso as a supported ASGI framework when using piccolo asgi new to generate a web app.

Thanks to @sinisaos for adding this template, and @adriangb for reviewing.

We also took this opportunity to update our FastAPI and BlackSheep ASGI templates.

Update queries without a where clause

If you try and perform an update query without a where clause you will now get an error:

>>> await Band.update({Band.name: 'New Band'})
UpdateError

If you want to update all rows in the table, you can still do so, but you must pass force=True.

>>> await Band.update({Band.name: 'New Band'}, force=True)

This is a similar to delete queries, which require a where clause or force=True.

It was pointed out by @theelderbeever that an accidental mass update is almost as bad as a mass deletion, which is why this safety measure has been added.

See PR 412.

JSONB improvements

Fixed some bugs with nullable JSONB columns. A value of None is now stored as null in the database, instead of the JSON string 'null'. Thanks to @theelderbeever for reporting this.

See PR 413.

create_user

BaseUser now has a create_user method, which adds some extra password validation vs just instantiating and saving BaseUser directly.

>>> await BaseUser.create_user(username='bob', password='abc123XYZ')
<BaseUser: 1>

We check that passwords are a reasonable length, and aren't already hashed. See PR 402.

async first

All of the docs have been updated to show the async version of queries.

For example:

# Previous:
Band.select().run_sync()

# Now:
await Band.select()

Most people use Piccolo in async apps, and the playground supports top level await, so you can just paste in await Band.select() and it will still work. See PR 407.

We decided to use await Band.select() instead of await Band.select().run(). Both work, and have their merits, but the simpler version is probably easier for newcomers.

In Piccolo you can print out any query to see the SQL which will be generated:

>>> print(Band.select())
SELECT "band"."id", "band"."name", "band"."manager", "band"."popularity" FROM band

It didn't represent UUID and datetime values correctly, which is now fixed (courtesy @theelderbeever). See PR 405.

Using descriptors to improve MyPy support PR 399.

MyPy is now able to correctly infer the type in lots of different scenarios:

class Band(Table):
    name = Varchar()

# MyPy knows this is a Varchar
Band.name

band = Band()
band.name = "Pythonistas"  # MyPy knows we can assign strings when it's a class instance
band.name  # MyPy knows we will get a string back

band.name = 1  # MyPy knows this is an error, as we should only be allowed to assign strings

You can see it working here in VSCode:

Fixed bug with BaseUser and Piccolo API.

The BaseUser table hashes passwords before storing them in the database.

When we create a fixture from the BaseUser table (using piccolo fixtures dump), it looks something like:

{
    "id": 11,
    "username": "bob",
    "password": "pbkdf2_sha256$10000$abc123"
}

When we load the fixture (using piccolo fixtures load) we need to be careful in case BaseUser tries to hash the password again (it would then be a hash of a hash, and hence incorrect). We now have additional checks in place to prevent this.

Thanks to @mrbazzan for implementing this, and @sinisaos for help reviewing.

Added initial support for ForeignKey columns referencing non-primary key columns. For example:

class Manager(Table):
    name = Varchar()
    email = Varchar(unique=True)

class Band(Table):
    manager = ForeignKey(Manager, target_column=Manager.email)

Thanks to @theelderbeever for suggesting this feature, and with help testing.

Fixed an issue with the value_type of ForeignKey columns when referencing a table with a custom primary key column (such as a UUID).

Added an exclude_imported option to table_finder.

APP_CONFIG = AppConfig(
    table_classes=table_finder(['music.tables'], exclude_imported=True)
)

It's useful when we want to import Table subclasses defined within a module itself, but not imported ones:

# tables.py
from piccolo.apps.user.tables import BaseUser # excluded
from piccolo.columns.column_types import ForeignKey, Varchar
from piccolo.table import Table


class Musician(Table): # included
    name = Varchar()
    user = ForeignKey(BaseUser)

This was possible using tags, but was less convenient. Thanks to @sinisaos for reporting this issue.

Fixed the error message in LazyTableReference.

Fixed a bug with create_pydantic_model with nested models. For example:

create_pydantic_model(Band, nested=(Band.manager,))

Sometimes Pydantic couldn't uniquely identify the nested models. Thanks to @wmshort and @sinisaos for their help with this.

Added a max password length to the BaseUser table. By default it's set to 128 characters.

Fixed a bug with Readable when it contains lots of joins.

Readable is used to create a user friendly representation of a row in Piccolo Admin.