Piccolo ORM Documentation

Piccolo is a fast, easy-to-learn ORM and query builder designed with asyncio in mind. Key features include:

• Sync and Async Support: Works with both synchronous and asynchronous code patterns.
• Built-in Playground: An interactive environment to help learn and test queries.
• Type Safety: Fully type-annotated for excellent IDE support (e.g., VSCode, iPython) and tab completion.
• Batteries Included: Comes with built-in support for User models, authentication, database migrations, and an admin interface.
• Web App Templates: Provides tools like piccolo asgi new to quickly scaffold ASGI web applications.

The Piccolo ecosystem includes specialized tools to extend its functionality:

Piccolo Admin

A powerful, modern admin interface and content management system built on top of Piccolo. It features a Vue.js frontend, REST backend, data filtering, multi-factor authentication, and media support (local/S3). It can be used standalone or integrated with ASGI frameworks.

Piccolo API

Utilities for exposing Piccolo tables as REST endpoints in ASGI applications (like FastAPI or Starlette). It includes middleware for:

• Session Auth
• Token Auth
• Rate Limiting
• CSRF
• Content Security Policy (CSP)

Piccolo provides a suite of string functions available in the piccolo.query.functions.string module. These functions can be used within query expressions to manipulate string data at the database level during select, update, or other query operations.

Available string functions include:

• Concat: Concatenates multiple strings.
• Length: Returns the length of a string.
• Lower: Converts a string to lowercase.
• Ltrim: Removes leading characters from a string.
• Replace: Replaces occurrences of a substring within a string.
• Reverse: Reverses the characters in a string.
• Rtrim: Removes trailing characters from a string.
• Upper: Converts a string to uppercase.

Piccolo provides primary support for PostgreSQL and also supports CockroachDB and SQLite.

• PostgreSQL: The primary database Piccolo was designed for. It is robust and feature-rich.
• CockroachDB: Supported and mostly compatible with Postgres. Note that some minor features may not be supported.
• SQLite: Supported for both tooling and production use. However, automatic database migrations are not supported for SQLite due to its limited support for ALTER TABLE DDL statements.

Piccolo API provides utilities for building APIs around your Piccolo tables. Key features include:

• Creating CRUD endpoints for ASGI applications based on Piccolo tables.
• Automatically generating Pydantic models from Piccolo tables.
• Deep integration with FastAPI.
• Built-in support for authentication and rate limiting.

For detailed usage, refer to the Piccolo API documentation.

Piccolo Admin allows you to create a powerful web-based GUI for managing your tables. It is built with Vue JS and features:

• Modern UI for data management.
• Powerful data filtering capabilities.
• CSV export functionality.

You can find the source code on GitHub and view the official documentation.

Piccolo provides a set of math functions available in the piccolo.query.functions.math module. These functions can be used within queries to perform mathematical operations on database columns at the database level.

Available functions include:

• Abs: Returns the absolute value of a number.
• Ceil: Returns the smallest integer greater than or equal to a number.
• Floor: Returns the largest integer less than or equal to a number.
• Round: Rounds a number to a specified precision.

Piccolo's API is designed to be as close to SQL as possible. Key differences from other ORMs include:

• Tables instead of Models: You define Table classes rather than generic models.
• where instead of filter: Instead of using a filter method common in other ORMs, Piccolo uses the where method to match SQL syntax.

The having clause is used in conjunction with the group_by clause to filter the results of a Select query based on aggregate values. Its syntax is identical to the where clause, allowing you to apply conditions to the groups created by group_by.

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
)

In Piccolo, a one-to-one relationship is implemented using a ForeignKey column with the unique=True constraint. This ensures that each record in the child table maps to exactly one record in the parent table.

from piccolo.table import Table
from piccolo.columns import ForeignKey, Varchar, Text

class Band(Table):
    name = Varchar()

class FanClub(Table):
    band = ForeignKey(Band, unique=True)  # The unique=True makes it one-to-one
    address = Text()

Piccolo handles joins automatically using a fluent interface that allows you to traverse foreign keys. When you access a foreign key attribute on a column, Piccolo performs a LEFT JOIN to include the related table's data.

• In Select Queries: You can select columns from related tables by traversing the foreign key.
• In Where Clauses: You can filter results based on columns in related tables by traversing the foreign key within the .where() method.