1.32.0

Added the having clause, which is useful when working with group_by.

For example, here we get the number of albums per band, but exclude any bands with less than 2 albums:

>>> from piccolo.query.functions.aggregate import Count

>>> await Album.select(
...     Album.band.name.as_alias('band_name'),
...     Count()
... ).group_by(
...     Album.band
... ).having(
...     Count() >= 2
... )

[
    {"band_name": "Pythonistas", "count": 2},
]

We also updated our CockroachDB support to the latest version (thanks to @sinisaos for this).

1.31.0

• Added official Postgres 18 support.
• Removed graphlib backport (no longer needed in supported Python versions).
• Improved documentation for column kwargs.
• Updated README to mention other parts of the Piccolo ecosystem (thanks to @sinisaos for this).
• Table instances can now be compared using the == operator, and will return True if they have the same primary key value (thanks to @aarcex3 and @Skelmis for this).
• Fixed missing import for auto migrations when tables use LazyTableReference (thanks to @sinisaos for this).
• Improved docs for the auto generated primary key (thanks to @badlydrawnrob for this).
• Improved Table._table_str (this is most obvious in the playground, where the table definitions printed out now show more information - thanks to @badlydrawnrob for raising this issue).
• Fixed a bug with ModelBuilder when using Array columns with choices defined.
• add_m2m now returns the id of the joining table row, to match the docs (thanks to @diklios5768 for reporting this).
• Improved the docs for UUID columns (what the UUID4 default value does).
• Moved connection pooling to its own page in the docs.
• Improved the on_conflict clause - a target must be specified if using DO UPDATE (thanks to @mafuyuuu1 for raising this issue, and @sinisaos for this fix).
• Changed Esmerald to Ravyn in the ASGI templates (thanks to @sinisaos for this).
• Fixed a bug with auto migrations when changing a column to a foreign key - an exception was being raised (thanks to @gsavchuk for raising this issue and @sinisaos for this fix).
• Array(Numeric()) columns now work with SQLite (thanks to @sinisaos for this).
• UUID columns now use the built-in gen_random_uuid() function in Postgres to generate a default value, instead of using the uuid-ossp extension (thanks to @sinisaos for this).

1.30.0

Added support for Python 3.14 (thanks to @sinisaos for this).

1.29.0

• Fixed a bug with adding / subtracting Integer columns from one another in queries (thanks to @ryanvarley for this).
• Updated the ASGI templates, and BlackSheep dependencies (thanks to @sinisaos for this).
• Fixed a bug where decimal values generated by ModelBuilder could be too large.
• Added an example M2M relationship in the playground to make learning M2M easier (thanks to @sinisaos for this).
• Added documentation for MigrationManager.get_table_from_snapshot, which is a way of getting a Table from the migration history - useful when running data migrations (thanks to @sinisaos for this).
• Columns with the secret=True argument are now added to Table._meta.secret_columns (thanks to @sinisaos for this).
• Added documentation for the migration table.
• Tidied up Pydantic tests (thanks to @sinisaos for this).

1.28.0

Playground improvements

• Added an Array column to the playground (Album.awards), for easier experimentation with array columns.
• CoachroachDB is now supported in the playground (thanks to @sinisaos for this).

piccolo playground run --engine=cockroach

Functions

Added lots of useful array functions (thanks to @sinisaos for this).

Here's an example, where we can easily fix a typo in an array using replace:

>>> await Album.update({
...     Album.awards: Album.awards.replace('Grammy Award 2021', 'Grammy Award 2022')
... }, force=True)

The documentation for functions has also been improved (e.g. how to create a custom function).

The Cast function is now more flexible.

Array concantenation

Values can be prepended:

>>> await Album.update({
...     Album.awards: ['Grammy Award 2020'] + Album.awards
... }, force=True)

And multiple arrays can be concatenated in one go:

>>> await Album.update({
...     Album.awards: ['Grammy Award 2020'] + Album.awards + ['Grammy Award 2025']
... }, force=True)

is_in and not_in sub queries

You can now use sub queries within is_in and not_in Thanks to @sinisaos for this.

>>> await Band.select().where(
...     Band.id.is_in(
...         Concert.select(Concert.band_1).where(
...             Concert.starts >= datetime.datetime(year=2025, month=1, day=1)
...         )
...     )
... )

Other improvements

• Auto convert a default value of 0 to 0.0 in Float columns.
• Modernised the type hints throughout the codebase (e.g. using list instead of typing.List). Thanks to @sinisaos for this.
• Fixed a bug with auto migrations, where the Array base column class wasn't being imported.
• Improved M2M query performance by using sub selects (thanks to @sinisaos for this).

1.27.1

Improved the type annotations in ColumnKwargs - made some optional. Thanks to @stronk7 and @sinisaos for their help with this.

1.27.0

Improved auto completion / typo detection for column arguments.

For example:

  class Band(Table):
      name = Varchar(nul=True)  # linters will now warn that nul is a typo (should be null)

Thanks to @sinisaos for this.

1.26.1

Updated the BlackSheep ASGI template - thanks to @sinisaos for this.

Fixed a bug with auto migrations when a ForeignKey specifies target_column - multiple primary key columns were added to the migration file. Thanks to @waldner for reporting this issue.

Added a tutorial for moving tables between Piccolo apps - thanks to @sarvesh4396 for this.

1.26.0

Improved auto migrations - ON DELETE and ON UPDATE can be modified on ForeignKey columns. Thanks to @sinisaos for this.

1.25.0

Improvements to Piccolo app creation. When running the following:

piccolo app new my_app

It now validates that the app name (my_app in this case) is valid as a Python package.

Also, there is now a --register flag, which automatically adds the new app to the APP_REGISTRY in piccolo_conf.py.

piccolo app new my_app --register

Other changes:

• table_finder can now use relative modules.
• Updated the Esmerald ASGI template (thanks to @sinisaos for this).
• When using the remove method to delete a row from the database (e.g. await some_band.remove()), then some_band._exists_in_db is now set to False. Thanks to @sinisaos for this fix.