Connection management in the backend

[kgodey]

I'm working on:

• Refactor db code to work with existing connections #592
• Schema sqlalchemy engine caching is per-instance #280

I'm thinking through different ideas for managing connections, and here's what I think makes sense:

DDL Operations

Since we're already using alembic, I think we should register all our DDL operations using their operation plugin API: https://alembic.sqlalchemy.org/en/latest/api/operations.html#operation-plugins. Alembic's MigrationContext already manages connections and we can run multiple operations in the same migration context if needed. This means that all our DDL operations will use the same API, which I like.

DML Operations

I think we should set up something similar for DML operations where each operation is registered and needs a context to run. This will making setting up connections much more explicit, and we can cache instances of the DML context as needed.

---

We'd replace most direct uses of engine and connection in our code with these operations.

I'm not sure if this is clear enough, please feel free to ask questions.

[mathemancer]

For DDL operations, I think registering them so they work easily with the alembic MigrationContext makes sense.

I think setting up a context object makes sense, or at least using the connection object as a context object. I'm not sure what context would be useful beyond the engine and connection currently in play, and those are both visible in the connection object. I don't think passing lots of operation-specific parameters via some context blob is a good idea, since it obscures what's actually being passed to / used in a function (kind of the same problem as star imports in a way).

As far as global registration of DML operations, what would be the goal of that? It seems bound to cause namespacing problems (we're already experiencing that a bit). I might be missing how that would help with connection management, but I can't see it at the moment. Keep in mind that the SQLAlchemy Engine class already has pretty good connection pooling.

[kgodey]

I'll start by setting up our DDL operations as alembic operations.

For DML operations, I was thinking about something like this every time we needed to (pseudocode):

context = DMLContext.configure(connection)
operations = DMLOperations(context)
operations.insert_record(blah)
operations.delete_record(blah)

I was thinking that the benefits would be:

• We'd need to be very intentional about what connections we open, which seems like a good thing.
• We'd also be very intentional about what goes in a single transaction vs. not.
• We could inspect all available operations by introspecting operations

I'm not actually sure it's worth the trouble, what do you think?

[mathemancer]

I'm also not sure it's worth the trouble. I do see your point about the inspection and control aspects. Unfortunately, I don't have a good way to decide if it's worth the trouble without just trying it to see how it goes :)

[kgodey]

Okay cool. I'll start implementing DDL using Alembic operations and DML just by passing connection objects around and see how it goes. If I think of anything better, I'll just implement it in the PR.