Choosing the Right pgdbm Pattern

Overview

Core Principle: Choose pattern based on deployment context and reusability needs.

pgdbm supports three main patterns. This skill helps you choose the right one in <30 seconds without reading multiple docs.

Quick Decision Tree

What are you building?
│
├─ Reusable library/package for PyPI?
│  └─ → DUAL-MODE LIBRARY pattern
│     • Accept connection_string OR db_manager
│     • Works standalone AND embedded
│
├─ Single application with multiple services/modules?
│  └─ → SHARED POOL pattern
│     • ONE pool, many schema-isolated managers
│     • Most efficient for production
│
└─ Simple standalone service/microservice?
   └─ → STANDALONE pattern
      • AsyncDatabaseManager(DatabaseConfig(...))
      • Simplest setup

Pattern Selection Table

| If you have... | Use this pattern | Key indicator | |----------------|------------------|---------------| | Library published to PyPI | Dual-Mode | Code needs to work in someone else's app | | FastAPI monolith with routers | Shared Pool | Multiple services, same process | | Multiple services, same app | Shared Pool | Need connection efficiency | | Background worker (separate process) | Standalone | Different OS process | | Simple microservice | Standalone | One service, own database | | Multi-tenant SaaS | Shared Pool | Many tenants, schema isolation |

Detailed Decision Criteria

Use DUAL-MODE LIBRARY When:

Triggers:

• You're publishing to PyPI
• You're building internal shared library
• Code will be used by other developers
• Library might be used alongside other pgdbm libraries

Key characteristics:

• Unknown deployment context
• Must work standalone OR embedded
• Always runs own migrations
• Schema-agnostic via {{tables.}}

Red flags you need this:

• [ ] You're writing import mylib in your README
• [ ] Someone else's app will import your code
• [ ] You don't control the database configuration

Example minimal setup:

class MyLibrary:
    def __init__(
        self,
        connection_string: Optional[str] = None,
        db_manager: Optional[AsyncDatabaseManager] = None,
    ):
        if not connection_string and not db_manager:
            raise ValueError("Provide one or the other")
        self._external_db = db_manager is not None
        self.db = db_manager
        self._connection_string = connection_string

    async def initialize(self):
        if not self._external_db:
            config = DatabaseConfig(connection_string=self._connection_string)
            self.db = AsyncDatabaseManager(config)
            await self.db.connect()

        # ALWAYS run migrations
        migrations = AsyncMigrationManager(
            self.db, "migrations", module_name="mylib"
        )
        await migrations.apply_pending_migrations()

For complete implementation: See pgdbm:dual-mode-library skill

Use SHARED POOL When:

Triggers:

• Multiple services in same Python process
• FastAPI app with multiple routers
• Monolith with logical service separation
• Multi-tenant SaaS application

Key characteristics:

• Services share database connection pool
• Each service gets own schema
• Connection efficiency critical
• All services in same application

Red flags you need this:

• [ ] You're creating multiple AsyncDatabaseManager(DatabaseConfig(...)) in same app
• [ ] You're hitting PostgreSQL connection limits
• [ ] You have >2 routers/services needing database

Example minimal setup:

# In lifespan
config = DatabaseConfig(connection_string="postgresql://...")
shared_pool = await AsyncDatabaseManager.create_shared_pool(config)

# Each service gets schema-isolated manager
users_db = AsyncDatabaseManager(pool=shared_pool, schema="users")
orders_db = AsyncDatabaseManager(pool=shared_pool, schema="orders")

# Run migrations for each
for db, path, name in [(users_db, "migrations/users", "users"), ...]:
    migrations = AsyncMigrationManager(db, path, name)
    await migrations.apply_pending_migrations()

For complete implementation: See pgdbm:shared-pool-pattern skill

Use STANDALONE When:

Triggers:

• Single service, dedicated database
• Background worker (separate process)
• Simple microservice
• Development/testing
• Service can't share connections

Key characteristics:

• Creates own connection pool
• Controls full database lifecycle
• Simplest pattern
• Most straightforward

Red flags you need this:

