Provide database connection from outside

[Strernd]

We added our models to their own repository, because it will be used in multiple places (lambda functions).

The database has to be provided to the Meta Model, which we do with a BaseMetaModel. Unfortunately it is not easily feasible to provide the DB connection via an environment variable.

We instead want to instantiate the database in our lambda functions, using calls to Secretes Manager to even get the DB password. The models are imported also in the lambda handler file, so there is no db connection available for them.

So I'm looking for a way to provide our instantiated database to the models.

Example folder structure:

# model-repository
/alembic
/model-repository
  models.py 
  
# some-lambda
/handler
  /some
    some_lambda.py
...

Rough structure of models.py

class BaseMeta(ormar.ModelMeta):
  database = None
  metadata = metadata
    
class Example(ormar.Model):

  class Meta(BaseMeta):
    tablename = 'examples'

  id: uuid.UUID = ormar.UUID(primary_key=True, uuid_format='string')
  name: str = ormar.String(max_length=300)

Rough structure of some_lambda.py

app = FastAPI()
database = create_db_with_secrets_manager()

# How to get database into the models?

@app.get(
    '/examples',
    response_model=Page[models.Example]
)
async def list_examples():
    return await paginate(models.Example.objects)

@collerek I would expect such an example in the docs, because in bigger software systems it's not always the case that models and API will live together in peace and harmony in the same repository.

[collerek]

You want to set one database for current launch of the lambda or each lambda can have their own database and they can run in parallel in the same execution context (meaning, can you change the database on those concrete models or you need to copy of them in each lambda with different databases)?

[Strernd]

The underlying database is an Aurora Serverless PSQL. Instances of the lambda (and other lambdas) need to access that one database instance.

[collerek]

Since you already follow best practice with BaseMeta you can easily switch to the db on the fly, a dummy example is like follows:

import sqlite3
import uuid
from typing import Any, Type

import databases
import pytest
import sqlalchemy

import ormar
from tests.settings import DATABASE_URL

metadata = sqlalchemy.MetaData()


def create_db_with_secrets_manager():
    # logic to grab database from somewhere
    return databases.Database(DATABASE_URL)


class BaseMeta(ormar.ModelMeta):
    # for now a valid databases db instance is required but it can be switched on the fly
    database = databases.Database("sqlite:///not_used.db")
    metadata = metadata


class Example(ormar.Model):
    class Meta(BaseMeta):
        tablename = "examples"

    id: uuid.UUID = ormar.UUID(primary_key=True, uuid_format="string")
    name: str = ormar.String(max_length=300)


# following best practice with BaseMeta simply reassign all subclasses like this
# to assign your database to all models 

# YOU CAN IGNORE THIS IF YOU SUBSTITUTE WITH SOMETHING OTHER THAN SQLITE
# in that case skip to actual db change
# Note! It will assign default connection pool for sqlite, 
# while ormar builds a custom one with support for foreign keys
# which are turned off by default -> so do not re-assign to different sqlite db.
# or provide your own connection pool to the database like this
class Connection(sqlite3.Connection):
    def __init__(self, *args: Any, **kwargs: Any) -> None:  # pragma: no cover
        super().__init__(*args, **kwargs)
        self.execute("PRAGMA foreign_keys=1;")


def substitute_sqlite_pool(meta: Type[ormar.ModelMeta]):
    backend = meta.database._backend
    if (
        backend._dialect.name == "sqlite" and "factory" not in backend._options
    ):  # pragma: no cover
        backend._options["factory"] = Connection
        old_pool = backend._pool
        backend._pool = old_pool.__class__(backend._database_url, **backend._options)


# actual db change
database = create_db_with_secrets_manager()
for meta in BaseMeta.__subclasses__():
    meta.database = database
    # AGAIN - only for sqlite - turn on foreign keys
    substitute_sqlite_pool(meta)


@pytest.mark.asyncio
async def test_database_is_assigned():
    async with database:
        used_url = str(Example.Meta.database.url)
        assert used_url == "sqlite:///test.db"

        # for tests only - use migrations in prod
        engine = sqlalchemy.create_engine(used_url)
        metadata.drop_all(engine)
        metadata.create_all(engine)

        example_id = uuid.uuid4()
        await Example(id=example_id, name="test").save()
        check = await Example.objects.get()
        assert check.name == "test"

        # explicit sync query to check the proper database is used
        connection = engine.connect()
        assert str(engine.url) == used_url
        result = connection.execute("select * from examples")
        assert list(result)[0] == (str(example_id), "test")

        # for tests only
        metadata.drop_all(engine)

[Strernd]

I guess this would work, but I would prefer an explicit interface for setting the database from outside. Maybe this would be a good addition to the docs under "Providing database connection externally" or in general start a section "Making your models reusable in multiple projects and creating an own package for your models."

[collerek]

That's a nice idea but ormar does not keep a registry of all models you declare.
Even if it would you can use ormar with different databases at the same time, so you end up changing the database per model class and not for all models.

If you want to change it per model it's as simple as:

from repository import YourModel
YourModel.Meta.database = new_database()

Or how would you like to do this?

[Strernd]

In the end we decided to create the database connection in the model-repository and pass all requirements by environment variables. (SecretsManager Secret ARN, DB cluster endpoint, Db table name). This is sufficient but a little bit ugly because the library now implicitly relies on a certain environment.

Ideally we would see a nice interface for setting the database from outside provided by Ormar.