Data store extensibility

[agronholm]

With APScheduler 4.0 beta looming ahead, I have one major concern: the extensibility of the data store database schemas. Imagine a scheduler running in production where you need to upgrade to a newer version of APScheduler that uses fields that didn't exist in previous versions. While the MongoDB data store could probably cope with this, the SQLAlchemy data store is another matter. Sure, there will be the export/import feature, but that requires downtime as the backing store is erased and the data is subsequently imported. I wouldn't want the 4.1 update to already cause such a major disruption.

With that in mind, I'm thinking of backtracing a little bit on the idea of storing every field in tasks, schedules and jobs directly in the tables. I'm thinking that maybe we only need to directly store the fields that require indexing (like next_fire_time) and the rest could be stored as a dict in a JSON field or something. This could accomodate the data for new features without having to modify the schema. Thoughts?

[cfaaron]

Appreciate all your effort on this library. I do not have strong views, but as you are soliciting them, I think a JSON field or the like for "misc" fields is suitable. However, adding a (nullable) column is one of the least invasive schema changes one can force upon people, so I personally would not make the design decision to always add fields via a JSON dict or the like a strict design goal but rather the starting point when it is not clear how useful a field is to query.

I would recommend avoiding storing pickle formatted data in the database, but you have not mentioned that.

[agronholm]

Would you like to offer an alternative for storing arbitrary argument values then?

[cfaaron]

Apologies, I should have been more specific. I would avoid using database fields storing only a pickle where it is possible to store string keys to pickled values (i.e., prefer dict[str, pickle[object]] to pickle[dict[str, object]] where possible).

[agronholm]

What would that look like as a column definition?

[econeale]

Really looking forward to this update! Thanks for all your work!

I can't say that I have strong opinions either but it's a personal pet peeve of mine to have structured data in a database column. I would rather have a schema check / update type function that could run on application startup to handle migration to a new schema. In FastAPI, for example, I would do something like:

1. Create a context manager that checks that the schema for my job store matches an expected schema
2. If it doesn't, dump the current table; update the table schema; repopulate the table with the old data and default values for the new columns

I realize this would make a fairly sizeable headache for folks implementing APScheulder but I think it makes a cleaner implementation.

[agronholm]

What specific problem do you have with structured data in a database column? It would offer a non-disruptive way to upgrade, unlike the method you just suggested.

[econeale]

Absolutely true. It's a pet peeve more than a technical criticism. Generally speaking I can't fully utilize the features of the database (compression, indexing, whatever) with complex data in a column so I'd rather move that complexity to the application level. But, you make the application more complex.