• [ ] Separate OS process (can't share pool anyway)
• [ ] Only one logical service
• [ ] Development environment
• [ ] Learning pgdbm

Example minimal setup:

config = DatabaseConfig(connection_string="postgresql://...")
db = AsyncDatabaseManager(config)
await db.connect()

migrations = AsyncMigrationManager(db, "migrations", module_name="myservice")
await migrations.apply_pending_migrations()

# Use db
user_id = await db.fetch_value(
    "INSERT INTO {{tables.users}} (email) VALUES ($1) RETURNING id",
    email
)

await db.disconnect()

For complete implementation: See pgdbm:standalone-service skill

Wrong Pattern Red Flags

🚫 You Chose WRONG Pattern If:

Using Standalone but should use Shared Pool:

• [ ] You create multiple AsyncDatabaseManager(DatabaseConfig(...)) to same database
• [ ] You see warning: "Creating another connection pool to..."
• [ ] You're hitting PostgreSQL max_connections limit
• [ ] All services run in same Python process

Using Shared Pool but should use Dual-Mode:

• [ ] Your code will be imported by other apps
• [ ] You're publishing to PyPI
• [ ] Users need to provide their own database

Using Dual-Mode but should use Standalone:

• [ ] You control entire deployment
• [ ] Code never used as library
• [ ] Adding unnecessary complexity

Common Ambiguous Cases

"Background worker, same database as main app"

Question to ask: Same process or different process?

• Same process (threads/asyncio tasks): Use Shared Pool
• Different process (separate Python process): Use Standalone
  • Each process creates own pool (can't share across processes)
  • Use schema isolation to prevent table conflicts
  • Worker uses schema="worker", main uses schema="main"

"Multiple microservices, containerized"

Answer: Each container = Standalone pattern

• Containers are separate processes
• Can't share pools across containers
• Use schema isolation if sharing same database
• Each service: AsyncDatabaseManager(DatabaseConfig(...))

"Library used internally, not published"

Answer: Still use Dual-Mode if used by multiple apps

• "Internal" doesn't mean "standalone"
• If imported by different projects, use Dual-Mode
• If only used in one app, can use Shared Pool

Pattern Comparison

| Aspect | Standalone | Dual-Mode | Shared Pool | |--------|-----------|-----------|-------------| | Complexity | Low | Medium | Medium | | Flexibility | Low | High | Medium | | Connection Efficiency | Low | Varies | High | | Use Case | Simple services | Reusable libraries | Multi-service apps | | Pool Creation | Creates own | Conditional | Uses provided | | Migration Management | Owns | Always runs own | Each service runs own | | Best For | Microservices, workers | PyPI packages | Monoliths, multi-tenant |

Decision Process Example

Scenario: "I'm building a FastAPI app with user authentication, blog posts, and comments"

Decision process:

1. Multiple services? Yes (auth, blog, comments)
2. Same Python process? Yes (all in FastAPI app)
3. Will be reused as library? No (application code)

Answer: Shared Pool Pattern

Why:

• Multiple services = need isolation
• Same process = can share pool
• Not reusable = don't need dual-mode flexibility

Quick Self-Check

Before implementing, ask:

1. Who creates the database manager?

   • Me = Standalone or Shared Pool
   • Could be me or someone else = Dual-Mode

2. How many services need database access?

   • 1 = Standalone (probably)
   • 2+ in same process = Shared Pool
   • 2+ in different processes = Standalone each

3. Will my code be imported by other projects?

   • Yes = Dual-Mode
   • No = Standalone or Shared Pool

Next Steps

Once you've chosen:

• Dual-Mode: See pgdbm:dual-mode-library for full implementation
• Shared Pool: See pgdbm:shared-pool-pattern for full implementation
• Standalone: See pgdbm:standalone-service for full implementation

All patterns use:

• {{tables.}} syntax (mandatory)
• Unique module_name in migrations (mandatory)
• Schema isolation for multi-service (recommended)

The Iron Rule

Whatever pattern you choose, NEVER:

• Create multiple pools to same database in same process
• Hardcode schema names in SQL
• Skip module_name in AsyncMigrationManager
• Switch db.schema at runtime

These violations break pgdbm's core assumptions.