Fixed a bug with create_pydantic_model when used with a Decimal / Numeric column when no digits arguments was set (courtesy @AliSayyah).

Added the create_tables function, which accepts a sequence of Table subclasses, then sorts them based on their ForeignKey columns, and creates them. This is really useful for people who aren’t using migrations (for example, when using Piccolo in a simple data science script). Courtesy @AliSayyah.

from piccolo.tables import create_tables

create_tables(Band, Manager, if_not_exists=True)

# Equivalent to:

Fixed typos with the new fixtures app - sometimes it was referred to as fixture and other times fixtures. It’s now standardised as fixtures (courtesy @hipertracker).


The piccolo user create command can now be used by passing in command line arguments, instead of using the interactive prompt (courtesy @AliSayyah).

For example piccolo user create --username=bob ....

This is useful when you want to create users in a script.


You can now use pip install piccolo[all], which will install all optional requirements.


Added the fixtures app. This is used to dump data from a database to a JSON file, and then reload it again. It’s useful for seeding a database with essential data, whether that’s a colleague setting up their local environment, or deploying to production.

To create a fixture:

piccolo fixtures dump --apps=blog > fixture.json

To load a fixture:

piccolo fixtures load fixture.json

As part of this change, Piccolo’s Pydantic support was brought into this library (prior to this it only existed within the piccolo_api library). At a later date, the piccolo_api library will be updated, so it’s Pydantic code just proxies to what’s within the main piccolo library.


Improvements to piccolo schema generate. It’s now smarter about which imports to include. Also, the Table classes output will now be sorted based on their ForeignKey columns. Internally the sorting algorithm has been changed to use the graphlib module, which was added in Python 3.9.


Added the piccolo schema graph command for visualising your database structure, which outputs a Graphviz file. It can then be turned into an image, for example:

piccolo schema map | dot -Tpdf -o graph.pdf

Also made some minor changes to the ASGI templates, to reduce MyPy errors.


Updated to_dict so it works with nested objects, as introduced by the prefetch functionality.

For example:

band = Band.objects(Band.manager).first().run_sync()

>>> band.to_dict()
{'id': 1, 'name': 'Pythonistas', 'manager': {'id': 1, 'name': 'Guido'}}

It also works with filtering:

>>> band.to_dict(,
{'name': 'Pythonistas', 'manager': {'name': 'Guido'}}


Added the ability to prefetch related objects. Here’s an example:

band = await Band.objects(Band.manager).run()
>>> band.manager
<Manager: 1>

If a table has a lot of ForeignKey columns, there’s a useful shortcut, which will return all of the related rows as objects.

concert = await Concert.objects(Concert.all_related()).run()
>>> concert.band_1
<Band: 1>
>>> concert.band_2
<Band: 2>
>>> concert.venue
<Venue: 1>

Thanks to @wmshort for all the input.


Migrations containing Array, JSON and JSONB columns should be more reliable now. More unit tests were added to cover edge cases.


You can now use all_columns at the root. For example:


You can also exclude certain columns if you like:



Fix a regression where if multiple tables are created in a single migration file, it could potentially fail by applying them in the wrong order.


Fixed a bug where if all_columns was used two or more levels deep, it would fail. Thanks to @wmshort for reporting this issue.

Here’s an example:,

Also, the ColumnsDelegate has now been tweaked, so unpacking of all_columns is optional.

# This now works the same as the code above (we have omitted the *),


Loosen the typing-extensions requirement, as it was causing issues when installing asyncpg.


Added nested output option, which makes the response from a select query use nested dictionaries:

>>> await, *Band.manager.all_columns()).output(nested=True).run()
[{'name': 'Pythonistas', 'manager': {'id': 1, 'name': 'Guido'}}]

Thanks to @wmshort for the idea.


Added to_dict method to Table.

If you just use __dict__ on a Table instance, you get some non-column values. By using to_dict it’s just the column values. Here’s an example:

class MyTable(Table):
    name = Varchar()

instance = MyTable.objects().first().run_sync()

