redbot-orm 1.0.8

Postgres and SQLite extensions for Red-DiscordBot

redbot-orm

Database ORM integration for Red-DiscordBot cogs using Piccolo ORM.

redbot-orm streamlines database setup, migrations, and lifecycle management for both PostgreSQL and SQLite backends, so cog authors can focus on their domain logic instead of plumbing.

Note: The helpers rely on Red-DiscordBot's data manager and event hooks; they aren't intended as a drop-in replacement for arbitrary discord.py bots.

Table of Contents

• Features
• Installation
• Quick Start
• Usage Examples
• Configuration
• Development and Migrations
• API Reference
• Troubleshooting
• Running Tests

Features

• One-line database registration tailored for Red-DiscordBot cogs
• Automatic database creation, table binding, and Piccolo migrations
• Unified API for PostgreSQL and SQLite
• Safe directory handling and UNC path checks for Windows compatibility
• Guided scaffolding command to bootstrap Piccolo project files

Installation

pip install redbot-orm

Quick Start

1. Scaffold your cog — run inside your cog folder to generate the Piccolo project structure:

   python -m redbot_orm scaffold .
   # or equivalently:
   redbot-orm scaffold .
   

   This creates the db/ folder, Piccolo config files, migrations directory, and a starter build.py script. The generated piccolo_conf.py automatically switches between SQLite and Postgres depending on which environment variables are present.

2. Define your tables in db/tables.py:

   from piccolo.columns import BigInt, Text, UUID
   from piccolo.table import Table
   
   class MyTable(Table):
       id = UUID(primary_key=True)
       guild_id = BigInt(unique=True)
       name = Text()
   

3. Register in your cog's cog_load to automatically create the database, bind tables, and run migrations:

   from redbot_orm import register_cog
   from .db.tables import MyTable
   
   async def cog_load(self) -> None:
       self.db = await register_cog(self, [MyTable])
   

4. Create migrations when you change your tables by running build.py or using the helper functions.

Expected cog layout:

my_cog/
├── db/
│   ├── __init__.py
│   ├── migrations/
│   ├── piccolo_app.py
│   ├── piccolo_conf.py
│   └── tables.py
├── __init__.py
├── cog.py
└── build.py

If you're targeting Postgres, create a .env file in your cog's root with the required POSTGRES_* variables. Be sure to add .env to your .gitignore so credentials never end up in version control.

Usage Examples

SQLite Example

SQLite is the simplest option, no external database server required. Data is stored in the cog's data directory.

from redbot.core import commands
from redbot.core.bot import Red
from piccolo.engine.sqlite import SQLiteEngine

from redbot_orm import register_cog

from .db.tables import MyTable


class SQLiteCog(commands.Cog):
    def __init__(self, bot: Red) -> None:
        self.bot = bot
        self.db: SQLiteEngine | None = None

    async def cog_load(self) -> None:
        self.db = await register_cog(self, [MyTable])

    async def cog_unload(self) -> None:
        # SQLite is file-based; no connection pool to close.
        pass

PostgreSQL Example

PostgreSQL offers better performance for high-traffic bots and advanced features like JSON columns and full-text search.

from redbot.core import commands
from redbot.core.bot import Red
from piccolo.engine.postgres import PostgresEngine

from redbot_orm import register_cog

from .db.tables import MyTable


class PostgresCog(commands.Cog):
    def __init__(self, bot: Red) -> None:
        self.bot = bot
        self.db: PostgresEngine | None = None

    async def cog_load(self) -> None:
        # Option A: fetch credentials from Red's shared API tokens (recommended)
        config = await self.bot.get_shared_api_tokens("postgres")

        # Option B: fallback to hardcoded defaults for local development
        if not config:
            config = {
                "database": "postgres",
                "host": "localhost",
                "port": 5432,
                "user": "postgres",
                "password": "postgres",
            }

        self.db = await register_cog(
            self,
            [MyTable],
            config=config,
            max_size=10,
            min_size=1,
            extensions=("uuid-ossp",),
        )

    async def cog_unload(self) -> None:
        if self.db:
            await self.db.close_connection_pool()

Migration Helpers

The unified API exposes helper functions that automatically choose the correct backend based on whether a Postgres config is supplied:

