When using Pony ORM or sqlalchemy, where to create Database object?

Question

I began playing a bit with Object Relational Mappers (Pony ORM specifically).

In Pony, all entity definitions inherit from the db.Entity class. However in order to do that of course the db object needs to be created first somewhere. (db.Entity is somewhat similar to the declarative base in sqlalchemy, so I believe my question below is also similar valid for sqlalchemy)

All the examples I have seen in the Pony ORM docs present the examples inline, where the Database object db is simply declared in the interpreter prompt before the entities are declared.

This leaves for me the question: Where should I create my db object in a "real" project?.

Especially consider the case where I want to keep my entity definitions separate from where I actually use these entities (say I only want to build a nice ORM wrapper package to access a database, which then should be used in multiple different other projects). Then I probably want the users to provide the own db object that is configured to their needs in order to access the DB.

Some example code:

Say I have a Database that stores persons and addresses and my package my_orm should provide an ORM for the database, which then will be used in app.py:

my_orm/init.py

from my_orm.person import Person
from my_orm.address import Address

my_orm/person.py:

from pony.orm import Required

class Person(db.Entity): # Where should `db` be defined?
    name = Required(str)
    age = Required(int)

my_orm/address.py:

from pony.orm import Required

class Address(db.Entity): # Where should `db` be defined?. Must be the same `db` object as in person.py
    street_name = Required(str)
    zip_code = Required(int)

app.py

from pony.orm import Database
db = Database()
import my_orm

Besides already looking ugly because it mixes the imports with the creation of the db, this will also throw the error NameError: name 'db' is not defined. So what should I do instead?

Answer 1

You can use metaclasses for defining entities.

file 1:

class LazyEntityMeta(type):
    def __new__(mcs, name, bases, attrs):
        entity = mcs._entities[name] = LazyEntity(bases, attrs)
        return entity

    @classmethod
    def attach(mcs, db):
        for name, lazy in mcs._entities.items():
            lazy.entity = type(name, lazy.bases + (db.Entity,), attrs)

    _entities = {}

class LazyEntity:
    def __init__(self, bases, attrs):
        self.bases = bases
        self.attrs = attrs

file 2:

class A(metaclass=LazyEntityMeta):
    id = PrimaryKey(int, auto=True)

file 3:

db = Database()

LazyEntityMeta.attach(db)
db.bind('sqlite', ':memory:')
db.generate_mapping(create_tables=True)

with db_session:
    a1 = db.A()
    a2 = A.entity()

Answer 2

There are several ways to organize the code.

1. Place all entities in a single file

This is a convenient way for simple and medium-sized projects. It is the simplest and probably you can start this way. You can define Database object in this file right before entity definitions:

models.py

from pony.orm import Database, Required, Optional

db = orm.Database()

class Person(db.Entity):
    name = Required(str)
    addresses = Set('Address') # or Set(lambda: Address)

class Address(db.Entity):
    street_name = Required(str)
    persons = Set('Person')

main.py

from models import db, Person, Address
from settings import db_params
from pony.orm import db_session, select

db.bind(**db_params)
db.generate_mapping(create_tables=True)

with db_session:
    persons = select(p for p in Person if p.age > 20)[:]

This way is simple and suitable for medium-sized projects, you can start with it

2. Define entities inside a function

This may be useful if you want to connect to several different instances of Database in the same program

models.py

from pony.orm import Required, Optional

def define_entities(db):
    class Person(db.Entity):
        name = Required(str)
        addresses = Set('Address')

    class Address(db.Entity):
        street_name = Required(str)
        persons = Set('Person')

main.py

from models import define_entities
from settings import db_params
from pony.orm import Database, db_session, select

db = Database()
define_entities(db)
db.bind(**db_params)
db.generate_mapping(create_tables=True)

with db_session:
    persons = select(p for p in db.Person if p.age > 20)[:]

Note that it is possible to access entity classes as properties of a Database object: db.Person. It may be convenient, as it is not necessary to import the Person entity -- it is enough to have access to the db object. The drawback is that IDEs like PyCharm don't understand what db.Person is and don't provide suggestions for code completion of attributes like Person.name.

It is also possible to split entiti definitions between several functions which are imported from different files:

models1.py

from pony.orm import Required, Optional

def define_entities(db):
    class Person(db.Entity):
        name = Required(str)
        addresses = Set('Address') # or: Set(lambda: db.Address)

models2.py

from pony.orm import Required, Optional

def define_entities(db):
    class Address(db.Entity):
        street_name = Required(str)
        persons = Set('Person')  # or: Set(lambda: db.Person)

main.py

import models1, models2
from settings import db_params
from pony.orm import Database, db_session, select

db = Database()
models1.define_entities(db)
models2.define_entities(db)
db.bind(**db_params)
db.generate_mapping(create_tables=True)

with db_session:
    persons = select(p for p in db.Person if p.age > 20)[:]

This is probably overkill, but can sometimes be used for a pluggable architecture, when exact set of entities is defined dynamically after the application start.

3. Define entities in separate files (not inside the function)

You can follow the schema that I described in the corresponding answer: PonyORM - multiple model files