>>> instance.__dict__
{'_exists_in_db': True, 'id': 1, 'name': 'foo'}

>>> instance.to_dict()
{'id': 1, 'name': 'foo'}

Thanks to @wmshort for the idea, and @aminalaee and @sinisaos for investigating edge cases.


Removed problematic type hint which assumed pytest was installed.


Minor changes to get_or_create to make sure it handles joins correctly.

instance = (
        ( == "My new band")
        & ( == "Excellent manager")

In this situation, there are two columns called name - we need to make sure the correct value is applied if the row doesn’t exist.


get_or_create now supports more complex where clauses. For example:

row = await Band.objects().get_or_create(
    ( == 'Pythonistas') & (Band.popularity == 1000)

And you can find out whether the row was created or not using row._was_created.

Thanks to @wmshort for reporting this issue.


Added ModelBuilder, which can be used to generate data for tests (courtesy @aminalaee).


Fixed an issue where like and ilike clauses required a wildcard. For example:


You can now omit wildcards if you like:


Which would match on 'guido' and 'Guido', but not 'Guidoxyz'.

Thanks to @wmshort for reporting this issue.


  • Improved PrimaryKey deprecation warning (courtesy @tonybaloney).
  • Added piccolo schema generate which creates a Piccolo schema from an existing database.
  • Added piccolo tester run which is a wrapper around pytest, and temporarily sets PICCOLO_CONF, so a test database is used.
  • Added the get convenience method (courtesy @aminalaee). It returns the first matching record, or None if there’s no match. For example:
manager = await Manager.objects().get( == 'Guido').run()

# This is equivalent to:
manager = await Manager.objects().where( == 'Guido').first().run()


Added the get_or_create convenience method (courtesy @aminalaee). Example usage:

manager = await Manager.objects().get_or_create( == 'Guido'


  • Bug fix, where compare_dicts was failing in migrations if any Column had an unhashable type as an argument. For example: Array(default=[]). Thanks to @hipertracker for reporting this problem.
  • Increased the minimum version of orjson, so binaries are available for Macs running on Apple silicon (courtesy @hipertracker).


Fix for auto migrations when using custom primary keys (thanks to @adriangb and @aminalaee for investigating this issue).


Migrations can now have a description, which is shown when using piccolo migrations check. This makes migrations easier to identify (thanks to @davidolrik for the idea).


Added an all_columns method, to make it easier to retrieve all related columns when doing a join. For example:

await, *Band.manager.all_columns()).first().run()

Changed the instructions for installing additional dependencies, so they’re wrapped in quotes, to make sure it works on ZSH (i.e. pip install 'piccolo[postgres]' instead of pip install piccolo[postgres]).


The database drivers are now installed separately. For example: pip install piccolo[postgres] (courtesy @aminalaee).

For some users this might be a breaking change - please make sure that for existing Piccolo projects, you have either asyncpg, or piccolo[postgres] in your requirements.txt file.


The user can now specify the primary key column (courtesy @aminalaee). For example:

class RecordingStudio(Table):
    pk = UUID(primary_key=True)

The BlackSheep template generated by piccolo asgi new now supports mounting of the Piccolo Admin (courtesy @sinisaos).


Added aggregations functions, such as Sum, Min, Max and Avg, for use in select queries (courtesy @sinisaos).


Added uvloop as an optional dependency, installed via pip install piccolo[uvloop] (courtesy @aminalaee). uvloop is a faster implementation of the asyncio event loop found in Python’s standard library. When uvloop is installed, Piccolo will use it to increase the performance of the Piccolo CLI, and web servers such as Uvicorn will use it to increase the performance of your ASGI app.


Added eq and ne methods to the Boolean column, which can be used if linters complain about using SomeTable.some_column == True.


  • Changed the migration IDs, so the timestamp now includes microseconds. This is to make clashing migration IDs much less likely.
  • Added a lot of end-to-end tests for migrations, which revealed some bugs in Column defaults.


A bug fix for migrations. See issue 123 for more information.


Lots of improvements to JSON and JSONB columns. Piccolo will now automatically convert between Python types and JSON strings. For example, with this schema:

class RecordingStudio(Table):
    name = Varchar()
    facilities = JSON()

We can now do the following:

    name="Abbey Road",
    facilities={'mixing_desk': True}  # Will automatically be converted to a JSON string

Similarly, when fetching data from a JSON column, Piccolo can now automatically deserialise it.

[{'id': 1, 'name': 'Abbey Road', 'facilities': {'mixing_desk': True}]

>>> studio = RecordingStudio.objects().first().output(load_json=True).run_sync()
>>> studio.facilities
{'mixing_desk': True}


Added the create_table_class function, which can be used to create Table subclasses at runtime. This was required to fix an existing bug, which was effecting migrations (see issue 111 for more details).


  • An error is now raised if a user tries to create a Piccolo app using piccolo app new with the same name as a builtin Python module, as it will cause strange bugs.
  • Fixing a strange bug where using an expression such as in a query would cause an error. It only happened if multiple joins were involved, and the last column in the chain was id.
  • where clauses can now accept Table instances. For example: await == some_manager).run(), instead of having to explicity reference the id.


Fixing a bug with serialising Enum instances in migrations. For example: Varchar(


Fix missing imports in FastAPI and Starlette app templates.


  • Added a freeze method to Query.
  • Added BlackSheep as an option to piccolo asgi new.


Added choices option to Column.


  • Added piccolo user change_permissions command.
  • Added aliases for CLI commands.


Changes to the BaseUser table - added a superuser, and last_login column. These are required for upgrades to Piccolo Admin.

If you’re using migrations, then running piccolo migrations forwards all should add these new columns for you.

If not using migrations, the BaseUser table can be upgraded using the following DDL statements:

ALTER TABLE piccolo_user ADD COLUMN "last_login" TIMESTAMP DEFAULT null


  • Fixed a bug when multiple tables inherit from the same mixin (thanks to @brnosouza).
  • Added a log_queries option to PostgresEngine, which is useful during debugging.
  • Added the inflection library for converting Table class names to database table names. Previously, a class called TableA would wrongly have a table called table instead of table_a.
  • Fixed a bug with SerialisedBuiltin.__hash__ not returning a number, which could break migrations (thanks to @sinisaos).


Improved Array column serialisation - needed to fix auto migrations.


Added support for filtering Array columns.


Add the Array column type as a top level import in piccolo.columns.


  • Refactored forwards and backwards commands for migrations, to make them easier to run programatically.
  • Added a simple Array column type.
  • table_finder now works if just a string is passed in, instead of having to pass in an array of strings.


Catching database connection exceptions when starting the default ASGI app created with piccolo asgi new - these errors exist if the Postgres database hasn’t been created yet.


Added a help_text option to the Table metaclass. This is used in Piccolo Admin to show tooltips.


Added a help_text option to the Column constructor. This is used in Piccolo Admin to show tooltips.


  • Exposing index_type in the Column constructor.
  • Fixing a typo with start_connection_pool` and ``close_connection_pool - thanks to paolodina for finding this.
  • Fixing a typo in the PostgresEngine docs - courtesy of paolodina.


Fixing a bug with SchemaSnapshot if column types were changed in migrations - the snapshot didn’t reflect the changes.


  • Migrations now directly import Column classes - this allows users to create custom Column subclasses. Migrations previously only worked with the builtin column types.
  • Migrations now detect if the column type has changed, and will try and convert it automatically.


The Postgres extensions that PostgresEngine tries to enable at startup can now be configured.


  • Fixed a bug with MyTable.column != None
  • Added is_null and is_not_null methods, to avoid linting issues when comparing with None.


  • Added WhereRaw, so raw SQL can be used in where clauses.
  • piccolo shell run now uses syntax highlighting - courtesy of Fingel.


Reordering the dependencies in requirements.txt when using piccolo asgi new as the latest FastAPI and Starlette versions are incompatible.


Added Timestamptz column type, for storing datetimes which are timezone aware.


  • Fixed a bug with creating a ForeignKey column with references="self" in auto migrations.
  • Changed migration file naming, so there are no characters in there which are unsupported on Windows.


Changing the status code when creating a migration, and no changes were detected. It now returns a status code of 0, so it doesn’t fail build scripts.


Added Bytea / Blob column type.


Fixing a bug with migrations which drop column defaults.


  • Fixing a bug where re-running Table.create(if_not_exists=True) would fail if it contained columns with indexes.
  • Raising a ValueError if a relative path is provided to ForeignKey references. For example, .tables.Manager. The paths must be absolute for now.


Fixing a bug with Boolean column defaults, caused by the Table metaclass not being explicit enough when checking falsy values.


  • The ForeignKey references argument can now be specified using a string, or a LazyTableReference instance, rather than just a Table subclass. This allows a Table to be specified which is in a Piccolo app, or Python module. The Table is only loaded after imports have completed, which prevents circular import issues.
  • Faster column copying, which is important when specifying joins, e.g. await
  • Fixed a bug with migrations and foreign key contraints.


Modified the exit codes for the forwards and backwards commands when no migrations are left to run / reverse. Otherwise build scripts may fail.


  • Improved the method signature of the output query clause (explicitly added args, instead of using **kwargs).
  • Fixed a bug where output(as_list=True) would fail if no rows were found.
  • Made piccolo migrations forwards command output more legible.
  • Improved renamed table detection in migrations.
  • Added the piccolo migrations clean command for removing orphaned rows from the migrations table.
  • Fixed a bug where get_migration_managers wasn’t inclusive.
  • Raising a ValueError if is_in or not_in query clauses are passed an empty list.
  • Changed the migration commands to be top level async.
  • Combined print and sys.exit statements.


  • Added missing type annotation for run_sync.
  • Updating type annotations for column default values - allowing callables.
  • Replaced instances of with run_sync.
  • Tidied up aiosqlite imports.


  • Added JSON and JSONB column types, and the arrow function for JSONB.
  • Fixed a bug with the distinct clause.
  • Added as_alias, so select queries can override column names in the response (i.e. SELECT foo AS bar from baz).
  • Refactored JSON encoding into a separate utils file.


  • Removed old iPython version recommendation in the piccolo shell run and piccolo playground run, and enabled top level await.
  • Fixing outstanding mypy warnings.
  • Added optional requirements for the playground to


  • Added piccolo sql_shell run command, which launches the psql or sqlite3 shell, using the connection parameters defined in This is convenient when you want to run raw SQL on your database.
  • run_sync now handles more edge cases, for example if there’s already an event loop in the current thread.
  • Removed asgiref dependency.


  • Queries can be directly awaited - await, as an alternative to using the run method await
  • The piccolo asgi new command now accepts a name argument, which is used to populate the default database name within the template.


  • Centralised code for importing Piccolo apps and tables - laying the foundation for fixtures.
  • Made orjson an optional dependency, installable using pip install piccolo[orjson].
  • Improved version number parsing in Postgres.


Fixing a bug with dropping tables in auto migrations.


Added Interval column type.


  • Added allowed_hosts to create_admin in ASGI template.
  • Fixing bug with default root argument in some piccolo commands.


  • Fixed bug with SchemaSnapshot when dropping columns.
  • Added custom __repr__ method to Table.


Added piccolo shell run command for running adhoc queries using Piccolo.


  • Fixing bug with auto migrations when dropping columns.
  • Added a root argument to piccolo asgi new, piccolo app new and piccolo project new commands, to override where the files are placed.


Added support for group_by and Count for aggregate queries.


Added required argument to Column. This allows the user to indicate which fields must be provided by the user. Other tools can use this value when generating forms and serialisers.


  • Fixing a typo in TimestampCustom arguments.
  • Fixing bug in TimestampCustom SQL representation.
  • Added more extensive deserialisation for migrations.


  • Improved PostgresEngine docstring.
  • Resolving rename migrations before adding columns.
  • Fixed bug serialising TimestampCustom.
  • Fixed bug with altering column defaults to be non-static values.
  • Removed response_handler from Alter query.


Using orjson for JSON serialisation when using the output(as_json=True) clause. It supports more Python types than ujson.


Improved piccolo user create command - defaults the username to the current system user.


Fixing bug when sorting extra_definitions in auto migrations.


  • Fixed typos.
  • Bumped requirements.


  • Added Date and Time columns.
  • Improved support for column default values.
  • Auto migrations can now serialise more Python types.
  • Added Table.indexes method for listing table indexes.
  • Auto migrations can handle adding / removing indexes.
  • Improved ASGI template for FastAPI.


ASGI template fix.


  • Improved UUID columns in SQLite - prepending ‘uuid:’ to the stored value to make the type more explicit for the engine.
  • Removed SQLite as an option for piccolo asgi new until auto migrations are supported.


Added support for FastAPI to piccolo asgi new.


Fixed bug in BaseMigrationManager.get_migration_modules - wasn’t excluding non-Python files well enough.


  • Stopped piccolo migrations new from creating a file - was legacy.
  • Added a README file to the piccolo_migrations folder in the ASGI template.


Fixed __pycache__ bug when using piccolo asgi new.


  • Showing a warning if trying auto migrations with SQLite.
  • Added a command for creating a new ASGI app - piccolo asgi new.
  • Added a meta app for printing out the Piccolo version - piccolo meta version.
  • Added example queries to the playground.


  • Added table_finder, for use in AppConfig.
  • Added support for concatenating strings using an update query.
  • Added more tables to the playground, with more column types.
  • Improved consistency between SQLite and Postgres with UUID columns, Integer columns, and exists queries.


Added Numeric and Real column types.


Fixing a bug where Postgres versions without a patch number couldn’t be parsed.


Improving release script.


Sorting out packaging issue - old files were appearing in release.


Auto migrations can now run backwards.


Fixing some typos with Table imports. Showing a traceback when piccolo_conf can’t be found by engine_finder.


Adding missing jinja templates to


Fixing a bug when using piccolo project new in a new project.


Fixing bug with enum default values.


Using targ for the CLI. Refactored some core code into apps.


Suppressing exceptions when trying to find the Postgres version, to avoid an ImportError when importing


.first() bug fix.


Auto migration fixes, and .first() method now returns None if no match is found.


Added support for auto migrations.


Can use operators in update queries, and fixing ‘new’ migration command.


Fixing release issue.


Improved transaction support - can now use a context manager. Added Secret, BigInt and SmallInt column types. Foreign keys can now reference the parent table.


Fixing bug when joining across several tables. Can pass values directly into the Table.update method. Added if_not_exists option when creating a table.


Column sequencing matches the definition order.


Supporting ON DELETE and ON UPDATE for foreign keys. Recording reverse foreign key relationships.


Made response_handler async. Made it easier to rename columns.


Bug fixes and dependency updates.


Adding missing file.


Changed migration import paths.


Added remove_db_file method to SQLiteEngine - makes testing easier.


Renamed create to create_table, and can register commands via piccolo_conf.


Adding missing files.


Moved BaseUser. Migration refactor.


Moved drop table under Alter - to help prevent accidental drops.


Added batch support.


Refactored the Table Metaclass - much simpler now. Scoped more of the attributes on Column to avoid name clashes. Added engine_finder to make database configuration easier.


SQLite is now returning datetime objects for timestamp fields.


Refactored to improve code completion, along with bug fixes.


Allowing Update queries in SQLite


Falling back to LIKE instead of ILIKE for SQLite


Renamed User to BaseUser.


Added ilike.


Added value types to columns.


Default values infer the engine type.


Update click version.


Tweaked API to support more auto completion. Join support in where clause. Basic SQLite support - mostly for playground.


Using QueryString internally to represent queries, instead of raw strings, to harden against SQL injection.


Allowing joins across multiple tables.


Added playground.