Function	Purpose
create_migrations()	Generates an auto migration
run_migrations()	Applies all pending forward migrations
reverse_migration()	Rolls back to a specific timestamp
diagnose_issues()	Runs Piccolo diagnostics

from redbot_orm import create_migrations, run_migrations, diagnose_issues

# SQLite (no config)
await run_migrations(cog_instance)

# PostgreSQL (with config)
await run_migrations(cog_instance, config=postgres_config)

Configuration

PostgreSQL Credentials

You can store Postgres credentials in Red's shared API tokens:

[p]set api postgres database,mydb host,localhost port,5432 user,postgres password,secret

Or as a dictionary in your code:

config = {
    "database": "mydb",
    "host": "localhost",
    "port": 5432,  # Can be int or str
    "user": "postgres",
    "password": "secret",
}

Environment Variables

For local development with the build.py script, create an .env file:

POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_USER=postgres
POSTGRES_PASSWORD=postgres
POSTGRES_DATABASE=mydb

Warning: Add .env to your .gitignore to avoid committing credentials.

Development and Migrations

The scaffolded build.py script provides an interactive way to create migrations during development:

python build.py
# Enter a description for the migration: added user preferences table

This runs create_migrations() under the hood. For SQLite, leave the CONFIG variable as None; for Postgres, populate it from environment variables.

API Reference

register_cog

async def register_cog(
    cog_or_path,
    tables,
    *,
    config: dict[str, Any] | None = None,
    trace: bool = False,
    skip_migrations: bool = False,
    max_size: int = 20,
    min_size: int = 1,
    extensions: Sequence[str] = ("uuid-ossp",),
) -> PostgresEngine | SQLiteEngine

Parameter	Description
cog_or_path	The cog instance (self) or a Path to the cog directory
tables	List of Piccolo Table classes to bind to the engine
config	Postgres connection dict; omit or pass None for SQLite
trace	Enable --trace flag for migration commands
skip_migrations	Skip automatic migration execution
max_size / min_size	Postgres connection pool sizing (ignored for SQLite)
extensions	Postgres extensions to enable, e.g. ("uuid-ossp",)

Returns: A fully initialized PostgresEngine or SQLiteEngine with all tables bound.

Migration Functions

Function	Signature
create_migrations	(cog_or_path, *, config=None, trace=False, description=None, is_shell=True) -> str
run_migrations	(cog_or_path, *, config=None, trace=False) -> str
reverse_migration	(cog_or_path, *, timestamp, config=None, trace=False) -> str
diagnose_issues	(cog_or_path, *, config=None) -> str

All functions accept config to switch between Postgres and SQLite. Set is_shell=False in CI/tests to capture output instead of streaming to stdout.

Troubleshooting

Error	Solution
ValueError: Postgres options can only be used when a config is provided.	Provide Postgres credentials via config, or remove Postgres-only kwargs (max_size, min_size, extensions) when using SQLite.
FileNotFoundError: Piccolo package not found!	Install Piccolo in your environment: pip install piccolo
DirectoryError: Missing db/piccolo_app.py	Run redbot-orm scaffold . in your cog directory first
Migration tracebacks	Re-run with trace=True and call diagnose_issues() for guidance
Tables not appearing	Ensure tables are: (1) defined in db/tables.py, (2) listed in table_finder() in piccolo_app.py, and (3) passed to register_cog()

Running Tests (Optional)

Integration tests for both backends are included:

# Activate your virtual environment first
pytest                  # Run all tests
pytest tests_sqlite/    # SQLite only (no setup required)
pytest tests_postgres/  # Postgres only (requires POSTGRES_* env vars)

Additional Notes

• Database naming: Each cog gets its own database named after the cog's folder (lowercase).
• SQLite location: Databases are stored in the cog's data directory via Red's cog_data_path.
• Postgres auto-creation: Databases are created automatically; ensure your user has CREATE DATABASE privileges.
• Cross-backend compatibility: Prefer column types supported by both backends (UUID, Text, BigInt, Timestamptz). Gate backend-specific columns behind conditional logic if needed.
• Connection cleanup: Always close the Postgres connection pool in cog_unload using await self.db.close_connection_pool().