How do I effectively share the database engine between different instances of a DB class?

Question

Preamble: Not sure if this kind of question belongs to stackoverflow or Software Engineering, please tell me if I'm at the wrong place.

The issue

I am working on a DB-wrapper for a CLI-app. For using that wrapper (a class), I want to instantiate it only once for each table I want to map. The version I am using at the moment however has the issue, that multiple engines get created per DB (every time I instantiate that class).

That's my current class (A version with tests is at Code Review):

from sqlmodel import Session, SQLModel, create_engine, select

class DB:
    def __init__(self, url: str, table: SQLModel, *, echo=False):
        """Database wrapper specific to the supplied database table.

        url: URL of the database file.
        table: Model of the table to which the operations should refer.
                Must be a Subclass of SQLModel.
        """
        self.url = url
        self.table = table
        self.engine = create_engine(url, echo=echo)

    def create_metadata(self):
        """Creates metadata, call only once per database connection."""
        SQLModel.metadata.create_all(self.engine)

    def read_all(self):
        """Returns all rows of the table."""
        with Session(self.engine) as session:
            entries = session.exec(select(self.table)).all()
            return entries

    def read(self, _id):
        """Returns a row of the table."""
        with Session(self.engine) as session:
            entry = session.get(self.table, _id)
            return entry

    def add(self, **fields):
        """Adds a row to the table. Fields must map to the table definition."""
        with Session(self.engine) as session:
            entry = self.table(**fields)
            session.add(entry)
            session.commit()

    def update(self, _id, **updates):
        """Updates a row of the table. Updates must map to the table definition."""
        with Session(self.engine) as session:
            entry = self.read(_id)
            for key, val in updates.items():
                setattr(entry, key, val)
            session.add(entry)
            session.commit()

    def delete(self, _id):
        """Delete a row of the table."""
        with Session(self.engine) as session:
            entry = self.read(_id)
            session.delete(entry)
            session.commit()

The use would work like this:

from db import DB
from models import Project, Account

URL = "sqlite:///database.db"

projects = DB(url=URL, table=Project)
accounts = DB(url=URL, table=Account)

projects.read_all()
accounts.read(4)

However, projects and accounts now use different engines. I am not that familiar with ORMs, but I could imagine, that this is undesirable.

Is there a way to only create the engine once and keep the usage of the class the same, while still being able to connect to multiple DBs?

Solution idea (only if the URL stays the same)

I was thinking of something like this, which however only works when the url stays the same for all instances. This however already causes my tests to not pass anymore, since they use a temporary DB which is different for each test function. And if I ever wanted to connect to more than 1 DB, this wouldn't work anymore either.

Maybe there is something more robust than this?

class DB:
    engine = None

    def __init__(self, url: str, table: SQLModel, *, echo=False):
        self.url = url
        self.table = table
        if DB.engine is None:
            DB.engine = create_engine(url, echo=echo)

    def create_metadata(self):
        SQLModel.metadata.create_all(self.engine)

Possible alternative?

The second solution I thought of is this, which however adds an extra step to getting the DB connection. It solves the issues of the solutions above though: I only create one engine per DB, while still being able to connect to multiple DBs. It also works for my tests. It complicates setup though when using the DB wrapper, I now need to keep track of the DB instances AND the engine instance.

class Engine:
    def __init__(self, url, *, echo=False):
        self.url = url
        self.engine = create_engine(url, echo=echo)
    
    def create_metadata(self):
        """Creates metadata, call only once per database connection."""
        SQLModel.metadata.create_all(self.engine)

class DB:
    def __init__(self, table: SQLModel, engine):
        self.table = table
        self.engine = engine.engine

Usage:

from db import Engine, DB
from models import Project, Account

URL = "sqlite:///database.db"
engine = Engine(URL)

projects = DB(table=Project, engine=engine)
accounts = DB(table=Account, engine=engine)

projects.read_all()
accounts.read(4)

Answer 1

The second approach is a perfectly valid solution, see dependency injection.

If you wish, you can take it a step further and "hide" the creation of Engine inside DB using a class attribute.

This however will increase the coupling between the classes.

A way to mitigate that would be to just make Engine a proper singleton class.