Install Piccolo ORM in Python

Piccolo ORM is a modern, lightweight, and async-first Object-Relational Mapper for Python. It works with PostgreSQL, SQLite, and CockroachDB. Unlike heavy frameworks like Django ORM, Piccolo is designed for speed and simplicity. It is perfect for FastAPI, Starlette, or any async Python project.

This guide will walk you through installing Piccolo ORM step by step. You will learn how to set up your environment, install the package, configure a database, and run your first query. No prior ORM experience is needed.

Prerequisites

Before you begin, ensure you have Python 3.8 or newer installed. You can check your version with the following command:

python --version

You also need a database. For this tutorial, we will use SQLite because it requires no server setup. If you prefer PostgreSQL, make sure it is running and accessible.

Step 1: Create a Virtual Environment

It is best practice to isolate your project dependencies. Create a virtual environment to keep your Piccolo installation clean.

# Create a virtual environment
python -m venv venv

# Activate it on Windows
venv\Scripts\activate

# Activate it on macOS/Linux
source venv/bin/activate

You will see (venv) in your terminal prompt. This means the environment is active.

Step 2: Install Piccolo ORM

Use pip to install Piccolo. The core package is called piccolo. If you plan to use PostgreSQL, also install piccolo[postgres]. For SQLite, the base package is enough.

# Install Piccolo with SQLite support (default)
pip install piccolo

# For PostgreSQL users
pip install piccolo[postgres]

This command installs Piccolo and its dependencies, including asyncpg for PostgreSQL or aiosqlite for SQLite. Wait for the installation to complete.

Step 3: Verify the Installation

Check if Piccolo is installed correctly by importing it in Python.

import piccolo
print(piccolo.__version__)

The output should show a version number like 1.5.0. If you see an error, ensure your virtual environment is active and the package was installed successfully.

Step 4: Configure a Database

Piccolo uses configuration files to connect to your database. Create a new file called piccolo_conf.py in your project root. This file tells Piccolo where your database is.

# piccolo_conf.py
from piccolo.conf.apps import AppConfig
from piccolo.engine import SQLiteEngine

DB = SQLiteEngine(path='my_database.sqlite')

# Register your app tables here (we will add them later)
APP_CONFIGS = [
    AppConfig(
        app_name="my_app",
        migrations_folder_path="migrations",
    ),
]

For PostgreSQL, use PostgresEngine instead:

from piccolo.engine import PostgresEngine

DB = PostgresEngine(
    config={
        'host': 'localhost',
        'port': 5432,
        'database': 'my_database',
        'user': 'my_user',
        'password': 'my_password',
    }
)

Important: Keep your database credentials secure. Do not hardcode them in production. Use environment variables instead.

Step 5: Create a Table (Model)

Piccolo uses Python classes to define database tables. Create a file named tables.py and define a simple Band model.

# tables.py
from piccolo.table import Table
from piccolo.columns import Varchar, Integer, Boolean

class Band(Table):
    name = Varchar(length=100)
    genre = Varchar(length=50)
    members = Integer(default=4)
    active = Boolean(default=True)

This table has four columns: name, genre, members, and active. The Table base class automatically adds an id primary key.

Step 6: Generate Migrations

Piccolo uses migrations to create and update your database schema. First, register your table in piccolo_conf.py by adding it to the APP_CONFIGS list.

# Update piccolo_conf.py
from piccolo.conf.apps import AppConfig
from piccolo.engine import SQLiteEngine
from tables import Band  # Import your table

DB = SQLiteEngine(path='my_database.sqlite')

APP_CONFIGS = [
    AppConfig(
        app_name="my_app",
        migrations_folder_path="migrations",
        table_classes=[Band],  # Add your tables here
    ),
]

Now run the Piccolo command-line tool to create the migration file.

# Generate a new migration
piccolo migrations new my_app --auto

This command scans your table definitions and creates a migration file inside the migrations folder. You should see output like Created migration: 2024_01_01t12_00_00.py.

Step 7: Apply Migrations

Run the migration to create the actual table in your database.

# Apply all pending migrations
piccolo migrations forward my_app

If everything works, you will see a success message. Your band table is now created in my_database.sqlite.

Step 8: Run Your First Query

Open a Python shell and write some data using Piccolo's async API.

import asyncio
from tables import Band

async def main():
    # Create a new band
    band = Band(name="The Beetles", genre="Rock", members=4)
    await band.save()

    # Fetch all bands
    bands = await Band.select()
    print(bands)

# Run the async function
asyncio.run(main())

The output will show the inserted row:

[{'id': 1, 'name': 'The Beetles', 'genre': 'Rock', 'members': 4, 'active': True}]

You have successfully installed Piccolo ORM and executed your first database operation.

Common Issues and Fixes

If you get a ModuleNotFoundError, ensure you are in the correct virtual environment. Also, verify that piccolo_conf.py is in the same directory where you run commands.

If migrations fail, check your database connection string. For SQLite, make sure the path is writable. For PostgreSQL, confirm the server is running and credentials are correct.

Conclusion

Installing Piccolo ORM is straightforward. You created a virtual environment, installed the package, configured a database, defined a table, generated migrations, and ran your first query. Piccolo's async nature makes it ideal for modern Python web applications.

To explore more, check the official Piccolo documentation for advanced features like joins, aggregations, and custom queries. With Piccolo, you can build fast, clean, and scalable database layers in Python.